SQL SERVER 占用资源高的SQL语句

最新推荐文章于 2024-06-21 10:14:50 发布

曹振华

最新推荐文章于 2024-06-21 10:14:50 发布

阅读量758

点赞数 2

分类专栏： sql server 文章标签： SQL 资源占用

本文链接：https://blog.csdn.net/chadcao/article/details/103375316

版权

sql server 专栏收录该内容

77 篇文章 0 订阅

订阅专栏

--SQL SERVER 占用资源高的SQL语句：
--查询占用cpu高的前 50 个 SQL 语句
SELECT total_cpu_time,[total_physical_Reads], total_execution_count, number_of_statements, s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(total_physical_reads) AS [total_physical_Reads], 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset, 
            --MAX(statement_end_offset) AS statement_end_offset
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
--------------------------------------------------------------------

--查询物理IO高的前 50 个 SQL 语句
SELECT total_cpu_time, total_physical_Reads , total_execution_count, number_of_statements, s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(total_physical_reads) AS [total_physical_Reads], 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset, 
            --MAX(statement_end_offset) AS statement_end_offset
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_physical_Reads) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
--------------------------------------------------------------------

--查看实时数据库占用资源
SELECT st.text, qp.query_plan, rq.* 
FROM sys.dm_exec_requests RQ CROSS APPLY sys.dm_exec_sql_text(rq.sql_handle) as st 
CROSS APPLY sys.dm_exec_query_plan(rq.plan_handle) as qp order by RQ.CPU_time desc