Using SQL Profiler to See What's Really Going on With Your SQL Queries
Updated: May 29, 2020
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 ToolsSQL 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 FileNew 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.