Shulou(Shulou.com)06/01 Report--

Full resolution of SQL Server monitoring

In the daily management of SQL Server, it is necessary for DBA to make SQL Server run efficiently and perform well. DBA needs to know the daily operation of the database, analyze and tune the performance, and monitor the deployment of the online environment. So what aspects do we all need to monitor?

How about the performance of CPU, memory, IO, network traffic, cache and other resources of the SQL Server server, and whether various related services, such as SQL Server service and SQL Server proxy service, are running normally? these generally use the open source monitoring software Zabbix to set alarms. Of course, according to the characteristics of the database server, add some performance counters of the SQL Server database engine to collect.

SQL Server logs record useful information. So you can monitor SQL Server error logs, SQL Server agent logs, and so on.

The SQL Server database cannot avoid some abnormal states, such as exceptions caused by incorrect scripts, insufficient space, disk hanging, replication failure, and so on. Here I would like to mention the SQL Server incident first. This means that events resulting from specific errors occur in SQL Server, each with a corresponding database, severity level, error number, and error text. Specific error monitoring can be carried out for some extremely serious errors, such as 823, 824, 825, 832, 855, 856, etc., as well as for serious error levels, such as error levels from 19 to 25.

All kinds of highly available technologies are deployed in production environment, whether they are mirroring, log shipping, replication or Alwayson, you need to deploy corresponding monitoring. One is to monitor whether it is running normally, and the other is to set a certain alarm threshold.

The above monitoring can basically meet the basic production needs, so what other aspects do we need to monitor?

Connection timeout, execution timeout, deadlock for SQL Server.

SQL Server active process, slow query, blocking.

Waiting statistics are critical to analyzing SQL Server engine performance bottlenecks and helping to diagnose performance problems with SQL Server as well as specific queries and batches.

The ring buffer contains the smallest system output and records a large amount of XML format information, which is used to help analyze state changes and provide better ideas. Can monitor connections, exceptions, scheduling, security, memory, and so on.

Audit SQL Server database engine instances or separate databases to track and log events that occur in the database engine.

Can be combined with Powershell to achieve automatic monitoring and deployment, combined with × × S to achieve platform display. Further into the web side of the deployment, operation and maintenance, monitoring, performance analysis and other integration.

Monitoring is a major theme of the SQL Server database engine. It is necessary to understand the monitoring architecture of the whole database engine and do a comprehensive monitoring. Of course, after reading the author's book "SQL Server Monitoring and Diagnostics", there is an answer to which method is most appropriate and how to design and deploy, and even provide a large number of code with practical solutions, which can be used directly.

Let's talk about SQL Server monitoring with the most common deadlocks.

How to monitor deadlock (Deadlock) in SQL Server

What is a deadlock?

The so-called deadlock: refers to two or more processes in the implementation process, due to competition for resources or communication caused by a blocking phenomenon, if there is no external force, they will not be able to push forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes.

Because the occupation of resources is mutually exclusive, when a process applies for resources, the process can never allocate the necessary resources and can not continue to run without external assistance, which gives rise to a special phenomenon: deadlock.

In order to prevent a large number of deadlocks from flooding the system in SQL Server, we have a deadlock monitoring back-end thread to help resolve deadlocks.

Deadlock monitoring thread

If we look at sys.dm_os_waiting_tasks, we can see that a system task has been waiting: REQUEST_FOR_DEADLOCK_SEARCH. The thread is awakened every five seconds to see if there is a deadlock. If a deadlock is found, it ends a session. It kills one of the two sessions and gives the other session all the resources it needs.

SQL Server will decide to make sure that the session that is most easily rolled back is killed. Because if SQL Server kills a transaction, any work it does must be rolled back to the database's synchronized state. It is determined by the value of LOG USED.

We can see that the above figure kills session 75 instead of 192, because session 75 uses 648 words of holiday history and session 192 uses 944 bytes.

The back-end thread wakes up every five minutes to check for deadlocks. If found, it follows the process of the above example to decide how to solve it. However, when it wakes up for the first time, it immediately wakes up for the second time to make sure it is not a nested deadlock. If there is, it will be killed and then returned to sleep. The next wake-up time is after 4.90 seconds (the estimated wake-up time takes 10 milliseconds). Each decrement of 100 milliseconds will wake up 10 times per second to deal with deadlocks.

How to monitor deadlocks?

Method 1:

Windows performance Monitor (Performance Monitor)

Object: SQLServer:Locks

Counter: Number of Deadlocks/sec

