Commands to help tune queries

SET STATISTICS IO ON

This command displays the amount of disk activity for transact SQL statements. Sample output is

Table 'servers'. Scan count 1, logical reads 7,physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To turn off run
SET STATISTICS IO OFF


SET STATISTICS TIME ON

SQL Server firstly makes logical reads to retrieve data from the data cache, if the data is not in the data cache it makes physical reads from disk. This script displays the amount of CPU resources needed by the query to run, parse, compile, and execute each statement in milliseconds

A sample output is

CPU time = 0 ms, elapsed time = 145 ms

To turn off run
SET STATISTICS TIME OFF