Database Reference
In-Depth Information
Before executing this procedure, drop the index
IXTest
.
DROP INDEX Production.WorkOrder.IX_Test;
When you execute this procedure, the
SELECT
statement returns the complete data set (all rows and columns)
from the table and is therefore best served through a table scan on the table
WorkOrder
. As explained in Chapter 4, the
processing of the
SELECT
statement won't benefit from a nonclustered index on any of the columns. Therefore, ideally,
creating the nonclustered index (as follows) before the execution of the stored procedure shouldn't matter.
EXEC dbo.WorkOrderAll;
GO
CREATE INDEX IX_Test ON Production.WorkOrder(StockedQty,ProductID);
GO
EXEC dbo.WorkOrderAll; --After creation of index IX_Test
But the stored procedure execution after the index creation faces recompilation, as shown in the corresponding
extended event output in Figure
17-3
.
Figure 17-3.
Nonbeneficial recompilation of the stored procedure
The
sql_statement_recompile
event was used to trace the statement recompiles. There is no longer a separate
procedure recompile event as there was in the older trace events.
In this case, the recompilation is of no real benefit to the stored procedure. But unfortunately, it falls within the
conditions that cause SQL Server to recompile the stored procedure on every execution. This can make plan caching
for the stored procedure ineffective and wastes CPU cycles in regenerating the same plan on this execution. Therefore,
it is important to be aware of the conditions that cause the recompilation of queries and to make every effort to avoid
those conditions when implementing stored procedures and parameterized queries that are targeted for plan reuse.
I will discuss these conditions next, after identifying which statements cause SQL Server to recompile the statement in
each respective case.
