开发者社区> ndblog> 正文

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

简介: 要知道线程等待时间是制约SQL Server效率的重要原因,这一个随笔中将学习怎样收集SQL Server中的线程等待时间,类型等信息,这些信息是进行数据库优化的依据。    sys.dm_os_wait_stats 这是一个系统视图,里面存储线程所遇到的所有的等待信息,具体的列如下表 列名 数据类型 说明 Wait_type Nvarchar(60) 等待类型名称 waiting_tasks_count Bigint 等待类型的等待数。
+关注继续查看

要知道线程等待时间是制约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的线程等待时间最长,也就是最繁忙的时候。

作者:Tyler Ning
出处:http://www.cnblogs.com/tylerdonet/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,如有问题,可以通过以下邮箱地址williamningdong@gmail.com  联系我,非常感谢。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Java神鬼莫测之MyBatis中$与#的区别(五)
Java神鬼莫测之MyBatis中$与#的区别(五)
4 0
图神经网络02-图与图学习(上)
图(graph)近来正逐渐变成机器学习的一大核心领域,在开始PGL框架学习之前,我们先简单学习一下图论的基本概念,图论的经典算法,以及近些年来图学习的发展。
5 0
【蓝桥真题7】贴吧车队作弊?应对线上考和双填趋势,我们该如何备考?(下)
【蓝桥真题7】贴吧车队作弊?应对线上考和双填趋势,我们该如何备考?
4 0
Java神鬼莫测之MyBatis多表操作延迟加载(四)
Java神鬼莫测之MyBatis多表操作延迟加载(四)
4 0
阿里云成长计划
在阿里云开发者团队的全面支持下,相信全新升级的“开发者成长计划”将为培养国内云技术领域优秀人才提供高效助益。
7 0
Java神鬼莫测之MyBatis注解开发之动态SQL语句(六)
Java神鬼莫测之MyBatis注解开发之动态SQL语句(六)
4 0
Swift5.0 - day4-闭包、属性、方法、下标
Swift5.0 - day4-闭包、属性、方法、下标
4 0
零基础学算法100天第1天——Dijkstra(图解最短路算法)(上)
零基础学算法100天第1天——Dijkstra(图解最短路算法)
5 0
零基础学算法100天第3天——Floyd(多源最短路径算法)(上)
零基础学算法100天第3天——Floyd(多源最短路径算法)
3 0
+关注
ndblog
不冒任何险,什么都不做,什么也不会有,什么也不是。
327
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载