sql server查询占用系统资源高的sql语句
程序员文章站
2022-07-05 19:19:06
在系统资源占用比较高的时候,需要分析优化sql语句,对其增加索引或者优化查询方法等。我们需要先查询到占用资源较高的语句,用如下sql搞定...
--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
推荐阅读
-
SQL Server:基于WEB的数据库查询
-
利用SQL Server 2008中的SSIS进行大规模的数据库查询操作
-
sql 查询本年、本月、本日记录的语句,附SQL日期函数
-
Sql语句与存储过程查询数据的性能测试实现代码
-
T-SQL查询进阶—理解SQL Server中的锁
-
SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
-
SQL Server 数据库管理常用的SQL和T-SQL语句
-
sql server中select语句需要申请的锁
-
Sql Server 索引使用情况及优化的相关Sql语句分享
-
oracle查询当前运行的进程、查询被锁的对象、杀Seesion的SQL、设置SQL语句计时等教程