Instance: _ Total

The following query provides all the deadlocks that have occurred on this server since the last restart:

SELECT cntr_value AS NumOfDeadLocksFROM sys.dm_os_performance_countersWHERE object_name = 'SQLServer:Locks'AND counter_name =' Number of Deadlocks/sec'AND instance_name ='_ Total'

Method 2:

Tracking identification (Trace Flags) 1204 and 1222

Trace Flag 1204 has existed since at least SQL Server 2000. Trace Flag 1222 is included from SQL Server 2005. The deadlock information for both is recorded in the SQL Server error log (ERRORLOG).

Method 3:

SQL Server Profiler and server-side SQL Trace

Trace Event Class: Locks Event Name: Deadlock Graph

A XML diagram is given as shown in the above example. It is very easy to read and find out what actions are currently being done.

Method 4:

Extended event (Extended Events)

A new way of monitoring since SQL Server 2008. The extension event will eventually replace SQL Server Profiler (note: SQL Server Profiler is in the list of discarded attributes). It provides the same XML diagrams as SQL Server Profiler and is lighter in terms of performance impact.

Method 5:

System Health

A new default trace, but it does not have a limited amount of trace information like the SQL Server default trace (Default Trace) and cannot be modified. We can modify the definition of system health, which is built into the extension event. Unlike the default trace, system health can trace the deadlock information that has just occurred. We can get this information from system health for analysis without deploying our own extended event monitoring.

Monitor deadlocks using extended event tracking

We deploy an extended event tracking session through the SQL Server 2012 graphical interface. You can then generate a SQL script to run a similar trace under version 2008 or 2008 R2.

Step 1:

Connect to the instance through "Object Explorer" and expand "Management", "Extended Events", "Sessions".

Step 2:

Right-click "Sessions" to create a new session wizard.

Step 3:

Enter the session name "Deadlock_Monitor" and click next.

Step 4:

Choose not to use templates (like SQL Server Profiler templates, some default options are preset to start together, but there is no template that meets our needs), click next.

Step 5:

Select the event you want to capture, enter deadlock in "Event library", and you can see the following figure:

Step 6:

Select "xml_deadlock_report" to add to the list of events selected on the right. Click next again.

Step 7:

Select the columns to capture, and here we choose the next step.

Step 8:

Define the filter criteria. Here we ignore this setting and click next.

Step 9:

Choose to save data to a file, set the file path and maximum value, etc. Click next.

Step 10:

Check all the configurations and click finish to install and enable the session.

Step 11:

Now we can start the capture and view the activity data.

Step 12:

Right-click on the just created session "Deadlock_Monitor" to generate the script.

