找出cpu占用率高的sql语句

最近一直在弄数据库数据传输问题，每天传输的数据大概在40W-50W条，经常SQL的服务占用了cpu内存消耗在1.7G，还好，数据服务器都是8-16核。自己在网上搜到一条自己添加点的元素代码测试数据库的性能和记录很不错：

SELECT     TOP 10 rank() OVER (ORDER BY (total_worker_time + 0.0) / execution_count DESC, sql_handle, statement_start_offset) AS row_no, (rank()
OVER (ORDER BY (total_worker_time + 0.0) / execution_count DESC, sql_handle, statement_start_offset)) % 2 AS l1, creation_time, last_execution_time,
(total_worker_time + 0.0) / 1000 AS total_worker_time, (total_worker_time + 0.0) / (execution_count * 1000) AS [AvgCPUTime],
total_logical_reads AS [LogicalReads], total_logical_writes AS [LogicalWrites], execution_count, total_logical_reads + total_logical_writes AS [AggIO],
(total_logical_reads + total_logical_writes) / (execution_count + 0.0) AS [AvgIO], CASE WHEN sql_handle IS NULL THEN '' ELSE (substring(st.text,
(qs.statement_start_offset + 2) / 2, (CASE WHEN qs.statement_end_offset = - 1 THEN len(CONVERT(nvarchar(MAX), st.text))
* 2 ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2)) END AS query_text, db_name(st.dbid) AS db_name, st.objectid AS object_id
FROM         sys.dm_exec_query_stats qs CROSS apply sys.dm_exec_sql_text(sql_handle) st
WHERE     total_worker_time > 0
ORDER BY [AvgCPUTime] DESC

---

作者：LiFuyun
出处：http://lifuyun.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。