SQL Server监控全解析51CTO博客 - 娱乐之横扫全球

SQL Server监控全解析51CTO博客

2019年03月04日12时15分35秒 | 作者: 晨潍 | 标签: 监控,死锁,事情 | 浏览: 442

SQL Server监控全解析

 

在SQL Server的日常办理中,让SQL Server高效运转,且功能杰出,是DBA需求做的事。DBA需求了解数据库的日常运转状况,对功能进行剖析和调优,需求对线上环境布置监控。那咱们都需求监控哪些方面呢?


  1. SQL Server效劳器的CPU、内存、IO、网络流量、缓存等资源功能怎样样,各个相关效劳如SQL Server效劳、SQL Server署理效劳等是否正常运转,这些一般运用开源的监控软件Zabbix来设置告警,当然针对数据库效劳器的特性,增加一些SQL Server数据库引擎的功能计数器进行搜集。


  2. SQL Server各种日志会记载有用的信息。因而能够监控SQL Server过错日志、SQL Server署理日志等。


  3. SQL Server数据库避免不了一些反常状况,比方过错的脚本导致的反常,空间不行,磁盘挂了,仿制失利了等。这儿我先提提SQL Server事情。这个意味着SQL Server发作特定过错发作的事情,每个事情都有对应的数据库、严峻等级、过错号、过错文本。能够针对一些极端严峻的过错如823、824、825、832、855、856等进行特定过错监控,还能够针对严峻的过错等级进行监控,如过错等级从19到25。


  4. 出产环境都会布置各种高可用技能,无论是镜像、日志传送、仿制仍是Alwayson,都需求布置相应的监控,留意一个是要监控是否正常运转,再就是功能怎样样,设置必定的告警阈值。


    上面的监控基本能满意基本出产需求,那么咱们还要监控哪些方面呢?

  5. SQL Server的衔接超时、履行超时、死锁。


  6. SQL Server活动进程、慢查询、堵塞。


  7. 等候计算关于剖析SQL Server引擎功能瓶颈十分要害,帮忙确诊SQL Server以及特定查询和批处理的功能问题。


  8. 环形缓冲区包括了最小的体系输出,记载了很多的XML格局信息,用于帮忙剖析状况的改变供给更好的思路。能够监控衔接、反常、调度、安全、内存等。


  9. 审阅SQL Server数据库引擎实例或独自的数据库,盯梢和记载数据库引擎中发作的事情。


  10. 能够结合Powershell完结自动化监控布置、结合***S完结渠道化展现。再进一步深化到Web端的布置、运维、监控、功能剖析等一体化。


监控是SQL Server数据库引擎的一大主题,了解整个数据库引擎的监控架构,并做好全面的监控,是很必要的。当然,具体运用什么办法最合适,怎样去规划和布置,看完笔者的《SQL Server监控和确诊》一书自有答案,乃至供给有很多实践处理方案的代码,直接拿来可用。


咱们就拿最常见的死锁来谈谈SQL Server的监控。


SQL Server 中怎样监控死锁(Deadlock)


什么是死锁?


所谓死锁: 是指两个或两个以上的进程在履行过程中,由于竞赛资源或许由于相互通讯而形成的一种堵塞的现象,若无外力作用,它们都将无法推动下去。此刻称体系处于死锁状况或体系发作了死锁,这些永久在相互等候的进程称为死锁进程。


由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力帮忙下,永久分配不到必需的资源而无法持续运转,这就发作了一种特别现象:死锁。


在SQL Server中为了阻挠死锁很多充满在体系中,咱们有一个死锁监控的后端线程来帮忙处理死锁。


死锁监控线程


假如咱们检查sys.dm_os_waiting_tasks,咱们能够发现一个体系使命一向处于等候状况:REQUEST_FOR_DEADLOCK_SEARCH。该线程每五秒钟被唤醒,来检查是否有死锁。假如发现死锁,它将完毕一个会话。它会杀掉两个会话中的一个,让另一个会话具有需求的一切资源。


SQL Server会判别,要保证杀掉的是最简单回滚的会话。由于假如SQL Server杀掉一个业务,它所做的任何作业有必要回滚到数据库的同步状况。它由LOG USED的值来决议。



咱们能够看到上例图杀掉了会话75而不是192,由于会话75运用了648字节日志而会话192运用了944字节。


后端线程每五分钟唤醒检查死锁。假如发现,它遵循上例的流程去决议怎样处理。但是,当它第一次唤醒,立马唤醒第2次,保证不是一个嵌套死锁。假如有,会被杀掉,然后回来睡觉状况。下一次唤醒在4.90秒之后(预估唤醒时刻花费10毫秒)。每次递减100毫秒,将每秒唤醒10次处理死锁。


怎样监控死锁?


办法一:

Windows功能监控器(Performance Monitor)

Object: SQLServer:Locks

Counter: Number of Deadlocks/sec

Instance: _Total


下面的查询供给了自从前次重启以来在本效劳器上发作的一切死锁:

