Here’s a very useful tip when working on queries in SSMS. While you are in SSMS you can quickly open the SQL Server Profiler program by selecting SQL Server Profiler from the tools menu. This is probably common knowledge. However, did you know you can open up a profiler session already configured to capture the command related to a specific query window you have open in SSMS? While you are in a query window you can press the key sequence Ctrl-Alt-P, or from the Query menu select Trace Query in SQL Server Profiler.
Either of these menu commands will open SQL Server Profiler. The first command prompts you to connect to a database with the last database connection you used in SSMS. You then have to define from scratch the events and column properties you wish to profile. This is a really powerful tool you should get to know if you are doing a lot of SQL tuning or debugging.
The second command, specific to a query you are working on in SSMS automatically opens SQL Server Profiler. It configures the events to trace with default settings. It also configures a filter on the connection by setting the SPID to the current connection ID for your query. This restricts output to only those commands executed in your query window.
Why would you want to trace the queries from a single command window? You already know what commands were executed because you did them yourself in your query window. The biggest value I see is that SQL Server Profiler maintains a history of all commands that have been executed while the trace is running. In this configuration, you have a running history of each command that you execute. Even if you modify the query in your query tool, and then execute the query with the modification, the history in profiler won’t change.
This can be helpful for auditing purposes if you need to track what you have done in an ad-hoc scenario of a production system, for example. You can save the trace results to a file for review at a later time.
You can also start SQL Server Profiler by running the executable yourself from windows. You don’t have to open SSMS in order to start the profiler program. On my machine it is found from the start menu, Microsoft SQL Server, Performance Tools, SQL Server Profiler. You may be able to launch it by choosing Run and typing SQL Server Profiler. Most versions of Windows will display a link to run this tool.
That’s the tip for today. Get to know SQL Server Profiler.
Cheers,
Ben