SET STATISTICS TIME and IO statements

We will close this chapter with two statements that can give you additional information about your queries and that you can use as an additional tuning technique. These can be a great complement to execution plans to get additional information about your queries’ optimization and execution. One common misunderstanding we sometimes see is developers trying to compare plan cost to plan performance. You should not assume a direct correlation between a query-estimated cost and its actual runtime performance. Cost is an internal unit used by the query optimizer and should not be used to compare plan performance; SET STATISTICS TIME and SET STATISTICS IO can be used instead. This section explains both statements.

You can use SET STATISTICS TIME to see the number of milliseconds required to parse, compile, and execute each statement. For example, run the following command:

SET STATISTICS TIME ON

Then, run the following query:

SELECT...