Capturing SQL STATISTICS TIME and IO into a table

0 votes
asked Jun 5, 2010 by tonyp

Is there a way to capture STATISTICS IO and TIME within T-SQL, for logging into a table?

3 Answers

0 votes
answered Jun 5, 2010 by mitch-wheat

No, not using SET STATISTICS IO ON.

But then you don't need to; run SQL Profiler and start a trace to output to a file. Include Reads and Duration.

0 votes
answered Jun 5, 2010 by remus-rusanu

Sort of.

The same statistics as those given by SET STATISTICS TIME are captured by the Query Statistics DMV: sys.dm_exec_query_stats.

DMVs can be queried from T-SQL, just like normal views.

However, the SET STATISTICS IO are only captured as aggregate values (last_logical_reads, last_physical_read) per execution, without the differentiation per-rowset given by SET STATISTICS IO.

Overall though, the DMVs can serve the same purpose as SET STATISTICS IO.

0 votes
answered Jun 10, 2013 by karl

Adding to what @Remus Rusanu said - it is then easy enough to capture the elapsed_time to a table (once you know the query handle).

For example:

CREATE TABLE #times (
    MS BIGINT
);


INSERT INTO #times
SELECT total_elapsed_time
FROM sys.dm_exec_query_stats 
WHERE sql_handle = 0x02000000DEE9FC09E552D1E33008EED4E8732B21E171EC160000000000000000000000000000000000000000;

If there are only a few rows returned by total_elapsed_time (thus only a few cached queries), you can look at the total_rows column and find the handle with the number of rows returned by the queries you want to time. Then pass the handle to this DMO to see the text of the query and if it matches the query you want to time:

SELECT *
FROM  sys.dm_exec_sql_text (0x02000000DEE9FC09E552D1E33008EED4E8732B21E171EC160000000000000000000000000000000000000000) ;

(Hopefully someone else will add to this and tell us a more robust way to get the query_handle for a specific query. I'm not able to research it now.)

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...