SELECT cntr_value AS NumOfDeadLocks
FROM sys.dm_os_performance_counters
WHERE object_name = SQLServer:Locks
AND counter_name = Number of Deadlocks/sec
AND instance_name = _Total


办法二:

盯梢标识(Trace Flags)1204和1222


Trace Flag 1204至少从SQL Server 2000开端存在。Trace Flag 1222从SQL Server 2005被包括进来。两者的死锁信息被记载到SQL Server过错日志(ERRORLOG)。


办法三:

SQL Server Profiler和效劳端的SQL Trace

Trace Event Class: Locks Event Name: Deadlock Graph

像上面示例相同给出一个XML图示。十分简单阅览并找出当时正在进行什么动作。


办法四:

扩展事情(Extended Events)


自从SQL Server 2008开端的监控新办法。扩展事情最终会替代SQL Server Profiler(留意:SQL Server Profiler在被抛弃特点列表中)。和SQL Server Profiler相同它供给了相同的XML图示,并且在功能影响上更轻量级。


办法五:

System Health

一个新的默许盯梢,但它不像SQL Server默许盯梢(Default Trace)那样有有限数量的盯梢信息且不能修正。咱们能够修正system health的界说,它内置于扩展事情中。不像默许盯梢,system health能够盯梢到方才现已发作过的死锁信息。咱们能够从system health获取这些信息用来剖析而不必布置咱们自己的扩展事情监控。


运用扩展事情盯梢监控死锁


咱们经过SQL Server 2012图形界面来布置一个扩展事情盯梢会话。然后能够生成SQL脚本,在2008或2008 R2版别下运转相似的盯梢。


过程1:

经过“Object Explorer”衔接到实例,翻开“Management”、“Extended Events”、“Sessions”。



过程2:

右键点击“Sessions”,创立一个新的会话导游。


过程3:

输入会话称号“Deadlock_Monitor”,点击下一步。


过程4:

挑选不运用模板(像SQL Server Profiler模板相同,预设了一些默许选项一同发动,但没有一个满意咱们需求的模板),点击下一步。


过程5:

挑选要捕获的事情,在“Event library”输入deadlock,可看到如下图所示:


过程6:

挑选“xml_deadlock_report”,增加到右侧挑选的事情列表中。再单击下一步。


过程7:

挑选要捕获的列,这儿咱们挑选下一步。


过程8:

界说过滤条件,这儿咱们疏忽这个设置,点击下一步。


过程9:

挑选保存数据到文件,设置文件途径和最大值等。点击下一步。


过程10:

检查一切的装备,点击完结来装置和启用会话。


过程11:

现在咱们能够发动捕获,并检查活动数据。


过程12:

在方才创立会话“Deadlock_Monitor”上右键点击生成脚本。

CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=ND:\MSSQL\DATA\MSSQL11.MSSQLSERVER\MSSQL\Log\Deadlock_Monitor.xel,max_file_size=(256),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


过程13:

在会话“Deadlock_Monitor”上右键挑选发动会话。


过程14:

分别在两个查询窗口履行如下句子。

Window1
USE AdventureWorks2012
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = New address WHERE AddressID = 20
WAITFOR DELAY 0:0:10
SELECT * FROM Person.Address WHERE AddressID = 25
Window 2
USE AdventureWorks2012
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = New address WHERE AddressID = 25
WAITFOR DELAY 0:0:10
SELECT * FROM Person.Address WHERE AddressID = 20


过程15:

在“Deadlock_Monitor”上的package0.event_file上右键挑选“View Target Data…”。挑选对应timestamp的死锁条目,在Details的xml_report值里显现的就是死锁的XML文件,可双击翻开。点击Deadlock即可看到死锁的图形化展现。






深化进阶


死锁具体信息还有几个过程可用来装备扩展事情来监控死锁。


我想去评论别的两个事情来捕获到剖析死锁更具体的信息。


1. Lock: Deadlock事情类

这个事情类能够用来验证死锁牺牲品。这个事情阐明什么时分恳求需求一个锁,但被撤销作为一个死锁牺牲品。


2. Lock: Deadlock chain事情类

这个事情类用于监控死锁状况。当有一个死锁时该事情被触发。经过在实例等级监控这个事情,咱们能够辨认那些方针在死锁中,咱们是否在使用程序中有死锁导致的功能问题。


过程1:

在之前的“Deadlock_Monitor”会话上右键挑选“Properties”。挑选“Events”页,将lock_deadlock和lock_deadlock_chain事情类增加到右侧已挑选事情列表。


过程2:

运转之前的死锁示例。


过程3:

在“Deadlock_Monitor”上的package0.event_file上右键挑选“View Target Data…”。挑选对应timestamp的死锁条目。





假如有用户反应说他们在使用程序的过错日志里发现了输出了死锁信息,并且是在深夜。咱们就能够知道怎样监控和获取死锁数据了。


运用system_health默许盯梢会话监控死锁


