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);
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)
4 Comments. Leave new
Do yo hace a similar query forma memory?
I normal use below command to get the maximum cpu consumed time.
SELECT * FROM SYSPROCESSES
ORDER BY CPU
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())
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