SQL之收集SQL Server线程等待信息

要知道线程等待时间是制约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语句填充数据,下面介绍步骤

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

在新建job界面,General标签中填写job名称,描述,如图2

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

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 

点击Shedules标签,设置job的执行计划,这里设置的是每天的每5分钟执行一次如图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 < DATE 
ADD(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的线程等待时间最长,也就是最繁忙的时候。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏企鹅号快讯

带你认识一下mysql中数据库information

information_schema 大家在安装或使用MYSQL时,会发现除了自己安装的数据库以外,还有一个information_schema数据库。 inf...

2188
来自专栏运维技术迷

连仕彤博客Mysql数据库归档利器之pt-archiver

2456
来自专栏我和PYTHON有个约会

数据库连接引擎那点事儿

天长,地久。天地之所以能长且久者,以其不自生也,故能长生。是以圣人后其身而身先,外其身而身存,非以其无私邪?故能成其私。——老子

912
来自专栏芋道源码1024

数据库中间件 MyCAT源码分析:【单库单表】插入

本文主要基于 MyCAT 1.6.5 正式版 1. 概述 2. 接收请求,解析 SQL 3. 获得路由结果 4. 获得 MySQL 连接,执行 SQL 5. 响...

52512
来自专栏cloudskyme

oracle10g分区的几种类型

为了简化数据库大表的管理,例如在数据仓库中一般都是TB级的数量级.ORACLE8以后推出了分区选项.分区将表分离在若于不同的表空间上,用分而治之的方法来支撑元限...

35311
来自专栏菩提树下的杨过

mysql 学习笔记

以前主要使用oracle做数据库,现在换成mysql了,发现不一样的地方还是挺多的,记录一下: 一、centos上的yum install方式安装  完全卸...

2187
来自专栏CaiRui

Mysql-4-数据库的基本操作

1.创建数据库 create database database_name; 例:create database aa; show create databas...

2017
来自专栏沃趣科技

MVCC原理探究及MySQL源码实现分析

目录预览 数据库多版本读场景 MVCC实现原理 1、通过DB_ROLL_PT 回溯查找数据历史版本 2、通过read view判断行...

6108
来自专栏逸鹏说道

SQL Server 执行计划缓存

概述 了解执行计划对数据库性能分析很重要,其中涉及到了语句性能分析与存储,这也是写这篇文章的目的,在了解执行计划之前先要了解一些基础知识,所以文章前面会讲一些...

3979
来自专栏杨建荣的学习笔记

浅谈exp/imp(下) (r5笔记第84天)

相关链接:浅谈exp/imp(上) (r5笔记第81天) 你可能 不了解的dump文件 在工作中,dump文件对于dba而言是再平常不过的文件了。不过因为dum...

3289

扫码关注云+社区

领取腾讯云代金券