SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time]
,SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
sys.dm_exec_query_stats :是在msdb Database 中的动态管理视图,返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。在从缓存删除计划时,也将从该视图中删除对应行。
total_worker_time :此计划自编译以来执行所用的 CPU 时间总量(微秒)。
execution_count: 计划自上次编译以来所执行的次数。
statement_start_offset :指示行所说明的查询在其批查询或持久化对象文本中的开始位置(字节,以 0 开头)。statement_end_offset :指示行所说明的查询在其批查询或持久化对象文本中的结束位置(字节,以 0 开头)。值 -1 指示批查询的结尾处。
sys.dm_exec_sql_text(@sql_handle) : msdb Databaes 中的系统表值函数,返回由指定的 sql_handle 标识的 SQL 批处理的文本。该表值函数将替换系统函数 fn_get_sql.
@sql_handle : 要查找的批处理的 SQL 句柄。@sql_handle 的数据类型为 varbinary(64)。可以从以下动态管理对象中获得
- sys.dm_exec_query_stats
- sys.dm_exec_requests
- sys.dm_exec_cursors
- sys.dm_exec_xml_handles
- sys.dm_exec_query_memory_grants
返回 dbid,objectid,number (对于带编号的存储过程,此列返回存储过程的编号),encrypted(bit),text( nvarchar(max) 以加密对象返回NULL
什么是sql句柄:对于批处理,SQL 句柄是基于 SQL 文本的哈希值。对于诸如存储过程、触发器或函数之类的数据库对象,SQL 句柄派生自数据库 ID、对象 ID 和对象编号。
-
几个相关的查询
-
批处理执行统计信息
- SELECT s2.dbid,
-
s1.sql_handle,(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,( (CASE WHEN statement_end_offset = -1THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,execution_count,plan_generation_num,last_execution_time,total_worker_time,last_worker_time,min_worker_time,max_worker_time,total_physical_reads,last_physical_reads,min_physical_reads,max_physical_reads,total_logical_writes,last_logical_writes,min_logical_writes,max_logical_writesFROM sys.dm_exec_query_stats AS s1CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2WHERE s2.objectid is nullORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
-
SELECT sp.name AS [Name], 'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''')
+ ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']'
+ '/StoredProcedure[@Name=' + quotename(sp.name,'''')
+ ' and @Schema='
+ quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' AS [Urn]
, SCHEMA_NAME(sp.schema_id) AS [Schema]
, CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL
THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
, CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType]
, sp.create_date AS [CreateDate]
FROM sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(CAST( case when sp.is_ms_shipped = 1 then 1
when ( select major_id
from sys.extended_properties
where major_id = sp.object_id
and minor_id = 0
and class = 1
and name = N'microsoft_database_tools_support') is not null
then 1 else 0 end AS bit)=1)
ORDER BY [Schema] ASC,[Name] ASC