-- Origin: [SQLInstance]
-- Node: [BILL_VU\ATE|Databases|DBA|StoredProcedures|dbo.GetCPU]
-- Version: [n/a]
1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5
6 /******************************************************************************
7 ** Auth: Bill Wunder
8 ** Date: Nov 5, 2009
9 **
10 ** Desc: display CPU info
11 ** http://blogs.msdn.com/joesack/archive/2009/04/08/checking-for-cpu-pressure-via-sys-dm-os-schedulers.aspx
12 ** common signs of CPU pressure include:
13 ** * Sustained "% Processor Time" values (>75% for extended periods)
14 ** * High signal wait time percentage from sys.dm_os_wait_stats
15 ** * SOS_SCHEDULER_YIELD waits
16 ** http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx
17 ** if Signal Waits are a significant percentage of total waits, you have
18 ** CPU pressure which may be alleviated by faster or more CPUs.
19 ** Alternately, CPU pressure can be reduced by eliminating unnecessary
20 ** sorts (indexes can avoid sorts in order & group by’s) and joins, and
21 ** compilations (and re-compilations). If Signal Waits are not significant,
22 ** a faster CPU will not appreciably improve performance.
23 **
24 *******************************************************************************
25 ** Change History
26 *******************************************************************************
27 ** Date Author Description of Change
28 **
29 *******************************************************************************/
30 CREATE PROC [dbo].[GetCPU]
31 ( @DBName NVARCHAR (128)
32 , @QueriesToShow INT = 30 )
33 AS
34 BEGIN
35
36 DECLARE @Query [NVARCHAR] (MAX)
37
38 BEGIN TRY
39
40 -- no SQL Injection
41 IF @DBName not in ( SELECT [name]
42 FROM sys.databases)
43 RAISERROR('database not found : %s', 16, 1, @DBName)
44
45 -- CPU pressure
46 SELECT CASE WHEN SUM(s.runnable_tasks_count) > 0
47 THEN CASE
48 WHEN CAST(100.0 * SUM(w.[signal_wait_time_ms]) / SUM(w.[wait_time_ms]) AS [NUMERIC] (20,2)) > 40
49 THEN 'Severe! CHECK %Processor Now!'
50 WHEN CAST(100.0 * SUM(w.[signal_wait_time_ms]) / SUM(w.[wait_time_ms]) AS [NUMERIC] (20,2)) > 30
51 THEN 'High! CHECK %Processor Now!'
52 WHEN CAST(100.0 * SUM(w.[signal_wait_time_ms]) / SUM(w.[wait_time_ms]) AS [NUMERIC] (20,2)) > 20
53 THEN 'Moderate, Monitor %Processor! '
54 ELSE 'Light, Monitor %Processor.'
55 END
56 ELSE 'None detected' END AS [CPU_Pressure]
57 , SUM(w.[signal_wait_time_ms])/1000.0 AS [TotRunnableSeconds]
58 , CAST(100.0 * SUM(w.[signal_wait_time_ms]) / SUM(w.[wait_time_ms]) AS [NUMERIC] (20,2)) AS [RunnablePct]
59 , SUM(w.[wait_time_ms]/1000.0 - w.[signal_wait_time_ms]/1000.0) AS [ResourceWaitSeconds]
60 , CAST(100.0 * SUM(w.[wait_time_ms] - w.[signal_wait_time_ms]) / SUM(w.[wait_time_ms]) AS [NUMERIC] (20,2)) [ResourceWaitsPct]
61 , SUM(s.[current_tasks_count]) AS [TaskCount]
62 , SUM(s.[runnable_tasks_count]) AS [WaitingTasks]
63 , SUM(s.[pending_disk_io_count]) AS [PendingDiskIO]
64 FROM sys.dm_os_wait_stats w
65 CROSS JOIN sys.dm_os_schedulers s
66 WHERE s.[scheduler_id] < 255 -- exclude DAC
67
68 SELECT TOP 5 qt.[text] AS [Top(5)AllDatabases-TotalCPU]
69 , qs.[total_worker_time]/1000000.0 AS [TotCPUSeconds]
70 , qs.[total_worker_time]/1000000.0/qs.[execution_count] AS [AvgCPUSeconds]
71 , qs.[execution_count] AS [ExecCount]
72 , ISNULL(qs.[execution_count]/DATEDIFF(Second, qs.[creation_time], GetDate()), 0) AS [ExecPerSecond]
73 , ISNULL(qs.[total_elapsed_time]/1000000.0/qs.[execution_count], 0) AS [AvgElpasedSeconds]
74 , qs.[max_logical_reads] AS [MaxLReads]
75 , qs.[max_logical_writes] AS [MaxLWrites]
76 FROM sys.dm_exec_query_stats AS qs
77 CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
78 ORDER BY qs.[total_worker_time] DESC
79
80 SET @Query = 'USE [' + @DBName + '];
81
82 SELECT TOP(@QueriesToShow)
83 p.[name] AS [Top ' + CAST(@QueriesToShow AS [NVARCHAR] (10)) + ' ' + @DBName + 'Procs-TotalCPUSeconds]
84 , COUNT(*) AS [PlanCount]
85 , SUM(qs.[total_worker_time])/1000000.0 AS [TotalCPUSeconds]
86 , SUM(qs.[total_worker_time]/1000000.0/qs.[execution_count])/COUNT(*) AS [AvgCPUSeconds]
87 , MAX(qs.[total_worker_time]/1000000.0/qs.[execution_count]) AS [MostCPUSeconds]
88 , SUM(qs.[execution_count]) AS [ExecCount]
89 , MAX(qs.[execution_count]) AS [MostExec]
90 , ISNULL( SUM(qs.[execution_count])/DATEDIFF( Second
91 , MIN(qs.[cached_time])
92 , GETDATE())
93 , 0) AS [AvgExecPerSecond]
94 , SUM(qs.[total_elapsed_time])/1000000.0 AS [TotalElapsedSeconds]
95 , SUM(qs.[total_elapsed_time]/1000000.0/qs.[execution_count]) AS [AvgElapsedSeconds]
96 , MAX(qs.[max_elapsed_time])/1000000.0 as [MaxElapsedSeconds]
97 , MAX(qs.[last_elapsed_time])/1000000.0 AS [LastElapsedSeconds]
98 , MIN(qs.[cached_time]) AS [FirstCompile]
99 , MAX(qs.[cached_time]) AS [LastCompile]
100 FROM sys.procedures AS p
101 INNER JOIN sys.dm_exec_procedure_stats AS qs
102 ON p.[object_id] = qs.[object_id]
103 GROUP BY p.[name]
104 ORDER BY [TotalCPUSeconds] DESC;'
105
106
107 EXEC sp_executesql @Query, N'@QueriesToShow INT', @QueriesToShow
108
109 END TRY
110
111 BEGIN CATCH
112
113 SELECT ERROR_NUMBER() AS [ErrorNumber]
114 , ERROR_SEVERITY() AS [ErrorSeverity]
115 , ERROR_STATE() AS [ErrorState]
116 , ERROR_PROCEDURE() AS [ErrorProcedure]
117 , ERROR_LINE() AS [ErrorLine]
118 , ERROR_MESSAGE() AS [ErrorMessage]
119 , ORIGINAL_LOGIN() AS [OriginalLogin];
120
121 END CATCH
122
123 END
124
125
126 GO
127 ALTER AUTHORIZATION ON [dbo].[GetCPU] TO SCHEMA OWNER
128 GO