Special Indexing and Storage Features - Pro SQL Server Internals - page 82

Figure 4-3. Execution plan with covering index

The new index has all of the required columns and, therefore, Key Lookup is no longer needed. This leads to a
much more efficient execution plan. Table 4-1 shows the number of logical reads in all three cases.

Table 4-1. Number of logical reads with different execution plans

Clustered Index Scan Nonclustered Index Seek without

covering index

Nonclustered Index Seek with
covering index

853 logical reads 6,146 logical reads 12 logical reads

■ the new covering index IDX_LastName_FirstName_PhoneIncluded makes the original nonclustered index
IDX_LastName_FirstName redundant. We will discuss index consolidation in greater detail in Chapter 6, “designing
and tuning the indexes.”

Note

Although covering indexes is a great tool that can help optimize queries, they come at a cost. Every column in the
index increases its leaf-level row size and the number of data pages it uses on disk and in memory. That introduces
additional overhead during index maintenance and increases the database size. Moreover, queries need to read more
pages when scanning all or part of the index. It does not necessarily introduce a noticeable performance impact
during small range scans when reading a few extra pages is far more efficient as compared to Key Lookups . However, it
could negatively affect the performance of the queries that scan a large number of data pages or the entire index.
By adding a column to nonclustered indexes, you store the data in multiple places. This improves the
performance of queries that select the data. However, during updates, SQL Server needs to change the rows in every
index where updated columns are present.
Let's look at the example and run two UPDATE statements, as shown in Listing 4-3. The first statement modifies
the Placeholder column, which is not included in any nonclustered index. The second statement modifies the Phone
column, which is included in the IDX_Customers_LastName_FirstName_PhoneIncluded index.

Listing 4-3. Updating data in dbo.Customers table
update dbo.Customers
set Placeholder = 'Placeholder'
where CustomerId = 1;

update dbo.Customers
set Phone = '505-123-4567'
where CustomerId = 1;