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)
