question

narendba avatar image
narendba asked

CPU trends in DB level report by script in sql server 2014

CPU trends in DB level report by script in sql server 2014. Any script for finding the DB level CPU usage report for couple of days. Example: We have 10 DB's in server and need to know about CPU usage for each and individual database by script.
sqlserversql server 2014script
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Sagar Bhargava avatar image
Sagar Bhargava answered
Hi, Here is something to get you started. You can schedule a job to run this and capture over a period of time to report. -- Get CPU utilization by database (CPU Usage by Database) -- Helps determine which database is using the most CPU resources on the instance 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 CONVERT(INT, value) 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] 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_Stats WHERE DatabaseID 32767 ORDER BY [CPU Rank] OPTION ( RECOMPILE ); Hope this helps.
7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I've adjusted the answer so the script is properly indented / formatted. This should help with the syntax errors.
2 Likes 2 ·
narendba avatar image narendba commented ·
Hi Thanks for sharing the Script, But I got the below error after execution. I have seen Red color lines syntactical alerts on CPU_DB_STATS related columns in New Query Window. Error: Msg 102, Level 15, State 1, Line 7 Incorrect syntax near '1.0'.
0 Likes 0 ·
Sagar Bhargava avatar image Sagar Bhargava commented ·
There should be a '*' just before 1.0 and before 100.0
0 Likes 0 ·
narendba avatar image narendba commented ·
DB_CPU_Stats not a valid object or not available Error.
0 Likes 0 ·
narendba avatar image narendba commented ·
I got it thanks lot. But in my laptop I have checked the percentage of CPU column shows 95% on Master database. But CPU is at 10%. But why it's showing 95% on Master database.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.