Loading...

Tech Talk Live Blog

Using SQL Server Extended Events to Diagnose Data-Specific Problems in Your Applications

Matt Kernicky


Relevant Technologies:

  • SQL Server 2012 or later
  • SQL Server Management Studio

Say you are a developer that is developing a custom support ticket application, and you recently published the latest version of the site to your test environment. You ​receive a call from the helpdesk saying that the system appears to be working fine except for one specific ticket. Every time they attempt to open that ticket, they get a rather unfriendly error message.

Certainly, there is a problem with how the application logic is handling the data, but it will be tough to diagnose what that issue is without knowing more about the database call your application is making and what data, if any, it is returning. This article is about using Extended Events in SQL Server Management Studio to monitor the activity on your server.

The Setup

For the purposes of this article, I created the database and tables below and populated them with test data.

create database TTLTestBed

 

create table technician(

id int identity (1,1),

name nvarchar(255)

)

 

–names from http://www.generatedata.com/

insert into technician(name) values(‘Lyle E. Oconnor’)

insert into technician(name) values(‘Wendy W. Wilder’)

insert into technician(name) values(‘Thomas B. Olsen’)

insert into technician(name) values(‘Jemima U. Price’)

insert into technician(name) values(‘Reed N. Whitehead’)

insert into technician(name) values(‘Shafira P. Harding’)

insert into technician(name) values(‘Stacey U. Byers’)

insert into technician(name) values(‘Daryl X. Parker’)

insert into technician(name) values(‘Jelani A. Irwin’)

insert into technician(name) values(‘Hilda Y. Atkinson’)

 

create table ticket(

id int identity(1,1),

created datetime,

lastmodified datetime,

assignedto int

)

 

declare @row int;

declare @startDate datetime = ‘2014-01-01’;

declare @endDate datetime = ‘2014-12-31’;

declare @created datetime;

declare @lastmodified datetime;

declare @techniciancount int;

declare @assignedto int;

set @row = 0;

set @techniciancount = (select count(*) from technician)

while @row < 10000

begin

 

set @row = @row+1

set @created = dateadd(day, rand(checksum(newid()))*(1+datediff(day,@startDate, @endDate)),@startDate)

set @lastmodified = dateadd(day, rand(checksum(newid()))*(1+datediff(day,@created, @endDate)),@created)

set @assignedto = (select rand() * (@techniciancount)+1);

 

insert into ticket(created, lastmodified, assignedto)

values (@created, @lastmodified, @assignedto)

end

 

After that, I create this simple stored procedure to return the data from the two tables.

Create PROCEDURE [dbo].[GetTickets]

@id int = null,

@created datetime = null,

@lastmodified datetime = null,

@assignedto int = null

AS

BEGIN

SET NOCOUNT ON;

 

SELECT i.id, i.created, i.lastmodified, t.name

from ticket i

left join technician t

on i.assignedto = t.id

where (@id is null or i.id = @id)

and (@created is null or i.created = @created)

and (@lastmodified is null or i.lastmodified = @lastmodified)

and (@assignedto is null or i.assignedto = @assignedto)

END

I created a very basic asp.net website that does little more than call the stored procedure above. The .net code is not especially relevant here, since the point of this article is to figure out what sql statements or procedures are being executed with what parameters without having to know much of the calling application. To that end, we will launch SQL Server Management Studio 2012.

Extended Events made their debut in SQL Server 2008, but there was no GUI for them. If you want to, you can still manually write SQL statements for Extended Events, but the GUI in Management Studio definitely makes the process easier. An article on Microsoft’s blog talks about this in detail and gives a good overview of the Management Studio interface. We will get a bit more specific here, and look at a specific application of Extended Events.

Getting Started

Create a new Extended Events Session in Management studio by expanding the server, then navigating to Management->Extended Events->Sessions. Right click the folder and select New Session …

You will start on the General Tab of the Session Properties screen below. Give your session a name, then click Events to add events you want to monitor. There are a few other things we could do on this page, such as automatically start the session once created and use a template to define events for us, but for learning purposes, I am sticking with the manual approach here.

The events page is pretty overwhelming, so you will probably want to spend some quality time with the SQL Server Event Class Reference. Since we want to know what statements are being executed on our servers, we will want to know more about the Stored Procedures and TSQL Event Categories. I chose to monitor the four events below, but your application may throw different events. This is definitely an area where you will want to experiment. You could cast a wide net and do a whole bunch of events (like SQL Profiler did for its templates), but keep in mind each event you monitor adds to the performance impact of your Events Session.

Once you have added your events, click Configure to refine how you are monitoring the events you selected.

For our example, we will want all four events to return the same fields and have the same filters applied to them. Shift-clicking the events on the left applies the configuration options to all four events at once.

This tab contains additional information that will be returned for each row of monitoring data. For my example, I selected client_app_name, client_hostname, database_name, sql_text, and username, but you may have other data you would rather see.

Once you are done selecting global fields, click Filter (Predicate) to apply filters to your data.

Ideally, your filters should be very specific. Even if you restrict your events to a handful as we did above, and on a test environment, it is astounding how many events fly around on a SQL box. If you do not want to generate thousands of rows of monitoring data, use filters. Below I am using a basic filter on the database name to make sure I am not getting any traffic from other databases that may be on this server. Since I am on a test box, I am able to do this, but you will want to consider applying additional filters in a real-world scenario.

Storing/Saving Data

Once you are done configuring your events, click Data Storage on the left to choose where and how your session data is saved.

I am going to use a simple event file to store the data. It defaults to directory local to the SQL Server installation and has an XEL extension. You can also place limits on file growth here, but since we will be turning the Session on, performing a task, then shutting it off again, the limits here should not be much of a concern.

Starting the Session

When you are done here, click OK to create the session.

Notice that your session has a red downward-pointing arrow for its icon. This indicates that the session is not started. To start your session manually, right click it and select Start Session. Once it is running, Watch Live Data is enabled. Right click your session again and select Watch Live Data to get a constantly refreshing list of captured events from your session.

Once you start your session, you will want to replicate the activity that revealed the data problem in the first place. In our original example, that would mean running the ticket system and trying to open the ticket that was causing the error. Once you have completed this activity, you can go ahead and shut off your Session in Management Studio.

The Result

What you will get, either by having Live Data on or by double clicking the event file under your session, is a list like the one below. If you select an event, you will see beneath it a Details tab that gives more information about that event, including the fields you set in the Global Fields (Actions) tab under Events for your session. If you double click the Statement line, you will open a window that shows you the sql statement(s) that were executed.


This information is the prize: not only do you see the statements that were executed, in this case a stored procedure; you also see the parameters that were sent to it from the originating application. Now you know with certainty what data is actually being passed to your database. This information is relatively easy to obtain and incredibly useful when you need it.

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770