SQL Profiler Data Columns

Administering SQL Server

Administering SQL Server

SQL Profiler Data Columns

SQL Profiler allows you to select data columns when you create a template. These data columns represent the information you would like returned when a trace is running. The data displayed in SQL Profiler can be displayed either in the order the events occur or in a group based on one or a combination of data columns.

For example, to identify the user events that are taking the longest to execute, group events by DBUserName and Duration. SQL Profiler displays the execution time for each event. This functionality is similar to the Transact-SQL GROUP BY clause. For more information, see GROUP BY.

Note  You cannot group by the StartTime or EndTime data columns.

If SQL Profiler can connect to an instance of Microsoft® SQL Server™ on which the trace data was captured, it will try to populate the Database ID, Object ID, and Index ID data columns with the names of the database, object, and index respectively. Otherwise, it will display identification numbers (IDs).

The following table describes the SQL Profiler data columns, and which are selected by default.

Data column Column Number
Description
Application Name1 10 Name of the client application that created the connection to an instance of SQL Server. This column is populated with the values passed by the application rather than the displayed name of the program.
Binary Data 2 Binary value dependent on the event class captured in the trace.
ClientProcessID1 9 ID assigned by the host computer to the process where the client application is running. This data column is populated if the client process ID is provided by the client.
Column Permissions 44 Indicates whether a column permission was set. Parse the statement text to determine which permissions were applied to which columns.
CPU 18 Amount of CPU time (in milliseconds) used by the event.
Database ID1 3 ID of the database specified by the USE database statement or the default database if no USE database statement has been issued for a given instance. SQL Profiler displays the name of the database if the Server Name data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function.
DatabaseName 35 Name of the database in which the user statement is running.
DBUserName1 40 SQL Server user name of the client.
Duration 13 Amount of time (in milliseconds) taken by the event.
End Time 15 Time at which the event ended. This column is not populated for event classes that refer to an event starting, such as SQL:BatchStarting or SP:Starting.
Error 31 Error number of a given event. Often this is the error number stored in sysmessages.
EventClass1 27 Type of event class captured.
EventSubClass1 21 Type of event subclass, providing further information about each event class. For example, event subclass values for the Execution Warning event class represent the type of execution warning:

1 = Query wait. The query must wait for resources (for example, memory) before it can execute.
2 = Query time-out. The query timed out while waiting for required resources to execute. This data column is not populated for all event classes.

FileName 36 The logical name of the file being modified.
Handle 33 Integer used by ODBC, OLE DB, or DB-Library to coordinate server execution.
Host Name1 8 Name of the computer on which the client is running. This data column is populated if the host name is provided by the client. To determine the host name, use the HOST_NAME function.
Index ID 24 ID for the index on the object affected by the event. To determine the index ID for an object, use the indid column of the sysindexes system table.
Integer Data 25 Integer value dependent on the event class captured in the trace.
LoginName 11 Name of the login of the user (either SQL Server security login or the Microsoft Windows® login credentials in the form of DOMAIN\Username).
LoginSid1 41 Security identification number (SID) of the logged-in user. You can find this information in the sysxlogins table of the master database. Each SID is unique for each login in the server.
Mode 32 Integer used by various events to describe a state the event has received or is requesting.
NestLevel 29 Integer representing the data returned by @@NESTLEVEL.
NT Domain Name1 7 Microsoft Windows NT® 4.0 or Windows 2000 domain to which the user belongs.
NT User Name1 6 Windows NT 4.0 or Windows 2000 user name.
Object ID 22 System-assigned ID of the object.
ObjectName 34 Name of the object being referenced.
ObjectType 28 Value representing the type of the object involved in the event. This value corresponds to the type column in sysobjects.
Owner Name 37 Database user name of the object owner.
Permissions 19 Integer value representing the type of permissions checked. Values are:

1 = SELECT ALL
2 = UPDATE ALL
4 = REFERENCES ALL
8 = INSERT
16 = DELETE
32 = EXECUTE (procedures only)
4096 = SELECT ANY (at least one column)
8192 = UPDATE ANY
16384 = REFERENCES ANY

Reads 16 Number of logical disk reads performed by the server on behalf of the event.
RoleName 38 Name of an application role being enabled.
Server Name1 26 Name of the instance of SQL Server being traced.
Severity 20 Severity level of an exception.
SPID1 12 Server Process ID assigned by SQL Server to the process associated with the client.
Start Time1 14 Time at which the event started, when available.
State 30 Equivalent to an error state code.
Success 23 Represents whether the event was successful. Values include:

1 = Success.
0 = Failure

For example, a 1 means success of a permissions check and a 0 means a failure of that check.

TargetLoginName 42 For actions which target a login (for example, adding a new login), the name of the targeted login.
TargetLoginSid 43 For actions which target a login (for example, adding a new login), the SID of the targeted login.
TargetUserName 39 For actions which target a database user (for example, granting permission to a user), the name of that user.
TextData 1 Text value dependent on the event class captured in the trace. However, if you are tracing a parameterized query, the variables will not be displayed with data values in the TextData column.
Transaction ID 4 System-assigned ID of the transaction.
Writes 17 Number of physical disk writes performed by the server on behalf of the event.

1 These data columns are populated by default for all events.