SET STATISTICS IO ON and SET STATISTICS TIME ON are beautiful ways which can be used while performance tuning individual queries.
SET STATISTICS IO ON
Enabling STATISTICS IO is very informative as it displays Scan count, logical reads, physical reads, read-ahead reads, lob logical reads, lob physical reads, and lob read-ahead reads.
SET STATISTICS TIME ON
Enabling STATISTICS TIME will display the time taken to complete the execution Time.
Combination of enabling STATISTICS and Query execution plan will help to analyze the performance and fine-tune the query!
SET STATISTICS IO ON; SET STATISTICS TIME ON; GO select * from AdventureWorks2012.sales.SalesOrderDetail SET STATISTICS IO OFF; SET STATISTICS TIME OFF; GO ---- OUTPUT --SQL Server parse and compile time: -- CPU time = 0 ms, elapsed time = 1 ms. --(121317 row(s) affected) --Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. -- SQL Server Execution Times: -- CPU time = 171 ms, elapsed time = 1636 ms. -- SQL Server Execution Times: -- CPU time = 0 ms, elapsed time = 0 ms.