To enable timing of your query:
SET STATISTICS TIME ON
Time statistics provides output like this in the “Messages” tab of SSMS after running a query:
(127 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 42 ms.
To show IO statistics:
SET STATISTICS IO ON
..which provides stuff like this:
(74394 row(s) affected) Table 'Audit'. Scan count 1, logical reads 284, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Users'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
This shows you things like logical and physical reads (memory vs. disk reads). As you can see, my current query runs entirely in memory, but we can change that…
To completely flush the disk cache, query cache, and whatever other cache and start with a clean slate
DBCC FREESYSTEMCACHE ('All')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS
Lastly, I’d like to point in the general direction of Erland Sommarskog‘s excellent article with a very long title:
“Slow in the Application, Fast in SSMS? – Understanding Performance Mysteries” – archived here.
Happy debugging!