Systems Engineering and RDBMS

SQL Server Profiler – Basic Concepts

Posted by decipherinfosys on January 24, 2009

We have used SQL Server Profiler in several of our posts before and have included the enhancements done to this wonderful utility in SQL Server 2005.  A recent question that was asked by one of our clients was to put together 2-3 training lunch and learns for them to help them understand how to make use of this utility.  In this series of posts on SQL Server profiler, we will cover some of those concepts that we covered in the training for our client.

First thing to understand is that SQL Server Profiler is comprised of two parts -

a) Client GUI Application.  This is what you see and most of the folks have experience with this part of the profiler only.

b) Server application.  This is also called as SQL trace and all of the actual work is done by it.  What you do using the GUI on the profiler client utility actually uses this code.  And one does not just need to use the profiler GUI to use SQL Trace – it can be initiated by using system stored procedures or by custom code which uses SMO (Server Management Objects).

So, think of SQL Trace as a trace utility that captures the communication between the client applications and SQL Server instance and it’s databases.  One can put filters and select specific events and data columns that one wants to use to restrict the output and get exactly what one needs.  And once the trace data has been collected, that can be saved to a database table so that one can use T-SQL to query against that data or save it as a XML file or as a profiler file (proprietary format).

As you might have already seen in the GUI application of profiler, there are event classes & events, data columns and filters – these are the three main pieces of terminology to understand before we get cranking out profiler templates.  Otherwise, you will be getting so much data by making poor choices of events and data columns that the analysis will be very cumbersome and sometimes not even useful.  Information (Data) is useful but only when you can separate the noise from actual meaningful data and can do proper analysis on it.  Before we start, get yourself familiarized with all the profiler terminology – it is succinctly mentioned here.

Since wading through the GUI or BOL for finding out which event classes contain which events and which columns are then available within those events is a cumbersome process, one can simply use T-SQL to get to that information.

Example: To see which trace event classes contain what events, you can simply use this T-SQL:

select v2.name as Event_Class, v1.name as event_name
from
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
order by v2.name
GO

If we want to now, take it one step further and want to see which data columns are available within those events as well as a list of named column values, we can do this:

select
v2.name as Event_Class
, v1.name as event_name
, v4.name as data_column
, case v4.is_filterable when 0 then ‘No’ else ‘Yes’ end as Is_Column_Filterable
, v5.subclass_name
, v5.subclass_value
from
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
inner join sys.trace_event_bindings as v3
on v1.trace_event_id = v3.trace_event_id
inner join sys.trace_columns as v4
on v3.trace_column_id = v4.trace_column_id
inner join sys.trace_subclass_values as v5
on v3.trace_event_id = v5.trace_event_id
and v3.trace_column_id = v5.trace_column_id
where v1.name like ‘%DeadLock%’
order by v2.name, v1.name
GO

Over here, we filtered the events based on the event names containing the word “Deadlock”.   Now that we know what event classes we have available, what events we have within those classes and what data columns are available for us to filter by – this SQL serves as a quick way to form a plan to set a profiler template in place.  In subsequent posts in this series, we will see how we can use the GUI to capture information for the different scenarios, how we can do the same using just system stored procedures, how we can perform stress tests using profiler and much more.  Stay tuned…

About these ads

2 Responses to “SQL Server Profiler – Basic Concepts”

  1. Here is how you can use SQL Server Profiler in SQL Server 2005.

    http://sqlservernation.com/blogs/tipweek/archive/2009/05/27/introduction-to-sql-server-profiler.aspx

  2. [...] provides all the pertinent information) – using the same SQL that we had used in our Profiler basics post, we can change the filter condition to see which event classes and which event name and data [...]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

 
Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: