Thursday, October 28, 2010

SET STATISTICS IO - Sql Server - tune performance

Say hello to one of may friends: SET STATISTICS IO [ON|OFF]
Information it provides helps identify problems and tune performance. Though you will have to have a lot of practice before this info will became useful. I am still not 100% sure I fully understand data this statement provides. Example:
SET STATISTICS IO ON

SELECT my_user.*, user_profile.*
FROM my_user 
 JOIN user_profile on my_user.id = user_profile.user_id

SET STATISTICS IO OFF
Results:
(3 row(s) affected)
Table 'user_profile'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'my_user'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Syntax and explanation of results read here...