Index Optimization and Statistics
Appropriate indexes are often critical for optimal query performance. Looking on the internet for directives for index optimization can be pretty helpful. Here is an interesting Conundrum for any DBA. One site tells you to create multiple indexes on individual columns for performance; SQL Server will join the different indexes as necessary and utilize the intersection for composite performance. Another site tells you a composite index is best for performance on multiple column queries as long as it is a best fit index.
I won’t give you the standard DBA comment “It Depends.” I have found that the multiple index option works for situations where queries vary considerable, and indexes would abound for many different kinds of queries. I have also found a specific composite index can outperform multiple single column indexes.
Recently I experienced degraded query performance on a query where the query optimizer was choosing my query specific composite index, perfect for solving the requirements. Still, performance was horrible. Looking at the query plan I see warnings that there are no statistics on columns included in the composite index…so I think, who cares? I have the perfect index, and the optimizer is selecting that index; performance should be optimal.
This is brand new table in a development environment. I have the setting to automatically create statistics turned off. So, I create the requested statistics on the columns of the composite index, and query performance drops from 15 seconds to 2 seconds. Using show plan, the query plan is identical to the previous plan.
Perhaps some of our readers have an answer regarding why those statistics make a difference in the overall query performance. Leave your comment online or drop an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
How do companies prevent remote workers from feeling left out?
I received the following question from a reporter: How do companies prevent remote workers from feeling "left out"? Incentives, such as bringing in lunch to headquarters staff and allowing casual attire in the office after employees meet certain goals, do nothing for those who work from home. What techniques improve or solve this issue?
Featured White Paper(s)
Ten Tips for Optimizing SQL Server Performance
read more)