I am doing a trace on a database and dumping the trace realtime into a table.
The reason I'm doing a trace is I want to see all the queries that are being executed against the database.
+-----------+------------+----------+------------------------------+------------+-----------+-----+-------+--------+-----------+-----------------+------+-------------------------+-------------------------+------------+
| RowNumber | EventClass | TextData | ApplicationName | NTUserName | LoginName | CPU | Reads | Writes | Duration | ClientProcessID | SPID | StartTime | EndTime | BinaryData |
+-----------+------------+----------+------------------------------+------------+-----------+-----+-------+--------+-----------+-----------------+------+-------------------------+-------------------------+------------+
| 13073 | 15 | NULL | .Net SqlClient Data Provider | NULL | reviewer | 0 | 2172 | 0 | 439776000 | 5096 | 104 | 2012-12-14 12:54:22.893 | 2012-12-14 13:01:42.670 | NULL |
| 13057 | 15 | NULL | .Net SqlClient Data Provider | NULL | reviewer | 0 | 3198 | 4 | 436033000 | 4424 | 158 | 2012-12-14 12:52:50.457 | 2012-12-14 13:00:06.490 | NULL |
| 10372 | 15 | NULL | .Net SqlClient Data Provider | NULL | reviewer | 0 | 2124 | 0 | 400456000 | 2664 | 200 | 2012-12-14 12:48:12.347 | 2012-12-14 12:54:52.803 | NULL |
| 4498 | 15 | NULL | .Net SqlClient Data Provider | NULL | reviewer | 0 | 2099 | 0 | 399923000 | 3432 | 128 | 2012-12-14 12:06:10.140 | 2012-12-14 12:12:50.063 | NULL |
| 8288 | 15 | NULL | .Net SqlClient Data Provider | NULL | reviewer | 0 | 2525 | 2 | 399730000 | 2664 | 194 | 2012-12-14 12:38:46.863 | 2012-12-14 12:45:26.593 | NULL |
+-----------+------------+----------+------------------------------+------------+-----------+-----+-------+--------+-----------+-----------------+------+-------------------------+-------------------------+------------+
I'm grabbing the rows where the duration
is highest:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [RowNumber]
,[EventClass]
,[TextData]
,[ApplicationName]
,[NTUserName]
,[LoginName]
,[CPU]
,[Reads]
,[Writes]
,[Duration]
,[ClientProcessID]
,[SPID]
,[StartTime]
,[EndTime]
,[BinaryData]
FROM [salesdwh_testing].[dbo].[mytrace]
order by duration desc
Why is the TextData null?
Please note that not all TextData is null, but it looks like these top 1000 are!
Best Answer
EventClass 15 is "Audit Logout" there's no TextData to associate with that. It's simply the connection closing. TextData would only be associated with events like "SQL:StmtStarting", etc...
See here for more info: http://weblogs.sqlteam.com/mladenp/archive/2007/11/09/Map-SQL-Server-Profiler-EventClass-ID-to-its-name-in.aspx
From comments: The duration it shows in the log is the total connection duration.