And now for something not so different with week 6 of the Expert Performance Indexing for SQL Server book giveaway. So far, I’ve sent out five copies of the book based on comments from these (1, 2, 3, 4, and 5) posts.
Expert Performance Indexing for SQL Server is a book I co-wrote with Grant Fritchey (Blog | @gfritchey) last summer, which can be a valuable resource to anyone building indexes on SQL Server. As part of finishing the book, Apress sent me a number of copies of the book to share with people. I figured the best way to share these out is to give them away to people that comment on this blog.
So here’s a topic for leaving a comment this week…
The Question
For week six, the question will focus on chapters eight, which covers index myths and best practices. The topic for discussion in the comments this time is…
What indexing myths have you had to deal with? Have you had trouble with those who think databases don’t need indexes? Have you dealt with those that believe key column order doesn’t matter? Is there one you’ve dealt with that isn’t often talked about? Or how about an indexing best practice that you use?
If you have a story to share in this area, leave it in a comment below. After a week, I’ll select one of the comments and send that author a copy of the book.
August 1 Update
A bit late, again, on selecting the winner for this post, but after looking over the comments, the winner for the book this week is Peter Schott. Peter worked with the “order doesn’t matter” myth. This is probably one of my favorite myths, mainly because it logically doesn’t make sense that order would matter. Order does indeed matter for key columns and the selectivity or frequency of filtering on those columns makes a huge difference. This myth is dealt with directly in chapter 8. Learn more about indexing myths and best practices in Expert Performance Indexing for SQL Server.
The other comments this week were from:
- Aaron dealt with a problem of heaps and compression and how their maintenace appeared to interrupt the desired state
- Erik dealt with primary key columns needing to be added added as key columns to non-clustered indexes
- John had a client that had a ‘no downtime’ requirement and bad practices around index and statistic maintenance.
Strate SQL 
Hopefully this is relevant enough. I work on a 2008r2 system where an archive database is set up to receive aged out data on a weekly basis. It had been configured with compression on all tables (inherited… Of course). This DB was growing quickly, strangely so. On investigation it turned out some key ingredients were missing… All tables were heaps, so compression was not maintained on insert (bulk insert was not used either…) The weekly DBA maintenance only touched indexes, so no luck from a table rebuild either. So this DBs tables had compression enabled at some point in history, and at that point it did a great job. From that point onwards, not so much…
Of course if there had been some sensible incrementing clustered indexes this would not have been an issue!
LikeLike
One I’ve had to deal with is the Primary Key needing to be added to every Non-clustered Index as a Key Column for that Non-Clustered Index.
LikeLike
Here’s one from a long time ago – someone before me decided their standard should be heaps with non-clustered indexes, clustered indexes were not allowed.
LikeLike
I’ve hit a couple of times with “order doesn’t matter” and one or two who thought a single index per column would work as effectively as a covered index. Overall, I’ve worked with some really good co-workers through the years and haven’t hit horrible ignorance. The biggest issues I tend to hit are people who either don’t index at all or those who decide to take all of the advice of the DTA. 🙂 (More indexes is better, right?)
LikeLike
I inherited a client where two things we seriously wrong. to overcome performance issues they had set teh SQL service to have a higher than any other process, which did nothing but slow the entire server down. This same client also had automatic statics turned off and never once re-indexed because, and I quote, ‘We cannot have any downtime’ – Statistics were three years out of date. I was not allowed to take the system down for maintenance, updating statistics and re-indexing took around 72 hours, once I set the service priority back to something more sane.
LikeLike