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.
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.