The SQL Server Profiler is an absolutely essential tool for both DBAs and developers alike. Profiler provides the ability to monitor and record virtually every facet of SQL Server activity. It is actually a graphical interface for SQL Trace, which is a collection of stored procedures and functions that are used to monitor and record server activity.
SQL Server Profiler can be launched from the Tools menu of SQLServer Management Studio, or from the All Programs Microsoft SQL Server 2008 Performance Tools menu.
SQL Server Trace
The Profiler can be used to create and view SQL Server Traces. When creating a new trace, the Profiler will prompt you for the server on which you will be running the trace. Remember that the Profiler is just a graphical interface for SQL Trace, and what is occurring in the background is the execution of stored
procedures and functions on the server you connect to. If the server is very busy and is operating at the edge of its capabilities, the additional load of running SQL Trace on it may well put it over the edge.
Trace Properties
When creating a new trace, the Trace Properties dialog is shown below. The Trace Properties dialog includes two tabs: the General tab and the Events Selection tab. A third tab, Events Extraction Settings, will be enabled if any XML SHOWPLAN event is selected in the Events Selection tab. Trace Properties dialog.
General Tab
The General tab provides the ability to set the basic structure of the trace (such as the trace name, trace template, saving options, and trace stop time). It also displays the provider name and type, because SQL Server Profiler is not limited to the Data Engine. It can also be used to trace SQL Server Analysis Services.
Use the Template — This dropdown list contains several pre-built trace templates. Each template is a pre-defined set of events and filters that provide for the monitoring of SQL Server for particular purposes. These templates can be a good place to start when creating traces to monitor SQL Server. It is also possible to create your own templates, and it is strongly recommended that you do so. The provided templates are fine, but you will undoubtedly want to collect different information from that which the templates provide. To avoid having to create the same
custom trace over and over again, create and save a template to capture the information you are interested in.
Save to File — Selecting this checkbox will display a dialog prompting for a file location to save the trace data to. The filename defaults to the name assigned to the trace with the .trc extension. However, the name can be changed if desired. The default maximum file size for a trace file is 5 MB, but it can be set to virtually any size. When the ‘‘Save to file’’ option is selected, two additional options are enabled: the ‘‘Enable file rollover’’ option and the ‘‘Server processes trace data’’ option.
Enable File Rollover — This option causes a new file to be created every time the maximum file size is reached. Each file created is named the same as the original file with a sequential number added to the end of the name. Each sequential file is linked to the preceding file, so that each file can be opened in sequence, or they can all be opened in a single trace window.
Server Processes Trace Data — This option causes the server that the traces are running on to also process the trace information. By default, the Profiler application processes the trace information. During high-stress operations, if the Profiler processes the data, it may drop some events and even become unresponsive. If the server processes the trace data, no events will be dropped. However, having the server process the trace data and run the trace puts an additional load on the server, which can have a negative impact on server performance.
❑ Save to Table — Trace data can also be saved to a table instead of a file by selecting the ‘‘Save to table’’ option. This is very useful if the trace data is going to be analyzed by an external application that requires access to the data stored in a relational format. The down side is that large traces will generate huge amounts of data that will be inserted into the storage table. This can also cause server performance issues, but you can mitigate this by saving trace information to a different server from your production system. If saving trace data to a table, the maximum amount of rows to be stored can also be assigned.
❑ Enable Trace Stop Time — Traces can be started and configured to automatically stop at a pre-defined time by enabling the ‘‘Enable trace stop time’’ option and assigning a stop time.
Events Selection Tab
The Events Selection tab provides the ability to choose what SQL Server events are to be traced Events are grouped in 21 SQL Server event groups with a total of 170 distinct SQL Server events, plus 10 user-definable events. There are also 11 Analysis Services Groups with 38 distinct events.SQL Server Books Online has an excellent reference that describes each group and event. Search for the titles of ‘‘SQL Server Event Class Reference’’ for SQL Server events and ‘‘Analysis Services Event Classes’’for Analysis Services Events.
❑ Column Filters — Also in the Events Selection tab is the option to filter the events that are traced. The ability to filter the data is incredibly useful. For example, if you are troubleshooting a particular application, you can filter on just the events generated by the application of interest and avoid having to sift through all the events generated by SQL Server and other applications.
❑ Organize Columns — The Organize Columns button enables you to place the trace columns you are most interested in so that they are easily seen when viewing the trace. Because a great deal of data can be returned, it may very well be that the column you are most interested in is off the screen to the left. The Organize Columns button helps prevent this.
SQL Server Profiler can be launched from the Tools menu of SQLServer Management Studio, or from the All Programs Microsoft SQL Server 2008 Performance Tools menu.
SQL Server Trace
The Profiler can be used to create and view SQL Server Traces. When creating a new trace, the Profiler will prompt you for the server on which you will be running the trace. Remember that the Profiler is just a graphical interface for SQL Trace, and what is occurring in the background is the execution of stored
procedures and functions on the server you connect to. If the server is very busy and is operating at the edge of its capabilities, the additional load of running SQL Trace on it may well put it over the edge.
Trace Properties
When creating a new trace, the Trace Properties dialog is shown below. The Trace Properties dialog includes two tabs: the General tab and the Events Selection tab. A third tab, Events Extraction Settings, will be enabled if any XML SHOWPLAN event is selected in the Events Selection tab. Trace Properties dialog.
General TabThe General tab provides the ability to set the basic structure of the trace (such as the trace name, trace template, saving options, and trace stop time). It also displays the provider name and type, because SQL Server Profiler is not limited to the Data Engine. It can also be used to trace SQL Server Analysis Services.
Use the Template — This dropdown list contains several pre-built trace templates. Each template is a pre-defined set of events and filters that provide for the monitoring of SQL Server for particular purposes. These templates can be a good place to start when creating traces to monitor SQL Server. It is also possible to create your own templates, and it is strongly recommended that you do so. The provided templates are fine, but you will undoubtedly want to collect different information from that which the templates provide. To avoid having to create the same
custom trace over and over again, create and save a template to capture the information you are interested in.
Save to File — Selecting this checkbox will display a dialog prompting for a file location to save the trace data to. The filename defaults to the name assigned to the trace with the .trc extension. However, the name can be changed if desired. The default maximum file size for a trace file is 5 MB, but it can be set to virtually any size. When the ‘‘Save to file’’ option is selected, two additional options are enabled: the ‘‘Enable file rollover’’ option and the ‘‘Server processes trace data’’ option.
Enable File Rollover — This option causes a new file to be created every time the maximum file size is reached. Each file created is named the same as the original file with a sequential number added to the end of the name. Each sequential file is linked to the preceding file, so that each file can be opened in sequence, or they can all be opened in a single trace window.
Server Processes Trace Data — This option causes the server that the traces are running on to also process the trace information. By default, the Profiler application processes the trace information. During high-stress operations, if the Profiler processes the data, it may drop some events and even become unresponsive. If the server processes the trace data, no events will be dropped. However, having the server process the trace data and run the trace puts an additional load on the server, which can have a negative impact on server performance.
❑ Save to Table — Trace data can also be saved to a table instead of a file by selecting the ‘‘Save to table’’ option. This is very useful if the trace data is going to be analyzed by an external application that requires access to the data stored in a relational format. The down side is that large traces will generate huge amounts of data that will be inserted into the storage table. This can also cause server performance issues, but you can mitigate this by saving trace information to a different server from your production system. If saving trace data to a table, the maximum amount of rows to be stored can also be assigned.
❑ Enable Trace Stop Time — Traces can be started and configured to automatically stop at a pre-defined time by enabling the ‘‘Enable trace stop time’’ option and assigning a stop time.
Events Selection Tab
The Events Selection tab provides the ability to choose what SQL Server events are to be traced Events are grouped in 21 SQL Server event groups with a total of 170 distinct SQL Server events, plus 10 user-definable events. There are also 11 Analysis Services Groups with 38 distinct events.SQL Server Books Online has an excellent reference that describes each group and event. Search for the titles of ‘‘SQL Server Event Class Reference’’ for SQL Server events and ‘‘Analysis Services Event Classes’’for Analysis Services Events.
❑ Column Filters — Also in the Events Selection tab is the option to filter the events that are traced. The ability to filter the data is incredibly useful. For example, if you are troubleshooting a particular application, you can filter on just the events generated by the application of interest and avoid having to sift through all the events generated by SQL Server and other applications.
❑ Organize Columns — The Organize Columns button enables you to place the trace columns you are most interested in so that they are easily seen when viewing the trace. Because a great deal of data can be returned, it may very well be that the column you are most interested in is off the screen to the left. The Organize Columns button helps prevent this.
