TechNet WebCast: SQL Server Locking & Blocking

Next Monday, I’m presenting a TechNet WebCast for Microsoft Thrive Live! The subject: SQL Server Locking & Blocking Made Simple. Here are the details:

Event Overview

A good working knowledge of how Microsoft SQL Server makes use of locking and transaction isolation levels can help you greatly improve an application’s performance. In this webcast, we explore the SQL Server locking methodology and share techniques for enhancing query response times.

Language(s): English
Product(s): Microsoft SQL Server
Audience(s): IT Generalist
Duration: 60 Minutes
Start Date: Monday, December 07, 2009 9:00 AM Pacific Time (US & Canada)
Event ID: 1032432983

I’d love to have you join me for this free session. Click here to register.

Indentifying Performance Bottlenecks presentation

heartmonitor-2009-11-13c

Thanks to everyone who attended the State of Tennessee’s .NET User Group meeting yesterday. I really enjoyed spending time with you and learning about how you use SQL Server and some of the challenges you face.

As promised, here is a copy of the presentation materials that I used during the session.

Speaking at the .NET User Group meeting

cogs2009-11-10When a SQL Server application is under performing, how can you isolate where the actual problem is? Would more memory help? What about adding additional processors? Or maybe some indexes are missing?

At the Tennessee State Employee’s .NET User Group meeting this Thursday (November 12th, 2009), I will demonstrate how SQL Profiler and Performance Monitor can be used to uncover your SQL Server’s bottleneck.

If you’d like more information about this meeting, just send me an email. I hope to see you there.

 

Running a Server Side Trace

This is part two of three part series on creating, running, and scheduling Server Side Traces in SQL Server. Part I, Scripting Server Side Traces, was posted on August 31, 2009. Part III will be posted soon.

SQL Server Profiler can bring your production server to a screeching halt

In a prior post entitled Scripting Server Side Traces I lauded the laurels of SQL Server Profiler as a wonderful tool to use in development and QA environments to really see what’s happening inside your SQL Server. It’s a great way to shine a spotlight into the nooks and crannies of your database server. But try to use it on a production server and you’re likely to notice some unpleasant performance degradations. My warning to you was to use with caution on production servers. It’s better to use Server Side Traces and I demonstrated how SQL Server Profiler can be used to script out the Server Side Traces for you. If you haven’t already read that blog post, it may be worth while to go back and read it before continuing on with this one.

In this, the second of a three part series on Server Side Traces, we’ll examine the trace script to see what it’s doing. Then I’ll show how you can start and stop Server Side Traces using T-SQL procedures.

Modifying the script

We’ll start with a relatively simple Server Side Trace script that was generated for us by SQL Server Profiler. The script can be seen below. It’s rather long, but we’ll break it down into just a few sections that can be easily digested.

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 08/27/2009  08:59:12 AM         */
/****************************************************/

–- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

–- Please replace the text InsertFileNameHere, with an appropriate
–- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
–- will be appended to the filename automatically. If you are writing from
–- remote server to local drive, please use UNC path and make sure server has
–- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL
if (@rc != 0) goto error

–- Client side File and Table cannot be scripted

–- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
exec sp_trace_setevent @TraceID, 27, 8, @on
exec sp_trace_setevent @TraceID, 27, 32, @on
exec sp_trace_setevent @TraceID, 27, 56, @on
exec sp_trace_setevent @TraceID, 27, 64, @on
exec sp_trace_setevent @TraceID, 27, 1, @on
exec sp_trace_setevent @TraceID, 27, 9, @on
exec sp_trace_setevent @TraceID, 27, 41, @on
exec sp_trace_setevent @TraceID, 27, 49, @on
exec sp_trace_setevent @TraceID, 27, 57, @on
exec sp_trace_setevent @TraceID, 27, 2, @on
exec sp_trace_setevent @TraceID, 27, 10, @on
exec sp_trace_setevent @TraceID, 27, 26, @on
exec sp_trace_setevent @TraceID, 27, 58, @on
exec sp_trace_setevent @TraceID, 27, 66, @on
exec sp_trace_setevent @TraceID, 27, 3, @on
exec sp_trace_setevent @TraceID, 27, 11, @on
exec sp_trace_setevent @TraceID, 27, 35, @on
exec sp_trace_setevent @TraceID, 27, 51, @on
exec sp_trace_setevent @TraceID, 27, 4, @on
exec sp_trace_setevent @TraceID, 27, 12, @on
exec sp_trace_setevent @TraceID, 27, 52, @on
exec sp_trace_setevent @TraceID, 27, 60, @on
exec sp_trace_setevent @TraceID, 27, 13, @on
exec sp_trace_setevent @TraceID, 27, 6, @on
exec sp_trace_setevent @TraceID, 27, 14, @on
exec sp_trace_setevent @TraceID, 27, 22, @on
exec sp_trace_setevent @TraceID, 189, 7, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 55, @on
exec sp_trace_setevent @TraceID, 189, 8, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 56, @on
exec sp_trace_setevent @TraceID, 189, 64, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 41, @on
exec sp_trace_setevent @TraceID, 189, 49, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 26, @on
exec sp_trace_setevent @TraceID, 189, 58, @on
exec sp_trace_setevent @TraceID, 189, 66, @on
exec sp_trace_setevent @TraceID, 189, 3, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 51, @on
exec sp_trace_setevent @TraceID, 189, 4, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 52, @on
exec sp_trace_setevent @TraceID, 189, 60, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 122, 7, @on
exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 64, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 25, @on
exec sp_trace_setevent @TraceID, 122, 41, @on
exec sp_trace_setevent @TraceID, 122, 49, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 22, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 34, @on
exec sp_trace_setevent @TraceID, 122, 50, @on
exec sp_trace_setevent @TraceID, 122, 66, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 4, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 28, @on
exec sp_trace_setevent @TraceID, 122, 60, @on
exec sp_trace_setevent @TraceID, 122, 5, @on
exec sp_trace_setevent @TraceID, 122, 29, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

–- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

–- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

–- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Defining variables

The script starts off declaring some variables. You’ll want to make sure that the values you configured in SQL Server Profiler were exported correctly. In particular, make sure the @maxfilesize variable is set to the number of megabytes that you’d like for capture. In the example below, I’ve configured the trace to capture 500 MB of data.

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

Creating the trace

The next part of the script creates the trace using the sp_trace_create stored procedure. Here, as the comments indicate, you’ll want to provide the fully qualified path for the output file that you’ll use to store the captured data. In this example, I’m collecting it in a E:\demo\demotrace2.trc. Notice that the trc extension is automatically added for us; you don’t need to include it. If you do, you’ll end up with a file named demotrace2.trc.trc. That’s redundantly redundant.

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL

The sp_trace_create procedure also accepts some other parameters that are not automatically produced when you export from SQL Server Profiler. For example, you can specify the TRACE_FILE_ROLLOVER option and the @filecount parameter to allow the trace information to span multiple operating system files. Modifying the preceding stored procedure call as shown below would allow the trace to rollover to subsequent files (the second parameter is 2), up to a maximum of five files (the last parameter is 5).

exec @rc = sp_trace_create @TraceID output, 2, N’E:\demo\demotrace2′, @maxfilesize, NULL, 5

You can also specify a time for the trace to end. This is the second to last parameter, which is NULL in the example above.

Notice that the @TraceId parameter is defined as an output parameter. When the sp_trace_create procedure is called, it creates the new trace and returns to us a unique number that we can use to refer to the new trace. We’ll use the @TraceId variable throughout the rest of the script to update the trace properties.

For more information, visit Books Online sp_trace_create.

Specifying the events

