SQL SERVER – Who is consuming CPU on my SQL Server?

The topics around performance tuning is always interesting and I get quizzed by a number of you during conferences on these topic areas post my presentation. A relatively simple yet a powerful question was asked by one of the attendee in my session at MODS (Mobile Developer Conference) this year. I was walking about databases in the web world and how one needs to preserve, protect and optimize for web based workloads. I was fortunate to have a packed crowd but couldn’t take all the questions on stage during the session. As it always happens, there is a mob that follows me outside and the session’s second innings happens there for close to an hour. These are conversations to remember and many bring back questions that I need to work on.

This blog got inspired by one such question one of the gentleman asked at this corridor conversation couple of weeks back. He said, they had a server which was consolidated from 10’s of servers into a monolithic big machine. The consolidation activity went well and everything was running absolutely fine till one fine day the server was not responsive. This resulted in a number of applications suffering to connect to SQL Server.

They were wondering if the consolidation exercise was worth all the pain on first place? When they were on separate servers they exactly knew which server / application was creating the problem and the fix was easy. Now they have about 60+ database on this one server and they are not able to find the cause for CPU spike for an elongated period of time.

So the question was – how can I find out what are the databases that consumed maximum time on my CPU relative to other databases? Do I know which are the consumers of my CPU on a given server? The initial instinct was to search SQLAuthority and I was surprised I haven’t written about this simple need. This is my first shot for this requirement using DMVs:

WITH DB_CPU_Statistics
AS
(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms] FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS
APPLY (SELECT CONVERT(INT, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent] FROM DB_CPU_Statistics
WHERE DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPU Ranking] OPTION (RECOMPILE);

SQL SERVER - Who is consuming CPU on my SQL Server? cpu-consumers-01

The result for me on a freshly restarted SQL Server looks like this. Do let me know does this query make any sense on your actual environments? Did you ever need such a requirement in your troubleshooting of SQL Server? What have you been doing to get to this information in the past? Please let me know via your comments as it would be really helpful for all to know the other methods.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – What is Semantics Search Inside SQL Server?
Next Post
SQL SERVER – Adding Datetime and Time Values Using Variables

Related Posts

4 Comments. Leave new

  • Luis Rodríguez
    October 29, 2015 10:13 am

    Do yo hace a similar query forma memory?

    Reply
  • I normal use below command to get the maximum cpu consumed time.

    SELECT * FROM SYSPROCESSES
    ORDER BY CPU

    Reply
  • I often find it helpful to limit the history it’s searching through when looking at history like this… like adding this to your CTE query:
    AND qs.last_execution_time >= DATEADD(MI, -5, GETDATE())

    Reply
  • Edward Pochinski
    December 7, 2015 9:08 pm

    This will sort by the Avg CPU consumer and show a plethora of other useful information regarding each query. Then what I did was built a SQLPS wrapper to run and pipe to csv files. The wrapper prompts me for a servername to fetch the data from.

    select top 25 getdate() as logtime, rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no
    , (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1
    , creation_time
    , last_execution_time
    , (total_worker_time+0.0)/1000 as total_worker_time
    , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
    , total_logical_reads as [LogicalReads]
    , total_logical_writes as [logicalWrites]
    , execution_count
    , total_logical_reads+total_logical_writes as [AggIO]
    , (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
    , case when sql_handle IS NULL
    then ‘ ‘
    else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end – qs.statement_start_offset) /2 ) )
    end as query_text
    , db_name(st.dbid) as database_name
    , st.objectid as object_id
    from sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(sql_handle) st
    where total_worker_time > 0
    order by AvgCPUTime desc

    Reply

Leave a Reply

Menu