Using SQL Server Profiler

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Performance Tuning - SQL Server Profiler

SQL Server Profiler Tutorial Video

In this ten minute video, Brent Ozar explains how to get started tracing your slow queries with Profiler:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090219-Profiler.flv

For more tutorial videos like this, check out the SQL Server Tutorials page.

How to Use SQL Server Profiler

You can start SQL Profiler by choosing Start --> Programs --> Microsoft SQL Server --> Profiler. Note that you must be a member of the SYSADMIN fixed server role to be able to setup traces with Profiler.

SQL Profiler does not immediately start a trace. Instead you must make a selection from one of the following menus:
  • File - allows you to open a previously saved trace or start a new trace. You may also open a previously saved trace template or create a new template. Once you have a trace with some data you can save it as a trace file (default extension is .trc), as a table or as SQL script containing all commands captured with the trace. The File menu also allows you to control the execution of the trace and to script trace definition.
     
  • Edit - allows you to find a particular word or phrase within the trace output, clear the trace window and copy data.
     
  • View - lets you control the toolbar and status bar.
     
  • Replay - lets you replay and debug previously saved trace commands.
     
  • Tools - lets you jump to other SQL Server tools, such as Query Analyzer, Enterprise Manger, etc.
     
  • Window - standard menu for arranging horizontal or vertical tiling of windows.
     
  • Help - lets you open online documentation.

When setting up a trace, you must first specify the server that you wish to trace and logon credentials. Once you supply a valid login that is a member of the SYSADMIN server role (if you fail to do so you will get an error) you'll get a dialog that helps with configuring trace properties. The trace properties dialog has four tabs. The General tab lets you specify the trace name, which is helpful if you wish to save the trace for future analysis, as well as the destination where the trace should be saved. If you choose to save the trace to a file you, have an option of setting the maximum file size before the file is rolled-over - that is, a new file is created to continue saving trace data. If you allow traces to grow large, opening such trace files may take dozens of minutes or even several hours. You also have an option to save the file as a SQL Server table; if you choose this option you can specify the maximum number of rows to be saved and the trace table's destination. Note that you don't have to save the trace table on the server where the trace is taken. In fact, it is often useful to trace the production server and save the trace table on a monitoring or testing server for later investigation.

The same tab allows you to specify the template to be used as the starting point for your trace. You can also create and make changes to your own templates. You are allowed to change the server you wish to trace, as long as you can provide a valid administrative login for that server. Finally, you can a define trace stop time if you wish to stop recording a trace at some point in the future.

The Events tab lets you select the events and event categories to be recorded. The Data Columns tab allows you to select particular data columns that you wish to see in your trace output. The Filters tab is where you can set filters to limit trace output to certain database, connection or by other criteria.

SQLServerPedia Articles on Profiler

Retrieved from "http://sqlserverpedia.com/wiki/Using_SQL_Server_Profiler"