The next section of the script defines the events that the trace will capture. The sp_trace_setevent stored procedure is executed repeatedly to define each and every event we’ve asked to see. If you’re monitoring a lot of events, this section is rather tedious to look at. For more information, see sp_trace_setevent in Books Online.

–- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on

Filtering the events

Filters are set using the sp_trace_setfilter stored procedure in the next section. This is where we can narrow down our trace to just one database or to queries that take longer than some threshold we’ve set. On a SQL Server instance with lots of databases, you’ll definitely want to set some filters. For more information on filtering traces, visit sp_trace_setfilter in Books Online.

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

Starting and stopping the trace

Once the trace has been fully defined within SQL Server, all we have left to do is to start the trace. We do that by setting its status using the sp_trace_setstatus stored procedure.

– Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

There are two parameters that we must supply to the sp_trace_setstatus stored procedure. The first is the trace that we wish to modify; this was the output of the sp_trace_create procedure at the beginning of the script. The second is that status of the trace.

A value of 0 for the status stops the trace. 1 starts the trace. 2 gets rid of the trace from the server. See Books Online for information about the sp_trace_setstatus stored procedure.

Next up: scheduling Server Side Traces

trainschedule_2009-09-14Alright! Now that we have a pretty good understanding of what this script does and how to manually start and stop Server Side Traces, next time we’ll look at ways to automate the trace.  Scheduling the trace will make it easy for us automatically collect data as  at various points throughout the business day and week.  This information will prove invaluable in helping us to benchmark our systems.

Until next time,

Joe

Scripting Server Side Traces

If you’ve never used SQL Server Profiler, you truly don’t know what your missing. And that statement has an intentional double meaning. You don’t know what a powerfully informative tool you’re missing, and you really don’t know what’s going on inside your SQL Server box either. Once you try it and figure it out, it’ll be like getting a new birthday present. Or finding $20 in your pants pocket when you wash your jeans.

Using SQL Profiler

There are plenty of tutorials online to help get you started using SQL Profiler. Books Online is a great place to start. It provides a good introduction as well as showing you the permissions required to run SQL Server Profiler. Brad McGehee, who probably knows as much about it as anyone, has written a book dedicated to the subject; it’s available as a free pdf download. And SQLServerPedia and JumpStartTv both have video tutorials so you can watch someone else show you how it’s done.

But Performance was bad before…

SQL Server Profiler can be great on development and test systems, but it only takes a time or two of running SQL Server Profiler on a production server before you learn the hard way that the SQL Server Profiler can have a significantly negative impact on performance. When you fire up the trusty tool, it doesn’t take long before your phone starts ringing. If you thought performance was an issue before, you ain’t seen nothin’ yet.

A disgruntled user calling

In speaking engagements on identifying performance bottlenecks, I often refer to Linchi Shea’s eye-opening work on quantifying the performance impacts fo SQL Server Profiler. His blog post on the subject is clear and convincing. It puts numbers on something that we knew anecdotally.

But what is really noteworthy in the blog is the impact, or really the lack of impact, that Server Side Traces have on performance. I won’t repeat his proof or findings here; go read it for yourself. But suffice it to say, you should use Server Side Traces and not SQL Server Profiler on production systems. Server Side Traces are very similar to SQL Server Profiler traces only without the overhead.

Configuring Server Side Traces

Okay, okay, Server Side Traces are the way to go. But have you seen the scripts required to set one of those up? They are ugly! Just look at the Books Online example!

Here’s another example. The script below creates a really simple Server Side Trace. It only captures a few events (Stored Procedures – RPC:Completed, SP:StmtCompleted and TSQL – SQL:BatchCompleted) for the AdventureWorks and AdventureWorksDW databases, saving the output to a local drive. It’s not quite Assembler, but it’s not that fun to write either.

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100

– Create the trace
exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace’, @maxfilesize, NULL
if (@rc != 0) goto error

– Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

