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.
|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.|
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.
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.
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.
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.
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.
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.
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:
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.
|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|
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.
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.
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.
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.
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.
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.
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.