Profiling

for RuBoard

SQL Server's Profiler is also a fairly capable tool. It could be a lot better, but it provides the basic functionality you need, and it's a vast improvement over the SQL Trace facility in releases of SQL Server prior to 7.0. It presents a graphical interface wherein you specify the events for which you'd like to watch on the server. You can trace virtually anything that happens on the serverfrom T-SQL batches to stored procedures to sort warnings and error log entries.

Starting a Trace

To start a new trace using Profiler, follow these steps:

1. Click FileNewTrace in the Profiler GUI to bring up the Trace Properties dialog.

2. Specify the trace attributes you want, selecting the elements that make the most sense in your particular situation. As a rule, select all columns .

3. Click the Run button. You should see the trace start.

Tracing versus Viewing

I strongly encourage you to use stored procedures to run traces. The traces you generate via stored procedures can still be viewed in Profiler, so you lose nothing in terms of analyzing a trace file once it's collected. What you gain is a means of gathering trace information that will be much less burdensome on your SQL Server.

Command-Line Parameters

In addition to its GUI interface, SQL Profiler supports a few command-line parameters that can be used to control how it works. Table 18-1 summarizes them.

Table 18-1. SQL Profiler Command-line Options

Option	Meaning
/S	Specifies a trace definition to start.
/F	Specifies a trace file to open .
/D	Specifies a trace definition file to open.

General Advice and Caveats

• If possible, don't run Profiler on your SQL Server machine. Run it on a different machine and connect across the network. I've seen Profiler take as much as 80% of the CPU time on a reasonably fast machine. This doesn't leave much for SQL Server.

• Never trace to a table. Tracing to a table forces Profiler to open a loopback connection over ODBC to the SQL Server to work with the table. Tracing to a table has been known to eat up resources on the server and is generally a bad idea.

• Don't trace events you don't need, especially statement-level events. Tracing too many events can have a noticeable performance impact on your server and will bloat your trace files.

• As a rule, include all columns in your traces. It's not that much more expensive to do so, and some events depend on certain columns being present to return useful info or ancillary details. For example, Profiler's showplan events won't display correctly unless the BinaryData column is included. If you don't want to include every column in the trace, I think a good standard set would include at least the following ones:

  • BinaryData

  • ClientProcessID

  • CPU

  • Duration

  • EndTime

  • EventClass

  • EventSubClass

  • HostName

  • IntegerData

  • LoginName

  • NTUserName

  • Reads

  • SPID

  • StartTime

  • TextData

  • Writes

  Keep in mind that the event classes use the various ancillary fields in different ways. Often, fields like EventSubClass and IntegerData contain information that further defines a particular type of event.

• Remember that you can place bookmarks (Ctrl-F2) in a trace file as you browse through it in Profiler and can jump (F2) from bookmark to bookmark.

• When you can, use the sp_trace_XXXX extended procedures to start and stop traces. See the sp_start_trace routine in Chapter 21 for an example of how to do this. Using the sp_trace_XXXX procedures can greatly reduce the performance impact of running Profiler traces on your system.

• You can use the FileScript Trace menu option to generate a T-SQL batch that will start the currently defined trace using extended procedure calls. If nothing else, this is a great way to learn how to build these kinds of routines, even if you don't end up using the generated script. Listing 18-1 shows what one of these scripts looks like:

  Listing 18-1 Profiler will generate T-SQL tracing code for you.

   *****************************************************/ /* Created by: SQL Profiler                         */ /* Date: 09/10/2000 00:14:40 AM                      */ /****************************************************/ -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\ MyFolder\ MyTrace. The .trc -- extension will be appended to the filename automatically. If you are -- writing from remote server to local drive, please use UNC path and make -- sure server has write access to your network share exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL if (@rc != 0) goto error -- Client-side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 10, 1, @on exec sp_trace_setevent @TraceID, 10, 6, @on exec sp_trace_setevent @TraceID, 10, 9, @on exec sp_trace_setevent @TraceID, 10, 10, @on exec sp_trace_setevent @TraceID, 10, 11, @on exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 10, 16, @on exec sp_trace_setevent @TraceID, 10, 17, @on exec sp_trace_setevent @TraceID, 10, 18, @on exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 6, @on exec sp_trace_setevent @TraceID, 12, 9, @on exec sp_trace_setevent @TraceID, 12, 10, @on exec sp_trace_setevent @TraceID, 12, 11, @on exec sp_trace_setevent @TraceID, 12, 12, @on exec sp_trace_setevent @TraceID, 12, 13, @on exec sp_trace_setevent @TraceID, 12, 14, @on exec sp_trace_setevent @TraceID, 12, 16, @on exec sp_trace_setevent @TraceID, 12, 17, @on exec sp_trace_setevent @TraceID, 12, 18, @on exec sp_trace_setevent @TraceID, 14, 1, @on exec sp_trace_setevent @TraceID, 14, 6, @on exec sp_trace_setevent @TraceID, 14, 9, @on exec sp_trace_setevent @TraceID, 14, 10, @on exec sp_trace_setevent @TraceID, 14, 11, @on exec sp_trace_setevent @TraceID, 14, 12, @on exec sp_trace_setevent @TraceID, 14, 13, @on exec sp_trace_setevent @TraceID, 14, 14, @on exec sp_trace_setevent @TraceID, 14, 16, @on exec sp_trace_setevent @TraceID, 14, 17, @on exec sp_trace_setevent @TraceID, 14, 18, @on exec sp_trace_setevent @TraceID, 15, 1, @on exec sp_trace_setevent @TraceID, 15, 6, @on exec sp_trace_setevent @TraceID, 15, 9, @on exec sp_trace_setevent @TraceID, 15, 10, @on exec sp_trace_setevent @TraceID, 15, 11, @on exec sp_trace_setevent @TraceID, 15, 12, @on exec sp_trace_setevent @TraceID, 15, 13, @on exec sp_trace_setevent @TraceID, 15, 14, @on exec sp_trace_setevent @TraceID, 15, 16, @on exec sp_trace_setevent @TraceID, 15, 17, @on exec sp_trace_setevent @TraceID, 15, 18, @on exec sp_trace_setevent @TraceID, 17, 1, @on exec sp_trace_setevent @TraceID, 17, 6, @on exec sp_trace_setevent @TraceID, 17, 9, @on exec sp_trace_setevent @TraceID, 17, 10, @on exec sp_trace_setevent @TraceID, 17, 11, @on exec sp_trace_setevent @TraceID, 17, 12, @on exec sp_trace_setevent @TraceID, 17, 13, @on exec sp_trace_setevent @TraceID, 17, 14, @on exec sp_trace_setevent @TraceID, 17, 16, @on exec sp_trace_setevent @TraceID, 17, 17, @on exec sp_trace_setevent @TraceID, 17, 18, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go 

  Note the use of the @On bit variable. Many extended procedures, including those used to manage Profiler traces, have parameters that are very strongly typed. Because there's no way to tell sp_trace_setevent to cast the 1 that's passed into it as a bit, the script is forced to put the 1 in a bit variable and pass it to the extended procedure instead. You'll notice that the sp_start_trace routines in Chapter 21 do the same thing.

