Poor Index Design

Poor index design is another reason—often a primary reason—why queries might not optimize as you expect them to. If no supporting indexes exist for a query, or if a query contains SARGs that cannot be optimized effectively to use the available indexes, SQL Server ends up performing either a table scan, an index scan, or another hash or merge join strategy that is less efficient. If this appears to be the problem, you need to re-evaluate your indexing decisions or rewrite the query so it can take advantage of an available index. For more information on designing useful indexes, see Chapter 30.

Get Microsoft® SQL Server 2012 Unleashed now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.