Optimization « WebbTech Solutions

Join me at the Memphis SQL Server User Group meeting

February 8, 2010 1 Comment

For almost a year now, I’ve exchanged emails with the leaders of the Memphis SQL Server User Group, trying to find a time when our schedules would align themselves and I’d be able to join them for a meeting. After many attempts and several near misses, we’ve finally set a date!

This Thursday, February 11, 2010, I will be speaking on a SQL Server Locking & Blocking. I’ll talk about:

Why databases lock resources
The consequences of blocking
How SQL Server manages locks
Techniques for influencing locking behavior
Using Transaction Isolation Levels

You can download the presentation materials here.

During my presentation, I’ll demonstrate how to analyze locks that SQL Server is holding using the sys.dm_tran_locks Dynamic Management View (DMV) as shown below.

–examine the resources
SELECT
resource_type
,(CASE
WHEN resource_type = ‘OBJECT’ THEN object_name(resource_associated_entity_id)
WHEN resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN ‘N/A’
WHEN resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN (
SELECT
object_name(object_id)
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id)
ELSE
‘Undefined’
END) AS resource_name
,request_mode as lock_type
,resource_description
,request_status
,request_session_id
,request_owner_id AS transaction_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> ‘DATABASE’;

If you live in or near Memphis, I hope you come out and join us for the meeting. I’d love to meet you. For more information, visit mem-pass.org.

Filed under Community, DMVs, Events, Optimization, SQLServerPedia Syndication

Speaking at the Baltimore SQL Server User Group

January 27, 2010 Leave a comment

On Monday, the first of February, 2010, I will be speaking at the Baltimore SQL Server User Group meeting at 7:00 PM EST. My topic will be SQL Server Locking and Blocking Made Simple. In it, I will discuss:

The need for locking
The consequences of blocking
How SQL Server locks resources
How blocking affects performance
Influencing locks with granularity hints
Setting Transaction Isolation Levels

Here is a sample slide from the presentation. You can download the entire presentation here.

During the presentation, I will regularly use the following script to view the locks being held by SQL Server.

–examine the resources
SELECT
resource_type
,(CASE
WHEN resource_type = ‘OBJECT’ THEN object_name(resource_associated_entity_id)
WHEN resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN ‘N/A’
WHEN resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN (
SELECT
object_name(object_id)
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id)
ELSE
‘Undefined’
END) AS resource_name
,request_mode as lock_type
,resource_description
,request_status
,request_session_id
,request_owner_id AS transaction_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> ‘DATABASE’;

I hope you’ll join me there.

If you’re interested in having me speak at your local user group, please send me an email or direct message me in twitter. I’d love to talk with you.

Filed under Community, DMVs, Optimization, SQLServerPedia Syndication, T-SQL

Locking Scripts

December 11, 2009 1 Comment

Thanks to everyone who attended my TechNet Thrive! and Nashville SQL Server User Group presentations this week! I hope it was worth your while. As promised, here is the presentation.

And here’s the script I used to review the locks.

Filed under Events, Optimization, SQLServerPedia Syndication, T-SQL

Locking & Blocking

December 8, 2009 2 Comments

On the gridiron, two opposing teams are contending for one very limited resource, the football. One team has it; the other team wants it. The team that has it, tries very hard to protect it by securing the football and blocking all others from getting to it.

The same can be said about SQL Server. When one process needs to access a resource, say a table, it secures the resource (locking) which prevents other processes from accessing it (blocking).

Locking in SQL Server is a good thing. Locking helps to maintain data integrity by ensuring that two different processes are not going to use a single resource in a way that is considered incompatible.

Blocking, a natural by-product of locking, is not so good of a thing.

If a resource is locked by one process and another process needs access to that resource, the second process will be blocked until the resource is released by the first process. The effect? The second process experiences a longer wait time and performance suffers.

But locking and blocking are two sides of the same coin.

Locking & Blocking Made Simple

Okay, if locking is good and blocking is bad, what can be done about locking? I’m glad you asked! Tomorrow, I’m speaking at the Nashville SQL Server User Group. In the session, I’ll cover those topics and more. Here’s the abstract:

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 session, we explore the SQL Server locking methodology and share techniques for enhancing query response times.

If you’re in the Middle Tennessee area, come on out and join us for the meeting. For more information on time, location, etc, visit the Nashville SQL Server User Group web site.

If you’re not in the Nashville area, I believe the meeting will be available via LiveMeeting provided by PASS. Stay tuned and follow me or NashSQL on Twitter for up to the connection information.

Thanks and I hope to see you there!

Filed under Community, Events, Optimization, SQLServerPedia Syndication

TechNet WebCast: SQL Server Locking & Blocking

November 30, 2009 2 Comments

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.

Filed under Events, Optimization, SQLServerPedia Syndication, T-SQL

Indentifying Performance Bottlenecks presentation

November 13, 2009 Leave a comment

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.

Filed under Community, Events, Optimization, SQLServerPedia Syndication, Tools

Speaking at the .NET User Group meeting

November 11, 2009 Leave a comment

When 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.

Filed under Events, Optimization, SQLServerPedia Syndication, Tools

Running a Server Side Trace

September 11, 2009 3 Comments

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

Alright! 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

Filed under Optimization, SQLServerPedia Syndication, T-SQL, Tools

Scripting Server Side Traces

August 31, 2009 3 Comments

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

Filed under Optimization, SQLServerPedia Syndication, T-SQL, Tools

The effects UNION in a SQL query

August 6, 2009 9 Comments

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

Filed under Optimization, SQLServerPedia Syndication, T-SQL

← Older posts

Newer posts →

WebbTech Solutions

Join me at the Memphis SQL Server User Group meeting

Speaking at the Baltimore SQL Server User Group

Locking Scripts