原 MSSQL中的耗费性能的SQL查询

发布日期 2023年11月24日 · 已更新 2024年5月15日  

0    279    1

Tags： 原创MSSQLSQL Server查找SQL抓取耗费性能

👉 本文共约339个字，系统预计阅读时间或需2分钟。

显示/隐藏全文目录

目录

• 耗费CPU的历史SQL
• 按平均 CPU 时间获取有关前五个查询的信息
• 按 CPU 对现有请求进行排序
• 正在运行的SQL统计
• 最耗费CPU的正在运行的SQL
• 最耗费磁盘IO的正在运行的SQL
• 最耗费内存的正在运行的SQL
• 提供批处理执行统计信息
• 占用tempdb的SQL
• 查询存储

耗费CPU的历史SQL

按平均 CPU 时间获取有关前五个查询的信息

以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。

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; select creation_time, execution_count, TotalWorkerTime, TotalLogicalReads, TotalPhysicalReads, TotalElapsedTime, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS QueryText, qp.query_plan AS ExecutionPlan from ( SELECT TOP 10 qs.creation_time, qs.execution_count, qs.total_worker_time AS TotalWorkerTime, qs.total_logical_reads AS TotalLogicalReads, qs.total_physical_reads AS TotalPhysicalReads, qs.total_elapsed_time AS TotalElapsedTime, qs.statement_start_offset, qs.statement_end_offset, qs.plan_handle, qs.sql_handle FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time/execution_count desc ) qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

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;     select   creation_time,     execution_count,     TotalWorkerTime,     TotalLogicalReads,     TotalPhysicalReads,     TotalElapsedTime,         SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,                    ((CASE qs.statement_end_offset                          WHEN -1 THEN DATALENGTH(qt.text)                          ELSE qs.statement_end_offset                     END - qs.statement_start_offset)/2) + 1) AS QueryText,         qp.query_plan AS ExecutionPlan   from ( SELECT TOP 10         qs.creation_time,         qs.execution_count,         qs.total_worker_time AS TotalWorkerTime,         qs.total_logical_reads AS TotalLogicalReads,         qs.total_physical_reads AS TotalPhysicalReads,         qs.total_elapsed_time AS TotalElapsedTime, qs.statement_start_offset, qs.statement_end_offset, qs.plan_handle, qs.sql_handle     FROM         sys.dm_exec_query_stats qs     ORDER BY         qs.total_worker_time/execution_count desc ) qs       CROSS APPLY         sys.dm_exec_sql_text(qs.sql_handle) AS qt     CROSS APPLY         sys.dm_exec_query_plan(qs.plan_handle) AS qp

按 CPU 对现有请求进行排序

SELECT [req].[session_id], [req].[start_time], [req].[cpu_time] AS [cpu_time_ms], OBJECT_NAME([ST].[objectid], [ST].[dbid]) AS [ObjectName], SUBSTRING( REPLACE( REPLACE( SUBSTRING( [ST].[text], ([req].[statement_start_offset] / 2) + 1, ((CASE [req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE [req].[statement_end_offset] END - [req].[statement_start_offset] ) / 2 ) + 1 ), CHAR(10), ' ' ), CHAR(13), ' ' ), 1, 512 ) AS [statement_text] FROM [sys].[dm_exec_requests] AS [req] CROSS APPLY [sys].dm_exec_sql_text([req].[sql_handle]) AS [ST] ORDER BY [req].[cpu_time] DESC; GO

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SELECT     [req].[session_id],     [req].[start_time],     [req].[cpu_time] AS [cpu_time_ms],     OBJECT_NAME([ST].[objectid], [ST].[dbid]) AS [ObjectName],     SUBSTRING(         REPLACE(             REPLACE(                 SUBSTRING(                     [ST].[text], ([req].[statement_start_offset] / 2) + 1,                     ((CASE [req].[statement_end_offset]                             WHEN -1 THEN DATALENGTH([ST].[text])                             ELSE [req].[statement_end_offset]                         END - [req].[statement_start_offset]                         ) / 2                     ) + 1                 ), CHAR(10), ' '             ), CHAR(13), ' '         ), 1, 512     ) AS [statement_text] FROM     [sys].[dm_exec_requests] AS [req]     CROSS APPLY [sys].dm_exec_sql_text([req].[sql_handle]) AS [ST] ORDER BY     [req].[cpu_time] DESC; GO

正在运行的SQL统计

最耗费CPU的正在运行的SQL

select top 5 * from vw_session_lhr order by cpu_time desc;

1 2 3

select top  5 * from  vw_session_lhr order by cpu_time desc;

最耗费磁盘IO的正在运行的SQL

select top 5 * from vw_session_lhr order by (reads+writes) desc;

1 2 3

select top  5 * from  vw_session_lhr order by (reads+writes) desc;

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

相关文章

• sqlcmd备份MSSQL数据库报错 SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
• datediff 函数导致溢出。用于分隔两个日期 时间实例的日期部分的数字太大。请尝试使用日期部分精度较低的 datediff
• MSSQL一次重建索引未成功中途强制重启引起的故障
• MSSQL数据库连接工具之Azure Data Studio介绍
• MSSQL分离和附加数据库后变为只读
• 不允许对索引 'xxx' 显式地使用 DROP INDEX。该索引正用于 PRIMARY KEY 约束的强制执行
• MSSQL作业JOB介绍
• MSSQL新建实例后需要做哪些基本优化操作
• MSSQL查询数据库大小
• sqlcmd备份MSSQL数据库报错 TCP Provider Error code 0x2746
• PG通过tds_fdw访问SQL Server数据库
• SQL Server中如何找到历史阻塞的会话相关信息
• MSSQL中的幽灵数据删除进程、虚影清除进程（ghost cleanup）
• SSMS配置错误，导致 SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER, ANSI_PADDING'。请确保 SET 选项正确无误，可以用于 计算列上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。
• MSSQL删除tempdb的数据文件
• MSSQL中的等待事件SQLTRACE_FILE_BUFFER说明
• MSSQL的数据库快照
• MSSQL 2014新功能之延迟事务持续性(Delayed Transaction Durability)--延迟提交
• MSSQL中的实时查询统计
• MSSQL中查询索引的创建时间和删除时间

打赏赞(1)分享

标签： 原创MSSQLSQL Server查找SQL抓取耗费性能

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

• 下一篇 当你无聊时，你可以选择做哪些事
• 上一篇 GreenPlum启动数据库报错pre-existing shared memory block

您可能还喜欢...

• 2

  小麦苗MySQL DBA培训（从基础到高可用）

  2021年6月29日

   由 小麦苗 · Published 2021年6月29日 · Last modified 2023年6月15日

  · 已更新 2023年6月15日  

  2    39556    16
• 0

  Linux查看隐藏目录的文件大小

  2024年10月8日

   由 小麦苗 · Published 2024年10月8日

   

  0    173    1
• 0

  Linux 挂载 sshfs 文件系统，可挂载为数据库的异地备份目录

  2024年10月30日

   由 小麦苗 · Published 2024年10月30日 · Last modified 2024年11月14日

  · 已更新 2024年11月14日  

  0    247    1