Free indexing books, awesome!
Before I tell you how, lets start with what we are talking about here. If you weren’t aware, last summer I worked with Grant Fritchey (Blog | @gfritchey) to update Expert Performance Indexing for SQL Server. The new edition jumps from 10 to 15 chapters and contains a bunch of new stuff. This includes material that didn’t have a chance to include in the first edition, some chapter reorganization, and updates to include information for SQL Server 2014.
So, how to give away these books?
I figure the best way to do this is to talk about indexes. For the next 13 Fridays, I’m going to propose an indexing questions related to different chapters of the book. The week following each question, I’ll select one of the comments at random and send out a book. Basically, tell a good story, make me laugh, share something unique and you got a chance to get the book.
For week one, let’s start with some fundamentals of indexes and bullet 1 from chapter 1 – why build indexes? What interesting adventures have you had in the past where you built an index and dramatically improved performance? Leave your thoughts in a comment below and I’ll select the first person to receive a book a week from now.
June, 10 Update
The winner for the book this week is Cujo DeSoque. I loved how he identified a poor index and then disabled it when he wasn’t allowed to drop it. Solving both requirements… in spirit.
Here’s a summary of the other comments:
- Tim talked about how improving performance with an index led to him becoming a DBA.
- Todd talked about crazy DBAs who had no indexes.
- Ken included a story where indexes were avoided due to their percieved imapct on triggers.
- SQLPRODDBA performance was improved from 30 minutes to 2 seconds with an index.
- Lohith shared a situation where a lack of indexes led to CPU utilization issues.
Thanks all for sharing your stories. Be sure to check out the next set of indexing questions and a chance for the second book.
Strate SQL 
when I started looking into performance issue, as rookie DBA, i was surprised to see table(that was frequently used to load data) with 0 indexes on it. This was causing heavy cpu utilization during nightly loads. The issue got resolved after the proper indexes was created.Also, The expert performance indexing which was bought in 2014 has changed my perspective on indexes.
LikeLike
Thanks for the comment, Lohith. Glad to hear that the previous edition of the book was helpful for you.
LikeLike
Indexes are generally useful in faster data retrieval. I had a situation where one of our customer reported poor query performance while fetching some business reports. Upon examining its execution plan, it was clear that it was lacking meaningful index. Estimated execution plan did give a hint by throwing missing index warning and there were some obvious warning signs as well in execution plan like index spool, CL index scan delivering more than required data etc. After reproducing the scenario in test environment and creating meaningful NC index(meaningful as in not the dumb index suggested by SQL server :), but I must say it was a good starting point), the performance improved drastically. The query which took 30 mins to execute was now performing in less than 2 secs. Obviously, our customer was very happy by seeing this much improvement and so were we. Lesson learnt: SQL indexes rock!(But only when you add meaningful indexes. Otherwise it can kill you too. :)).
LikeLike
Thanks for the comment, SQLProdDBA. Meaningful is an incredible important bit about creating indexes. Great improvement that you provided.
LikeLike
A bit of background, this was for a recently implemented system with frequent and ongoing roll outs of new units from the legacy systems so the tables were growing VERY rapidly.
My favorite index story was for an SQL Server 2000 system where the developer had a VP approve (read: demand) bypassing change control policy/QA testing/review and created the index themselves and ramrodded it through, rather than fix the code as they were instructed. Well, they did change the code to force the use of the index and changed it to a “select *” and claimed he optimized the code as much as possible. Gotta be fair. The design of the index was predictably bad as columns were added to the index seemingly in random order. It was a disaster but there was a lot of pressure on the developer from the VP to push it out. The optimizer made a lot of mistakes in those days too.
Once it hit production, it made it run up to about from 4 to 9 times slower depending on the variable input. We are talking up to 14,000 seconds, folks. I didn’t know that was possible at that time. Welcome to IT.
Of course this was the fault of the DBAs since an index is magic. Dammit, I said MAGIC! Bow to the index!
As the original running time was about 1000 – 2000 seconds and escalating with more data, it obviously had to be something wrong with the database and not the code. The VP said it was, so it had to be true. Can’t argue with that logic unless you quit the job.
Removing the index brought performance back to “normal” which meant the VP still blamed the database admins. As long as it could be blamed on someone else, I suppose it was win-win from his perspective. That counts, right? No point in trusting the advice and evidence compiled by the consultant (me) who you’re paying loads of bucks when you can have a VP solve everything by delegating.
The index itself was on about 12 columns, coincidentally that’s exactly how many the table had. Imagine that. The order seemed to be random and it was, it turned out the developer randomly added columns to the index in the order of testing and apparently became disgusted with the work and claimed a success even though when we went back to the development system and it displayed the same horrid running time. “But it worked in development until you tried it! What did you change?”
At that point it was over the fence and in my yard. I was told to fix it by the end of day tomorrow and to do anything I wanted to fix it. Woo-hoo! By this time I already knew what to fix, basically my recommendations they ignored, and it took about 40 minutes to redesign the index in dev and make the code changes on a copy to allow parallel testing.
Simply putting the columns which would always be populated by the where clause in the query first and eliminating the columns the program would not use from the index (no includes in this version) as well as fixing the “select *” that was introduced during this “optimization” got it to an acceptable level of performance which was less than 100 seconds. The query in question had subsecond response times after that but other parts of the (hideous) program were responsible for the rest. At the start it does a full table scan of a huge driver table three times to get three different values for a column and puts them in three separate temp areas.
They eventually rewrote the program with the help of a developer with a clue but wouldn’t let us remove the index created for the now obsolete program because it “did such a great job”. I disabled it instead and an upgrade was used as a pretext to finally remove the index altogether.
The original developer? He left not long after and hear he’s running a sandwich shop back in his home town. I’ve resisted the urge to order some crow sandwiches with extra salt under his name so far.
PS: Option #2: Blame the consultant. There is no option #1.
LikeLike
Thanks for the comment, Cujo. I love that you kept in the spirit of the requirement to keep the index by disabling it. I might have to use that trick in the future.
LikeLike
This one is more likely to make you cry (or at least shake your head) than laugh, but ..
Recently we had a service group come to us asking if we could archive off a chunk of data. Now they want full access to it, and they want it completely available to the application, etc, etc. So after a brief look we found that this database of 50 tables (some of them in the 4-5 bil row range) had exactly 3 indexes (none of them clustered).
A few points on usage. The tables are archive tables being fed by update/delete triggers on application tables. 99% of the access of these tables is “All rows that match these 1-3 columns”, always the same column(s) for a given table. So of course we suggested indexing! Clustered index on the ever increasing insert date column, and a NCI on the column(s) being used in the queries. We spent some time getting the indexes in place in dev (similar size to prod, ie multi billion row tables). We ran a few tests (changing response time from 10-20 minutes to subsecond) and then asked them to approve moving to the test environment.
They came back and said their vendor contact was concerned that putting indexes on these tables might hurt the trigger performance on the application tables and they didn’t want to proceed. Needless to say production performance is still unusable. Oh, and the performance of the application triggers in dev? No change.
LikeLike
Thanks for the comment, Kenneth. Those guys sound crazy, especially the vendor. It sounds like the most important thing to them is the performance of getting data in versus getting it out.
LikeLike
Yea, I’d agree with you if it weren’t for the random way the rest of the databases are indexed. Some tables have no indexes, others have a single column index on each column.
LikeLike
It could be that the application tables would also
benefit from some decent indexes. Those deletes and updates from the
application can often benefit from using an index (about the only thing that
generally won’t at all are the inserts). And, if you find that a lot of those
single column indexes can be dropped (in favour of fewer multi-column ones) –
even better still (it might end up speeding up EVERYTHING).
You will deinitely want to gather some baseline performance data first (if you
can), possibly with Perf Mon (or similar) and some SQL Profiler traces from
production. The latter can form the basis of a workload analysis to figure out
what indexes would be of most benefit to the application SQL in use on your
particular system.
Keep up the pressure. If you can show performance benefits on the
application tables themselves, then they’ll almost certainly give way on any
new ideas you have on the archive tables (which would probably take much longer
to implement anyway).
Remember that if the data is not accessible in a reasonable timeframe – it
isn’t worth keeping online. The original request for archiving was probably
along those lines. Maybe you could create a completely separate database from
the recent archive data, indexed “your way”. If you then set-up some
SSRS reporting on that new database to allow users to “run queries”
through a friendly interface – you might just be their new DBA god.
Of course, test, test and re-test before you make any changes. A DBA that
breaks stuff in the first few improvements will not get much further. Of course
first you’ve got to grind those stats from your baselines and Profiler /
Extended Events, before picking the small handful. The DTA can do that for you,
but you need a lot expertise to interpret the results – which is why it is kind
of self-defeating. Lastly the missing and unused index DMV’s are probably
you’re best friend in this situation. Again, a bit like the DTA, you have to
talk the information with a pinch of salt – and sift it very carefully. But,
they can be a fairly rapid route to finding the right indexes to try out in
your tests.
LikeLike
I had the chance to work on a mission-critical OLTP ~250GB. Based on some whacky guidance and some crazy dba(s), they had ZERO clustered indexes. After I showed them the Statistics IO numbers before and after, it still took 6 months before we could get and outage window. All indexes got re-built and things started to purr.
LikeLike
Thanks for the comment. I’ve run into this a few times. Getting push back on the obvious performance advantage for a change in lieu of keeping things the same. Even though the same sucks.
LikeLike
Aha, sounds like the “Oracle DBA rules” have been blindly applied to a SQL Server database design. Many heavily Oracle-based shops, or just a few tech leads / managers who did Oracle many mons ago, often apply the “All clustered indexes are bad” heuristic. Which just doesn’t make sense for SQL Server.
LikeLike
Building an index to dramatically improve performance is why I became a full time DBA. I was working as a sysadmin where I also had some limited DBA duties – taking care of backups and restores, creating accounts, etc. One of the application teams we supported was having some performance issues. Our full time DBA was busy on other tasks, so I took a look. I do not remember the details, but I do remember that one index reduced the query time from 30 minutes to 4 seconds, improving performance by several orders of magnitude. That was the moment I decided to become a full time DBA.
LikeLike
Thanks for the comment, Tim. I had one of these moments a few months back. They always come with great feel good moments.
LikeLike