Getting Started: Profiler

SQL Server 2005 ships with a powerful user interface tool that can be used to create, manipulate, and manage traces. SQL Server Profiler is the primary starting point for most tracing activity, and thanks to the ease with which it can help you get traces up and running, it is perhaps the most important SQL Server component available for quickly troubleshooting database issues. SQL Server Profiler also adds a few features to the toolset that are not made possible by SQL Trace itself. This section discusses those in addition to the base tracing capabilities.

Profiler Basics

The Profiler tool can be found in the Performance Tools subfolder of the SQL Server 2005 Start Menu folder. Once the tool is started, you will be greeted by a blank screen, as shown in Figure 2-1. Click File, then New Trace . . . and connect to a SQL Server instance. You will be shown a Trace Properties dialog box with two tabs, General and Events Selection.

Figure 2-1 SQL Server Profiler main screen

The General tab, shown in Figure 2-2, allows you to control how the trace will be processed by the consumer. The default setting is to use the rowset provider, displaying the events in real time in the SQL Server Profiler window. Also available are options to save the events to a file (on either the server or the client), or to a table. However, we generally recommend that you avoid these options on a busy server.

Figure 2-2 Choosing the I/O provider for the trace

When you ask Profiler to save the events to a server-side file (done by selecting Server processes trace data), it actually starts two equivalent traces—one using the rowset provider and the other using the file provider. Two traces means twice as much overhead, and that is generally not a great idea. See the section “Server-Side Tracing and Collection” later in this chapter for information on how to correctly set up a trace using the file provider in order to efficiently save to a server-side file. Saving to a client-side file does not use the file provider at all. Rather, the data is routed to the Profiler tool via the rowset provider, then saved from there to a file. This is more efficient than using Profiler to write to a server-side file, but you do incur network bandwidth because of using the rowset provider, and you also do not get the benefit of the lossless guarantee that the file provider offers.

Seeing the Save To Table option, you might wonder why our “SQL Trace Architecture and Terminology” section stated that this is not an available feature of SQL Trace. The fact is, SQL Trace exposes no table output provider. Instead, when you use this option, the SQL Server Profiler tool uses the rowset provider and routes the data back into a table. If the table you save to is on the same server you’re tracing, you can create quite a large amount of server overhead and bandwidth utilization, so if you must use this option we recommend saving the data to a table on a different server. SQL Server Profiler also provides an option to save the data to a table after you’re done tracing, and this is a much more scalable choice in most scenarios.

The Events Selection tab, shown in Figure 2-3, is where you’ll spend most of your time configuring traces in SQL Server Profiler. This tab allows you to select events that you’d like to trace, along with associated data columns. The default options, shown in Figure 2-3, collect data about any connections that exist when the trace starts (the ExistingConnection event) when a login or logout occurs (the Audit Login and Audit Logout events), when remote procedure calls complete (the RPC:Completed event), and when Transact-SQL batches start or complete (the SQL:BatchCompleted and SQL:BatchStarting events). By default, the complete list of both events and available data columns is hidden. Checking the Show All Events and Show All Columns check boxes brings the available selections into the UI.

Figure 2-3 Choosing event/column combinations for the trace

These default selections are a great starting point and can be used as the basis for a lot of commonly required traces. The simplest questions that DBAs generally answer using SQL Trace are based around query cost and/or duration. What are the longest queries or the queries that are using the most resources? The default selections can help you answer those types of questions, but on an active server a huge amount of data would have to be collected. This would not only mean more work for you to be able to answer your question, but also more work for the server to collect and distribute that much data.

In order to narrow your scope and help ensure that tracing does not cause performance issues, SQL Trace offers the ability to filter the events based on various criteria. Filtration is exposed in SQL Server Profiler via the Column Filters . . . button in the Events Selection tab. Click this button to bring up an Edit Filter dialog box, similar to the one shown in Figure 2-4. In this example, we only want to see events with a duration of greater than or equal to 200 ms. This is just an arbitrary number; an optimal choice should be discovered iteratively as you build up your knowledge of the tracing requirements for your particular application. Keep raising this number until you mostly receive only the interesting events (in this case, those with long durations) from your trace. By working this way, you can easily and quickly isolate the slowest queries in your system. See the “Performance Tuning” section later in this chapter for more information on this technique.

