Creating Traces

Because SQL Server Profiler can trace numerous events, it is easy to "get lost" when reading the trace output. You need to roughly determine the information you need and how you want the information grouped. For example, if you want to see the SQL statements that each user is submitting through his application, you could trace incoming SQL statements and group them by user and by application.

The first place you should look when creating a new trace is at the trace templates. These templates contain predefined trace settings that address some common auditing needs. The available trace templates are listed in Table 7.1.

Table 7.1. SQL Profiler Templates

Template	Description
SQLProfilerSP_Counts	Tracks all of the stored procedures as they start. No other event, besides the stored procedure starting, is traced.
SQLProfilerStandard	Traces the completion of SQL statements and Remote Procedure Calls (RPCs). This will include the duration of each.
SQLProfilerTSQL	Traces the start of SQL statements and RPCs.
SQLProfilerTSQL_Duration	Traces the total execution time for each completed SQL statement or RPC.
SQLProfilerTSQL_Grouped	Traces the start of SQL statements and RPCs grouped by Application, NTUser, LoginName, and ClientProcessId.
SQLProfilerTSQL_Replay	Captures profiling information that is useful for replay. It contains the same type of information as SQLProfilerTSQL, but it adds more detail, including cursor and RPC output details.
SQLProfilerTSQL_SPs	Traces detailed stored procedures, including the start and completion of each stored procedure. The SQL statements within each procedure are traced as well.
SQLProfilerTuning	A streamlined trace that only tracks the completion of SQL statements and RPCs. The completion events provide duration details that can be useful for performance tuning.

NOTE

The Trace Wizard that came with SQL Server 7.0 is no longer available in SQL Server 2000. Microsoft provides the templates instead, which can help you set up similar traces that were created with the wizard. For example, one wizard option was Find the Worst Performing Queries. You can use the SQLProfilerTuning template, which provides duration information, for this instead.

The templates that come with SQL Server 2000 are not actual traces. They simply provide a foundation for you when creating your own traces. You will see, in the next section, how to utilize these templates as you create new traces.

General Properties

You can start the SQL Server Profiler from the SQL Server Program group in the Start menu or from the Tools menu in Enterprise Manager.

Basic properties of the trace are defined in the General tab of the Trace Properties window, as shown in Figure 7.2.

Figure 7.2. General properties.

To create a new trace, you can click the File menu and select New, Trace, or you can click the New Trace icon on the toolbar. This will launch the SQL Server connection screen where you specify the SQL Server that you want to trace. This is different from SQL Server 7.0. In that version, you opened the Trace Properties window first, and then you had to specify a server on the General Properties tab.

After you are connected to the server, the General tab of the Trace Properties window is displayed. This window has some of the same basic functionality as its 7.0 predecessor, including the ability to specify the trace name and the destination of the trace output (either file or table). The destination options will be discussed in more detail in the "Saving and Exporting Traces" section later in this chapter.

A new addition to the screen is the Trace Template section. This portion of the screen allows you to select a template from which to build your own trace. A template has predefined trace properties that are reflected in your trace when selected. In other words, the events, data columns , and filters that define a trace will be preselected based on the template you choose.

After a template is selected, you can modify the trace setting and customize it for your own needs. You can then save the modified template as its own template file that will appear in the template drop-down list for future trace creation.

TIP

The Trace Name is a relatively unimportant trace property for future traces. When you create a new trace, you can specify a name for the trace; however, this trace name will not be used again. For instance, if you have a trace definition that you like, you can save it as a template file. If you want to run the trace again in the future, you can create a new trace and select the template file that you saved. You will not be selecting the trace to run based on the Trace Name that you entered originally. Trace Name is useful only if you are running multiple traces simultaneously and need to distinguish between them more easily. The Trace Name is used as the window title.

Another new feature, on the General Properties screen, is the Enable Trace Stop Time option. This is a scheduling-oriented feature that allows you to specify a date and time at which you want to stop tracing. This is handy if you want to start a trace in the evening before you go home. You can set the stop time so that the trace will run for a few hours but won't affect any nightly processing that might occur later in the evening.

Events

In the Events tab, you specify the events that you want to capture in the trace. The Events tab is shown in Figure 7.3.

Figure 7.3. Event Selection screen.

TIP

If you capture too many events in one trace, the trace becomes difficult to review, especially if the auto-scroll option is enabled. Instead, you can create several traces, one for each type of information that you want to examine, and run them simultaneously.

