【SQLpassion Performance Tuning Training Plan】 - Week 7: Non-ClusteredIndexes

Last week I talked about Clustered Indexes in SQL Server. When you define a Clustered Index on your table, you are physically sorting your table data on the provided Clustered Key column(s). In addition to a Clustered Index, you can also define multiple Non-Clustered Indexes (up to 999) on a table in SQL Server.

A Non-Clustered Index is just a secondary index that you can define on some columns of your table. You can again compare a Non-Clustered Index with a book. But this time you have to think about a book like a T-SQL Language Reference. The book itself is our Clustered Index, which means that the different T-SQL commands are physically sorted by their name. And in the back of the book, you have an index. When you are searching for a specific command (like CREATE TABLE), you can use that index in the back of the book to find out where a specific command is described in more detail.

The book gives you here a lookup value - the page number - where you can find further details about the command. The same concept applies to SQL Server: when you are accessing your table through a Non-Clustered Index in the Execution Plan, SQL Server also gives you in the leaf level of the Non-Clustered Index only a lookup value where you can find further details about the record. SQL Server needs that lookup value to navigate from the Non-Clustered Index into the Clustered Index or Heap Table to find the additional requested columns from the record that are not part of the Non-Clustered Index This is a so-called Bookmark Lookup in SQL Server. Let's talk a little bit more about them.

Bookmark Lookups

 

Every time you access a Non-Clustered Index in the Execution Plan of a query, and you reference columns in your query that are not part of the Non-Clustered Index, SQL Server has to perform a Bookmark Lookup in the Execution Plan. The following picture shows a typical Bookmark Lookup in an Execution Plan:

 

 

 

As you can see, SQL Server performs a Non-Clustered Index Seek operation on the table Person.Address. And in addition SQL Server retrieves( vt. 取回; 恢复; [计] 检索; 重新得到) all the other requested columns through a Key Lookup (Clustered) operation from the underlying Clustered Table. This seems to be a very cool feature of SQL Server, but in reality Bookmark Lookups are very, very, very DANGEROUS! 

They can lead to Bookmark Lookup Deadlocks, and their performance will suffer with old out-of-date Statistics, or when you have to deal with Parameter Sniffing(vt.嗅;发现;) problems. Bookmark Lookups can only occur in combination( n. 结合; 联合体; ) with Non-Clustered Indexes. For that reason I also talk next week about how we can avoid Bookmark Lookups in our Execution Plans, and why SQL Server sometimes just ignores your almost perfect Non-Clustered Index.

Clustered Key Dependency

As I have said earlier, SQL Server stores in the leaf level of the Non-Clustered Index a lookup value that points to the record that is stored in a Clustered Table or Heap Table. When you have defined( adj.有定义的，明确的; ) a Non-Clustered Index on a Heap Table, that lookup value is calledRow-Identifier lookup value. It's just an 8 byte long value, which stores the page number (4 bytes), file id (2 bytes), and the slot number (2 bytes) where your record is physically stored.

If you have defined your Non-Clustered Index on a Clustered Table, SQL Server uses the Clustered Key value as the lookup value. This means that your carefully chosen Clustered Key column is part of EVERY Non-Clustered Index. There is a huge dependency(n.属国，属地; 从属，从属物;) between the Clustered and a Non-Clustered Index. And the Clustered Key is the most redundant( adj.多余的，累赘的; ) data in your table. Therefore you have to think really carefully when you choose your Clustered Key column(s). Because of that strong dependency, an optimal( adj.最佳的，最优的; 最理想的;) Clustered Key should have 3 properties:

• Unique
• Narrow( adj.狭窄的; 狭隘的; （种类或数目） 有限的; 仔细的，精确的;)
• Static

Keep that always in mind that your Clustered Key is ALWAYS present in the Non-Clustered Index!

Summary

Non-Clustered Indexes are very important for improving performance of your queries. When you introduce Bookmark Lookups through a not very well designed Non-Clustered Index, you can introduce huge problems and side-effects into your database. If you want to have a deeper understanding about the used structures used in a Non-Clustered Index, I also recommend that you watch my SQL Server Quickie about it.

As already promised I will talk next week about Covering Non-Clustered Indexes which can eliminate( vt. 淘汰; 排除，消除; 除掉; ) Bookmark Lookups. And we will also talk about the Tipping( adj.倾翻，倾卸;) Point, which defines, if SQL Server is using a Non-Clustered Index - or not. Stay tuned.