SQL Saturday 572–Extended Events and Always On


imageThis is the second year that I will be speaking at SQL Saturday at Portland. Last year I had talked about troubleshooting slow queries. This year I will talk about debugging SQL Server issues without having to use the debugger! As always, this will be an awesome prelude to the PASS Summit!

The event sees a great turnout with a number of data professionals talking about various topics. It’s a great opportunity to meet the SQL Server community and network with the folks that help you on the forums and social media. Also, you have a large number of experts under the same roof. So bring your questions.

Session Title: SQL Server Debugging Made Easy using Extended Events
Track: Enterprise Database Administration & Deployment
Level: Intermediate
Time: 1.30PM – 2:45PM
Abstract
This is a demo filled session that will show you how to debug several common issues in SQL server database engine and identify hotspots as well failures using only extended events. We will showcase how extended events make scenario based troubleshooting easier without having to collect disparate sets of diagnostic data, gather memory dumps and comprise on performance. There have been a plethora of Extended Events which have been added to SQL Server recently based on customer feedback which deprecates the need to run profiler in many commonly encountered situations in production environments. This session covers the new enhancements and capabilities available for Extended Events.

Additional Notes
Understand the diagnostics enhancements in SQL Server database engine Use the diagnostics to troubleshoot and mitigate issues quickly in mission-critical environments Setup session templates proactively to reduce mitigation time during reactive situations.

I will also be delivering  a lightning talk on Enhancements in SQL Server 2016 which will keep your SQL Server environment Always On.

A big thank you to the organizers for giving me the opportunity to speak at the event again and talk about the enhancements that the Tiger team is shipping!

SQL Saturday 511: Extended Events and more


imageExtended Events have evolved a lot since they were first introduced in SQL Server 2008. And today, there are multiple extended events available in SQL Server which allow you to debug common scenarios without collecting having to collect memory dumps or diagnostic data which can be detrimental to your SQL Server instance’s performance.

On April 2nd, join me at Redmond to understand how to leverage the new extended events that are available in SQL Server 2012 Service Pack 3 and above in your environments. I will talk about common scenarios where the new extended events are available and provide canned scripts to help collect and analyze data for complex scenarios.

There are a host of SQL experts and as well as folks from the Microsoft SQL Server product group who will be at the event to present and answer questions that you have! Looking forward to meet the SQL Community at the event!

WHEN: April 2nd, 2016, 2.15 – 3.15 PM PST
SESSION: Troubleshooting made easier using Extended events

Details about the session are available here. The full schedule of the event is available here.

The slide deck that I used during the session is available below.

Creating custom XEvent Templates


SQL Server 2012 added the Extended Events UI which made working with Extended Events session much easier. You no longer need to chalk up lengthy T-SQL scripts to spawn off your Extended Events monitoring session. The UI also provides you an option to view the collected data and perform groupings and aggregations for easier analysis.

The templates that you see while configuring an Extended Event session are located at “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Templates\sql\xevent“. When you export an Extended Events session, you will be given an option to export the Session as a Template to the following folder: “C:\Users\<user name>\Documents\SQL Server Management Studio\Templates\XEventTemplates“. The template is always exported as an XML file. Now when you create a new Extended Event Session, you will see the template under the user templates section in the templates view as shown in the screen shot below:

You can now choose to create as many templates as you want for your monitoring purposes. However, the template files have to be deposited in the above mentioned location so that Management Studio can show them to you while creating an Extended Events session.

The above blog post is a manifestation of a seemingly simple question asked by Naman Vadhera yesterday while he was exploring the Extended Events UI option.

Reference: http://msdn.microsoft.com/en-us/library/ms174169.aspx