Example SQL Server 2008(R2) Snapshot Performance Statistics

Description:
Example to show the SQL Server 2008(R2) Performance Statistics as a snapshot,
Check the example below:

------------------------------------------------------------------------------------------------------

SET NOCOUNT ON
IF (CHARINDEX ('10.0', @@VERSION) = 0) BEGIN
  PRINT ''
  PRINT '**** NOTE ****'
  PRINT '**** This script is for SQL Server 2008.  Errors are expected when run on earlier versions.'
  PRINT '**************'
  PRINT ''
END
ELSE BEGIN
  PRINT 'This script captures a one-time snapshot of SQL performance-related info. It is executed '
  PRINT 'once at collector startup, and again at shutdown. For the perf stats script that remains '
  PRINT 'running and captures regular shapshots of server state, see the output file named '
  PRINT '[SERVER]_[INSTANCE]_SQL_2008_Perf_Stats_Startup.OUT'
  PRINT ''
END
GO
DECLARE @runtime datetime 
DECLARE @cpu_time_start bigint, @cpu_time bigint, @elapsed_time_start bigint, @rowcount bigint
DECLARE @queryduration int, @qrydurationwarnthreshold int
DECLARE @querystarttime datetime
SET @runtime = GETDATE()
SET @qrydurationwarnthreshold = 5000

PRINT ''
PRINT ''
PRINT ''
PRINT 'Start time: ' + CONVERT (varchar, @runtime, 126)
PRINT ''
PRINT '==============================================================================================='
PRINT 'Top N Query Plan Statistics: '
PRINT 'For certain workloads, the sys.dm_exec_query_stats DMV can be a very useful way to identify '
PRINT 'the most expensive queries without a profiler trace. The query output below shows the top 50 '
PRINT 'query plans by CPU, physical reads, and total query execution time. However, be cautious of '
PRINT 'relying on this DMV alone, as it has some sigificant limitations. In particular: '
PRINT ' - This query provides a view of query plans in the procedure cache. However, not every query '
PRINT '   plan will be inserted into the cache. For example, a DBCC DBREINDEX might be an extremely '
PRINT '   expensive operation, but the plan for this query will not be cached, and its execution '
PRINT '   statistics will therefore not be reflected by this query. '
PRINT ' - A plan can be removed from cache at any time. The sys.dm_exec_query_stats DMV can only show ' 
PRINT '   statistics for plans that are still in cache.'
PRINT ' - The statistics exposed by sys.dm_exec_query_stats are cumulative for the lifetime for the '
PRINT '   query plan, but not all plans in cache have the same lifetime. For example, the query plan '
PRINT '   that is the most expensive right now might not appear to be the most expensive if it has '
PRINT '   only been in cache for a short period. Another query plan that is less expensive over any '
PRINT '   given period of time might seem more expensive because its statistics have been '
PRINT '   accumulating for a longer period. '
PRINT ' - Execution statistics are only recorded in the DMV at the end of query execution. Thge DMV '
PRINT '   may not reflect the execution cost for a long-running query that is still in-progress. ' 
PRINT ' - sys.dm_exec_query_stats only reflects the cost of query execution. Query compilation, plan ' 
PRINT '   lookup, and other pre-execution costs are not reflected in statistics.' 
PRINT ' - Any query plan that contains inline literals and is not explicitly or implicitly ' 
PRINT '   parameterized will not be reused. Every execution of this query with different parameter '
PRINT '   values will get a new compiled plan. If a query does not see consistent plan reuse, the '
PRINT '   sys.dm_exec_query_stats DMV will not show the cumulative cost of that query in a single row.'
PRINT ''
PRINT '-- Top N Query Plan Statistics --'
SELECT @cpu_time_start = cpu_time FROM sys.dm_exec_requests WHERE session_id = @@SPID
SET @querystarttime = GETDATE()
SELECT 
  CONVERT (varchar, @runtime, 126) AS runtime, 
  LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35) AS cacheobjtype,
  p.usecounts, p.size_in_bytes / 1024 AS size_in_kb, 
  PlanStats.total_worker_time/1000 AS tot_cpu_ms, PlanStats.total_elapsed_time/1000 AS tot_duration_ms, 
  PlanStats.total_physical_reads, PlanStats.total_logical_writes, PlanStats.total_logical_reads,
  PlanStats.CpuRank, PlanStats.PhysicalReadsRank, PlanStats.DurationRank, 
  LEFT (CASE 
    WHEN pa.value=32767 THEN 'ResourceDb' 
    ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname,pa.value))
  END, 40) AS dbname,
  sql.objectid, 
  OBJECT_NAME(sql.objectid,sql.dbid) AS procname, 
  REPLACE (REPLACE (SUBSTRING (sql.[text], PlanStats.statement_start_offset/2 + 1, 
      CASE WHEN PlanStats.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), sql.[text])) 
        ELSE PlanStats.statement_end_offset/2 - PlanStats.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' ') AS stmt_text 
FROM 
(
  SELECT 
    stat.plan_handle, statement_start_offset, statement_end_offset, 
    stat.total_worker_time, stat.total_elapsed_time, stat.total_physical_reads, 
    stat.total_logical_writes, stat.total_logical_reads, 
    ROW_NUMBER() OVER (ORDER BY stat.total_worker_time DESC) AS CpuRank, 
    ROW_NUMBER() OVER (ORDER BY stat.total_physical_reads DESC) AS PhysicalReadsRank, 
    ROW_NUMBER() OVER (ORDER BY stat.total_elapsed_time DESC) AS DurationRank 
  FROM sys.dm_exec_query_stats stat 
) AS PlanStats 
INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = PlanStats.plan_handle 
OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa 
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sql
WHERE (PlanStats.CpuRank < 50 OR PlanStats.PhysicalReadsRank < 50 OR PlanStats.DurationRank < 50)
  AND pa.attribute = 'dbid' 
ORDER BY tot_cpu_ms DESC

SET @rowcount = @@ROWCOUNT
SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
IF @queryduration > @qrydurationwarnthreshold
BEGIN
  SELECT @cpu_time = cpu_time - @cpu_time_start FROM sys.dm_exec_requests WHERE session_id = @@SPID
  PRINT ''
  PRINT 'DebugPrint: perfstats_snapshot_querystats - ' + CONVERT (varchar, @queryduration) + 'ms, ' 
    + CONVERT (varchar, @cpu_time) + 'ms cpu, '
    + 'rowcount=' + CONVERT(varchar, @rowcount) 
  PRINT ''
END


PRINT ''
PRINT '==============================================================================================='
PRINT 'Missing Indexes: '
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
PRINT 'be seen if the index was created.  This is a unitless number, and has meaning only relative '
PRINT 'the same number for other indexes.  The measure is a combination of the avg_total_user_cost, '
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
PRINT ''
PRINT '-- Missing Indexes --'
SELECT CONVERT (varchar, @runtime, 126) AS runtime, 
  mig.index_group_handle, mid.index_handle, 
  CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure, 
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
PRINT ''
GO

PRINT ''
PRINT '-- Current database options --'
SELECT LEFT ([name], 128) AS [name], 
  dbid, cmptlevel, 
  CONVERT (int, (SELECT SUM (CONVERT (bigint, [size])) * 8192 / 1024 / 1024 FROM master.dbo.sysaltfiles f WHERE f.dbid = d.dbid)) AS db_size_in_mb, 
  LEFT (
  'Status=' + CONVERT (sysname, DATABASEPROPERTYEX ([name],'Status')) 
  + ', Updateability=' + CONVERT (sysname, DATABASEPROPERTYEX ([name],'Updateability')) 
  + ', UserAccess=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'UserAccess')) 
  + ', Recovery=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'Recovery')) 
  + ', Version=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'Version')) 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoCreateStatistics') = 1 THEN ', IsAutoCreateStatistics' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoUpdateStatistics') = 1 THEN ', IsAutoUpdateStatistics' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsShutdown') = 1 THEN '' ELSE ', Collation=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'Collation'))  END
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoClose') = 1 THEN ', IsAutoClose' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoShrink') = 1 THEN ', IsAutoShrink' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsInStandby') = 1 THEN ', IsInStandby' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsTornPageDetectionEnabled') = 1 THEN ', IsTornPageDetectionEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiNullDefault') = 1 THEN ', IsAnsiNullDefault' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiNullsEnabled') = 1 THEN ', IsAnsiNullsEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiPaddingEnabled') = 1 THEN ', IsAnsiPaddingEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiWarningsEnabled') = 1 THEN ', IsAnsiWarningsEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsArithmeticAbortEnabled') = 1 THEN ', IsArithmeticAbortEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsCloseCursorsOnCommitEnabled') = 1 THEN ', IsCloseCursorsOnCommitEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsFullTextEnabled') = 1 THEN ', IsFullTextEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsLocalCursorsDefault') = 1 THEN ', IsLocalCursorsDefault' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsNumericRoundAbortEnabled') = 1 THEN ', IsNumericRoundAbortEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsQuotedIdentifiersEnabled') = 1 THEN ', IsQuotedIdentifiersEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsRecursiveTriggersEnabled') = 1 THEN ', IsRecursiveTriggersEnabled' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsMergePublished') = 1 THEN ', IsMergePublished' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsPublished') = 1 THEN ', IsPublished' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsSubscribed') = 1 THEN ', IsSubscribed' ELSE '' END 
  + CASE WHEN DATABASEPROPERTYEX ([name], 'IsSyncWithBackup') = 1 THEN ', IsSyncWithBackup' ELSE '' END
  , 512) AS status
FROM master.dbo.sysdatabases d
GO

-- Get stats_date for all db's
PRINT ''
PRINT '==== STATS_DATE and rowmodctr for indexes in all databases ===='
EXEC master..sp_MSforeachdb @command1 = '
PRINT ''''
PRINT ''-- STATS_DATE and rowmodctr for [?].sysindexes --''', 
  @command2 = '
use [?]
select db_id() as dbid, 
  case 
    when indid IN (0, 1) then convert (char (12), rows)
    else (select rows from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1)) -- ''-''
  end as rowcnt, 
  case 
    when indid IN (0, 1) then rowmodctr
    else convert (bigint, rowmodctr) + (select rowmodctr from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))
  end as row_mods, 
  case rows when 0 then 0 else convert (bigint, 
    case 
      when indid IN (0, 1) then convert (bigint, rowmodctr)
      else rowmodctr + (select convert (bigint, rowmodctr) from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))
    end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from [?].dbo.sysindexes i2 where i2.id =  i.id and i2.indid in (0,1))) * 100) 
  end as pct_mod, 
  convert (nvarchar, u.name + ''.'' + o.name) as objname, 
  case when i.status&0x800040=0x800040 then ''AUTOSTATS''
    when i.status&0x40=0x40 and i.status&0x800000=0 then ''STATS''
    else ''INDEX'' end as type, 
  convert (nvarchar, i.name) as idxname, i.indid, 
  stats_date (o.id, i.indid) as stats_updated, 
  case i.status & 0x1000000 when 0 then ''no'' else ''*YES*'' end as norecompute, 
  o.id as objid , rowcnt, i.status 
from [?].dbo.sysobjects o, [?].dbo.sysindexes i, [?].dbo.sysusers u 
where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ''U''
order by pct_mod desc, convert (nvarchar, u.name + ''.'' + o.name), indid
'
GO

PRINT 'End time: ' + CONVERT (varchar, GETDATE(), 126)
PRINT 'Done.'
GO

