Transactions/s trending upward causing the CPU to trend upward. RRS feed

  • Question

  • Hi,

    I'm investigating the performance issue on one of our SQL Server where the rate of transactions/s has suddenly increased significantly within last one week. This is causing the CPU to trend upward and we are running out of time to handle this situation. So far the CPU has increased from 20-30% (Our Normal Baseline) to above 55% and moving up daily. 

    I have been struggling to pinpoint the root cause so far. I have checked for the following:

    1. No Index scanning found. 
    2. No changes to the server or databases. 
    3. Two databases showing higher CPU/Wait time with one of them I suspect is causing this issue. 


    I would appreciate if someone can guide me to the right directions.

    Thursday, October 24, 2019 3:08 AM

All replies

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

    What version you are using? Since SQL Sevrer 2016 you can define query store tool to see what queries consume resources

    Best Regards,Uri Dimant SQL Server MVP,

    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

    Thursday, October 24, 2019 4:22 AM
  • Could this not be something which you are not aware ? Why do you think transactions/sec increase is causing high CPU. Did you made any changes to anything on SQL Server in last one week ?



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles


    Thursday, October 24, 2019 7:11 AM
  • Hi Uri,

    I'm using SQL Server 2017

    I ran the query above but there are no results which means there is no parallel tasks. 


    Here's are the screenshots of the Query Store, just an fyi both databases has been Failed over since yesterday to the secondary replica. We are using Basic AG. 

    Also since we failed over the CPU usage on Primary is now at 1-5%only and the CPU usage on Secondary is now fluctuating between 25-45%. This confirms that one of these 2 dbs are the problematic ones. 



    and this is the query:

        1 AS [C1], 
        [Extent1].[LB_Id] AS [LB_Id], 
        [Extent1].[Usr_Id] AS [Usr_Id], 
        [Extent1].[Dpnt] AS [Dpnt], 
        [Extent1].[Pts] AS [Pts], 
        [Extent2].[DptId] AS [DptId], 
        [Extent2].[FName] AS [FName], 
        [Extent2].[LName] AS [LName], 
        CASE WHEN ([Extent3].[Id] IS NULL) THEN [Extent2].[Avt] ELSE [Extent3].[DpPath] END AS [C2]
        FROM   [dbo].[LB_Mem] AS [Extent1]
        INNER JOIN [dbo].[Usr] AS [Extent2] ON [Extent1].[Usr_Id] = [Extent2].[Id]
        LEFT OUTER JOIN [dbo].[Stg_Loc] AS [Extent3] ON [Extent2].[AvtStgLoc_Id] = [Extent3].[Id]
        WHERE ([Extent1].[LB_Id] IN (cast('g5e24sd2-h2h2-5460-76bf-bffcdahsn60' as uniqueidentifier))) AND ([Extent2].[IsDeleted] <> 1)

    • Edited by Shaddy_1 Thursday, October 24, 2019 3:58 PM
    Thursday, October 24, 2019 3:01 PM
  • Hi Shashank,

    It is maybe my suspicion and I'm not saying Transactions rate/s is the absolute cause. It is our doubt as the CPU spike coincide with transactions rate/s spike and the ratio seems to be growing parallel. 

    As mentioned in the original post no changes for the past one week on Server/Databases. 

    Thursday, October 24, 2019 3:05 PM
  • Um, if the transaction rate is increasing that *sounds* like your application is just doing more work, so you expect the CPU number to go up as well.

    You can try to nail down the specific queries involved and then optimize whatever they're doing, but if the workload goes up, you may just need a bigger server.

    If you're seeing this on the secondary server, is that also smaller hardware, fewer cores, anything like that?


    • Edited by JRStern Thursday, October 24, 2019 7:17 PM
    Thursday, October 24, 2019 7:17 PM
  • DPA generates reports for Top SQL's and Top Waits for a Server, you could generate the report and find out the top queries/waits/databases. And from these reports you can drill down to the level of what DB, type of wait and the query.  

    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Thursday, October 24, 2019 7:30 PM