December 14, 2017 at 6:06 am
Good Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
December 14, 2017 at 6:55 am
coolchaitu - Thursday, December 14, 2017 6:06 AMGood Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
It's an indicator of disk latency which can be a problem with the I/O subsystem or it could be configuration issues with things such using compressed drives, not excluding database files from antivirus scans, it could be other processes depending on your I/O subsystem configuration and how it's used, if you are on a VM how the client is configured and the types of disks being used. In other words, it's telling you have some disk latency but not necessarily the cause of the latency.
You'd want to look things such as what database files does this happen with - tempdb can mean contention issues, inappropriate use of tempdb, does it happen with certain databases only, does it happen with certain drives only, does it happen at certain times or some pattern so when those are logged (for example whenever index maintenance is done or always at 4 am when a data load occurs), etc.
After checking configurations and making sure all the drivers , you would want to do some monitoring of some of the I/O metrics. The following articles go over different things to monitor, what performance counters to capture, configurations to check.
Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency
I/O requests taking longer than 15 seconds to complete on file
Sue
December 14, 2017 at 6:59 am
Sue_H - Thursday, December 14, 2017 6:55 AMcoolchaitu - Thursday, December 14, 2017 6:06 AMGood Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
It's an indicator of disk latency which can be a problem with the I/O subsystem or it could be configuration issues with things such using compressed drives, not excluding database files from antivirus scans, it could be other processes depending on your I/O subsystem configuration and how it's used, if you are on a VM how the client is configured and the types of disks being used. In other words, it's telling you have some disk latency but not necessarily the cause of the latency.
You'd want to look things such as what database files does this happen with - tempdb can mean contention issues, inappropriate use of tempdb, does it happen with certain databases only, does it happen with certain drives only, does it happen at certain times or some pattern so when those are logged (for example whenever index maintenance is done or always at 4 am when a data load occurs), etc.
After checking configurations and making sure all the drivers , you would want to do some monitoring of some of the I/O metrics. The following articles go over different things to monitor, what performance counters to capture, configurations to check.
Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency
I/O requests taking longer than 15 seconds to complete on fileSue
Could also be contention. Have you run profiler? Checked for deadlocks? Missing indexes?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 14, 2017 at 7:00 am
It could be badly written queries. It could be out of date statistics leading to poor execution plans. It could be incorrectly configured indexes. It could be....
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2017 at 7:10 am
Jeffery Williams - Thursday, December 14, 2017 6:59 AMSue_H - Thursday, December 14, 2017 6:55 AMcoolchaitu - Thursday, December 14, 2017 6:06 AMGood Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
It's an indicator of disk latency which can be a problem with the I/O subsystem or it could be configuration issues with things such using compressed drives, not excluding database files from antivirus scans, it could be other processes depending on your I/O subsystem configuration and how it's used, if you are on a VM how the client is configured and the types of disks being used. In other words, it's telling you have some disk latency but not necessarily the cause of the latency.
You'd want to look things such as what database files does this happen with - tempdb can mean contention issues, inappropriate use of tempdb, does it happen with certain databases only, does it happen with certain drives only, does it happen at certain times or some pattern so when those are logged (for example whenever index maintenance is done or always at 4 am when a data load occurs), etc.
After checking configurations and making sure all the drivers , you would want to do some monitoring of some of the I/O metrics. The following articles go over different things to monitor, what performance counters to capture, configurations to check.
Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency
I/O requests taking longer than 15 seconds to complete on fileSue
Could also be contention. Have you run profiler? Checked for deadlocks? Missing indexes?
I was going to say the same but someone came into my office and could not finish. So yes refresh statistics. I would check contention first because you can see those right... Then I would look at queries. Do you have a bunch of triggers? Are you using cursors? It could be so many things. Profiler is going to give you a heads up. Then if possible some of your larger or more complex stored procedures run them manually and tick actual execution plan. Get your eyes on what each step is doing and what cost factor those steps are relative to the rest of the query. Do you see index seeks or scans. You do NOT want to be scanning ever and if you are scanning a huge index that can be death to performance.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 14, 2017 at 8:14 am
Having gone through it and the crushing affects that we eventually ran into on our production boxes, my first step is now to check if the connection strings are MARS enabled. The documentation, for example, for Entity Framework states that it's off by default. I have anecdotal evidence that it actually defaults to on. MARS is for very special, tightly controlled, very well written specialty code. Since most people's code meets none of those requirements, you need to make sure that it's turned off.
Also anecdotally, all of those 15 second warnings seem to have simply disappeared after we turned off MARS on all connection strings for all applications.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2017 at 8:24 am
Jeff Moden - Thursday, December 14, 2017 8:14 AMHaving gone through it and the crushing affects that we eventually ran into on our production boxes, my first step is now to check if the connection strings are MARS enabled. The documentation, for example, for Entity Framework states that it's off by default. I have anecdotal evidence that it actually defaults to on. MARS is for very special, tightly controlled, very well written specialty code. Since most people's code meets none of those requirements, you need to make sure that it's turned off.Also anecdotally, all of those 15 second warnings seem to have simply disappeared after we turned off MARS on all connection strings for all applications.
There's the man. How you be Jeff?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 14, 2017 at 11:06 am
Jeffery Williams - Thursday, December 14, 2017 8:24 AMJeff Moden - Thursday, December 14, 2017 8:14 AMHaving gone through it and the crushing affects that we eventually ran into on our production boxes, my first step is now to check if the connection strings are MARS enabled. The documentation, for example, for Entity Framework states that it's off by default. I have anecdotal evidence that it actually defaults to on. MARS is for very special, tightly controlled, very well written specialty code. Since most people's code meets none of those requirements, you need to make sure that it's turned off.Also anecdotally, all of those 15 second warnings seem to have simply disappeared after we turned off MARS on all connection strings for all applications.
There's the man. How you be Jeff?
I'm doing fine. Thank you for asking. You'll find me thanking for every morning because it means that I'm still on this side of the dirt. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 14, 2017 at 11:14 am
Jeff Moden - Thursday, December 14, 2017 11:06 AMJeffery Williams - Thursday, December 14, 2017 8:24 AMJeff Moden - Thursday, December 14, 2017 8:14 AMHaving gone through it and the crushing affects that we eventually ran into on our production boxes, my first step is now to check if the connection strings are MARS enabled. The documentation, for example, for Entity Framework states that it's off by default. I have anecdotal evidence that it actually defaults to on. MARS is for very special, tightly controlled, very well written specialty code. Since most people's code meets none of those requirements, you need to make sure that it's turned off.Also anecdotally, all of those 15 second warnings seem to have simply disappeared after we turned off MARS on all connection strings for all applications.
There's the man. How you be Jeff?
I'm doing fine. Thank you for asking. You'll find me thanking for every morning because it means that I'm still on this side of the dirt. 😉
isn't that the truth! And with our current leadership that is something to keep in mind each day; just sayin.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 15, 2017 at 2:18 am
Jeffery Williams - Thursday, December 14, 2017 7:10 AMJeffery Williams - Thursday, December 14, 2017 6:59 AMSue_H - Thursday, December 14, 2017 6:55 AMcoolchaitu - Thursday, December 14, 2017 6:06 AMGood Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
It's an indicator of disk latency which can be a problem with the I/O subsystem or it could be configuration issues with things such using compressed drives, not excluding database files from antivirus scans, it could be other processes depending on your I/O subsystem configuration and how it's used, if you are on a VM how the client is configured and the types of disks being used. In other words, it's telling you have some disk latency but not necessarily the cause of the latency.
You'd want to look things such as what database files does this happen with - tempdb can mean contention issues, inappropriate use of tempdb, does it happen with certain databases only, does it happen with certain drives only, does it happen at certain times or some pattern so when those are logged (for example whenever index maintenance is done or always at 4 am when a data load occurs), etc.
After checking configurations and making sure all the drivers , you would want to do some monitoring of some of the I/O metrics. The following articles go over different things to monitor, what performance counters to capture, configurations to check.
Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency
I/O requests taking longer than 15 seconds to complete on fileSue
Could also be contention. Have you run profiler? Checked for deadlocks? Missing indexes?
I was going to say the same but someone came into my office and could not finish. So yes refresh statistics. I would check contention first because you can see those right... Then I would look at queries. Do you have a bunch of triggers? Are you using cursors? It could be so many things. Profiler is going to give you a heads up. Then if possible some of your larger or more complex stored procedures run them manually and tick actual execution plan. Get your eyes on what each step is doing and what cost factor those steps are relative to the rest of the query. Do you see index seeks or scans. You do NOT want to be scanning ever and if you are scanning a huge index that can be death to performance.
How do i find queries ? Please share the script
December 15, 2017 at 2:22 am
Grant Fritchey - Thursday, December 14, 2017 7:00 AMIt could be badly written queries. It could be out of date statistics leading to poor execution plans. It could be incorrectly configured indexes. It could be....
There are many databases on the instance. For which database, should i update statistics/ find missing indexes
December 15, 2017 at 2:47 am
coolchaitu - Friday, December 15, 2017 2:22 AMGrant Fritchey - Thursday, December 14, 2017 7:00 AMIt could be badly written queries. It could be out of date statistics leading to poor execution plans. It could be incorrectly configured indexes. It could be....There are many databases on the instance. For which database, should i update statistics/ find missing indexes
Are you monitoring the queries on the system? If so, identify the ones running slow and figure out which ones to tune, update statistics etc. If you're not monitoring query performance metrics, start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2017 at 4:14 am
coolchaitu - Thursday, December 14, 2017 6:06 AMGood Morning Experts,
Application users have reported slowness. We checked SQL Server CPU and its fine. We added some memory. We are seeing the below messages in ERRORLOG:
SQL Server has encountered 4 occurences of I/O requests taking longer than 15 seconds to complete...
Isnt this a strong indicator of I/O subsystem problem?
It's also a symptom of certain third-party backup products - are you using anything like that?
Thomas Rushton
blog: https://thelonedba.wordpress.com
December 16, 2017 at 3:28 am
coolchaitu - Friday, December 15, 2017 2:18 AMHow do i find queries ? Please share the script
You can use this for Blocking code
-------------------------------------------------
SELECT
dm_qp.query_plan,
dm_es.program_name,
dm_ws.wait_duration_ms,
dm_ws.session_ID,
dm_ws.blocking_session_id,
dm_ws.wait_type,
dm_es.status,
--dm_t.TEXT,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_r.wait_resource,
dm_es.login_name,
dm_r.command
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
GO
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply