PageRenderTime 61ms CodeModel.GetById 31ms RepoModel.GetById 0ms app.codeStats 0ms

/CustomDiagnostics/SQL 2005 Perf Stats/SQL_2005_Perf_Stats_Snapshot.sql

#
SQL | 212 lines | 203 code | 8 blank | 1 comment | 0 complexity | aebec07b5dac1a6a8eef226408d68efb MD5 | raw file
Possible License(s): CC-BY-SA-3.0
  1. SET NOCOUNT ON
  2. IF (@@microsoftversion <150997986) BEGIN
  3. PRINT ''
  4. PRINT '**** NOTE ****'
  5. PRINT '**** This script is for SQL Server 2005 sp2 or later. Errors are expected when run on earlier versions.'
  6. PRINT '**************'
  7. PRINT ''
  8. END
  9. ELSE BEGIN
  10. PRINT 'This script captures a one-time snapshot of SQL performance-related info. It is executed '
  11. PRINT 'once at collector startup, and again at shutdown. For the perf stats script that remains '
  12. PRINT 'running and captures regular shapshots of server state, see the output file named '
  13. PRINT '[SERVER]_[INSTANCE]_SQL_2005_Perf_Stats_Startup.OUT'
  14. PRINT ''
  15. END
  16. GO
  17. DECLARE @runtime datetime
  18. DECLARE @cpu_time_start bigint, @cpu_time bigint, @elapsed_time_start bigint, @rowcount bigint
  19. DECLARE @queryduration int, @qrydurationwarnthreshold int
  20. DECLARE @querystarttime datetime
  21. SET @runtime = GETDATE()
  22. SET @qrydurationwarnthreshold = 5000
  23. PRINT ''
  24. PRINT ''
  25. PRINT ''
  26. PRINT 'Start time: ' + CONVERT (varchar, @runtime, 126)
  27. PRINT ''
  28. PRINT '==============================================================================================='
  29. PRINT 'Top N Query Plan Statistics: '
  30. PRINT 'For certain workloads, the sys.dm_exec_query_stats DMV can be a very useful way to identify '
  31. PRINT 'the most expensive queries without a profiler trace. The query output below shows the top 50 '
  32. PRINT 'query plans by CPU, physical reads, and total query execution time. However, be cautious of '
  33. PRINT 'relying on this DMV alone, as it has some sigificant limitations. In particular: '
  34. PRINT ' - This query provides a view of query plans in the procedure cache. However, not every query '
  35. PRINT ' plan will be inserted into the cache. For example, a DBCC DBREINDEX might be an extremely '
  36. PRINT ' expensive operation, but the plan for this query will not be cached, and its execution '
  37. PRINT ' statistics will therefore not be reflected by this query. '
  38. PRINT ' - A plan can be removed from cache at any time. The sys.dm_exec_query_stats DMV can only show '
  39. PRINT ' statistics for plans that are still in cache.'
  40. PRINT ' - The statistics exposed by sys.dm_exec_query_stats are cumulative for the lifetime for the '
  41. PRINT ' query plan, but not all plans in cache have the same lifetime. For example, the query plan '
  42. PRINT ' that is the most expensive right now might not appear to be the most expensive if it has '
  43. PRINT ' only been in cache for a short period. Another query plan that is less expensive over any '
  44. PRINT ' given period of time might seem more expensive because its statistics have been '
  45. PRINT ' accumulating for a longer period. '
  46. PRINT ' - Execution statistics are only recorded in the DMV at the end of query execution. Thge DMV '
  47. PRINT ' may not reflect the execution cost for a long-running query that is still in-progress. '
  48. PRINT ' - sys.dm_exec_query_stats only reflects the cost of query execution. Query compilation, plan '
  49. PRINT ' lookup, and other pre-execution costs are not reflected in statistics.'
  50. PRINT ' - Any query plan that contains inline literals and is not explicitly or implicitly '
  51. PRINT ' parameterized will not be reused. Every execution of this query with different parameter '
  52. PRINT ' values will get a new compiled plan. If a query does not see consistent plan reuse, the '
  53. PRINT ' sys.dm_exec_query_stats DMV will not show the cumulative cost of that query in a single row.'
  54. PRINT ''
  55. PRINT '-- Top N Query Plan Statistics --'
  56. SELECT @cpu_time_start = cpu_time FROM sys.dm_exec_requests WHERE session_id = @@SPID
  57. SET @querystarttime = GETDATE()
  58. SELECT
  59. CONVERT (varchar, @runtime, 126) AS runtime,
  60. LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35) AS cacheobjtype,
  61. p.usecounts, p.size_in_bytes / 1024 AS size_in_kb,
  62. PlanStats.total_worker_time/1000 AS tot_cpu_ms, PlanStats.total_elapsed_time/1000 AS tot_duration_ms,
  63. PlanStats.total_physical_reads, PlanStats.total_logical_writes, PlanStats.total_logical_reads,
  64. PlanStats.CpuRank, PlanStats.PhysicalReadsRank, PlanStats.DurationRank,
  65. LEFT (CASE
  66. WHEN pa.value=32767 THEN 'ResourceDb'
  67. ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname,pa.value))
  68. END, 40) AS dbname,
  69. sql.objectid,
  70. object_name(sql.objectid, sql.dbid)
  71. AS procname,
  72. REPLACE (REPLACE (SUBSTRING (sql.[text], PlanStats.statement_start_offset/2 + 1,
  73. CASE WHEN PlanStats.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), sql.[text]))
  74. ELSE PlanStats.statement_end_offset/2 - PlanStats.statement_start_offset/2 + 1
  75. END), CHAR(13), ' '), CHAR(10), ' ') AS stmt_text
  76. FROM
  77. (
  78. SELECT
  79. stat.plan_handle, statement_start_offset, statement_end_offset,
  80. stat.total_worker_time, stat.total_elapsed_time, stat.total_physical_reads,
  81. stat.total_logical_writes, stat.total_logical_reads,
  82. ROW_NUMBER() OVER (ORDER BY stat.total_worker_time DESC) AS CpuRank,
  83. ROW_NUMBER() OVER (ORDER BY stat.total_physical_reads DESC) AS PhysicalReadsRank,
  84. ROW_NUMBER() OVER (ORDER BY stat.total_elapsed_time DESC) AS DurationRank
  85. FROM sys.dm_exec_query_stats stat
  86. ) AS PlanStats
  87. INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = PlanStats.plan_handle
  88. OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa
  89. OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sql
  90. WHERE (PlanStats.CpuRank < 50 OR PlanStats.PhysicalReadsRank < 50 OR PlanStats.DurationRank < 50)
  91. AND pa.attribute = 'dbid'
  92. ORDER BY tot_cpu_ms DESC
  93. SET @rowcount = @@ROWCOUNT
  94. SET @queryduration = DATEDIFF (ms, @querystarttime, GETDATE())
  95. IF @queryduration > @qrydurationwarnthreshold
  96. BEGIN
  97. SELECT @cpu_time = cpu_time - @cpu_time_start FROM sys.dm_exec_requests WHERE session_id = @@SPID
  98. PRINT ''
  99. PRINT 'DebugPrint: perfstats_snapshot_querystats - ' + CONVERT (varchar, @queryduration) + 'ms, '
  100. + CONVERT (varchar, @cpu_time) + 'ms cpu, '
  101. + 'rowcount=' + CONVERT(varchar, @rowcount)
  102. PRINT ''
  103. END
  104. PRINT ''
  105. PRINT '==============================================================================================='
  106. PRINT 'Missing Indexes: '
  107. PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
  108. PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '
  109. PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '
  110. PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
  111. PRINT ''
  112. PRINT '-- Missing Indexes --'
  113. SELECT CONVERT (varchar, @runtime, 126) AS runtime,
  114. mig.index_group_handle, mid.index_handle,
  115. CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
  116. 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  117. + ' ON ' + mid.statement
  118. + ' (' + ISNULL (mid.equality_columns,'')
  119. + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
  120. + ')'
  121. + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  122. migs.*, mid.database_id, mid.[object_id]
  123. FROM sys.dm_db_missing_index_groups mig
  124. INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
  125. INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
  126. WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
  127. ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
  128. PRINT ''
  129. GO
  130. PRINT ''
  131. PRINT '-- Current database options --'
  132. SELECT LEFT ([name], 128) AS [name],
  133. dbid, cmptlevel,
  134. 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,
  135. LEFT (
  136. 'Status=' + CONVERT (sysname, DATABASEPROPERTYEX ([name],'Status'))
  137. + ', Updateability=' + CONVERT (sysname, DATABASEPROPERTYEX ([name],'Updateability'))
  138. + ', UserAccess=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'UserAccess'))
  139. + ', Recovery=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'Recovery'))
  140. + ', Version=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'Version'))
  141. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoCreateStatistics') = 1 THEN ', IsAutoCreateStatistics' ELSE '' END
  142. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoUpdateStatistics') = 1 THEN ', IsAutoUpdateStatistics' ELSE '' END
  143. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsShutdown') = 1 THEN '' ELSE ', Collation=' + CONVERT (varchar(40), DATABASEPROPERTYEX ([name], 'Collation')) END
  144. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoClose') = 1 THEN ', IsAutoClose' ELSE '' END
  145. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAutoShrink') = 1 THEN ', IsAutoShrink' ELSE '' END
  146. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsInStandby') = 1 THEN ', IsInStandby' ELSE '' END
  147. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsTornPageDetectionEnabled') = 1 THEN ', IsTornPageDetectionEnabled' ELSE '' END
  148. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiNullDefault') = 1 THEN ', IsAnsiNullDefault' ELSE '' END
  149. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiNullsEnabled') = 1 THEN ', IsAnsiNullsEnabled' ELSE '' END
  150. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiPaddingEnabled') = 1 THEN ', IsAnsiPaddingEnabled' ELSE '' END
  151. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsAnsiWarningsEnabled') = 1 THEN ', IsAnsiWarningsEnabled' ELSE '' END
  152. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsArithmeticAbortEnabled') = 1 THEN ', IsArithmeticAbortEnabled' ELSE '' END
  153. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsCloseCursorsOnCommitEnabled') = 1 THEN ', IsCloseCursorsOnCommitEnabled' ELSE '' END
  154. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsFullTextEnabled') = 1 THEN ', IsFullTextEnabled' ELSE '' END
  155. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsLocalCursorsDefault') = 1 THEN ', IsLocalCursorsDefault' ELSE '' END
  156. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsNumericRoundAbortEnabled') = 1 THEN ', IsNumericRoundAbortEnabled' ELSE '' END
  157. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsQuotedIdentifiersEnabled') = 1 THEN ', IsQuotedIdentifiersEnabled' ELSE '' END
  158. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsRecursiveTriggersEnabled') = 1 THEN ', IsRecursiveTriggersEnabled' ELSE '' END
  159. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsMergePublished') = 1 THEN ', IsMergePublished' ELSE '' END
  160. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsPublished') = 1 THEN ', IsPublished' ELSE '' END
  161. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsSubscribed') = 1 THEN ', IsSubscribed' ELSE '' END
  162. + CASE WHEN DATABASEPROPERTYEX ([name], 'IsSyncWithBackup') = 1 THEN ', IsSyncWithBackup' ELSE '' END
  163. , 512) AS status
  164. FROM master.dbo.sysdatabases d
  165. GO
  166. -- Get stats_date for all db's
  167. PRINT ''
  168. PRINT '==== STATS_DATE and rowmodctr for indexes in all databases ===='
  169. EXEC master..sp_MSforeachdb @command1 = '
  170. PRINT ''''
  171. PRINT ''-- STATS_DATE and rowmodctr for [?].sysindexes --''',
  172. @command2 = '
  173. use [?]
  174. select db_id() as dbid,
  175. case
  176. when indid IN (0, 1) then convert (char (12), rows)
  177. else (select rows from [?].dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1)) -- ''-''
  178. end as rowcnt,
  179. case
  180. when indid IN (0, 1) then rowmodctr
  181. else convert (bigint, rowmodctr) + (select rowmodctr from [?].dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
  182. end as row_mods,
  183. case rows when 0 then 0 else convert (bigint,
  184. case
  185. when indid IN (0, 1) then convert (bigint, rowmodctr)
  186. else rowmodctr + (select convert (bigint, rowmodctr) from [?].dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
  187. 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)
  188. end as pct_mod,
  189. convert (nvarchar, u.name + ''.'' + o.name) as objname,
  190. case when i.status&0x800040=0x800040 then ''AUTOSTATS''
  191. when i.status&0x40=0x40 and i.status&0x800000=0 then ''STATS''
  192. else ''INDEX'' end as type,
  193. convert (nvarchar, i.name) as idxname, i.indid,
  194. stats_date (o.id, i.indid) as stats_updated,
  195. case i.status & 0x1000000 when 0 then ''no'' else ''*YES*'' end as norecompute,
  196. o.id as objid , i.status
  197. from [?].dbo.sysobjects o, [?].dbo.sysindexes i, [?].dbo.sysusers u
  198. where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ''U''
  199. order by pct_mod desc, convert (nvarchar, u.name + ''.'' + o.name), indid
  200. '
  201. GO
  202. PRINT 'End time: ' + CONVERT (varchar, GETDATE(), 126)
  203. PRINT 'Done.'
  204. GO