print 'getting resource governor info'
print '=========================================='
go
print 'sys.resource_governor_configuration'
select * from sys.resource_governor_configuration
go
print 'sys.resource_governor_resource_pools'
select * from sys.resource_governor_resource_pools
go
print 'sys.resource_governor_workload_groups'
select * from sys.resource_governor_workload_groups
go




print '--sys.dm_database_encryption_keys  Transparent Database Encryption (TDE) information'
select DB_NAME(database_id) as 'database_name', * from sys.dm_database_encryption_keys 
go
print '-- sys.dm_os_loaded_modules '
select * from sys.dm_os_loaded_modules

go
print '--sys.dm_server_audit_status'
select * from sys.dm_server_audit_status

go

print '--top 10 CPU consuming procedures '
SELECT TOP 10 d.object_id, d.database_id, db_name(database_id) 'db name', object_name (object_id, database_id) 'proc name',  d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], d.last_elapsed_time, d.execution_count
from sys.dm_exec_procedure_stats d
ORDER BY [total_worker_time] DESC;
GO


print '--top 10 CPU consuming triggers '
SELECT TOP 10 d.object_id, d.database_id, db_name(database_id) 'db name', object_name (object_id, database_id) 'proc name',  d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], d.last_elapsed_time, d.execution_count
from sys.dm_exec_trigger_stats d
ORDER BY [total_worker_time] DESC;
GO





print '-- query and plan hash capture --'
print '-- top 10 CPU by query_hash --'
SELECT TOP 10 query_hash, COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',
	 sum(execution_count) as 'execution_count', 
	 sum(total_worker_time) as 'total_worker_time',
	 SUM(total_elapsed_time) as 'total_elapsed_time',
	 SUM (total_logical_reads) as 'total_logical_reads',
	 
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1, 
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) 
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_hash
ORDER BY sum(total_worker_time) DESC;

go


print '-- top 10 logical reads by query_hash --'
SELECT TOP 10 query_hash, 
	COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',
	sum(execution_count) as 'execution_count', 
	 sum(total_worker_time) as 'total_worker_time',
	 SUM(total_elapsed_time) as 'total_elapsed_time',
	 SUM (total_logical_reads) as 'total_logical_reads',
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1, 
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) 
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_hash
ORDER BY sum(total_logical_reads) DESC;

go

print '-- top 10 elapsed time by query_hash --'
SELECT TOP 10 query_hash, 
	sum(execution_count) as 'execution_count', 
	COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',
	 sum(total_worker_time) as 'total_worker_time',
	 SUM(total_elapsed_time) as 'total_elapsed_time',
	 SUM (total_logical_reads) as 'total_logical_reads',
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1, 
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) 
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_hash
ORDER BY sum(total_elapsed_time) DESC;

go

print '-- top 10 CPU by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash, query_hash, 
COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',
sum(execution_count) as 'execution_count', 
	 sum(total_worker_time) as 'total_worker_time',
	 SUM(total_elapsed_time) as 'total_elapsed_time',
	 SUM (total_logical_reads) as 'total_logical_reads',
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1, 
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) 
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_plan_hash, query_hash
ORDER BY sum(total_worker_time) DESC;

go


print '-- top 10 logical reads by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash, query_hash, sum(execution_count) as 'execution_count', 
	 sum(total_worker_time) as 'total_worker_time',
	 SUM(total_elapsed_time) as 'total_elapsed_time',
	 SUM (total_logical_reads) as 'total_logical_reads',
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1, 
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) 
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_plan_hash, query_hash
ORDER BY sum(total_logical_reads) DESC;

go


print '-- top 10 elapsed time  by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash, query_hash, sum(execution_count) as 'execution_count', 
	 sum(total_worker_time) as 'total_worker_time',
	 SUM(total_elapsed_time) as 'total_elapsed_time',
	 SUM (total_logical_reads) as 'total_logical_reads',
    max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1, 
      CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text])) 
        ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
      END), CHAR(13), ' '), CHAR(10), ' '))  AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_plan_hash, query_hash
ORDER BY sum(total_elapsed_time) DESC;