Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH DB_CPU_Stats
- AS
- (
- SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
- 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 [row_num],
- DatabaseName,
- [CPU_Time_Ms],
- CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
- FROM DB_CPU_Stats
- --WHERE DatabaseID > 4 -- system databases
- --AND DatabaseID <> 32767 -- ResourceDB
- ORDER BY row_num OPTION (RECOMPILE);
- DECLARE @total INT
- SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)
- join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
- SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'
- FROM sys.sysprocesses sp (NOLOCK)
- JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid
- --WHERE sp.status = 'runnable'
- GROUP BY sb.name
- ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc
- ;WITH cte AS
- (
- SELECT stat.[sql_handle],
- stat.statement_start_offset,
- stat.statement_end_offset,
- COUNT(*) AS [NumExecutionPlans],
- SUM(stat.execution_count) AS [TotalExecutions],
- ((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS [AvgLogicalReads],
- ((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS [AvgCPU]
- FROM sys.dm_exec_query_stats stat
- GROUP BY stat.[sql_handle], stat.statement_start_offset, stat.statement_end_offset
- )
- SELECT CONVERT(DECIMAL(15, 5), cte.AvgCPU) AS [AvgCPU],
- CONVERT(DECIMAL(15, 5), cte.AvgLogicalReads) AS [AvgLogicalReads],
- cte.NumExecutionPlans,
- cte.TotalExecutions,
- DB_NAME(txt.[dbid]) AS [DatabaseName],
- OBJECT_NAME(txt.objectid, txt.[dbid]) AS [ObjectName],
- SUBSTRING(txt.[text], (cte.statement_start_offset / 2) + 1,
- (
- (CASE cte.statement_end_offset
- WHEN -1 THEN DATALENGTH(txt.[text])
- ELSE cte.statement_end_offset
- END - cte.statement_start_offset) / 2
- ) + 1
- )
- FROM cte
- CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
- ORDER BY cte.AvgCPU DESC;
- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- GO
- WITH DB_CPU_Stats
- AS
- (
- SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName],
- SUM(total_worker_time) AS [CPU_Time_Ms],
- SUM(total_logical_reads) AS [Logical_Reads],
- SUM(total_logical_writes) AS [Logical_Writes],
- SUM(total_logical_reads+total_logical_writes) AS [Logical_IO],
- SUM(total_physical_reads) AS [Physical_Reads],
- SUM(total_elapsed_time) AS [Duration_MicroSec],
- SUM(total_clr_time) AS [CLR_Time_MicroSec],
- SUM(total_rows) AS [Rows_Returned],
- SUM(execution_count) AS [Execution_Count],
- count(*) 'Plan_Count'
- 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 [Rank_CPU],
- DatabaseName,
- [CPU_Time_Hr] = convert(decimal(15,2),([CPU_Time_Ms]/1000.0)/3600) ,
- CAST([CPU_Time_Ms] * 1.0 / SUM(case [CPU_Time_Ms] when 0 then 1 else [CPU_Time_Ms] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU_Percent],
- [Duration_Hr] = convert(decimal(15,2),([Duration_MicroSec]/1000000.0)/3600) ,
- CAST([Duration_MicroSec] * 1.0 / SUM(case [Duration_MicroSec] when 0 then 1 else [Duration_MicroSec] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration_Percent],
- [Logical_Reads],
- CAST([Logical_Reads] * 1.0 / SUM(case [Logical_Reads] when 0 then 1 else [Logical_Reads] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Reads_Percent],
- [Rows_Returned],
- CAST([Rows_Returned] * 1.0 / SUM(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows_Returned_Percent],
- [Reads_Per_Row_Returned] = [Logical_Reads]/(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end),
- [Execution_Count],
- CAST([Execution_Count] * 1.0 / SUM(case [Execution_Count] when 0 then 1 else [Execution_Count] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution_Count_Percent],
- [Physical_Reads],
- CAST([Physical_Reads] * 1.0 / SUM(case [Physical_Reads] when 0 then 1 else [Physical_Reads] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal_Reads_Percent],
- [Logical_Writes],
- CAST([Logical_Writes] * 1.0 / SUM(case [Logical_Writes] when 0 then 1 else [Logical_Writes] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Writes_Percent],
- [Logical_IO],
- CAST([Logical_IO] * 1.0 / SUM(case [Logical_IO] when 0 then 1 else [Logical_IO] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_IO_Percent],
- [CLR_Time_MicroSec],
- CAST([CLR_Time_MicroSec] * 1.0 / SUM(case [CLR_Time_MicroSec] when 0 then 1 else [CLR_Time_MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR_Time_Percent],
- [CPU_Time_Ms],[CPU_Time_Ms]/1000 [CPU_Time_Sec],
- [Duration_MicroSec],[Duration_MicroSec]/1000000 [Duration_Sec]
- FROM DB_CPU_Stats
- WHERE DatabaseID > 4 -- system databases
- AND DatabaseID <> 32767 -- ResourceDB
- ORDER BY [Rank_CPU] OPTION (RECOMPILE);
- WITH DB_CPU_Stats
- AS
- (
- SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName],
- SUM(total_worker_time) AS [CPU Time Ms],
- SUM(total_logical_reads) AS [Logical Reads],
- SUM(total_logical_writes) AS [Logical Writes],
- SUM(total_logical_reads+total_logical_writes) AS [Logical IO],
- SUM(total_physical_reads) AS [Physical Reads],
- SUM(total_elapsed_time) AS [Duration MicroSec],
- SUM(total_clr_time) AS [CLR Time MicroSec],
- SUM(total_rows) AS [Rows Returned],
- SUM(execution_count) AS [Execution Count],
- count(*) 'Plan Count'
- 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 [Rank CPU],
- DatabaseName,
- [CPU Time Hr] = convert(decimal(15,2),([CPU Time Ms]/1000.0)/3600) ,
- CAST([CPU Time Ms] * 1.0 / SUM([CPU Time Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent],
- [Duration Hr] = convert(decimal(15,2),([Duration MicroSec]/1000000.0)/3600) ,
- CAST([Duration MicroSec] * 1.0 / SUM([Duration MicroSec]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration Percent],
- [Logical Reads],
- CAST([Logical Reads] * 1.0 / SUM([Logical Reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical Reads Percent],
- [Rows Returned],
- CAST([Rows Returned] * 1.0 / SUM([Rows Returned]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows Returned Percent],
- [Reads Per Row Returned] = [Logical Reads]/[Rows Returned],
- [Execution Count],
- CAST([Execution Count] * 1.0 / SUM([Execution Count]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution Count Percent],
- [Physical Reads],
- CAST([Physical Reads] * 1.0 / SUM([Physical Reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal Reads Percent],
- [Logical Writes],
- CAST([Logical Writes] * 1.0 / SUM([Logical Writes]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical Writes Percent],
- [Logical IO],
- CAST([Logical IO] * 1.0 / SUM([Logical IO]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical IO Percent],
- [CLR Time MicroSec],
- CAST([CLR Time MicroSec] * 1.0 / SUM(case [CLR Time MicroSec] when 0 then 1 else [CLR Time MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR Time Percent],
- [CPU Time Ms],[CPU Time Ms]/1000 [CPU Time Sec],
- [Duration MicroSec],[Duration MicroSec]/1000000 [Duration Sec]
- FROM DB_CPU_Stats
- --WHERE DatabaseID > 4 -- system databases
- --AND DatabaseID <> 32767 -- ResourceDB
- ORDER BY [Rank CPU] OPTION (RECOMPILE);
Add Comment
Please, Sign In to add comment