SQL Server instance slowness

  • 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?

  • 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

  • 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 Williams http://www.linkedin.com/in/jwilliamsoh

  • 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

  • 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 Williams http://www.linkedin.com/in/jwilliamsoh

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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 Williams http://www.linkedin.com/in/jwilliamsoh

  • 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. 😉

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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. 😉

    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

  • 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

  • Grant Fritchey - Thursday, December 14, 2017 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....

    There are many databases on the instance. For which database, should i update statistics/ find missing indexes

  • coolchaitu - Friday, December 15, 2017 2:22 AM

    Grant Fritchey - Thursday, December 14, 2017 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....

    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

  • 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 also a symptom of certain third-party backup products - are you using anything like that?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • coolchaitu - Friday, December 15, 2017 2:18 AM

    How 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