Enter: SET STATISTICS IO ON
It gives you an overview off how many I/Os your query is executing on each accessed table, and by working together with the query optimizer you can absolutely minimize these numbers.
And by doing so optimize and increase the work load a SQL Server instance can handle.
For example: what is the impact of replacing an SELECT IN sub-query by an IF EXISTS () construction.
This feature is available since SQL Server 6.x, so you can still tune your old instances.
In my opinion this option should always be used with any new or changed query against a representative amount of data: a copy from, or simulation of, a production environment to check if the results are in-line with expectations.
Here’s a simple query and result when the option is enabled in the message tab of the results pane (click to enlarge):
If you have a hosted SQL Server environment with tight security, it could well be that generating graphical query plans is prohibited, while this option could still be granted giving you lots of meaningful information.
Before diving in to it check that all indexes a reorganized and the statistics are up to date.
(see my post index defragmentation)
Also check my post auto index management and let SQL Server assist you with your indexes!
Oh yeah, switch it to off again when you are done tuning your queries, don’t include it in your stored procedures.
