SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server instance slowness


SQL Server instance slowness

Author
Message
coolchaitu
coolchaitu
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7447 Visits: 1520

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?


Sue_H
Sue_H
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62755 Visits: 13325
coolchaitu - Thursday, December 14, 2017 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?



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



Jeffery Williams
Jeffery Williams
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4920 Visits: 1054
Sue_H - Thursday, December 14, 2017 6:55 AM
coolchaitu - Thursday, December 14, 2017 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?



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

Could also be contention. Have you run profiler? Checked for deadlocks? Missing indexes?


<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williamshttp://www.linkedin.com/in/jwilliamsoh
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)SSC Guru (339K reputation)

Group: General Forum Members
Points: 339694 Visits: 33994
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeffery Williams
Jeffery Williams
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4920 Visits: 1054
Jeffery Williams - Thursday, December 14, 2017 6:59 AM
Sue_H - Thursday, December 14, 2017 6:55 AM
coolchaitu - Thursday, December 14, 2017 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?



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

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 Williamshttp://www.linkedin.com/in/jwilliamsoh
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816364 Visits: 46260
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeffery Williams
Jeffery Williams
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4920 Visits: 1054
Jeff Moden - Thursday, December 14, 2017 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.

There's the man. How you be Jeff?


<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williamshttp://www.linkedin.com/in/jwilliamsoh
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)SSC Guru (816K reputation)

Group: General Forum Members
Points: 816364 Visits: 46260
Jeffery Williams - Thursday, December 14, 2017 8:24 AM
Jeff Moden - Thursday, December 14, 2017 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.

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. Wink


--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeffery Williams
Jeffery Williams
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4920 Visits: 1054
Jeff Moden - Thursday, December 14, 2017 11:06 AM
Jeffery Williams - Thursday, December 14, 2017 8:24 AM
Jeff Moden - Thursday, December 14, 2017 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.

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. Wink

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 Williamshttp://www.linkedin.com/in/jwilliamsoh
coolchaitu
coolchaitu
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7447 Visits: 1520
Jeffery Williams - Thursday, December 14, 2017 7:10 AM
Jeffery Williams - Thursday, December 14, 2017 6:59 AM
Sue_H - Thursday, December 14, 2017 6:55 AM
coolchaitu - Thursday, December 14, 2017 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?



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

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search