自SQL Server 2008今后,供给了扩展事情(Extended Events)来盯梢体系剖析定位问题。默许的system_health会话一向在运转,能够帮忙你更快的定位问题。


运转如下脚本能够看到system_health扩展事情会话:

SELECT * FROM sys.dm_xe_sessions


即便是你没有发动任何扩展事情会话,这个查询也会回来一行system_health会话。


SQL Server 2012版别之前,并不供给办理扩展事情会话的图形界面,你能够从这儿下载SQL Server 2008 Extended Events SSMS Addin插件:http://extendedeventmanager.codeplex.com/


装置好后,能够按如图办法找到扩展事情办理界面:


而在SQL Server 2012版别中,则经过如图办法能够找到该界面:


咱们右键点击“system_health”,生成脚本,咱们能够看到该会话的内容。你也能够在SQL Server的装置目录:C:\Program Files\Microsoft SQL Server\MSSQL11.<instanceid>\MSSQL\Install

下找到脚本u_tables.sql文件。


从界说能够看到,会话的输出包括callstack、sessionID、TSQL和TSQL Call Stack

且当安全等级大于20或许过错号为17803等。它们与内存压力相关、Non-yielding scheduler问题、死锁和一些类型的等候。


会话输出被捕获到遵照FIFO规矩的ring_buffer中,ring_buffer是一个内存运用者,它以二进制格局存储捕获数据。当事情会话启用的时分,数据即可被捕获。当中止会话的时分,分配给ring_buffer的内存被开释,且数据消失。留意:关于SQL Server 2012之前,system_health的方针只要ring_buffer,从SQL Server 2012开端,增加了event_file的输出。


你能够经过相关sys.dm_xe_session_targets和sys.dm_xe_sessions视图来检查ring_buffer或event_file的内容,并转化二进制数据为XML格局。

SELECT name, target_name, CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = system_health
GO

留意:event_file的输出是文件的存储途径,而ring_buffer的输出是捕获到的数据。


在ring_buffer中,每一个事情元素都有一个数据子集和一个动作子集。这些动作是在会话的界说中。数据元素包括了每个事情的数据类型列的一切值。这些列可经过sys.dm_xe_object_columns视图输出。让咱们解析XML格局以表格格局检查内容。由于每个事情回来数据列的不同调集。下面给一个error_reported事情的比如。

DECLARE @x XML =
(SELECT CAST(target_data AS XML)
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = system_health and t.target_name = ring_buffer)
SELECT t.e.value(@name, varchar(50)) AS EventName
,t.e.value(@timestamp, datetime) AS DateAndTime
,t.e.value((data[@name="error"]/value)[1], int) AS ErrNo
,t.e.value((data[@name="severity"]/value)[1], int) AS Severity
,t.e.value((data[@name="message"]/value)[1], varchar(max)) AS ErrMsg
,t.e.value((action[@name="sql_text"]/value)[1], varchar(max)) AS sql_text
FROM @x.nodes(//RingBufferTarget/event) AS t(e)
WHERE t.e.value(@name, varchar(50)) = error_reported


关于system_health最有帮忙的用处之一是盯梢死锁。关于方针ringbuffer,存储多少数据依赖于被监控机器上的该方针的容量,以及发作最大数量的设置相关,这些将在每个会话的界说中。你能够在system_health会话的输出中找到曩昔的死锁记载。


一切查询都会在system_health输出中,能够经过运转下面的代码取得一个死锁报表。

 SQL Server 2008 R2
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = system_health
AND st.target_name = ring_buffer)
SELECT XEventData.XEvent.value(@timestamp,datetime)as Creation_Date,CAST(XEventData.XEvent.value((data/value)[1],VARCHAR(MAX)) AS XML) AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes(//RingBufferTarget/event) AS XEventData (XEvent)
WHERE XEventData.XEvent.value(@name,varchar(4000)) = xml_deadlock_report
ORDER BY Creation_Date DESC



 SQL Server 2012
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = system_health
AND st.target_name = ring_buffer)
SELECT XEventData.XEvent.value(@timestamp,datetime)as Creation_Date, XEventData.XEvent.query((data/value/deadlock)[1]) AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes(//RingBufferTarget/event) AS XEventData (XEvent)
WHERE XEventData.XEvent.value(@name,varchar(4000)) = xml_deadlock_report
ORDER BY Creation_Date DESC

检查process-list的inputbuf子元素,能够看到导致死锁的代码片段,process-list显现一切死锁参与者的进程ID。process元素包括spid、数据库id、登录名、阻隔等级、客户端使用程序名。Resource-list元素包括在死锁中的资源。检查owner-list和waiter-list元素能够看到这两个进程怎样相互堵塞。


测验将该XML的输出保存为XDL文档,用SSMS翻开反常。现在有两个挑选能够以图形办法翻开死锁图表:SQL Sentry Plan Explorer Pro 和 SQL Server 2012 Management Studio,详见:https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/


版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表娱乐之横扫全球立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章