An execution plan is the sequence of operators SQL Server selects to satisfy a query. It makes the optimizer’s strategy visible, including operations such as sorting and joins. Estimated and actual execution plans help investigate whether a reasonable-looking query is doing unnecessary work. section 5.4 section 10.5
Avoid treating a plan as a command sheet for forcing preferred operations:
- Mistake: ordering every result. ORDER BY is presentational rather than set-based, and the resulting sort represented 91% of estimated query cost in one example. section 5.4
- Mistake: assuming the optimizer chose badly because one plan is slow. Plans can vary by environment, while indexes, statistics, and other factors influence selection. section 10.5
- Mistake: forcing a join operator immediately. Query hints override optimizer decisions and should be exceptional; improving indexes may be safer. section 10.5
Compare the ordered and unordered query
Run SET STATISTICS TIME ON, then compare a plain CustomerContacts query with one ordered by CustomerContactEmail. The example reports 20,679 ms without ordering and 27,415 ms with it; inspect the actual plan in SQL Server Management Studio to see the sort’s relative cost. Do not capture plans while collecting timing data, because measurement results can change. section 5.4
Listing 5.10 Creating a covering index to improve performance
CREATE NONCLUSTERED INDEX
[NI-CustomerContactEmail-Include-FirstName-LastName]
ON dbo.CustomerContacts(CustomerContactEmail)
INCLUDE(CustomerContactFirstName, CustomerContactLastName) ;
Use the covering-index definition as a concrete alternative to paying for avoidable sorting when the query needs email, first name, and last name. section 5.4
