Database Reference
In-Depth Information
Figure 25-10.
New execution plan after modifying the index
At this point there are nothing but nested loop joins and index seeks. There's not even a sort operation any more
despite the
ORDER BY
statement in the query. This is because the output of the index seek against the
Person
table is
Ordered
. In short, you're largely in good shape as far as this query goes, but there were two queries in the
procedure now.
Tuning the Second Query
Eliminating the
COALESCE
allowed you to use existing indexes, but in doing this you effectively created two paths
through your query. Because you've explored the first path only because you have used only the single parameter,
you've been ignoring the second query. Let's modify the test script to see how the second path through the query
will work.
DBCC FREEPROCCACHE();
DBCC DROPCLEANBUFFERS;
GO
SET STATISTICS TIME ON;
GO
SET STATISTICS IO ON;
GO
EXEC dbo.PurchaseOrderBySalesPersonName @LastName = 'Hill%',
@VendorID = 1496;
GO
SET STATISTICS TIME OFF;
GO
SET STATISTICS IO OFF;
GO
Running this query results in a different execution plan entirely, as you can see in Figure
25-11
.