Tip The list of data columns made available by SQL Server Profiler for you to use as a filter is the same list of columns available in the outer Events Selection UI. Make sure to check the Show All Columns checkbox in order to ensure that you see a complete list.

Figure 2-4 Defining a filter for events greater than 200 milliseconds

Once events are selected and filters are defined, the trace can be started. Click the Run button on the Trace Properties dialog box. Because Profiler uses the rowset provider, data will begin streaming back immediately. If you find that data is coming in too quickly for you to be able to read it, consider disabling auto scrolling using the Auto Scroll Window button on the SQL Server Profiler taskbar.

An important note on filters is that, by default, events that do not produce data for a specific column will not be filtered if a trace defines a filter for that column. For example, the SQL:BatchStarting event does not produce duration data—the batch is considered to start more or less instantly the moment it is submitted to the server. Figure 2-5 shows a trace we ran with a filter the Duration column for values greater than 200. Notice that both the ExistingConnection and SQL:BatchStarting events are still returned even though they lack the Duration output column. To modify this behavior, check the Exclude Rows That Do Not Contain Values checkbox in the Edit Filter dialog, for the column you’d like to change the setting for.

Saving and Replaying Traces

The functionality covered up through this point has all been made possible by SQL Server Profiler merely acting as a wrapper over what SQL Trace provides. In the “Server-Side Tracing and Collection” section later in this chapter, we will show you the mechanisms by which SQL Server Profiler does its work. But first we’ll get into the features offered by SQL Server Profiler that make it more than a simple UI wrapper over the SQL Trace features.

Figure 2-5 By default, trace filters treat empty values as valid for the sake of the filter

When we discussed the General tab of the Trace Properties window earlier, we glossed over how the default events are actually set: They are included in the standard events template that ships with the product. A template is a collection of event and column selections, filters, and other settings that you can save to create reusable trace definitions. This can be an extremely useful feature if you do a lot of tracing; reconfiguring the options each time you need them is generally not a good use of your time.

In addition to the ability to save your own templates, Profiler ships with eight that have been predefined. Aside from the standard template that we already explored, one of the most important of these is the TSQL_Replay template, shown selected in Figure 2-6. This template selects a variety of columns for 15 different events, each of which are required for Profiler to be able to Play Back (or Replay) a collected trace at a later time. By starting a trace using this template, then saving the trace data once collection is complete, you can do things such as use a trace as a test harness for reproducing a specific problem that might occur when certain stored procedures are called in the correct order.

To illustrate this functionality, we started a new trace using the TSQL_Replay template, and sent two batches from each of two connections, as shown in Figure 2-7. The first spid (53, in the figure) selected 1, then the second spid (54) selected 2. Back to spid 53, which selected 3, and then finally back to spid 54, which selected 4. The most interesting thing to note in the figure is the second column, EventSequence. This column can be thought of almost like the IDENTITY property for a table. Its value is incremented globally as events are recorded by the trace controller, in order to create a single representation of the order in which events occurred in the server. This avoids problems that might occur when ordering by StartTime/EndTime (also in the trace, but not shown in the figure), as there will be no ties—the EventSequence will be unique per trace. The number is a 64-bit integer, and it is reset whenever the server is restarted, so it is unlikely that you will ever be able to trace enough to run it beyond its range.

Figure 2-6 Selecting the TSQL_Replay template

Figure 2-7 Two spids sending interleaved batches

Once the trace data has been collected, it must be saved and then reopened before a replay can begin. SQL Server Profiler offers the following options for saving trace data, which are available from the File menu:

