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