Menu
Is there a way to capture STATISTICS IO and TIME within T-SQL, for logging into a table?
986
Connect to a SQL Server instance in SSMS and expand the particular database. Expand the object ( for example, HumanResources. Employee), and we can view all available statistics under the STATISTICS tab. We can get details about any particular statistics as well.
Using Client StatisticsGo to Menu >> Query >> Select Include client Statistics. Execute your query. In the results panel, you can see a new tab Client Statistics. Go to the Client Statistics tab to see the execution time.
SQL Server statistics are one of the key inputs for the query optimizer during generating a query plan. Statistics are used by the optimizer to estimate how many rows will return from a query so it calculates the cost of a query plan using this estimation.
In SQL Server, you can use the SET STATISTICS IO statement to generate detailed information about the amount of disk activity generated by a T-SQL statement. In graphical tools like SSMS and Azure Data Studio, you can view this information in the Messages tab.
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.
114
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.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
