初涉SQL Server性能问题(4/4):列出最耗资源的会话

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文:初涉SQL Server性能问题(4/4):列出最耗资源的会话在上3篇文章里,我们讨论了列出反映服务器当前状态的不同查询。 初涉SQL Server性能问题(1/4):服务器概况 初涉SQL Server性能问题(2/4):列出等待资源的会话 初涉SQL Server性能问题(3/4):列出阻塞的会话 这篇文章我们看下从计划缓存里列出执行状态。
原文: 初涉SQL Server性能问题(4/4):列出最耗资源的会话

在上3篇文章里,我们讨论了列出反映服务器当前状态的不同查询。

这篇文章我们看下从计划缓存里列出执行状态。

 1 /*****************************************************************************************
 2 List heavy query based on CPU/IO. Change the order by clause appropriately
 3 ******************************************************************************************/
 4 SELECT TOP 20
 5 DB_NAME(qt.dbid) AS DatabaseName
 6 ,DATEDIFF(MI,creation_time,GETDATE()) AS [Age of the Plan(Minutes)]
 7 ,last_execution_time AS [Last Execution Time]
 8 ,qs.execution_count AS [Total Execution Count]
 9 ,CAST((qs.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) [Total Elapsed Time(s)]
10 ,CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)]
11 ,CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2)) AS [Total CPU time (s)]
12 ,CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU]
13 ,CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
14 ,CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
15 ,CAST((qs.total_physical_reads) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Physical Read]
16 ,CAST((qs.total_logical_reads) / qs.execution_count AS DECIMAL(28, 2))  AS [Avg Logical Reads]
17 ,CAST((qs.total_logical_writes) / qs.execution_count AS DECIMAL(28, 2)) AS [Avg Logical Writes]
18 ,max_physical_reads
19 ,max_logical_reads
20 ,max_logical_writes
21 , SUBSTRING (qt.TEXT,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
22    THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
23    ELSE qs.statement_end_offset
24    END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
25 , qt.TEXT AS [Batch Statement]
26 , qp.query_plan
27 FROM SYS.DM_EXEC_QUERY_STATS qs
28 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qt
29 CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(qs.plan_handle) qp
30 WHERE qs.total_elapsed_time > 0
31 ORDER BY 
32 [Total CPU time (s)] 
33 --[Avg Physical Read]
34 --[Avg Logical Reads]
35 --[Avg Logical Writes]
36 --[Total Elapsed Time(s)]
37 --[Total Execution Count]
38 DESC 

输出结果的每列说明介绍如下:

  • DatabaseName 执行计划的数据库环境(数据库名)。
  • Age of the Plan(Minutes) 计划缓存里计划的生存期,单位为分钟。
  • Last Execution Time 这个计划的上次执行日期和时间。
  • Total Execution Count   自上次编译后,总执行次数;在执行计划生存期内[Age of the Plan(Minutes)],总执行次数(自上次编译后)。
  • Total Elapsed Time(s)   执行这个计划总执行次数后[Total Execution Count]的总占用时间,单位为秒。
  • Average Execution time(s) 这个计划每次执行的平均时间,单位为秒。
  • Total CPU time (s)  执行这个计划总执行次数后[Total Execution Count]的总CPU时间,单位为秒。
  • % CPU 与Total Elapsed Time(s)相比,CPU占用时间比。
  • % Waiting  与Total Elapsed Time(s)相比,等待资源占用时间比。
  • CPU time average (s) 每次执行的平均CPU时间,单位为秒。
  • Avg Physical Read 每次执行的平均物理读数。
  • Avg Logical Reads 每次执行的平均逻辑读数。
  • Avg Logical Writes 每次执行的平均逻辑写数。
  • max_physical_reads 每次执行的时候,出新最大物理读数。
  • max_logical_reads 每次执行的时候,出新最大逻辑读数。
  • max_logical_writes 每次执行的时候,出新最大逻辑写数。
  • Individual Query  批处理语句的部分信息。
  • Batch Statement  批处理查询。
  • query_plan XML格式的执行计划,点击后我们可以看图示执行计划。

一般我们可以分析前5条记录(通过修改排序规则)的具体语句信息。大多数情况,我们会发现问题出现在临时表的滥用,distinct语句,游标,不合适的表连接条件,不合适的索引等等。其他经常发生的问题是,存储过程对数据库的大量调用(CPU消耗和执行时间都很小)。这个需要和开发人员反馈,修改下具体的实现方式。如果数据经常被调用,可以在程序里使用缓存方法避免与服务器的多次交互。有些对数据库的调用只是检查结果数据是否有改变。有些对数据库的调用是为检查数据库表里是否有新记录,且必须马上处理的。为了完成这些操作,程序会在1秒内多次查询表来找出未处理的记录。这个可以通过程序的异步调用来往表里插入数据来解决,或可以使用.net框架里的sqlDependency来解决。(sqlDependency提供了这样一种能力:当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的。)

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
14天前
|
SQL 数据库 开发者
MSSQL性能调优实战技巧:索引优化、SQL语句微调与并发控制策略
在Microsoft SQL Server(MSSQL)的管理与优化中,性能调优是一项复杂但至关重要的任务
|
14天前
|
SQL 监控 数据库
MSSQL性能调优实战策略:索引优化、SQL语句重构与并发控制
在Microsoft SQL Server(MSSQL)的管理和优化过程中,性能调优是确保数据库高效运行、满足业务需求的重要环节
|
14天前
|
SQL 运维 监控
MSSQL性能调优实战:索引优化、SQL查询效率提升与并发控制策略
在Microsoft SQL Server(MSSQL)的日常运维与性能优化中,精准的策略与技巧是实现高效数据库管理的关键
|
14天前
|
SQL 存储 数据库
MSSQL性能调优实战:索引优化、SQL语句精调与高效并发处理
在Microsoft SQL Server(MSSQL)的性能调优征途中,索引优化、SQL语句的精细调整以及高效并发处理是三大核心策略
|
14天前
|
SQL 数据库 开发者
MSSQL性能调优实战:索引策略、SQL优化与并发管理深度剖析
在Microsoft SQL Server(MSSQL)的性能调优过程中,索引策略、SQL查询优化以及并发管理是关键的三大支柱
|
14天前
|
SQL 监控 数据库
MSSQL性能调优实战技巧:索引优化策略、SQL查询重构与并发控制详解
在Microsoft SQL Server(MSSQL)的管理与优化过程中,性能调优是确保数据库高效运行的关键环节
|
14天前
|
SQL 运维 监控
MSSQL性能调优实战:索引深度优化、SQL查询技巧与高效并发控制
在Microsoft SQL Server(MSSQL)的运维环境中,性能调优是确保数据库高效、稳定运行的核心任务
|
14天前
|
SQL 监控 数据库
MSSQL性能调优实战指南:精准索引策略、SQL查询优化与高效并发控制
在Microsoft SQL Server(MSSQL)的性能调优过程中,精准索引策略、SQL查询优化以及高效并发控制是三大核心要素
|
5天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
24 3
|
14天前
|
SQL 运维 监控
MSSQL性能调优实战技巧:索引优化、SQL查询优化与并发控制策略
在Microsoft SQL Server(MSSQL)的运维过程中,性能调优是确保数据库高效运行、满足业务需求的关键环节