– display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Here is a list event ids and filter options for Server Side Traces. Fortunately, we don’t have to create all this by hand. We can return to our beloved SQL Server Profiler and have it do the heavy lifting for us.

Turning SQL Server Profiler traces into Server Side Traces

Once you’ve configure a trace in SQL Server Profiler, you can export the trace definition. Click on File | Export | Script Trace Definition and select the version of SQL Server you have. I’m using the version of SQL Server Profiler that came with SQL Server 2008 and I have the option to script the trace definition for SQL Server 2005-2008. If you’re using SQL Server 2000, you have your own special option for their trace definition.

To see the fruits of our efforts, let’s look at the script file to see the trace definition in T-SQL. As you can see, it’s all there.

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 08/27/2009  08:59:12 AM         */
/****************************************************/

– Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500

– Please replace the text InsertFileNameHere, with an appropriate
– filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
– will be appended to the filename automatically. If you are writing from
– remote server to local drive, please use UNC path and make sure server has
– write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace2′, @maxfilesize, NULL
if (@rc != 0) goto error

– Client side File and Table cannot be scripted

– Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 27, 7, @on
exec sp_trace_setevent @TraceID, 27, 15, @on
exec sp_trace_setevent @TraceID, 27, 55, @on
exec sp_trace_setevent @TraceID, 27, 8, @on
exec sp_trace_setevent @TraceID, 27, 32, @on
exec sp_trace_setevent @TraceID, 27, 56, @on
exec sp_trace_setevent @TraceID, 27, 64, @on
exec sp_trace_setevent @TraceID, 27, 1, @on
exec sp_trace_setevent @TraceID, 27, 9, @on
exec sp_trace_setevent @TraceID, 27, 41, @on
exec sp_trace_setevent @TraceID, 27, 49, @on
exec sp_trace_setevent @TraceID, 27, 57, @on
exec sp_trace_setevent @TraceID, 27, 2, @on
exec sp_trace_setevent @TraceID, 27, 10, @on
exec sp_trace_setevent @TraceID, 27, 26, @on
exec sp_trace_setevent @TraceID, 27, 58, @on
exec sp_trace_setevent @TraceID, 27, 66, @on
exec sp_trace_setevent @TraceID, 27, 3, @on
exec sp_trace_setevent @TraceID, 27, 11, @on
exec sp_trace_setevent @TraceID, 27, 35, @on
exec sp_trace_setevent @TraceID, 27, 51, @on
exec sp_trace_setevent @TraceID, 27, 4, @on
exec sp_trace_setevent @TraceID, 27, 12, @on
exec sp_trace_setevent @TraceID, 27, 52, @on
exec sp_trace_setevent @TraceID, 27, 60, @on
exec sp_trace_setevent @TraceID, 27, 13, @on
exec sp_trace_setevent @TraceID, 27, 6, @on
exec sp_trace_setevent @TraceID, 27, 14, @on
exec sp_trace_setevent @TraceID, 27, 22, @on
exec sp_trace_setevent @TraceID, 189, 7, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 55, @on
exec sp_trace_setevent @TraceID, 189, 8, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 56, @on
exec sp_trace_setevent @TraceID, 189, 64, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 41, @on
exec sp_trace_setevent @TraceID, 189, 49, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 26, @on
exec sp_trace_setevent @TraceID, 189, 58, @on
exec sp_trace_setevent @TraceID, 189, 66, @on
exec sp_trace_setevent @TraceID, 189, 3, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 51, @on
exec sp_trace_setevent @TraceID, 189, 4, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 52, @on
exec sp_trace_setevent @TraceID, 189, 60, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on
exec sp_trace_setevent @TraceID, 122, 7, @on
exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 64, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 25, @on
exec sp_trace_setevent @TraceID, 122, 41, @on
exec sp_trace_setevent @TraceID, 122, 49, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 22, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 34, @on
exec sp_trace_setevent @TraceID, 122, 50, @on
exec sp_trace_setevent @TraceID, 122, 66, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 4, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 28, @on
exec sp_trace_setevent @TraceID, 122, 60, @on
exec sp_trace_setevent @TraceID, 122, 5, @on
exec sp_trace_setevent @TraceID, 122, 29, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

– Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

– Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

– display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

This looks remarkably similiar to the one I showed earlier in the post. I used SQL Server Profiler to create that one for me, too.

Next Up: running and scheduling Server Side Traces

Whew! This blog has almost exceeded our collective attention spans, so we’ll stop here for now. But in the next post, I’ll show you how to run Server Side Traces and even schedule them to run automatically so you can capture benchmarking data.

Until then, I’d love to hear your experiences with SQL Server Profiler and Server Side Traces. Got some good scripts that you don’t mind sharing? Post or link to them in the comments section below.

Joe

The effects UNION in a SQL query

In a recent blog, I shared some of the natural but sometimes unanticipated consequences of adding DISTINCT to a SELECT statement. Adding DISTINCT ensures that you don’t have duplicated rows in your result set, but that assurance comes at a price. Performance is typically impacted negatively when you add DISTINCT to a query. You can read the blog and see the proof here.

But there is another operator that can have some not-so-obvious side affects. Let’s a have look how UNION can affect a query’s performance.

A brief review

The UNION operator combines the results from two completely separate queries into a single result set. For example, let’s say you want to retrieve all customers and all employees in a single list. You could individually issue two separate queries and then combine the results in the presentation layer or in Microsoft Excel to produce your list.

But you can also let SQL Server do the work for you. For example:

SELECT FirstName, LastName
FROM DimCustomer
UNION
SELECT FirstName, LastName
FROM DimEmployee

There are a couple of requirements for using the UNION operator. Each of the two queries that are combined using the UNION operator must have the same number of columns. Furthermore, the columns must be of compatible datatypes. You can read more about the UNION operator on MSDN.

Running with UNION

So, how can this be bad? It’s just running two queries and concatenating the results, right? Let’s have a look at the query execution plan to see what’s going on behind the scenes.

As you can see from the query and execution plan in the graphic above, each of the two queries are executed separately and then concatenated together into one result set. But notice that there’s another operation before results are returned; there’s a Distinct Sort operation which accounts for 61% of the overall cost of the query. What’s that doing in there?

When you use UNION to combine the results from two queries, SQL Server ensures that the combined result set does not contain any duplicated rows. Or put another way, if one of the rows in the first query is identical to a row produced in the second query, only one of the two rows will be returned in the results.

This sounds a bit confusing or at least wordy. So let’s consider our example again. If an employee had purchased something from our company, that person would be an employee and a customer. So each of the two queries in our example would return a row for that person. Using UNION, however, that person would only be listed once in the combined result set. UNION returns distinct rows.

Does this sound familiar? It should; this is essentially the same thing that the DISTINCT keyword does in a SELECT statement.

Before moving on, let’s look at the subtree cost for the entire query. The following graphic shows a subtree cost of 2.01387.

Running with UNION ALL

What if we don’t care about duplicates in our result set? What if we know that none of our employees would ever buy one of our products? Or, what if we are going to handle the duplicates in the presentation layer? Can we optimize the query so that the Distinct Sort doesn’t occur?

Sure. Using UNION ALL rather than UNION tells SQL Server to skip the Distinct Sort step.

By changing the query ever so slightly, using UNION ALL rather than UNION, we tell SQL Server that we don’t mind if there are duplicates in the result set and that it’s ok to skip the Distinct Sort.

What does this do to the over all cost of the query? Let’s look at the new subtree cost for the SELECT statement.

The overall cost decreased to 0.77873 from 2.01387; that’s an appreciable amount, particularly if this query is frequently run throughout the day.

Do you regularly use UNION in your queries? If so, I’d love to hear your experiences with UNION vs UNION ALL and how it’s affect performance.

Joe

The effects of DISTINCT in a SQL query

Earlier today, I had a great Twitter conversation with Tim Mitchell (@Tim_Mitchell), Jorge Segarra (@SQLChicken), and Jack Corbett (@unclebiguns) about the use of DISTINCT in a SQL Server query. By the way, this is yet another example of how Twitter can be used in a good and positive way within the work environment and within the SQL Server Community; Twitter is not just a time sink that squeezes productivity out of your day as some claim.

Many people (not those mentioned previously) resort to using the DISTINCT keyword in a SELECT statement to remove duplicates returned by a query. The fact that the resultset has duplicates is frequently (though not always) the result of a poor database design, an ineffective query, or both. In any case, issuing the query without the DISTINCT keyword yields more rows than expected or needed so the keyword is employed to limit what is returned to the user.

So what’s the problem? Is using DISTINCT that really bad? Why not just type the 9 additional keystrokes that will save us a lot of hard work? (8 letters and a space. You thought I miscounted, didn’t you?)

Running without the DISTINCT keyword

To answer that question, let’s have a look at a quick and simple example. Let’s consider the following query run against the AdventureWorks database.

withoutdistinct

As you can see from the query and its execution plan above, SQL Server resolves this query using a Clustered Index Scan, aka a table scan. Generally speaking, table scans are not something you want to see in your query plans but in this case it makes sense. I’m asking for 4 columns from all rows in the table.

Now let’s look at the cost of the subtree below. We see that the estimated subtree cost for the SELECT statement is 0.438585.

Looking at the execution time information from SET STATISTICS TIME ON we see the following information.

SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 586 ms.

Running with the DISTINCT keyword

Now, let’s type those 9 additional keystrokes, adding the DISTINCT keyword into the query as shown below. Before we run this, we’ll clear out our buffers and procedure cache to make sure we’re comparing apples to apples.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

NOTE: Do not run the aforementioned DBCC commands on a production server. If you do, your phone will ring, your pager will go vibrate, your users will have a hard time forgiving you, and performance will slow to a crawl for a little while.

withdistinct

A quick examination of the query plan reveals that a table scan is still being used to retrieve the data from the table. That’s to be expected. However, a new node has been added to the plan. A Sort node. This node is responsible for 75% of the total cost of the new query!

Looking at the node cost for the SELECT statement, we see that the cost shot up to 1.75954 from 0.438585. That’s a significant difference.

And finally comparing the time it took to resolve this query with the prior version, we can see that the total CPU time increased too.

SQL Server Execution Times:
CPU time = 78 ms,  elapsed time = 655 ms.

An exception to every rule

So, generally speaking the addition of DISTINCT to a SELECT statement will increase the workload for SQL Server. But this is not always the case. If a unique index exists that can guarantee that each row will indeed be distinct, the Sort operation can be skipped and the results immediately returned.

For example, consider the following trivial query as an example.

And compare it to the following query that includes the DISTINCT keyword.

Notice that the query plans are identical for these two queries. Why? A unique index exists on the ContactID column which guarantees that the rows will be distinct.

But wait, there’s more

These are really trivial examples of how DISTINCT can make a difference in a query plan and thus the performance of a query. In real life, very few queries are this simple. But I hope that these examples will serve to illustrate that DISTINCT does add an addtional load on the SQL Server. So, if you can do a little work up front by rewriting your query, you can avoid having use DISTINCT in your query. SQL Server will thank you for it.

For more information how a unique index can help performance, have a look at Rob Farley’s blog post entitled “Unique Indexes with GROUP BY“. It’s a good read.

Have you resorted to using DISTINCT to limit the results of a query? I’d love to hear of your experiences. Or if you’ve found an better alternative, please share.

Joe

Follow

Get every new post delivered to your Inbox.

Join 36 other followers