You can choose not to capture certain events after the trace has started, but you will have to stop the trace, change the properties, and restart the trace. Be sure to save the existing trace to a table or a file before restarting the trace, or the events will be lost.

CAUTION

When a trace is initiated from the client-side Profiler application there is the possibility of missing some events that have been traced. This will typically happen when a server is under heavy load and generating a large volume of events that your Profiler trace is capturing. Under these circumstances, the Profiler GUI may be unable to process all of the events passed from the server. When this occurs, Profiler should pause and the following entry in the Profiler Message EventClass should be displayed:

[View full width]

 
 [View full width] 
 Some trace events have not been reported to SQL Profiler because  the server has reached its maximum amount of available memory  for the process. 

This behavior is by design to minimize the risk of the Profiler GUI degrading server performance. Rather than slow down SQL Server or randomly capture intermittent commands (which would provide misleading trace information), the Profiler GUI simply stops processing events until it can again read all existing events in the Profiler queue.

Note that this is only an issue with the GUI front-end application being able to consume and display the events. The server-side SQL Trace is able to continue capturing all events. Therefore, if you need to capture events in a high volume environment, the solution is to use a server-side trace that writes to a file. A server side trace is very efficient and ensures that all of the events that you select are captured in the trace output file (information on setting up, running, and analyzing server-side traces is provided later in this chapter). Reserve the use of client-side traces for small tests on servers that are not under a heavy load.

You can choose to add an entire event group or individual events within a group. Expand an event group to list the available events within the group. You can add or remove an event group or single event by double-clicking the item or selecting it and clicking the Add/Remove button.

The available event groups and events are listed in Table 7.2. The table entries that are in bold type are new in SQL Server 2000. The Event ID column shows the corresponding event number to be specified when setting up server-side traces using the Profiler stored procedures, as covered in the "Defining Server-Side Traces" section later in this chapter.

Table 7.2. Event Groups and Events

Event Group	Event	Description of Captured Event	Event ID
Cursors (The cursor events are triggered only for API cursors .)	CursorClose	A cursor was closed.	78
	CursorExecute	A prepared cursor was executed.	74
	CursorImplicitConversion	A cursor was converted from one type to another.	76
	CursorOpen	A cursor was opened.	53
	CursorPrepare	A cursor was prepared.	70
	CursorRecompile	A cursor was recompiled directly or indirectly due to a schema change.	75
	CursorUnprepare	A cursor was deleted.	77
Database	DataFileAutoGrow	A data file grew automatically.	92
	DataFileAutoShrink	A data file was shrunk.	94
	LogFileAutoGrow	A log file grew automatically.	93
	LogFileAutoShrink	A log file was shrunk.	95
Error and Warning	Attention	An attention event, including client interrupts or broken connections, occurred.	16
	Errorlog	A message was written to the SQL Server error log.	22
	EventLog	A message was written to the Windows Application event log.	21
	Exception	A SQL Server exception occurred.	33
	Execution Warnings	A warning during the execution of a SQL statement or stored procedure was generated.	67
	Hash Warnings	A problem occurred during a hash operation.	55
	Missing Column Statistics	Distribution statistics for a column were not available.	79
	Missing Join Predicate	A join predicate was missing for the query. (This is often unintentional and results in lengthy execution time for the query.)	80
	OLE DB errors	An OLEDB error occurred.	61
	Sort Warnings	A sort operation did not fit into memory.	69
Locks	Lock:Aquired	A lock resource, such as a data page, was acquired .	24
	Lock:Cancel	Acquisition of a lock resource was canceled .	26
	Lock:Deadlock	Processes were rolled back due to a deadlock.	25
	Lock:Deadlock Chain	Provides information about each deadlock participant.	59
	Lock:Escalation	A higher order lock was obtained. For example, a page lock converted to a table lock.	60
	Lock:Released	A lock was released.	23
	Lock:Timeout	A blocking lock caused another lock to time out.	27
Objects	Auto Stats	The automatic creation or update of statistics occurred.	58
	Object:Closed	An object was closed, such as at the end of a SELECT , INSERT , or DELETE .	49
	Object:Created	A database object was created, such as for a CREATE TABLE or CREATE INDEX statement.	46
	Object:Deleted	A database object was deleted, such as for a DROP TABLE or DROP INDEX statement.	47
	Object:Opened	A database object was closed, such as after a SELECT or INSERT statement.	48
