-- Origin: [Repository [BILL_VU\ATE].[SQLClue]]
-- Node: [BILL_VU\ATE|Databases|DBA|StoredProcedures|dbo.GetCPU]
-- Version: [1]
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 **
12 *******************************************************************************
13 ** Change History
14 *******************************************************************************
15 ** Date Author Description of Change
16 **
17 *******************************************************************************/
18 CREATE PROC [dbo].[GetCPU]
19 ( @DBName NVARCHAR (128)
20 , @QueriesToShow INT = 30 )
21 AS
22 BEGIN
23
24 DECLARE @Query [NVARCHAR] (MAX)
25
26 BEGIN TRY
27
28 -- no SQL Injection
29 IF @DBName not in ( SELECT [name]
30 FROM sys.databases)
31 RAISERROR('database not found : %s', 16, 1, @DBName)
32
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
 
 
 
 
 
 
 
 
 
 
 
1
33 SET @Query = 'USE [' + @DBName + '];
34
35 SELECT TOP(@QueriesToShow)
36 p.[name] AS [Procs w/Most CPU cost]
37 , COUNT(*) AS [Plans]
38 , SUM(qs.[total_worker_time]) AS [Total CPU ms]
39 , SUM(qs.[total_worker_time]/qs.[execution_count])/COUNT(*) AS [Avg CPU ms]
40 , MAX(qs.[total_worker_time]/qs.[execution_count]) AS [Worst by Plan]
41 , SUM(qs.[execution_count]) AS [Calls]
42 , MAX(qs.[execution_count]) AS [Most by Plan]
43 , AVG(ISNULL(qs.[execution_count]/DATEDIFF(Second, qs.[cached_time], GETDATE()), 0)) AS [Avg per sec]
44 , SUM(qs.[total_elapsed_time]/qs.[execution_count]) AS [Avg Duration]
45 , MAX(qs.[last_elapsed_time]) AS [Last Duration]
46 , MIN(qs.[cached_time]) AS [First Compile]
47 , MAX(qs.[cached_time]) AS [Last Compile]
 
 
 
 
 
48 FROM sys.procedures AS p
49 INNER JOIN sys.dm_exec_procedure_stats AS qs
50 ON p.[object_id] = qs.[object_id]
51 GROUP BY p.[name]
52 ORDER BY [Total CPU ms] DESC;'
53
54
55 EXEC sp_executesql @Query, N'@QueriesToShow INT', @QueriesToShow
56
57 END TRY
58
59 BEGIN CATCH
60
61 SELECT ERROR_NUMBER() AS [ErrorNumber]
62 , ERROR_SEVERITY() AS [ErrorSeverity]
63 , ERROR_STATE() AS [ErrorState]
64 , ERROR_PROCEDURE() AS [ErrorProcedure]
65 , ERROR_LINE() AS [ErrorLine]
66 , ERROR_MESSAGE() AS [ErrorMessage]
67 , ORIGINAL_LOGIN() AS [OriginalLogin];
68
69 END CATCH
70
71 END
72
73
74 GO
75 ALTER AUTHORIZATION ON [dbo].[GetCPU] TO SCHEMA OWNER
76 GO