CODEWITHSUNDEEP
sql-servert-sql
TonyP
TonyP

Reputation: 5873

Capturing SQL STATISTICS TIME and IO into a table

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

Upvotes: 6

Views: 4951

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294177

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.

Upvotes: 5

Karl
Karl

Reputation: 1854

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

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300489

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.

Upvotes: 2

Related Questions