1006
Part VII: Performance Tuning and Optimization
Zen and the Art of Indexing
Indexing and performance tuning are a mixture of art and science. The science portion
includes knowing the details of how indexing works under the covers and when it makes
sense to defi ne indexes on the appropriate columns on a table. The art portion includes
knowing when to stray away from what conventional wisdom tells you to do and design-
ing a proper indexing strategy for what works best for your environment. There is no secret
formula to defi ne the correct indexes. An indexing strategy that works great for one system
may perform poorly on another system.Another aspect is that an indexing strategy is ever-evolving. Design and implement indexes
for what your system needs at the present time. As your system evolves, so too must your
indexes. Be careful defi ning indexes that your system does not currently use but may use
later. This type of strategy often ends up in indexes not being used, but having to be con-
stantly maintained.What’s New with Indexes?
New in SQL Server 2012 is the FORCESCAN query hint. Use this hint to ensure that a SCAN access
method is used to retrieve rows from a table or an index.One nugget of knowledge to take away is that a SCAN operation is not inherently evil. You may have
heard colleagues talk about how evil scans are and how you should have only SEEK operations in query
plans. Seek operations, although desireable for OLTP environments, are not without their perils. A seek
operation coupled with a Bookmark Lookup operation can quickly become much more expensive than
a SCAN operation. Always use query hints with caution.Also new in SQL Server 2012 are Columnstore Indexes, which are reviewed in the section titled “The
Columnstore Index,” later in this chapter.Indexing Basics
The following section presents an overview of the main indexes available in the SQL Server
database engine. The rest of the chapter builds upon the ideas introduced in this section.The B-Tree Index
The two main types of indexes in SQL Server are clustered and nonclustered indexes. Each
index type is implemented via a balanced-tree (B-tree) data structure. A B-tree is a struc-
ture that stores data in a sorted order and enables fast access to the data it holds.Although technically not the only type of index present in the database engine, this chapter focuses
exclusively on the clustered and nonclustered indexes. Other index types, such as Spatial and XML
indexes, are outside of the scope of this chapter.c45.indd 1006c45.indd 1006 7/31/2012 10:16:37 AM7/31/2012 10:16:37 AM
http://www.it-ebooks.info