• Remember that you can use filters to reduce the amount and type of data that's traced. Again, overtracing will slow down your server measurably.

• If you do end up managing traces using Profiler and discover that you use a given collection of events, columns, and filters repeatedly, set up a trace definition file to package them together so that you can use it to define your traces easily and consistently. Profiler comes with a number of trace definition files already set up.

• You can feed Profiler trace files into the Index Tuning wizard in order to receive automated help regarding index tuning and design.

• Profiler trace events can be used to audit security- related activities on the server. Add the Security Audit group of event classes to a trace to see how this works. As pointed out in Chapter 22, you can generate auditing events from your own applications using DBCC AUDITEVENT(). These will show up in a Profiler trace as Security Audit events.

• Make sure your TEMP folder has plenty of space. Profiler uses this area for a variety of things, and the files it creates there can be huge.

• The search facility in Profiler is case sensitive. If you search for something that you know is there, but it isn't found, check the case of your search string.

• Keep in mind that you can save Profiler traces as Transact -SQL. This allows you to play back those traces in any tool that can run T-SQL, including Query Analyzer and OSQL.

Replaying Traces

One of the more powerful aspects of having events collected by Profiler is that they can be played back. Once the trace file is created, just load it into Profiler and press F5 to replay it. There are some events that Profiler is simply incapable of playing back (the Attention event is one of them), but this works pretty well for the most part.

Loading a Trace File into a Table

Something that's really handy when you're working with large trace files is to load them into a table and use Transact-SQL to query, aggregate, pivot them, and so forth. The ::fn_trace_gettable() system function allows you to do this very easily. It's a rowset function that you can use to read a Profiler trace file from within a SELECT statement (Listing 18-2):

Listing 18-2 The ::fn_trace_gettable() system function lists the contents of a trace file.

 SELECT * FROM ::fn_trace_gettable('c:\ temp\ test.trc',DEFAULT) 

(Results abridged)

 TextData ------------------------------------------------------------------------- network protocol: Named Pipes set quoted_identifier off set implicit_transactions off set cursor_close_on_commit off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set language us_english set dateformat mdy set datefirst 7 

Of course, you can insert the results of a SELECT FROM ::fn_trace_gettable() query into a separate table for permanent storage or further analysis. Keep in mind that any trace file you open using ::fn_trace_gettable() must be accessible from the server, and remember that the path you specify is relevant to the server, not your local machine.

Emitting a Trace File as XML

If you'd rather work with a trace file as an XML document instead of a SQL Server table, this is easy enough to do. A nifty side benefit of the fact that Transact-SQL can translate SELECT results into XML is that you can translate a Profiler trace file into an XML document with a single command (Listing 18-3):

Listing 18-3 It's easy to translate a trace file to XML.

 SELECT TextData,       DatabaseID,       TransactionID,       NTUserName,       NTDomainName,       HostName,       ClientProcessID,       ApplicationName,       LoginName,       SPID,       Duration,       StartTime,       EndTime,       Reads,       Writes,       CPU,       Permissions,       Severity,       EventSubClass,       ObjectID,       Success,       IndexID,       IntegerData,       ServerName,       EventClass,       ObjectType,       NestLevel,       State,       Error,       Mode,       Handle,       ObjectName,       DatabaseName,       FileName,       OwnerName,       RoleName,       TargetUserName,       DBUserName,       TargetLoginName,       ColumnPermissions FROM ::fn_trace_gettable('c:\ _temp\ test.trc',DEFAULT) FOR XML AUTO 

Note the FOR XML AUTO clause at the end of the SELECT. This is what's responsible for the SELECT result being translated into XML. Once the data is in XML format, you can use an XML style sheet to translate it into virtually any other format.

Grouping Profiler Data

Profiler can group the data in a trace display based on a column or columns. This allows for easier navigation and analysis of trace info. To group on a set of columns:

1. Bring up the Trace Properties dialog.

2. Switch to the Data Columns tab.

3. Use the Up and Down buttons to move columns in and out of the Groups branch on the right side of the dialog.

ODBC Tracing

If you connect to SQL Server over ODBC and you're having trouble getting the info you need from Profiler, you can set up a separate ODBC trace at the driver level. You do this from the Tracing tab in the ODBC Administrator. This will at least tell you what's being sent to the server and what's being returned. Be sure to turn off the ODBC trace when you're finished with it.

for RuBoard