During the preconference of SQLSaturday Holland #666, the MCM (Microsoft Certified Master) Uwe Ricken gave the day-long session “Analyze and Tune SQL Server Like A Boss!”. It’s a popular event that is often sold out, much to Uwe’s surprise. Performance seems to be an ever popular subject, attracting both newcomers and seasoned veterans of the field. I told him that it is often a passion-subject, and also open ended: you will never know if you’ll hear a new trick, unsupported traceflag or VMware setting that can help you in your troubleshooting quest. Also, we get access to his script repository, so we can peek over his shoulder and see what he sees.
Uwe explains traceflag 1117 with cups of water
His presentation style is amusing, concise and well paced.
Just to share one of his scripts, based on an idea of Glenn Berry, the CPU consumption of each database and the percentage:
USE master;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
WITH DB_CPU_Stats
AS
(
SELECT DatabaseID,
DB_NAME(DatabaseID) AS [Database Name],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
(
SELECT CAST(value AS INT) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N’dbid’
) AS F_DB
GROUP BY DatabaseID
)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
[Database Name], [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_Stats
WHERE DatabaseID <> 32767 — ResourceDB
ORDER BY
[CPU Rank] OPTION (RECOMPILE);
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
