SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interpret Output of TSQL_Replay trace


Interpret Output of TSQL_Replay trace

Author
Message
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2900 Visits: 3636
Can someone please tell me which column provides the actual execution timings based on the output from TSQL_Replay trace?
//Ravi
//Ravi
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 451
The "Duration" column in the Profiler. In SQL 2008, it shows the timings in Microseconds (i.e. divide by a million to get seconds) and in SQL 2005, it shows the execution time in Milli Seconds. There is also a StartTime and EndTime column.

You can export the data into a table and then manipulate data using t-sql. You can either display "Duration" Column in seconds or subtract EndTime with StartTime. Hope this help. See the image attached.

------------
Smile
Attachments
BatchStartEndTimes.JPG (11 views, 101.00 KB)
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2900 Visits: 3636
//Ravi (9/10/2013)
The "Duration" column in the Profiler. In SQL 2008, it shows the timings in Microseconds (i.e. divide by a million to get seconds) and in SQL 2005, it shows the execution time in Milli Seconds. There is also a StartTime and EndTime column.

You can export the data into a table and then manipulate data using t-sql. You can either display "Duration" Column in seconds or subtract EndTime with StartTime. Hope this help. See the image attached.


Thanks for the response. Yeah i got that part, so when i replay at the bottom there are two columns ExecutionTime and ExecutionAndFetchTime, which ones should we consider as run times in test environment and is that in MS or S? Attached image has more details.
Attachments
Trace.jpg (12 views, 344.00 KB)
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2900 Visits: 3636
Incase if someone else is looking for an answer hopefully this might help. Jonathan Kehayias explained me in detail, below is the script from our email conversation.

There is a ProfilerSpecialEvents.XML.profiler file that defines these events, it is located in the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\TraceDefinitions on my 2012 instance. From the XML:



<COLUMNLIST>

<COLUMN>

<ID>65521</ID>

<TYPE>1</TYPE>

<NAME>SourceRow</NAME>

<DESCRIPTION>The original row number for the event executed</DESCRIPTION>

<FILTERABLE>true</FILTERABLE>

<REPEATABLE>false</REPEATABLE>

<REPEATEDBASE>false</REPEATEDBASE>

</COLUMN>

<COLUMN>

<ID>65520</ID>

<TYPE>1</TYPE>

<NAME>ExecutionTime</NAME>

<DESCRIPTION>Time taken for query execution excluding result fetching</DESCRIPTION>

<FILTERABLE>true</FILTERABLE>

<REPEATABLE>false</REPEATABLE>

<REPEATEDBASE>false</REPEATEDBASE>

</COLUMN>

<COLUMN>

<ID>65519</ID>

<TYPE>1</TYPE>

<NAME>ExecutionAndFetchTime</NAME>

<DESCRIPTION>Time taken for query execution including result fetching</DESCRIPTION>

<FILTERABLE>true</FILTERABLE>

<REPEATABLE>false</REPEATABLE>

<REPEATEDBASE>false</REPEATEDBASE>

</COLUMN>

</COLUMNLIST>


My guess would be that these are both in microseconds and they don’t get the same microsecond to millisecond conversion that the normal duration column does in Profiler. I’d have to stack walk the processing to validate this 100% from the callstack frames, but the numbers look to be microseconds equating to 1.2 second executions, unless you were replaying long running processes that took 20 minutes to replay which would be what the 1.2 million millisecond durations would equate to.


Which one do you use? It depends on whether you want to include the fetch time to send the result over the network as a part of your metric or not. I’d probably rely on ExecutionTime for server side processing tracking depending on what I was trying to compare.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search