本文共 2116 字，大约阅读时间需要 7 分钟。
ALTER TABLE [dbo].[PRIMARYCODE] DROP CONSTRAINT [PK__PRIMARYC__AA1D437813BCEBC1]
GOALTER TABLE [dbo].[PRIMARYCODE] ADD PRIMARY KEY CLUSTERED
( [CODE] ASC ) --平均耗CPU最多的前个SQL （SQL SERVER 2005或以上版本）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
5 平均耗CPU最多的前个SQL （SQL SERVER 2008或以上版本）
复制代码
SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)], max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC 复制代码6 总耗CPU最多的前个SQL （SQL SERVER 2008或以上版本）
SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY total_worker_time DESC转载地址：http://bzrli.baihongyu.com/