MSDN Webcast Q&A: Index Defrag Best Practices – Index Usage Questions

            Review the Q&A from the June 11 Webcast here.

            Review the Q&A page on SQLskills here.

Q: How does SQL server decide what index to use when there are multiple indexes in at table?

When SQL Server receives a request for data it goes through 5 primary steps: parsing, standardization, optimization, compilation and execution. The process of optimization (while a bit simplified here) includes multiple phases as well: query evaluation, index evaluation, join evaluation and so on. During index evaluation SQL Server determines whether or not USEFUL indexes exist to aid in processing your query. The usefulness of an index is based on the selectivity of the query and the columns requested in the query… There are many things for which SQL Server looks but the idea is that the better the information it has – the better the job of optimization. To ensure that the optimizer does a good job you should make sure to have auto update statistics and auto create statistics and you should consider using tools like Index Tuning Wizard to help you to create useful indexes.

While this is only touching the tip of the iceberg on these concepts last month’s webcast: Indexing Best Practices – will give you a lot more information. And – if you’re really interested… I offer a 5 day course in Performance Tuning and almost 2 full days cover almost nothing but indexes (nope, not kidding!). For details on the next SQL Immersion Event, click here.

Q: Why does the optimizer occasionally decide NOT to use an index(s)? In a few cases, we have had to force an index (e.g select * from a (index=index name) join b on a.id = b.id…

Also, related to how the optimizer makes decisions on index usage… and a bit hard to answer with certainty BUT there are a few reasons why the optimizer might make an incorrect decision. However, before I get there – are you sure that using an index was really the “right” decision? There are often cases where a query is not selective enough to use an index and a table scan is actually a better choice… But if you find a case where your queries performance improves when you force an index and SQL Server is unable to make that same optimization choice then I’d check these things:

• Make sure that statistics are current and up to date. First, make sure the auto create and auto update statistics database-level options are turned ON. Also, you can use these commands to get some information about statistics: DBCC SHOW_STATISTICS or the STATS_DATE function. If the statistics are up to date then you might also want to check to see if they were based on a sampling v. a full scan. In the output of DBCC SHOW_STATISTICS you can see the number of rows as well as the number of rows sampled. If the sampling is low compared to the number of rows in the table then you might want to automate an UPDATE STATISTICS with FULLSCAN. However, this can cause blocking so you’ll want to schedule it off hours.
  
• Maybe you don’t have the best index for the query… consider using Index Tuning Wizard on the query in Query Analyzer.
  
• Maybe consider re-writing the query.. If it’s a join consider a subquery and visa versa.
  
• If the command is in a stored procedure consider recompilation techniques. See this blog entry for more details on stored procedure recompilation issues.