【SQLpassion Performance Tuning Training Plan】 - Week 8: Covering Indexes & Tipping Point

Tipping Point( n. 卸载点; 1。引爆点、临界点、时间窗、价格点、量变到质变的分界点;)

 

Time goes by - in just a few short minutes you have already successfully passed the 2nd month of the SQLpassion Performance Tuning Training Plan! 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 it 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( v. 恢复; 取回( retrieve的过去式和过去分词 ); 寻回; 检索（储存的信息）;) from the underlying( adj. 潜在的，含蓄的; 基础的; ) 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 at 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( adj. 废弃的; 老式的) with a Covering Non-Clustered Index. Let's have a look at a very simple example. The following query produces a Bookmark Lookup, because the column PostalCode is not part of the Non-Clustered IndexIX_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( vt. 淘汰; 排除，消除; 除掉; ) 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( adj.可观的; 给人印象深刻的，感人的; 引人注目的; 显赫;)  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, it 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( v. 丢弃，抛弃( discard的第三人称单数 ); 不再使用;) all non-qualifying( adj. 使具有资格的;) rows. The point where that decision is taken, is the so-called Tipping Pointin SQL Server. The Tipping Point just decides whether 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 1061 rows using a Bookmark Lookup.

CREATE TABLE CustomerTippingPoint
(
   Value INT,
   Content VARCHAR(400)
)

DECLARE @p INT
SELECT @p=0;

WHILE(@p<1061)
    BEGIN
        INSERT INTO dbo.CustomerTippingPoint
                ( Value, Content )
        VALUES  ( 1062, -- Value - int
                  REPLICATE('x',400)  -- Content - varchar(400)
                )
        SELECT @p+=1;
    END
          

DECLARE @p INT
SELECT @p=0;

WHILE(@p<100)
    BEGIN
        INSERT INTO dbo.CustomerTippingPoint
                ( Value, Content )
        VALUES  ( 1063, -- Value - int
                  REPLICATE('x',400)  -- Content - varchar(400)
                )
        SELECT @p+=1;
    END
          

SELECT * FROM CustomerTippingPoint
WHERE Value < 1062

SELECT * FROM CustomerTippingPoint
WHERE Value < 1063

SELECT * FROM Customers

WHERE Value < 1062

GO 

If I return 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( adj.同一的; 完全同样的，相同的; ) queries, but 2 different Execution Plans! This can be in some cases a huge problem, because you have no Plan Stability( n. 稳定（性），稳固; 坚定，恒心;) 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 Point, I also suggest that you read my blog posting about it.

Summary

In this installment of the SQLpassion Performance Tuning Training Plan 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 on the other hand will degrade( vt. 降低，贬低; 使降级;) your write performance. Every index is maintained( v.保养; 坚持;) transparently( adv.明亮地，显然地，易觉察地;) by SQL Server, if you are performing 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( adj.经过验证或证实的; v.证明（ prove的过去分词 ）; ) technique on how you can verify if a given index change was a good or bad one, and how your write workload is influenced( adj.影响性的; v. 影响( influence的过去式和过去分词 );) 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( adj.合适的; 在（法律上或道德上）合格的; ) 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 to read and understand Execution Plans, and how they can be used for performance tuning. Stay tuned till the next week!