Performance	Degree of Parallelism1	Description of the degree of parallelism assigned to a SQL statement. If tracing a 7.0 server, this event will trace an INSERT statement.	28
	Degree of Parallelism2	Description of the degree of parallelism assigned to a SQL statement. If tracing a 7.0 server, this event will trace an UPDATE statement.	29
	Degree of Parallelism3	Description of the degree of parallelism assigned to a SQL statement. If tracing a 7.0 server, this event will trace a DELETE statement.	30
	Degree of Parallelism4	Description of the degree of parallelism assigned to a SQL statement. If tracing a 7.0 server, this event will trace a SELECT statement.	31
	Execution Plan	The plan tree generated for the SQL statement executed.	68
	Show Plan All	The query plan with full compile-time details for the SQL statement executed.	97
	Show Plan Statistics	The query plan with full run-time details, including actual number of rows for the SQL statement executed.	98
	Show Plan Text	The query plan text for the SQL statement executed.	96
Scans	Scan:Started	An index or table scan was started.	51
	Scan: Stopped	An index or table scan was stopped.	52
Security Audit	Audit Add DB User	A database user was added or dropped.	109
	Audit Add Login to Server Role	A login was added or dropped from a fixed server role.	108
	Audit Add Member to DB Role	A member was added or removed from a database role.	110
	Audit Add Role	A database role was added or dropped.	111
	Audit Addlogin	A SQL Server login was added or dropped.	104
	Audit App Role Change Password	The password was changed for an application role.	112
	Audit Backup/Restore	A BACKUP or RESTORE operation occurred.	115
	Audit Change Audit	A change was made to the AUDIT settings.	117
	Audit DBCC	The listed DBCC command was issued.	116
	Audit Login	A SQL Server connection was requested .	14
	Audit Login Change Password	A SQL Server login password was changed. (Passwords are not recorded.)	107
	Audit Login Change Property	A login property, other than the password, was modified.	106
	Audit Login Failed	A client login failed.	20
	Audit Login GDR	A grant, revoke, or deny action was performed on a Windows NT 4.0 or Windows 2000 account login rights.	105
	Audit Object Derived Permission	A CREATE , ALTER , or DROP command was executed.	118
	Audit Object GDR	A GRANT , DENY , or REVOKE action was performed on an object.	103
	Audit Object Permission	A permission on an object was successfully or unsuccessfully obtained.	114
	Audit Server Starts and Stops	A service was shut down, started, or paused .	18
	Audit Statement GDR	A GRANT , DENY , or REVOKE statement was performed for a statement.	102
	Audit Statement Permission	A permission on a statement was successfully or unsuccessfully obtained.	113
Server	Server Memory Change	SQL Server memory usage increased or decreased by the greater of 1MB or 5% of max server memory.	81
Sessions	Existing Connection	A connection that was obtained before the trace started.	17
Stored Procedures	RPC Output Parameter	Displays information for output parameters of a previously executed RPC.	100
	RPC:Completed	An RPC execution completed.	10
	RPC:Starting	An RPC execution was started.	11
	SP:CacheHit	The stored procedure being executed was found in the cache.	38
	SP:CacheInsert	The stored procedure being executed was placed in the cache.	35
	SP:CacheMiss	The stored procedure being executed was not found in the cache.	34
	SP:CacheRemove	The stored procedure being executed was removed from the cache.	36
	SP:Completed	The execution of a stored procedure completed.	43
	SP:ExecContextHit	The execution version of a stored procedure was found in cache.	39
	SP:Recompile	The stored procedure was recompiled.	37
	SP:Starting	The execution of a stored procedure was started.	42
	SP:StmtCompleted	A SQL statement within a stored procedure was completed.	45
	SP:StmtStarting	A SQL statement within a stored procedure was started.	44
Transactions	DTCTransaction	A distributed transaction was coordinated between two or more databases.	19
	SQLTransaction	A SQL Server transaction statement ( BEGIN , COMMIT , SAVE , or ROLLBACK TRANSACTION ) was issued.	50
	TransactionLog	A transaction was written to the log.	54
TSQL	Exec Prepared SQL	A prepared SQL statement was executed.	72
	Prepare SQL	A SQL statement was prepared for use.	71
	SQL:Batch Completed	A Transact SQL batch was completed.	12
	SQL:BatchStarting	A SQL batch was started.	13
	SQL:StmtCompleted	A SQL statement was completed.	41
	SQL:StmtStarting	A SQL statement was started.	44
	Unprepare SQL	A prepared SQL statement was unprepared.	73
User Configurable	User Configurable (0-9)	A user-defined event occurred.	82-91

NOTE