CREATE EVENT SESSION [Deadlock_Monitor] ON SERVERADD EVENT sqlserver.xml_deadlock_reportADD TARGET package0.event_file (SET filename=N'D:\ 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

Step 13:

Right-click on the session "Deadlock_Monitor" to select start the session.

Step 14:

Execute the following statement in the two query windows.

-- Window1USE AdventureWorks2012BEGIN TRANUPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20WAITFOR DELAY' 0:0:10'SELECT * FROM Person.Address WHERE AddressID = 25--Window 2USE AdventureWorks2012BEGIN TRANUPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25WAITFOR DELAY' 0:0:10'SELECT * FROM Person.Address WHERE AddressID = 20

Step 15:

Right-click on package0.event_file on "Deadlock_Monitor" and select "View Target Data …" . Select the deadlock entry corresponding to timestamp, and the deadlock XML file is displayed in the xml_ report value of Details. You can double-click to open it. Click Deadlock to see a graphical display of the deadlock.

Go deep into the advanced level

Deadlock details there are several steps you can use to configure extended events to monitor deadlocks.

I want to discuss the other two events to capture more detailed information about analyzing deadlocks.

1. Lock: Deadlock event class

This event class can be used to validate deadlock victims. This event indicates when a lock is requested, but is cancelled as a deadlock victim.

2. Lock: Deadlock chain event class

This event class is used to monitor deadlock status. This event is triggered when there is a deadlock. By monitoring this event at the instance level, we can identify which objects are in deadlocks and whether we have performance problems caused by deadlocks in our application.

Step 1:

Right-click on the previous "Deadlock_Monitor" session and select "Properties". Select the Events page and add the lock_deadlock and lock_deadlock_chain event classes to the list of selected events on the right.

Step 2:

Run the previous deadlock example.

Step 3:

Right-click on package0.event_file on "Deadlock_Monitor" and select "View Target Data …" . Select the deadlock entry for the corresponding timestamp.

If a user reports that they have found an output deadlock in the application's error log, it is late at night. We will know how to monitor and obtain deadlock data.

Use system_health default trace session monitoring deadlock

Since SQL Server 2008, extended events (Extended Events) have been provided to track system analysis of positioning problems. The default system_health session is running all the time to help you locate the problem more quickly.

Run the following script to see the system_health extension event session:

SELECT * FROM sys.dm_xe_sessions

Even if you do not start any extended event sessions, this query will return a row of system_health sessions.

Prior to SQL Server 2012, there was no graphical interface for managing extended event sessions, so you can download the SQL Server 2008 Extended Events SSMS Addin plug-in: http://extendedeventmanager.codeplex.com/

After installation, you can find the extended event management interface as shown in the figure:

In SQL Server 2012, you can find the interface as shown in the figure:

We right-click "system_health" to generate the script, and we can see the contents of the session. You can also go to the SQL Server installation directory: C:\ Program Files\ Microsoft SQL Server\ MSSQL11.\ MSSQL\ Install

Find the script u_tables.sql file under.

As you can see from the definition, the output of the session contains callstack, sessionID, TSQL, and TSQL Call Stack

And when the security level is greater than 20 or the error number is 17803, etc. They are related to memory pressure, Non-yielding scheduler problems, deadlocks, and some types of waits.

The session output is captured into a ring_buffer that complies with FIFO rules, and ring_buffer is a memory consumer that stores captured data in binary format. When the event session is enabled, the data can be captured. When the session is stopped, the memory allocated to the ring_buffer is freed and the data disappears. Note: before SQL Server 2012, the only target for system_health was ring_buffer, and since SQL Server 2012, the output of event_file has been increased.

You can view the contents of ring_buffer or event_file by associating sys.dm_xe_session_targets and sys.dm_xe_sessions views, and convert binary data to XML format.

SELECT name, target_name, CAST (target_data AS XML) target_dataFROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE s.name = 'system_health'GO

Note: the output of event_file is the storage path of the file, while the output of ring_buffer is the captured data.

In ring_buffer, each event element has a subset of data and a subset of actions. These actions are in the definition of the session. The data element contains all the values of the data type column for each event. These columns can be output through the sys.dm_xe_object_columns view. Let's parse the XML format and view the content in tabular format. Because each event returns a different collection of data columns. Here is an example of an error_reported event.

DECLARE @ x XML = (SELECT CAST (target_data AS XML) FROM sys.dm_xe_sessions sINNER JOIN sys.dm_xe_session_targets tON s.address = t.event_session_addressWHERE 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_textFROM @ x.nodes ('/ / RingBufferTarget/event') AS t (e) WHERE t.e.value ('@ name', 'varchar (50)') = 'error_reported'

One of the most helpful uses for system_health is to track deadlocks. For the target ringbuffer, how much data is stored depends on the capacity of the target on the monitored machine and the settings that generate the maximum number, which will be in the definition of each session. You can find past deadlock records in the output of the system_health session.

All queries will be in the system_health output, and you can get a deadlock report by running the following code.

SQL Server 2008 R2WITH SystemHealthAS (SELECT CAST (target_data as xml) AS TargetDataFROM sys.dm_xe_session_targets stJOIN sys.dm_xe_sessions sON s.address = st.event_session_addressWHERE 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 DeadLockGraphFROM SystemHealthCROSS 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 2012WITH SystemHealthAS (SELECT CAST (target_data as xml) AS TargetDataFROM sys.dm_xe_session_targets stJOIN sys.dm_xe_sessions sON s.address = st.event_session_addressWHERE 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 DeadLockGraphFROM SystemHealthCROSS APPLY TargetData.nodes ('/ / RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value ('@ name','varchar (4000)') = 'xml_deadlock_report'ORDER BY Creation_Date DESC

Looking at the inputbuf child element of process-list, you can see the code snippet that caused the deadlock, and process-list shows the process ID of all deadlock participants. The process element contains spid, database id, login, isolation level, and client application name. The resource contained in the deadlock with the Resource-list element. Looking at the owner-list and waiter-list elements, you can see how the two processes block each other.

Try to save the output of the XML as an XDL document and open the exception with SSMS. There are currently two options for graphically opening deadlock charts: SQL Sentry Plan Explorer Pro and SQL Server 2012 Management Studio, see https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/ for details

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.