Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.
Give this article a Google +1!
Issue 5 Number 1 January 2013
For many SQL Server DBA’s, mastering Extended Events (XE) involves a learning curve. First they must learn the syntax for creating XE sessions to gather system data. Then they will need to learn something about XML and XPATH to query the data gathered by the session if they do not already have those skills.
However it is not necessary to absolutely master XE in order to use it for monitoring the health of your SQL Server. By the time you finish this short article you will have the knowledge and the code to retrieve health check data like deadlocks, errors and other serious things that can compromise your server’s health and performance. Best of all, you can gather all this information without putting any increased load on your server as would happen if you tried to do this using more traditional methods like traces or trace flags.
You don’t have to know how to create an XE session in order to use XE to monitor SQL Server. All versions later than SQL 2008 contain a pre-defined XE session named system_health that starts each time SQL Server is started. It contains a lot of useful data like:
System_health data is stored in a 4 megabyte ring buffer. The older data drops off as new data is acquired, so you will always have access to 4 mb of the most recent system health data.
As I mentioned, querying an XE session can be complicated. There are two ways to approach it:
I prefer the latter.
This is not as lazy as it sounds. It is one way to achieve practical results quickly. Imitation leads to further knowledge as you tweak and extend the example code to do more useful things.
In the following paragraphs we will look at four example scripts that retrieve different data from the system_health XE session. You can run them on any version of SQL Server from SQL 2008 forward. You can copy and paste the code from this article if you like or you can Email Me or call me at 1.877.201.3428 toll-free (in the US or Canada) and I will be happy to email them to you.
This script pulls out all the data collected by system_health session into one big XML document. It is also the basis for the more specific queries that follow and the ones you will create for yourself later.
In itself it is useful for those who have some familiarity with xml and those who just want to get familiar with everything that’s available in the session. For those who have little experience with xml, I recommend downloading XMLNotePad free from Microsoft.
Paste the xml output from this query into XMLNotepad, select Expand All from the View menu and scroll normally through the text.
Four megabytes of data is quite a bit to digest, so users will usually prefer to look at specific items within the entire result set. The following queries return subsets of the data in the familiar row and column format.
This bit of code will show us the count of specific issues captured by the session.
This query returns the details of all deadlocks that have been captured by the session. Each row of this data is an XML fragment describing the properties of each deadlock captured by the session.
This query will show us all of the errors of severity 20 or above that have been captured. Each row contains the error type and the time the error occurred.
Hopefully these examples these examples will ease you into a familiarity with Extended Events. Your next step in learning might be to extend the functionality of these scripts to retrieve additional information.
By the way, these examples come from Microsoft and were written by Amit Banerjee.
I would like to correct what might be an error in last month’s newsletter where I said that SQL Profiler will be removed in the next new version of SQL Server. I have had a couple of people challenge that statement.
I have to admit that I did not double check my source before writing that. However I could not confirm that data when I tried later. It seems that the people who know exactly when it will be removed are not talking and no one can say anything for sure except that SQL Profiler will be removed in some future edition.
Still, it is sure that Extended Events are the future for monitoring SQL Server. There is a lot of incentive to start using them now. Compared to profiler traces, there are many more events available and there is a great deal more flexibility in the ways we can handle those events. Extended Events also imposes very little impact on server resources.
I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.
As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.
Kurt Survance, SQL Consulting, Inc.
On this page:
Elsewhere on this site: