SQL点滴13—收集SQLServer线程等待信息

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文:SQL点滴13—收集SQLServer线程等待信息要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化的依据。
原文: SQL点滴13—收集SQLServer线程等待信息

要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化的依据。

  

sys.dm_os_wait_stats

这是一个系统视图,里面存储线程所遇到的所有的等待信息,具体的列如下表

列名

数据类型

说明

Wait_type

Nvarchar(60)

等待类型名称

waiting_tasks_count

Bigint

等待类型的等待数。该计数器在每开始一个等待时便会增加。

Wait_time_ms

Bigint

该等待类型的总等待时间。

Max_wait_time_ms

Bigint

该等待类型的最长等待时间。

Signal_wait_time_ms

Bigint

正在等待的线程从收到信号通知到开始运行之间的时差。

要注意的是,这个视图的信息每次关闭SQL Server的时候都会自动清除,下次打开SQL Server的时候又会重新开始统计。

  

新建线程等待信息表

如果想得到连续的信息,在固定时间间隔内收集信息比如一个小时一次,这样就可以分析系统分配的等待时间,识别出繁忙时间段。这里我们将这些信息收集到一个数据表中保存并进行分析。使用下面的语句新建一个表:

 
 
use AdventureWorks

CREATE TABLE dbo.WaitStats

(

dt
DATETIME NOT NULL DEFAULT ( CURRENT_TIMESTAMP ),

wait_type
NVARCHAR ( 60 ) NOT NULL ,

waiting_tasks_count
BIGINT NOT NULL ,

wait_time_ms
BIGINT NOT NULL ,

max_wait_time_ms
BIGINT NOT NULL ,

signal_wait_time_ms
BIGINT NOT NULL

);

CREATE UNIQUE CLUSTERED INDEX idx_dt_type ON dbo.WaitStats(dt, wait_type);

CREATE INDEX idx_type_dt ON dbo.WaitStats(wait_type, dt);

  

新建job填充数据

要收集信息最好是用一个job来定时地执行insert语句填充数据,下面介绍步骤

  1. 点击数据库中的SQL Server Agent,展开,右击jobs文件夹,右击新建。如图1
    img_fa62efefb24ba50816a1117d51fb963a.png

    图1
  2. 在新建job界面,General标签中填写job名称,描述,如图2
    img_32a85937a8c6fb381feaff6983736119.png
    图2
  3. 点击Setps标签,点击新建按钮,在新建step界面内设置Step Name,Type,DataBase,Command等属性,如下图3。这些属性一看就知道是要做什么的,就不一一的解释了。还要说的 是截图是建好的job,如果你自己做的话对话框显示的标题可能不一样。

    img_ebdc2f2d7650ffbb8c11b292a6acb263.png
    图3
    Command属性是设置要执行的SQL语句或存储过程等等,这里设置如下的SQL Script:
     
       
    INSERT INTO Performance.dbo.WaitStats
    (wait_type, waiting_tasks_count, wait_time_ms,
    max_wait_time_ms, signal_wait_time_ms)
    SELECT
    wait_type, waiting_tasks_count, wait_time_ms,
    max_wait_time_ms, signal_wait_time_ms
    FROM sys.dm_os_wait_stats
  4. 点击Shedules标签,设置job的执行计划,这里设置的是每天的每5分钟执行一次如图4
    img_78046cb411e66642ce9d4f0941054a18.png                                                            
    图4

上述步骤包含了新建一个job的主要设置,其他的细节信息没有包含,遇到具体问题再具体分析吧。

收集等待信息数据

过一段时间之后在表WaitStats中就会有一些数据,每隔5分钟就会在这个表中写入一些数据,这些数据会不断的增加。这里为了得到相邻间隔之间线程等待时间的变化就要使用自连接,连接条件是等待类型相同,当前行号等于上一个的行号加上1,然后就可以用上一次等待时间减去这一次的等待时间得到这个变化值,下面使用一个函数来实现这个逻辑:

 
 
IF OBJECT_ID ( ' dbo.IntervalWaits ' , ' IF ' ) IS NOT NULL
DROP FUNCTION dbo.IntervalWaits;
GO
CREATE FUNCTION dbo.IntervalWaits
(
@fromdt AS DATETIME , @todt AS DATETIME )
RETURNS TABLE
AS
RETURN
WITH Waits AS
(
SELECT dt, wait_type, wait_time_ms,
ROW_NUMBER()
OVER (PARTITION BY wait_type
ORDER BY dt) AS rn
FROM dbo.WaitStats
)
SELECT Prv.wait_type, Prv.dt AS start_time,
CAST ((Cur.wait_time_ms - Prv.wait_time_ms)
/ 1000 . AS NUMERIC( 12 , 2 )) AS interval_wait_s
FROM Waits AS Cur
JOIN Waits AS Prv
ON Cur.wait_type = Prv.wait_type
AND Cur.rn = Prv.rn + 1
AND Prv.dt >= @fromdt
AND Prv.dt < DATEADD ( day , 1 , @todt )
GO

这个函数接受两个参数,开始统计时间,结束统计时间,返回等待变化的时间,并按照类型排序。调用这个函数如下:

SELECT wait_type, start_time, interval_wait_s

FROM dbo.IntervalWaits('20110509', '20110510') AS F

ORDER BY SUM(interval_wait_s) OVER(PARTITION BY wait_type) DESC,wait_type,start_time;

但是我们不能每次都去调用这个函数,所以可以吧这个查询放在一个视图里面,外部只需要使用视图来查询数据就可以了:

 
 
IF OBJECT_ID ( ' dbo.IntervalWaitsSample ' , ' V ' ) IS NOT NULL
DROP VIEW dbo.IntervalWaitsSample;
GO
CREATE VIEW dbo.IntervalWaitsSample
AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits( ' 20090212 ' , ' 20090215 ' ) AS F;
GO
从视图中查询得到的数据就是我们要得到的数据。但是这些并不明显,先写到这里,下一个随笔我将在EXECL中把这些数据制作成一个直方图或者连线图,横轴是时间,纵轴是等待间隔时间。这样就会更加直观地看到在那些时间SQL Server的线程等待时间最长,也就是最繁忙的时候。
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
94 0
|
2月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
72 0
|
2月前
|
SQL 数据库 数据安全/隐私保护
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
59 0
|
1天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
23 6
|
1天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
30 10
|
1月前
|
SQL
SQL 查找入职员工时间排名倒数第三的员工所有信息
SQL 查找入职员工时间排名倒数第三的员工所有信息
|
2月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
33 0
|
2月前
|
SQL 数据库 数据库管理
【数据库SQL server】关系数据库标准语言SQL的基本知识
【数据库SQL server】关系数据库标准语言SQL的基本知识
54 0
|
3月前
|
存储 JSON 运维
【运维】Powershell 服务器系统管理信息总结(进程、线程、磁盘、内存、网络、CPU、持续运行时间、系统账户、日志事件)
【运维】Powershell 服务器系统管理信息总结(进程、线程、磁盘、内存、网络、CPU、持续运行时间、系统账户、日志事件)
49 0
|
3月前
|
关系型数据库 MySQL
leetcode-SQL-1965.丢失信息的雇员
leetcode-SQL-1965.丢失信息的雇员
20 0