2

I imported a trace file using

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\my_trace.trc', default)

While analyzing my imported trace file i noticed that certain select statements take a significant amount of time (duration) and cpu but at the end do not return any rows (RowsCount == NULL).

SELECT TextData, ClientProcessID, EventClass
        , Duration, StartTime, EndTime
        , Reads, Writes, CPU, RowCounts
        , EventSequence,  TextData_md5_hash    
        , ApplicationName, SPID
FROM MyImportedTrace
WHERE 
      TextData_md5_hash in (0x4A943F266010BDD2A47179DC3481BC7F)
ORDER BY SPID, StartTime, EventSequence

Example

slow select without any rows

Test in query window

If i copy the same query from the TextData-Field into a query window i get a lot of rows (>200000)

Questions

  • What might be a reason that the query did not return any rows?
  • Was the query terminated ?
  • How do i recognize failed query executions in a trace file?

Update / Edit after Answer accepted

This query

Use MyDatabase
SELECT ti.EventClass, te.Eventname, Count(*) as CountAsterisk
    , Sum(ti.RowCounts) as RowCountsSum
    , Sum(ti.Duration/1000) as DurationSum_ms
    , Avg(ti.Duration/1000) as DurationAvg_ms
FROM TraceImport ti
    Left JOIN TraceEvents te  
         ON ti.EventClass = te.EventTraceID
Group By ti.EventClass, te.Eventname 
Order By EventClass

Returns this result

Group Trace Rows by Event Class

Community
  • 1
  • 1
surfmuggle
  • 5,527
  • 7
  • 48
  • 77
  • Are you checking if it contains data by doing the `rowscount==null` or displaying data? Because I believe it is `@@rowcount` – Rik Nov 17 '16 at 17:48
  • 1
    RowCounts of null means that you aren't collecting it. Not that it is 0. – Martin Smith Nov 17 '16 at 17:49
  • You are correct, that `@@rowcount` can be used to query the returned rows. In my case the imported trace file contains a column `RowsCount`. An this column usual has a value > 0 (and `NOT NULL`) for a select statement. Executing the query from the field `TextData` in MMS returns rows. But during our user-performance-test the same statement it seems that it did not return any rows (hence `NULL` in the imported trace file. – surfmuggle Nov 17 '16 at 17:57

1 Answers1

1

sys.fn_trace_gettable returns every possible column that might have been captured.

There is no guarantee that the actual trace you are looking at did capture RowCounts for every relevant event class.

Based on the information you have provided so far there is no reason to think that the trace wasn't set up like this.

Inside click on Tools / SQL Server Profiler that opens the application in the window Trace properties click on the tab Event selection and make sure that the column RowCounts is checked for the event class SQL:BatchCompleted

SQL Server profiler / Trace properties / Event selection

surfmuggle
  • 5,527
  • 7
  • 48
  • 77
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Some rows in the imported trace-file have a value `>0` in the column `RowCounts`. So i would assume that every row that contains a Select that returns rows should have `RowCounts >0`. – surfmuggle Nov 17 '16 at 19:50
  • @surfmuggle what event class do they have? – Martin Smith Nov 17 '16 at 19:56
  • The rows in question have event class 12 and 13. Regarding the [function `sys.fn_trace_gettable('filename', number_files)`](https://msdn.microsoft.com/en-us/library/ms188425.aspx) this is how i imported the trace-file. It seems that [sp_trace_setevent](https://msdn.microsoft.com/en-us/library/ms186265.aspx) is only available from sql-server 2012 onwards – surfmuggle Nov 17 '16 at 20:06
  • @surfmuggle - that can't be correct. 13 is batch starting. It can't have a row count. What does `SELECT COUNT(*), EventClass FROM MyImportedTrace WHERE RowCounts > 0 GROUP BY EventClass` return? – Martin Smith Nov 17 '16 at 20:11
  • The query returned `616 rows for event class 43` and `10968 rows for event class 45`. I filtered the trace using `TextData like 'SELECT%MyTable%'` for the given MD5-hash `0x4A943F266...BC7F` this returned only two rows with event class `12 = SQL:BatchCompleted` and `13= SQL:BatchStarting`. Could it be that the query was aborted for some reason (timeout)? – surfmuggle Nov 17 '16 at 21:43
  • @surfmuggle - No it means you aren't capturing that column for event class 12. 43 and 45 are `SP:Completed`, `SP:StmtCompleted` 12 is `SQL:BatchCompleted` – Martin Smith Nov 17 '16 at 21:44
  • 1
    Thanks i did not know that this has to be configured for every event class. Reading up on [`SQL:BatchCompleted`](https://msdn.microsoft.com/en-us/library/ms176010.aspx) and [`sp_trace_setevent`](https://msdn.microsoft.com/en-us/library/ms186265.aspx) did not help. But with your screenshot know this makes things clear. – surfmuggle Nov 17 '16 at 22:05