SQL Server Profiler has always been a great tool for tracing activity “under the hood” on a SQL Server and assessing its performance. One feature that helps us quickly set up a trace on our current connection is new in SSMS 2008, namely “Trace Query in SQL Server Profiler”. Let’s take a look.
The great thing about SQL Profiler is that it will allow you to see what an application is doing from a SQL Server point of view even if it is a 3rd party application and you do not have access to the source code. Every SQL statement submitted by the application can be traced. This can be a very educational experience and may help you understand how an application really works. It is also a very effective tool for analyzing performance. You can capture execution plans along with the SQL queries and analyze them later at your leisure. You can then use the trace data as input to the Database Engine Tuning Advisor to get suggestions on performance improvements. If you take these suggestions, you can even replay a trace to verify that improvements have been made. Very nice synergy between the tools.
Normally, if I wanted to set up a trace in SQL Server Profiler, I would open up the tool, select File/New Trace. Then I would select a Profiler template to use and select events that I wish to trace such as Logins, Logouts, Stored Procedures starting, finishing, T-SQL Batches starting etc. I would also select the output columns I wanted to capture when each event is traced, for instance, CPU time, Pages read, Duration, the user who submitted the command, the application running etc. By default, you will trace all declared events for all connections so watch out if you are tracing a heavily accessed server. A better idea may be to trace activity by a particular user or on a particular database. You can even trace a precise connection using the Server Process ID (SPID). This is all done through setting up what is called a Column Filter.
If you are concerned about the overhead of a Windows application like SQL Profiler, you can run a trace efficiently in the background by using sp_trace. SSMS Template Explorer has a couple templates under the SQL Trace folder that you can use to get started. Books Online has the full details.
When used in conjunction with the Windows System Monitor you can view a Profiler trace alongside a Windows performance log and Profiler will synchronize both based on timestamps in both files. In this way we can highlight a statement in the trace and Profiler will show through a red line exactly where on the performance graph that statement executed. You can also move the red line manually and it will show you what statement was executing at that time. For instance, moving the red line to a “spike” of CPU, I/O and Memory, will uncover what statement was running at the time and may identify a bottleneck on the server.
Now in SSMS 2008, when you are executing a script in the script editor, all you have to do is right-click and choose “Trace Query in SQL Server Profiler”. SQL Profiler is launched and a trace started that traces that particular connection using a column filter. It does this using the TSQL_SPs template which is used typically for tracing stored procedures. There are several Profiler templates available and you can define your own if you wish. If you pause the trace you can change the events and/or columns captured by updating the properties and resuming the trace.
All this can be used to assess the health of your database server using pre-existing Windows and SQL Server tools. Universal Health Care at no extra cost? Not exactly, but you get the idea…
Cheers
Brian
Recent Posts:
More on Server Time Dimensions…
ReportBuilder 2.0 – focus on the end user…
SSMS 2008: Prevent saving changes that require table re-creation…