If you are going to use SQL Server Profiler, I recommend that you spend some time getting to know the events first. Each event has a description that is displayed when you click on it. Start a trace with a few events at a time and execute some relevant statements to see what is displayed for the events. You will soon realize the strength of the SQL Server Profiler.

Data Columns

In the Data Columns tab, you specify what information to capture and display for your events and how they will be ordered and grouped within the trace. Capturing too much data can make the trace file or trace table grow too large and can affect performance as well.

You should only include columns that make sense and are valid for the events being traced. Figure 7.4 displays the Data Columns tab.

Figure 7.4. Defining the information to be captured in the trace.

TIP

There is a handy matrix for each event class found in the "Monitoring with SQL Profiler Event Categories" section in Books Online. This matrix shows you which data columns are valid and will be populated for each event.

Also, don't forget to use the simple help facility that is available with the Data Columns screens. Simply select the column that you might be interested in tracing and look at the bottom portion of the screen for a brief description of that column.

The EventClass column is the name of the event you have selected. If you capture more than one event, you probably want to include this column to differentiate the source of each row of output. If you want to capture the actual text ”for instance, the SQL command ”include the TextData column. The type of information stored in this column depends on the event class.

To differentiate between activities generated from different applications, include the ApplicationName column; to differentiate between different users, include the NTUserName column or DBUserName column.

The CPU , Reads , and Writes columns are useful if you want to find out how many resources the event incurs. The Duration column specifies the elapsed time in milliseconds . To trace activities against a specific object, include the ObjectName column.

TIP

The easiest way to learn about each column is to create a trace that includes all the events and all the columns. Generate some activities against SQL Server while the trace is running and examine the information in the data columns for the different types of events. Do not run such a trace on a heavily used production server because it could significantly degrade the server's performance.

When you analyze the data, you probably want to perform some grouping with the columns. You can group all the columns you include in the trace. If you group over several columns, you should note that the column ordering makes a difference.

To specify grouping, mark the column you want to group over and choose the Up and Down button until you move the column to the desired location.

Suppose you want to find out which application performs the most table scans and then group that information by each user of the application. In that case, you group by ApplicationName and NTUserName . You might also want the reverse grouping to see which user is performing the most tables scans and which application the user is running ”so then you group over NTUserName and ApplicationName .

TIP

If you save the trace to a file or a table, you can open it later and specify whatever grouping you want to reorganize output. This flexibility gives you almost endless possibilities for analyzing the trace data.

Filters

You can filter the events that are captured by the SQL Profiler via the Filters tab. SQL Server 2000 has dramatically improved this screen by giving you more items on which to filter and better filtering options. The three basic filtering options are as follows :

• Like/Not Like ”This gives you the ability to include or exclude events based on a wildcard. You should use the % character as your wildcard character and separate multiple entries with a semicolon. For example, with the ApplicationName filter, you can specify Like SQL Query% and you will get only those events related to applications that match the wildcard, such as SQL Query Analyzer. This filtering option is available for text data columns and data columns that contain name information, such as NTUserName and ApplicationName .

• Equal To/Not Equal To/Greater Than or Equal/Less Than or Equal ”Filters with this option have all four of these conditions available. For each condition, you can specify a single value or a series of values separated by semicolons. For example, you can filter on DataBaseID and input numeric values under the Equals To node of the filtering tree. This filtering option is available for numeric data columns such as Duration , IndexId , and ObjectId .

• Greater Than/Less Than ”This type of filtering option is only available on time-based data columns. This includes StartTime and EndTime filters. These filters expect date formats of the form YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.

Each data column is set up to have one of the three filtering options described. When you click on the data column that is available for filtering, you will expand the filter tree and expose the filtering options for that column. The values on which you want to filter are entered in the data entry area on the filter tree. This input area is shown when you select a specific filtering option. For multiple filter values, type a semicolon after each value has been entered. The semicolon will not appear in the tree text, but it acts as a carriage return that gives you another text entry node below the one you just entered.

Figure 7.5 displays the Filters tab and a text node available for input for the DatabaseName column.

Figure 7.5. Producing manageable results with filters.

CAUTION

Be careful if you use Like and Not Like on the same data column. The filters are combined as a logical OR and can produce unexpected results. For example, if you use the ApplicationName filter and specify Like MS% with Not Like SQL Profiler , you will still get trace rows for the SQL Query Analyzer application name even though it does not match the MS% wildcard. The reason is because the filters condition gets rows where the name is Like MS% or Not Like SQL Profiler . In this case, the SQL Query Analyzer passes the filter because it is not like SQL Profiler .