• The Trace File option is used to save the data to a file formatted using a proprietary binary format. This is generally the fastest way to save the data, and also the smallest in terms of bytes on disk.
• The Trace Table option is used to save the data to a new or previously created table in a database of your choosing. This option is useful if you need to manipulate or report on the data using T-SQL.
• The Trace XML File option saves the data to a text file formatted as XML.
• The Trace XML File For Replay option also saves the data to an XML text file, but only those events and columns needed for replay functionality are saved.

Any of these formats can be used as a basis from which to replay a trace, as long as you’ve collected all of the required events and columns needed to do a replay (guaranteed when you use the TSQL_Replay template). We generally recommend using the binary file format as a starting point, and saving to a table if manipulation using T-SQL is necessary. For instance, you might want to create a complex query that finds the top queries that use certain tables; something like that would be beyond the abilities of SQL Server Profiler. With regard to the XML file formats, so far, we have not found much use for them. But as more third-party tools hit the market that can use trace data, we may see more use cases.

Once the data has been saved to a file or table, the original trace window can be closed and the file or table reopened via SQL Server Profiler’s File menu. Once a trace is reopened in this way, a Replay menu appears on the Profiler tool bar, allowing you to start replaying the trace, stop the replay, or set a breakpoint—useful when you want to test only a small portion of a larger trace.

After clicking Start, you will be asked to connect to a server—either the server you did the collection from, or another if you’d like to replay the same trace somewhere else. After connecting, the options dialog shown in Figure 2-8 will be presented. The Basic Replay Options tab allows you to save results of the trace, in addition to modifying how the trace is played back.

Figure 2-8 Replay options dialog box

During the course of the replay, the same events used to produce the trace being replayed will be traced from the server on which you replay. The Save To File and Save To Table options are used for a client-side save. No server-side option exists for saving playback results.

The Replay options section is a bit confusing as worded. No matter which option you select, the trace will be replayed on multiple threads, corresponding to at most the Number Of Replay Threads specified. However, the Replay Events In The Order They Were Traced option ensures that all events will be played back in exactly the order in which they occurred, as based upon the EventSequence column. Multiple threads will still be used to simulate multiple spids. The Replay Events Using Multiple Threads option, on the other hand, allows SQL Server Profiler to reorder the order in which each spid starts to execute events, in order to enhance playback performance. Within a given spid, however, the order of events will remain consistent with the EventSequence.

To illustrate this difference, we replayed the trace shown in Figure 2-7 twice, each using a different Replay option. Figure 2-9 shows the result of the Replay In Order option, whereas Figure 2-10 shows the result of the Multiple Threads option. In Figure 2-9, the results show that the batches were started and completed in exactly the same order in which they were originally traced, whereas in Figure 2-10 the two participating spids have each had all of their events grouped together rather than interleaved.

The Multiple Threads option can be useful if you need to replay a lot of trace data where each spid has no dependency upon other spids. For example, this might be done in order to simulate, on a test server, a workload captured from a production system. The In Order option, on the other hand, is useful if you need to ensure that you can duplicate the specific conditions that occurred during the trace. For example, this might apply when debugging a deadlock or blocking condition that results from specific interactions of multiple threads accessing the same data.

Figure 2-9 Replay using the In Order option

Figure 2-10 Replay using the Multiple Threads option

SQL Server Profiler is a full-featured tool that provides extensive support for both tracing and doing simple work with trace data, but if you need to do advanced queries against your collected data or run traces against extremely active production systems, SQL Server Profiler falls short of the requirements. Again, SQL Server Profiler is essentially nothing more than a wrapper over functionality provided within the database engine, and instead of using it for all stages of the trace lifestyle, we can directly exploit the server-side tool to increase flexibility in some cases. In the following section, we’ll show you how SQL Server Profiler works with the database engine to start, stop, and manage traces, and how you can harness the same tools for your needs.

< Back      Next >

 

 

© Microsoft. All Rights Reserved.