Monitoring and Tuning SQL Server with Profiler
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
SQL Server comes with several excellent tools for monitoring and tuning application performance. Profiler is perhaps the most powerful of these tools; it allows you to spy on and analyze the impact of SQL statements sent to your server.
This article provides a very brief overview of main Profiler features. I also discuss some of the advanced Profiler features: profiler templates, replaying a previously recorded trace, and recording traces without Profiler’s graphical user interface.
Profiler Trace Contents
Profiler traces mainly consist of server events you want to monitor. Profiler provides dozens of events you can choose from, or you can define your own events. For example, you can record every login and logout event, start and stop of stored procedure executions, recompilation of stored procedures, and much more. In addition, you can choose from more than 40 data columns describing the events that you could record. Data columns you could record with the trace include the text of the SQL statement that was sent to your server, user name, application name, amount of CPU used by the statement, number of reads performed, and more.
As you might guess, a trace collecting all possible events and all possible data columns can get very large and difficult to decipher, particularly on a busy production server. I have seen traces that grow to be several gigabytes and can be rather overwhelming. That’s why you need to carefully consider the events you truly need to record.
Profiler also allows you to limit the output of your trace by adding filters. You can filter the trace by particular application, database, specific user, and number of other categories.
Note that not all data columns are available with each event; for example, the FileName data column makes sense for Data File Auto Grow event, but not for the SP:completed event which records the execution of the stored procedure. A description of each event and data column is available directly within the Profiler as well as in Books Online. I highly recommend examining these descriptions directly with Profiler so that you know which events matter to you the next time you wish to create a trace.