The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
The last couple days we’ve talked about what features SSMS offers for monitoring and troubleshooting your SQL Servers. One of the most useful frameworks SQL Servers offers for keeping an eye on the internal workings of the database server is Extended Events. Today we’ll take a look at what SSMS now offers for working with this framework.
Extended Events or XEvents as they are commonly called is a framework introduced with SQL Server 2008 and has steadily grown since then. This framework allows you as the administrator to collect events as the occur inside of SQL Server for analysis and low level troubleshooting. What constitutes an event? Well, it could be anything from the occurrence of a deadlock to an error being raised to a security, memory, or resource event. There are literally hundreds of events currently available. This is a very large topic (I mean really big) – today we’re just focusing on the SSMS features that support this framework.
Up until recently your only option for working with the XEvent framework was via T-SQL and the collection of stored procedures that administered them. SSMS however now features a GUI interface for setting up sessions and selecting events to trace, targets where they should be stored, global fields to add to the event etc.
If you look under the Management Folder in SSMS you will see the Extended Events node. Underneath this node are the sessions already running by default – the system_health session and the alwayson_health session. Right clicking the Sessions node gives you the options to define and manage additional custom sessions.
You can do this by going through the step by step Session Wizard or simply clicking ‘New Session…’ and defining the session to your liking. Frankly, there is very little difference between the two windows. The wizard usually makes more sense for those just getting started with XEvents but not quite ready to script everything out from scratch.
New Session Wizard
There are a handful of templates that basically define the session for you or you can select your own events. The key to leveraging extended events really comes down to knowing what events are available, overhead involved, and understanding the pros and cons of the different targets the events can be saved to. I’ll refer you to books online for more information about XEvents in general.
The SSMS GUI for setting up sessions allows you to search and filter events by name and category, shows you what fields are included with the event occurrence, and will walk you through selecting global fields available to all events. When searching events the library list will automatically filter and highlight as you type.
Finally you can define event filters and the targets to store events as they are collected. As stated above each of the targets has it’s own pro and con. Know the overhead, performance, and size implications of each target before randomly choosing a destination.
Management of Existing Event Sessions
You have some typical right click options with regards to your new session, or either of the default sessions actively running. ‘Script As’ is a great way to document your session and fine tune it for deployment on additional servers. You can stop sessions, start sessions, and view the target contents.
Opening up the session target allows you to browse the data collected which brings us to the XEvent options in SSMS that to me are the most useful for leveraging collected information. The toolbar options that appear on both the ‘Live Data’ window as well as when browsing the target window after collection has been paused or stopped.
With the toolbar you can create aggregates, add to the default columns, stop and start data collection, and set bookmarks for jumping from line to line. Extensive querying of target is still most effective though T-SQL and/or xpath (if necessary). But that Extended Events toolbar available under View->Toolbars->Extended Events is a great addition to SSMS. In the screen above you’ll notice the memory_utilization column is not one that appears by default but was one added via the Choose Columns… toolbar option.
I encourage you to get familiar with Extended Events – this is an extremely important tool in the hands of any database administrator. As this feature matures I expect SSMS to continue to improve it’s offering in working with and administering events.
Tomorrow we will return to a more development centric look at SSMS and go over organizing scripts into projects, solutions, and see what SSMS can provide with regards to working with source control.