Systems Engineering and RDBMS

SSMS – Tracing a Session in Profiler

Posted by decipherinfosys on November 24, 2009

SQL Server management studio in SQL Server 2008 has a neat little feature called “Trace Query in SQL Server Profiler”.   Using this option, one can start a profiler trace right from SSMS and the trace that gets started is already filtered on the spid of that particular session.  This option is present under the Query Option of the Menu:

As you can see, it also has a short cut key: Ctrl + Alt + P.  When you select this, a profiler window will open and if you look at it’s properties and then the filter, you will see that it has put a filter on your specific SPID for you:

The very next question then that comes to mind is what events is it tracking and what columns is it using?  The default template that it uses for the events and columns is “TSQL_SPs” – you can see this by clicking on properties of the trace that it started for you.  This is one of the templates that comes with the install.  You can modify this template and customize it to your needs.  All you need to do is open it up from Profiler and go to File\Templates\Edit Templates, make the changes and save it.

This is a pretty handy feature and can help a great deal when tuning SQL code in SSMS.  One can get all the detailed information at the click of the button now.

Resources:

  • SQL Server MSDN profiler reference – here.
About these ads

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 74 other followers

%d bloggers like this: