{"id":708,"date":"2004-07-26T05:14:19","date_gmt":"2004-07-26T05:14:19","guid":{"rendered":"\/blogs\/kimberly\/post\/MSDN-Webcast-QA-Index-Defrag-Best-Practices-Index-Usage-Questions.aspx"},"modified":"2013-01-02T07:13:24","modified_gmt":"2013-01-02T15:13:24","slug":"msdn-webcast-qa-index-defrag-best-practices-index-usage-questions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/msdn-webcast-qa-index-defrag-best-practices-index-usage-questions\/","title":{"rendered":"MSDN Webcast Q&A: Index Defrag Best Practices – Index Usage Questions"},"content":{"rendered":"


\n

Before I launch into the Q&A from this area there are a few other resources with Index Usage Q&A that you should also check out:<\/P>
\n

            <\/SPAN>Review the Q&A from the June 11 Webcast here<\/a>.<\/P>
\n

            <\/SPAN>Review the Q&A page on SQLskills here<\/a>.<\/P>
\n

 <\/o:p><\/P>
\n

Q: How does SQL server decide what index to use when there are multiple indexes in at table?<\/o:p><\/B><\/P>
\n

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<\/I>) 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. <\/P>
\n

 <\/o:p><\/P>
\n

While this is only touching the tip of the iceberg on these concepts last month’s webcast: Indexing Best Practices<\/a> – 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<\/A>. <\/P>
\n

 <\/o:p><\/P>
\n

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…<\/o:p><\/B><\/P>
\n

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:<\/P>
\n