Databases Reference
In-Depth Information
The Messages tab will show an output similar to the one in Listing 6-14.
Table 'SalesOrderDetail'. Scan count 1, logical reads 15615, physical reads 87, read-ahead
reads 150, …
Listing 6-14.
As you can see, on this execution alone, SQL Server is performing 15,615 logical reads
when the base table only has 1,244 pages; so it's using over 12 times more I/Os than just
simply scanning the entire table. As we have seen before, performing Index Seeks plus Key
Lookups to the base table, which uses random I/Os, is a very expensive operation.
Now clear the plan cache to remove the execution plan currently held in memory, and
run the stored procedure again, using the same parameter as in Listing 6-13.
DBCC
FREEPROCCACHE
GO
EXEC
test
@pid
=
870
GO
Listing 6-15.
This time, you'll get a totally different execution plan. The I/O information now will show
that only around 1,240 pages were read, and the execution plan will include a Clustered
Index Scan as shown in Figure 6-12. Since, this time, there was no optimized version of
the stored procedure stored in the plan cache, SQL Server optimized it from scratch using
the new parameter, and created a new optimal execution plan.
Figure 6-12:
Plan using a Clustered Index Scan.
