Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!


Profiler and Server Side Traces  
(Tools)

Overview

SQL Server provides a great tool that allows you to see what statements are running on your SQL Server as well as collecting metrics such as duration, number of reads, number of writes, the machine that ran the query, etc... this tool is known as Profiler. 

Profiler is a GUI based tool that runs a SQL Server trace to capture the metrics listed above as well additional data.  This data can then be used to determine where your SQL Server performance issues are related to your TSQL code.  Running a trace without using Profiler is known as a Server Side Trace.  You can create and start the trace using TSQL commands instead of having to use the GUI.

Explanation

Most people begin using Profiler to run a trace, because the GUI is pretty easy to get a trace setup and running.  Once you understand the advantages of using a server side trace you will begin to use these more frequently unless you are troubleshooting an issue that is occurring at that exact time.

Profiler

The Profiler tool can be launched in one of these ways:

  • In SSMS, select Tools > SQL Server Profiler from the menu

  • You can also launch Profiler from the Windows menu
    launch Profiler from the Windows menu

Once you have Profiler launched you need to:

  • Connect to the SQL Server instance you want to trace
    Connect Profiler to SQL Server

  • A Trace Properties window will open and you can click Run to start the trace with the default settings
profiler setup

Here is an example of data that is collected:

profiler sample output

Selecting Events, Columns and Filters

Ideally you want to select only the Events you want to capture along with setting Filters to minimize the amout of data that is returned.  If you just use the standard settings you usually end up with too much information and it is hard to determine what is going on.

Under the Events Selection tab you can select events, columns and set filters as shown below.

profiler select events, columns and filters

Events

A good starting place is to capture just these two events.  These will show you all completed batches and metrics related to the batch.  A batch is basically a set of work, like a stored procedure, that contains mulieple statements.

  • Stored Procedures - RPC:Completed
  • TSQL - SQL:BatchCompleted

Columns

As far as columns go just select all columns and once you see the data that is captured you can reduce the amount of columns you are capturing.

Filters

Filters allow you to further define what is captured. To set filters click on Column Filters. So if you only want to capture data for a specific process you can filter on SPID as an example.  Another good starting point is to filter on Duration.  I like to set the value to 500 to only show statements that take 500ms or longer.  Again this is just a starting point.

setting filters in profiler

Once you have the settings you want you can run the trace.

Reading the Output 

After you have collected the output you can see which statements are returned based on the events and filters that you have set.  If you start with the two events above you can begin to take a look at the statements that have the longest duration and these are the statements that you should begin trying to tune to improve performance.

Additional Information

Here are some additional articles related to Profiler and Server Side Traces.



 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.