By: Ben Snaidero
Overview
As was mentioned in the best practices topic earlier in this tutorial, you can create templates for your SQL Profiler trace sessions which allows you to save the events and data columns you've configured into a template. This topic will guide you through the process of creating and saving template and show you how you can reuse this template for a future SQL Profiler trace session.
Explanation
Creating a Template via SQL Profiler
Like everything else in SQL Profiler there are a couple different ways you can create templates. For the first method let's assume we are simply creating a SQL Profiler trace like we have in earlier topics. We'll start by selecting the "Standard (default)" template, but for this case when we get to the "Events Selection" tab we'll make some changes to the events we are going to configure. Let's remove the BinaryData column and add the RowCount, Hostname and DatabaseName columns. We'll also add the Deadlock graph event. These updates can be seen in the screenshot below.
Once your updates are complete and you've started your trace you can go to the menu and select "File-Save As-Trace Template" as shown below.
After you select this menu item you are presented with the dialog box below where you can give your template a descriptive name. For this case let's call this one "Standard with Deadlock" since we added the deadlock event to the standard trace template.
Click okay and if everything worked as expected you should see the following success message.
Create a SQL Profiler Template from Scratch
The second method for creating a template is by using the menu item "File-Templates-New Template" as shown below.
After selecting this menu option you are presented with the "Trace Template Properties" window. The "General" tab shown below is where you can enter a template name and, as we did above, select an existing template to base this template on. Notice it also give you the option to make this template the default when you start SQL Profiler. Below is a screenshot of this tab with all the information entered. The other tab in this window is the "Events Selection" tab which I won't go into any detail here as we've seen this tab many times before. Once you are done configuring your template click save and your template will be saved onto the file system.
User Defined Profiler Templates vs. Standard Templates
Now if we start a new trace session and expand the template dropdown we should see both of the templates we created above. The screenshot below shows this is indeed the case. It's also easy to tell which templates you created as the template name is suffixed with " (user)".
There is however one issue with these templates we've created. The way they are saved, we can quickly setup a trace session in SQL Profiler with them, but since they are actually stored with our profile other users are not able to use them. In order for everyone to be able to use these templates we need to copy them to the following folder, C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Profiler\Templates\Microsoft SQL Server\130 (note: Your path may differ depending on your installation and version of SQL Server). Once the .tdf file is copied there and we start a new trace session we should see our templates without the " (user)" suffix as show below.
Additional Information
- Creating a Trace Template in SQL Server Profiler
- Complete documentation on templates including descriptions of all predefined templates
Last Update: 3/3/2016
