The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

Profiling

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:

Click File|New|Trace in the Profiler GUI to bring up the Trace Properties dialog.

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

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.

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 File|Script 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:

Bring up the Trace Properties dialog.

Switch to the Data Columns tab.

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.