Microsoft SQL Server 2012 Bible

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 define 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 designing a proper indexing strategy for what works best for your environment. There is no secret formula to define 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 defining 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 constantly 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 ...