SQL Server Profiler

20 Feb

Profiler is a powerful GUI to help DBAs conduct traces of SQL traffic. Its a diagnostic tool mainly used to hunt down queries that taking too long and affecting system wide performance. Running the GUI comes with a minor performance impact, however the impact is exponential when the server is already under stress.

When you open the GUI first you will start a new trace by connecting to the instance you want to monitor. Of the predefined templates “tuning” is very valuable. You can take the tuning results of a trace and feed it into the Database Engine Tuning Advisor. This will suggest indexes and statistics based on the traffic you captured. Be sure to capture traffic representivtive of your users full workflow. Creating indexes can take up a lot of space and slow down all write activity.

The first option you have is where to save the output of the trace. File would create a .trc file somewhere on your filesystem that you can double click to open and view later. Table would save to a database of your choice. Table has the great advantages of being able to query and compile results of multiple traces. Just know that its actually writing data on your database server when you select this option and that comes with all the expected performance hits.

If you want to see what suggestions the DETA has to offer just pick the tuning template and fire away. If you start a trace you should be able to see it in the results of this query

select * from sys.traces

The first result is the system trace that always runs and any additional lines will be user traces.

If you need to get a closer look at what is running on your server you’ll have to pick certain events to watch. BatchCompleted and StatementCompleted are events that you will probably want to select. Also, in the columns, you’ll want to capture the effect of these events such as CPU/Duration/Reads etc… On this screen, you can also filter based on database so you don’t have to see all the system database activity.

Even after you choose these events you may not ever get quite what you expect out of the trace. When tracing production, queries are often represented how the sql driver requests it. Unless you application runs only adhoc queries you won’t get a simple

SELECT CustID FROM customers where un = 'test' and pw = 'hack'

What you will probably find is “test” and “hack” getting passed in as parameters in the profiler then you will have to locate the corresponding statement in the cache to actually reconstruct this query.

select sql from sys.syscacheobjects where dbid = '5'
Leave a comment

Posted by on February 20, 2011 in SQL Admin, SQL Dev


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: