none
Application jobs are taking more time than expected and DB server CPU utilization is high RRS feed

  • Question

  • Application performance is degraded from 18-Nov-2019. We suspected windows patching ,but even after uninstalling the patches also issue remains.


    Kiran


    • Edited by juniorkiran Sunday, December 8, 2019 12:37 PM html
    Sunday, December 8, 2019 12:37 PM

All replies

  • What does the query below return? BTW, do you have Query Store enabled?

    ;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, December 8, 2019 12:42 PM
    Answerer
  • Application performance is degraded from 18-Nov-2019. We suspected windows patching ,but even after uninstalling the patches also issue remains.

    The Windows patching may be related, but uninstalling is not going to help. Rather the reason is likely to be that because SQL Server was restarted, all plans fell out of the cache, and new plans were created and parameter-dependent plans were built on the first invocation. These first calls might have been atypical, but it may also be that statistics were out of date at the time.

    As Uri suggests, having Query Store in place is great in such situation, because it gives you the opportunity to pinpoint the regressed queries.


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

    Sunday, December 8, 2019 1:16 PM
  • Please find below output,

     

    wait_type

    wait_time_s

    pct

    runqty

    BACKUPIO

    1058673.46

    21.82

    21.82

    BACKUPTHREAD

    1043726.58

    21.51

    43.33

    CXCONSUMER

    937019.4

    19.31

    62.64

    CXPACKET

    697218.08

    14.37

    77.01

    OLEDB

    263895.61

    5.44

    82.45

    SOS_SCHEDULER_YIELD

    162268.68

    3.34

    85.79

    MSQL_DQ

    144382.33

    2.98

    88.77

    MSQL_XP

    133785.54

    2.76

    91.53

    PREEMPTIVE_COM_QUERYINTERFACE

    127249.88

    2.62

    94.15


    Kiran

    Tuesday, December 10, 2019 4:21 PM
  • Hi juniorkiran,

     

    According to your output, CXPACKET value is a bit high, which may cause high CPU. I suggest you reduce the value of maxdop: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15

     

    If you have very small number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a larger value. For example, you can set the MAXDOP value to 16.

     

    If you a have very large number of queries that are executing at the same time compared with the number of processors, you can set the MAXDOP value to a smaller value. For example, you can set the MAXDOP value to 4.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 11, 2019 9:39 AM
  • I do not agree that CXPACKET  is high, what is SQL Server version? Edition? I would recommend running Brent's SP dbo.sp_BlitzFirst @ExpertMode =1 and see what is going on

    https://www.brentozar.com/askbrent/

    What do you see?


    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

    Wednesday, December 11, 2019 9:45 AM
    Answerer
  • SQL Server version: Microsoft SQL Server 2017 (RTM-CU11) – 14.0.3038.14

    Kiran

    Wednesday, December 11, 2019 3:26 PM
  • We dropped maxdop from 3 to 2

    Kiran

    Friday, December 13, 2019 12:05 PM
  • We have raised this question due to unexpected performance degrade after applying specific patches, performance has not been improved after removing those patches, we are trying to find root cause and solution.

    Query store will definitely help us to find expensive queries and tune those. But we are looking for root cause and solution of performance degradation with the same code.


    Kiran

    Monday, December 23, 2019 7:52 AM
  • >>But we are looking for root cause and solution of performance degradation with >>>the same cod

    Actually expensive queries might really root cause fro performance degradation 


    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, December 23, 2019 8:02 AM
    Answerer
  • Query store will definitely help us to find expensive queries and tune those. But we are looking for root cause and solution of performance degradation with the same code.

    With the amount of information you have shared here, no one will be able to tell. In fact, even if you have engaged any of us as consultants and we had devoted full time to your case, we might not have been able to tell. To be able to tell with certainty why a degradation happened, it is often essential to have knowledge about the situation before the regression. And this is why we have been nagging about Query Store - this will put you into a lot better position for next time when this happens. But for this particular incident, it is not likely that you will ever find the exact answer.

    But all that said, I don't find it an unlikely reason that the fact that you restarted SQL Server, and thereby triggered recompilation of all queries that previously were in the cache, caused some queries to get a less optimal plan than before.


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

    Monday, December 23, 2019 10:34 AM