Rusty Divine

Live, Love, Learn, Teach

SQL Server Profiler Templates and How To Isolate My Database Calls from Others on my Team

Sometimes a time-saving feature is right under your nose for years. One example for me was the ability to save my SQL Server Profiler configuration and set it as the default template. I hope you find it useful!

Selecting a Base Template

After opening Profiler and connecting to a database a “Trace Properties” window will present itself for entering the trace details. To make a custom template, select any of the templates form the “Use the template” drop down and give it a Trace name like “{My Database} Standard Trace”.

When I run profiler, I am almost always just checking for excessive database calls (especially n+1 selects) or troubleshooting TSQL or stored procedure calls. Sometimes I’m running the trace on a development server that has multiple databases or users and I want to isolate it to just my calls. For me, “TSQL_Duration” a good starting point for a standard template.

image

Configuring Events and Filters on the Template

Next, click on the Events Selection tab to configure events to listen for:

image

Be sure to check the “Show all columns” checkbox. This is important because if the column isn’t visible in this events grid, then you can’t filter by it in the Column Filters tool.

Next, click on the Column Filters button and filter out any events from other databases on this database server (use the percent sign % as a wildcard here):

image

Occasionally I will also change the login name in my app’s connection string so that I can isolate just my connections by adding a filter on the “LoginName” column.

Click the Run button to try it out. To make changes to the columns and filters, be sure to stop the trace first, then click on the properties icon in the tool bar.

Saving and Exporting a Template

Once satisfied with the trace template, select File > Save As > Trace Template and enter the template name like “{My Database} Standard Trace”. The next time you start a trace, it will be in the drop down list of templates. You can also select File > Templates > Export Template to save it to a file and share it with your team.

Setting a Template to the Default Template

Setting the default template will save a few clicks each time you use Profiler. Select File > Templates > Edit Template. Choose the template from the drop down list, and then check “Use as default template..”

image

Final Thoughts and Other Options

A good code review should also check out the traffic hitting the database for the code under review. It is so easy to stop coding once something “works” and not consider how it will scale. Checking in with profiler is especially important if your project is using an ORM with lazy loading turned on.

I would not recommend running SQL profiler on a production database because it takes a significant amount of resources. If you do need to do something like this, consider whether there is another tool that would be safer.

I would also encourage you to consider the following tools in your web project:

blog comments powered by Disqus