Overview

What is this tool used for you may ask? It allows you to see virtually every operation that is going on within your SQL Server instance. You can use the tool to see who is connecting, stored procedures and SQL being executed, and just about anything else you could ever imagine wanting to inspect.

There are many scenarios where this tool is the perfect addition to your developer tool belt.

Real World Scenarios Legacy System Support Have you ever had to troubleshoot software you did not write? Or even worse, have you ever had to troubleshoot a system you have no source code for? Well, if there is a SQL Server database involved, there is no doubt that the profiler can come to the rescue in these kinds of scenarios.

I was once tasked with finding out why an application was so slow in one area. I knew virtually nothing about the code base at the time. Many layers of abstraction coupled with the fact that much of the application was asynchronous, made it very difficult to reproduce the issue while debugging.

As it turns out, I could run a trace using SQL Server Profiler and found that there were hundreds of queries being executed to check users’ roles and permissions each time the user clicked around the application.

LINQ to SQL, LINQ to Entities For many these days, LINQ to SQL or LINQ to Entity queries are commonly used with Microsoft’s Entity Framework to make database operations easier to code. If you have done much coding at all using these technologies, you have learned that how you craft your queries with LINQ makes all the difference in the world!

You can easily make simple careless mistakes and have queries execute multiple times when you would only want a given query to execute once. You can improperly perform a join operation, or mangle some lambda expressions and have extremely slow queries that are not apparent until you go to production and your system has a lot more data and users.

If you could only see the actual SQL that is being sent to the server and executed! Well … you can do that. Let us see how we can trace what is going on with SQL Server!

Start Tracing Open SQL Server Profiler The profiler can be started from Microsoft SQL Server’s start menu folders or from with SQL Server Management Studio (via ToolsSQL Server Profiler). If you do not have the profiler installed, restart your SQL Server installation and you should have the profiler as an installable option if you are using the Developer, Standard, or Enterprise edition of SQL Server.

Create New Trace A new trace will be started when you open the profiler. You can begin a new one also by clicking FileNew Trace in the profiler menu bar. Enter a name for you trace and select a template to use. The Standard (default) template usually works well for most common scenarios.

Before proceeding, let us see what events this default standard template is going to trace for us. Click on the Event Selection tab to review the events.

Select Events to Trace As you can see, the standard template we are using tracks logins/logouts, existing connections to the database, stored procedures being remotely called, and TSQL statement batches that are executed. If you wanted to trace more events, you can check the box to Show all events.

Generally, we do not care about login/logout operations or existing connections, so uncheck those events. Also, check to include the Text Data for Remote Procedure Call events. Text Data is the column that contains the actual raw SQL that is being executed.

Run The Trace Click Run to begin the trace. In the trace shown below, you can see that profiler begins tracing. For these events, I opened SQL Server Management Studio and executed a stored procedure named SalesByCategory against the NORTHWND database.

Notice that the selected event shows the actual SQL that was executed against the database in the lower window pane. In addition to the stored procedure I executed, you can also see many other events that were traced by the profiler as I was working in the management studio IDE.

Filter Tracing for Specific Database We are currently tracing events on all databases on our SQL Server (master, NORTHWND, etc.). As time goes on, the number of events traced will be so large, we will not be able to effectively search and review them. We need to limit our tracing to only use the NORTHWND database.

Stop the trace by clicking the red square stop button.

Open the properties of your trace by clicking the properties toolbar button.

1. Click the Event Selection tab

2. Check Show all columns check box.

3. Include the Database Name column by checking it in each row where it is available.

Now that we have included the database name as a column we want to track, we can now filter on it.

1. Click the Column Filters button.

2. Click on Database Name in the column list

3. Click the Like criteria option and enter %NORTHWND% for the filter

4. Check the Exclude rows that do not contain values check box.

Our trace will now only show activity that hits the NORTHWND database. You can enter multiple Like or Not Like values here if you need to.

Filter Tracing On Specific SQL In addition to limiting our trace to only use a specific database, we also want to limit what we record by certain keywords in the actual raw SQL. Let us setup an additional column filter so that the raw SQL must contain Customer or Category. This will allow us to see TSQL and stored procedures executing that contain these keywords in the TSQL query or in the name of the stored procedure.

1. Click the Column Filters button

2. Click on Text Data in the column list

3. Click the Like criteria option

4. Enter %Customer% and for the filter and press ENTER

5. Enter %Category% for the second filter

6. Check the Exclude rows that do not contain values check box.

Our trace will now only show activity that contains these keywords in the Text Data when it runs.

Note that we have not included the “SP:StmtStarting” event to trace each statement that executes within a stored procedure. So if a stored procedure has TSQL within it with Customer or Category in the query, these will not currently be recorded. If you wish to trace at this level, simply click the Show all events check box and include this event.

Trace with Database and Text Data Filter The resulting trace is now being filtered on the NORTHWND database and Text Data with Customer or Category contained in it.

Happy coding!

Elegant Software Solutions

#sql #performance #tsql #profiler