SQL Server Profiler Primer

4 minute read time.

Hello all.  In this blog I will provide an overview of an invaluable tool that should be in your toolkit. This tool has been packaged with SQL Server for a long time.  The tool is SQL Server Profiler. 

SQL Server comes with a great tool to help troubleshoot different types of SQL related issues and to collect metrics like number of Reads, Writes, CPU utilization. The tool is SQL Profiler and comes with SQL Server.  Profiler is a GUI tool that has been part of SQL Server for a long time, I think since before SQL 2000. Its main purpose to capture activity sent to SQL Server to process. The activity can then be analyzed to determine where a certain problem occurs. There are filters that can be set to limit what is being captured.  The best use for the Profiler tool is when you can reproduce the problem. Lets take a look at some of the options of using this tool

How to launch Profiler
From a machine that has SQL Server Management Studio installed, go to your Windows Run command and type Profiler. Also, you can go to Start, Programs, Microsoft SQL Server 2017 (or your version), Microsoft Tools and select SQL Server Profiler

Then select File, New Trace...

How to log in
You will need to connect to a SQL Server instance you will profile.  The login account used to login must have sysadmin rights. You can log in using either Windows Authentication or SQL Server authentication. Select the Server Type to be Database Engine and the Server name against which you will be profiling. Select the Authentication type and enter the login credentials and click Connect

Trace Properties
The General tab has some properties that you will need to fill in. Give the Profiler trace file a name. In my example I called it "Trace name".  From the Use the template dropdown, select the Standard (default) template   Place a checkmark next to Save to File and provide a name and location for the trace file.  You can set a maximum file size of the profiler trace in megabytes (I used 2 gigs as an example)
I always uncheck Enable file rollover. This option will create another Profiler file as soon as the value in the Set maximum file size is reached.  If you do not change the default value in the Set maximum file size from 5 megabytes, there will be many many Profiler files created.  I would rather work with just one Profiler file.  If you do enable the rollover and multiple files are created, you will need to have all the files present when you open Profiler

Events selections

This tab will show the list of events based on the template you selected or the events you manually selected if you chose the Blank template option.
Click on the Column Filters button to bring up the Filters window

Start the Profiler trace
Once you have the options selected in the General tab and the Events and filters set, click on the Run button to start the Profiler trace. But before you start the Profiler trace, have the user get to the point in the X3 application where the next action they take, causes the problem. This will minimize the performance disruption on the production SQL Server (See the Tips section at the end)

Stop-Pause-Start the profiler trace
While the profiler trace is running, if you need to stop, pause, or start the trace, there are VCR type buttons to enable you to do that.

  • If you want to temporarily stop the trace, click the Pause button(vertical blue bars). This will stop capturing activity until you start it again. When you start the
    Profiler trace it will pick up where it left off.
  • If you want to stop the trace , click the Stop button(red square). If you start the trace after stopping it first the trace activity capture thus far will
    be cleared out.
  • To start the trace after stopping or pausing, click the Play button (green triangle)
  • If you want to clear the contents of the Profiler trace file, click the Clear trace window button (white eraser; see black frame below) If the Profiler 
    trace is running and you click this button, the contents of the trace file will be cleared but continue to capture subsequent activity.


Final Thoughts


The Profiler trace is going to cause performance to degrade slightly on the SQL Server that you are profiling. If possible, you should create and start your Profiler trace someplace other than the production SQL server machine. A Profiler trace can be started and pointed to the production server.

If the issue can be reproduced on a test environment, and it is exactly the same problem as what's being reported in production, use the test environment

Be mindful of capturing the all events you need, not necessarily all the events. The more events you include in the Profiler trace, the larger it becomes the more data you will need to sift through to find the problem statement(s) Try not to choose too few events either because then you won't have enough information and will need to run another Profiler trace.