Skip to main content

 none
Waiting queries RRS feed

  • Question

  • Do waiting queries increase CPU load ? How to reduce wait time of waiting queries ? 
    Saturday, October 5, 2019 3:21 PM

Answers

  • >>Yes. We are facing a sudden rise in cpu usage upto 99% and >>>continues in the same state and the server fails to deliver. 

    Does it happen in the middle of work day or at night? 

    ---This first thing to check if CPU is at 100% is to look for parallel queries:


    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Curendra Friday, October 18, 2019 12:07 PM
    Monday, October 7, 2019 4:39 AM
    Answerer

  • But how can we know which query or object each row from sys.query_store_runtime_stats is about?

    In that view there is a plan_id. This links to sys.query_store_plan. In this table there is the query plan in XML form (but stored as nvarchar(MAX)) and a query_id. This links to sys.query_store_query. Here you find a query_text_id and a context_setting_id. The query_text_id links to sys.query_store_query_text and here you find the actual query text. There is a table for the context_setting_id as well, but as such it is not that important, but it is important that the same query (text) can have multiple cache entries depending on different SET options.

    I recommend that you take a look at the definition of the Query Store views in Books Online for more details.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 7, 2019 9:13 PM
  • A LIKE predicate? Yeah, that in the wrong place and have some effects.

    If it comes from a stored procedure, might it be possible for you to change the procedure? The first thing I would try is OPTION (RECOMPILE). That should at least permit for a better plan when there is no leading wildcard.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Friday, October 18, 2019 12:07 PM
    Wednesday, October 16, 2019 9:14 PM
  • Does index rebuild remove planned cache?

    When you rebuild indexes all plans relating to the table in question, will be invalidated, and there will be new compiles. The same applies if you drop or create indexes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Saturday, October 19, 2019 2:16 AM
    Friday, October 18, 2019 7:13 PM

All replies

  • Do waiting queries increase CPU load ?

    Depends what they are waiting for. :-) If there is a query with CXPACKET waits, this means that one or more threads are waiting for other threads to complete. And these threads are consuming CPUs.

    But a thread that is waiting for a lock to be released or for the I/O to complete is not consuming CPU.

    How to reduce wait time of waiting queries ? 

    That depends on what they are waiting for. And what is the root cause to that wait. There is no single simple answer to that question.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 5, 2019 6:41 PM
  • Thank you Erland.

    CXPACKET, SOS_SCHEDULER_YIELD are common waits seen in my db server. How to improve queries suffering from these two waits ? 


    • Edited by Curendra Sunday, October 6, 2019 12:48 AM
    Sunday, October 6, 2019 12:47 AM
    • Edited by SQLZealots Sunday, October 6, 2019 5:12 AM
    Sunday, October 6, 2019 3:23 AM
  • What percentage of CXPACKET, SOS_SCHEDULER_YIELD waits do you see?

    ;WITH Waits AS
    (
      SELECT 
        wait_type,
        wait_time_ms  /1000. AS wait_time_s, 
             100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
             ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
          FROM sys.dm_os_wait_stats
          WHERE wait_type NOT IN (N'CLR_SEMAPHORE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',   N'LAZYWRITER_SLEEP',
            N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
            N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
            N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
            N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
            N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
            N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
            N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
            N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
            N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
            N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP',
    N'ONDEMAND_TASK_QUEUE',N'BROKER_RECEIVE_WAITFOR',N'QDS_ASYNC_QUEUE'
    )
    )
          --filter out additional irrelevant waits
    ,cte1
    AS
    (
    SELECT 
      W1.wait_type,
      CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12,2))AS pct
      ,rn
     --- CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
    FROM Waits AS W1

     --- JOIN Waits AS W2
      ---  ON W2.rn<= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    -----HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
    ) SELECT * FROM 
    ( SELECT wait_type,wait_time_s,pct,SUM(pct) OVER(ORDER BY rn
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty FROM cte1
    ) AS Der WHERE runqty <95
    ORDER BY runqty;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, October 6, 2019 4:35 AM
    Answerer
  • Before I start, an important question: are you actually experience performance problems? I will have to assume you are, but if not, maybe the best is to sit on your hands.

    When you say common what do you mean? Number of waits, or total wait time?

    This is an important distinction for all waits, but it is even more important for SOS_SCHEDULER_YIELD, because any query that runs for some time will have such waits. This is because of the nature of the SQL Server Operating System, which is based on co-operative multitasking. That is, every task yields execution every once in a while (typical every 4 ms), and place itself on the execution queue. If there is a worker thread available, it will resume on the spot. But if there is contention on the CPU, it will actually have to wait. Thus, if you see a high degree of SOS_SCHEDULER_YIELD in terms of time, you have a CPU problem. What do to with it? Usually to analyse and find slow queries in the system and improve their query plan. But a solution can also be to throw hardware on the problem and get more CPUs. (Which also means that you may need to change your licensing.)

    When it comes to CXPACKET waits, there is a way to get rid of them entirely:

    EXEC sp_configure 'max degrees of parallelism', 1
    RECONFIGURE

    However, this can just as well degrade the performance of your system, and quite severely.

    CXPACKET waits always occur when there is parallelism, because there is always a main thread that is waiting for the rest of the threads to complete. Ideally, these threads complete at the same time, but if the work is not partitioned equally between the threads, you can end of with one thread doing all the work while the others are waiting. In this case, you would have been better off with a serial plan. But if you also have lots of SOS_SCHEDULER_YIELD waits, this indicates that most threads are working - but there are not enough of them for the workload, that is, you have CPU pressure.

    If you really want to address the root problem, there is no choice, you will need to analyse your queries, and if you are on SQL 2016 or later, this is a lot easier with Query Store than it was in the past where you had to run traces for some time to gather information.

    There are some configuration options that can help you a little. While setting 'max degrees of parallelism' to 1 may not be the best of ideas, holding it down to 4 or 6 can beneficiary. It is also popular to recommend increase the setting "cost threshold for parallelism" from the default of 5 to something like 25. This means that the optimizer will be less inclined to choose a parallel plan.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, October 6, 2019 9:58 AM
  • >>Thus, if you see a high degree of SOS_SCHEDULER_YIELD in terms of time, you have a CPU >>>problem

    I do not think it is precisely correct , as we need to consider high SOS_SCHEDULER_YIELD with a signal wait time. If the both are high  that means CPU pressure is highly possible , usually as Erland pointed  it requires some query tuning  as query itself probably is the problem

    On the other hand if it comes with the low signal wait time , and that indicates there a lot of threads running  on the scheduler  and all those threads are executing with the same priority.

    ---- Signal Waits above 10-15% is usually a sign of CPU pressure
    SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
           CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
    FROM sys.dm_os_wait_stats OPTION (RECOMPILE);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 6, 2019 11:55 AM
    Answerer
  • I do not think it is precisely correct , as we need to consider high SOS_SCHEDULER_YIELD with a signal wait time. If the both are high  that means CPU pressure is highly possible , usually as Erland pointed  it requires some query tuning  as query itself probably is the problem

    Uri makes a good point here. When a task waits for something, the wait time falls into two parts. First it waits for something to be avaialble, for instance a lock on a resource, and when this something becomes available, it has to wait for a worker thread to run the task. This latter time is reflected as signal_wait_time_ms in sys.dm_os_wait_stats. The column wait_time_ms includes the signal wait time. For the particular wait SOS_SCHEDULER_YIELD, the total wait time and the signal wait time should be more or less the same, since the "something" that is being waited for is a worker thread.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, October 6, 2019 12:13 PM
  • Thank you all for clear explanations.

    Now I can understand the problem well. Now my question is, can we solve waiting and expensive queries by some configuration changes or by rewriting queries? 

    Here execution plan also comes into play. One query may have multiple execution plans. But how to know which plan is better? Even query store does not show that a particular plan is better. Even it is difficult to conclude that the query was regressed due to a execution plan.

    There option to force a plan but how to conclude which is better?

    Sunday, October 6, 2019 12:36 PM
  • >>>But how to know which plan is better? 

    This is all about Query Store to help you choose the better plan. What version you are using?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 6, 2019 12:40 PM
    Answerer
  • I am using sql server 16.
    Sunday, October 6, 2019 12:46 PM
  • Now I can understand the problem well. Now my question is, can we solve waiting and expensive queries by some configuration changes or by rewriting queries? 

    As you may have understood by now, there are no simple answers. Performance tuning is about collecting a large amount of information and then drawing the correct conclusions from the information.

    From experience I can say that there is always things to improve by looking at individual queries, but that does not always mean that this solves the actual problem at hand. There was a post the other day from someone who had
    found the reason for the performance slowdown he experienced was a debug setting for some network drivers. (I had never heard of anything like that before.)

    Here execution plan also comes into play. One query may have multiple execution plans. But how to know which plan is better? Even query store does not show that a particular plan is better.

    Well, you can see from the stats that Query Store collects that some plans burns more CPU than others.

    However, it is not always straightforward. Say that there is a query that sometimes run for one customer, sometimes for about all customers. That query should have different plans for these scenarios - but that may not happen.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, October 6, 2019 1:21 PM
  • >>Even it is difficult to conclude that the query was regressed >>>due to a execution plan.

    Usually regressed query happens when there is a parameter sniffing faced..

    https://littlekendra.com/2016/01/21/query-store-regression-parameter-sniffing/

    https://blogs.technet.microsoft.com/dataplatform/2017/01/31/query-store-how-it-works-how-to-use-it/

    Again, do you see users complain about performance? 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 6, 2019 1:29 PM
    Answerer

  • Again, do you see users complain about performance? 

    Yes. We are facing a sudden rise in cpu usage upto 99% and continues in the same state and the server fails to deliver. This happens once or twice a month. SQL server service restart solves the issue for that time.
    Sunday, October 6, 2019 3:10 PM
  • Yes. We are facing a sudden rise in cpu usage upto 99% and continues in the same state and the server fails to deliver. This happens once or twice a month. SQL server service restart solves the issue for that time.

    So finally, you are telling us what the real problem is!

    This sounds to me like a perfect case for Query Store. Query Store saves data per intervals, default is one hour. So once you get into trouble, you can query sys.query_store_runtime_stats for that hour and see what tops the bill. And you can compare with a previous hour when all was calm and easy.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, October 6, 2019 4:50 PM
  • Thanks Erland.

    But how can we know which query or object each row from sys.query_store_runtime_stats is about?

    Monday, October 7, 2019 3:23 AM
  • >>Yes. We are facing a sudden rise in cpu usage upto 99% and >>>continues in the same state and the server fails to deliver. 

    Does it happen in the middle of work day or at night? 

    ---This first thing to check if CPU is at 100% is to look for parallel queries:


    -- Tasks running in parallel (filtering out MARS requests below):
    select * from sys.dm_os_tasks as t
     where t.session_id in (
       select t1.session_id
        from sys.dm_os_tasks as t1
       group by t1.session_id
      having count(*) > 1
      and min(t1.request_id) = max(t1.request_id));

    -- Requests running in parallel:
     select *
       from sys.dm_exec_requests as r
       join (
               select t1.session_id, min(t1.request_id)
              from sys.dm_os_tasks as t1
             group by t1.session_id
            having count(*) > 1
               and min(t1.request_id) = max(t1.request_id)
          ) as t(session_id, request_id)
         on r.session_id = t.session_id
        and r.request_id = t.request_id;



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Curendra Friday, October 18, 2019 12:07 PM
    Monday, October 7, 2019 4:39 AM
    Answerer
  • Yes. We are facing a sudden rise in cpu usage upto 99% and continues in the same state and the server fails to deliver. This happens once or twice a month. SQL server service restart solves the issue for that time.

    Aha.  Well, that's a very different issue that you started out with.

    But it takes a bunch of blind questions to try to track it down.  There is an art and a science of "SQL Server Performance Tuning" that helps diagnose and fix these kinds of things - which happen all the time.

    With luck you have just one misbehaving query which, if fixed, will let everything else run happily.  Though usually when you fix the one, all that does is make the *second* most expensive query the next problem, LOL.  By the time you've done the top 10 usually people are much happier.

    (until new software releases create a new top 10 to work on … ad infinitum!)

    So you can try stuff blindly, or you can start learning how to practice the art and science of SQL Server Performance Tuning - or you can hire it done.  At least look at the most expensive query reports that SMSS gives you!

    Josh


    • Edited by JRStern Monday, October 7, 2019 4:52 AM
    Monday, October 7, 2019 4:51 AM

  • Does it happen in the middle of work day or at night? 

    It happens in the middle of work, in active hours.
    Monday, October 7, 2019 5:15 AM
  • Great, when it happens again, run the queries I posted above to see what is going on 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 7, 2019 6:30 AM
    Answerer

  • But how can we know which query or object each row from sys.query_store_runtime_stats is about?

    In that view there is a plan_id. This links to sys.query_store_plan. In this table there is the query plan in XML form (but stored as nvarchar(MAX)) and a query_id. This links to sys.query_store_query. Here you find a query_text_id and a context_setting_id. The query_text_id links to sys.query_store_query_text and here you find the actual query text. There is a table for the context_setting_id as well, but as such it is not that important, but it is important that the same query (text) can have multiple cache entries depending on different SET options.

    I recommend that you take a look at the definition of the Query Store views in Books Online for more details.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 7, 2019 9:13 PM
  • I am thankful to everybody.

    I am using all the answers to figure out the things.

    I tried using plan guide for a query. I created template plan guide to use parameterization forced since the query is dynamic query in a procedure. But unfortunately, the query contains like operator and sp_get_query_template gives a template query which does not replace the value in like operator by a parameter.

    Because of this the plan guide didn't work.

    Any solution to this?

    Wednesday, October 16, 2019 4:40 AM
  • A LIKE predicate? Yeah, that in the wrong place and have some effects.

    If it comes from a stored procedure, might it be possible for you to change the procedure? The first thing I would try is OPTION (RECOMPILE). That should at least permit for a better plan when there is no leading wildcard.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Friday, October 18, 2019 12:07 PM
    Wednesday, October 16, 2019 9:14 PM
  • Does index rebuild remove planned cache?
    Friday, October 18, 2019 12:12 PM
  • Does index rebuild remove planned cache?

    When you rebuild indexes all plans relating to the table in question, will be invalidated, and there will be new compiles. The same applies if you drop or create indexes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Saturday, October 19, 2019 2:16 AM
    Friday, October 18, 2019 7:13 PM
  • In the production db server we have been running a job to rebuild indexes every night. The indexes fragmented above 30% are rebuilt and others are reorganized.

    The db server is performing badly with high cpu and waiting queries, is the above case the reason of this?

    Saturday, October 19, 2019 2:21 AM
  • In the production db server we have been running a job to rebuild indexes every night. The indexes fragmented above 30% are rebuilt and others are reorganized.

    The db server is performing badly with high cpu and waiting queries, is the above case the reason of this?

    That is not very likely. It can be disputed whether you actually should spend resources on defragmenting indexes these days with modern hardware, and my MVP colleage Tibor Karaszi has a couple blog posts about this, starting with this one: http://sqlblog.karaszi.com/does-index-fragmentation-matter/

    But even if you stop defragmenting your indexes, you still need to update your statistics, and that will also trigger recompilation.

    Overall, to be able to say why a system experience bad performance, one needs to know a lot of details, in many cases more that is even possible to cover in a forum thread. And in many casees, there are no simple answers like "press the the go-faster button". There are simply so many ways to make a database system go slow that developers are able sneak in more than one of them.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 19, 2019 8:55 AM
  • Is there a query to see the performance history, for example performance of last week or so?

    • Edited by Curendra Tuesday, November 5, 2019 6:10 AM
    Tuesday, November 5, 2019 6:09 AM
  • Is there a query to see the performance history, for example performance of last week or so?

    This answer is coming late, as I have been detached from the forums because of a trip to the US and the recovery of the resulting jet lag when coming om.

    But, yes, there are possibilities to see this on SQL 2016 or later if  you enable Query Store. Query Store saves execution data for queries by run-time intervals, default interval length is one hour.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, November 16, 2019 8:32 PM