// Week 8: Covering Indexes & Tipping Point

Time goes by - in a few minutes you have already successfully passed the 2nd month of the SQL Server Performance Tuning series! In todays installment I want to talk a little bit more about Non-Clustered Indexes and some few negative side-effects that you can introduce with them.

Last week I have already talked about the Bookmark Lookup in SQL Server, and that they can be very dangerous. A Bookmark Lookup occurs, when SQL Server accesses a Non-Clustered Index in the Execution Plan, and additional columns must be retrieved from the underlying table (because they are not part of the Non-Clustered Index). If you want to avoid Bookmark Lookups, you can define a so-called Covering Index in SQL Server. Let’s have a look on them.

Covering Indexes

A Covering Index is a traditional Non-Clustered Index in SQL Server. The only difference is that a Covering Non-Clustered Index includes all the requested columns for a given query. This means that a Bookmark Lookup is obsolete with a Covering Non-Clustered Index. Let’s have a look on a very simple example. The following query produces a Bookmark Lookup, because the column PostalCode is not part of the Non-Clustered Index IX_Address_StateProvinceID chosen by SQL Server in the Execution Plan.

SELECT
   AddressID,
   PostalCode
FROM Person.Address
WHERE StateProvinceID = 42
GO

The query itself also produces 18 logical reads. You can eliminate that Bookmark Lookup by defining a Covering Non-Clustered Index for that query. In our case this means that we have to include the column PostalCode in the leaf level of the Non-Clustered Index:

CREATE NONCLUSTERED INDEX idxAddress_StateProvinceID ON
Person.Address (StateProvinceID)
INCLUDE (PostalCode)
GO

When you now run the query again, you will see from the Execution Plan that the Bookmark Lookup is gone, and that SQL Server uses an Index Seek (NonClustered) operator. And the logical reads went down to just 2. A very impressive performance gain!

The only thing, that you have to be aware of is, that not every Bookmark Lookup is a dangerous one. The goal is NOT to eliminate every Bookmark Lookup, only the bad ones.

Tipping Point

In some cases, when SQL Server has to perform a Bookmark Lookup for a specific query, he can decide that the Bookmark Lookup is too expensive (regarding the necessary logical reads). In that case, SQL Server performs a whole scan of the table and just discards all non-qualifying rows. The point where that decision is taken, is the so-called Tipping Point in SQL Server. The Tipping Point just defines if SQL Server is performing a Bookmark Lookup or a complete scan of the table.

The Tipping Point lies somewhere between 1/4 and 1/3 of the pages that your query has to read. It has nothing to do with the number of records that you are reading. Because the size of the records define how many records you can store on 1 page of 8kb. For a very simple example I have defined a table where every record is 400 bytes long. This means that we can store 20 records on a page of 8kb. In addition I have defined a Non-Clustered Index on the column Value. The following query returns through a Bookmark Lookup 1061 rows.

SELECT * FROM Customers
WHERE Value < 1062
GO 

If I’m returning one row more, the query is for that specific example over the Tipping Point, and SQL Server scans the whole table:

SELECT * FROM Customers
WHERE Value < 1063
GO

2 almost identical queries, but 2 different Execution Plans! This can be in some cases a huge problem, because you have no Plan Stability anymore. Over the last years I have worked with a lot of different customers, where SQL Server was just going crazy, because of this specific problem! If you want to learn more about the Tipping.

Summary

In this installment of the SQL Server Performance Tuning series you have learned about Covering Non-Clustered Indexes and the Tipping Point in SQL Server. As you have learned over the last 4 weeks, Indexing in SQL Server can be a really magical thing.

Another side-effect of indexing is that every index can improve your read performance, but will degrade on the other hand your write performance. Every index is maintained transparently by SQL Server, if you are performance INSERT, UPDATE, and DELETE statements. Therefore you have to balance your indexing strategy based on the requirements of your read AND write workload.

If you want to learn a proven technique how you can verify if a given index change was a good or bad one, and how your write workload is influenced by it, you can also watch the training video Index Impact Analysis through the SQLpassion Online Academy. As I have mentioned in the 1st email, you are also eligible for a discount. So please use the discount code SQLTP3For2 during your checkout to get 3 training videos for the price of 2.

The following next 4 weeks we will concentrate a little bit more on Execution Plans in SQL Server, and you will learn how you read and understand Execution Plans, and how they can be used for performance tuning. Stay tuned till the next week!