T-SQL Tuesday #21: Too Much Of A Good Thing Can Sometimes Be Bad

This is the first time that I am taking part in a T-SQL Tuesday event but it seems that Adam knew I was going to take part so he did something special to celebrate my post and made it a T-SQL Wednesday event this month (the real reason is explained in his blog post).

T-SQL Tuesday is the brain child of Adam Machanic (Blog | Twitter) so I shall use his own words to explain the event:
"T-SQL Tuesday is the SQL Server blogosphere's first recurring, revolving blog party. The idea is simple: Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic. The event is called "T-SQL Tuesday", but any post that is related to both SQL Server and the theme is fair game. So feel free to post about SSIS, SSRS, Java integration, or whatever other technologies you're working with in conjunction with SQL Server. Even if your post includes no T-SQL we still want to see it. The posts must go live on the second Tuesday of the month, by (or before) 23:59 PST."
This month Adam hosted the party and asked bloggers to share with the world the crap that they have done.

Now I wanted to talk about something recent that I did but I remembered that my bosses read this blog (sometimes) and I didn’t want them to be asking themselves if they made a right choice. I thought about putting up some of my old stored procedures but then I was worried that I might upset people like Itzik Ben-Gan (Site), Rob Farley (Blog | Twitter) and Jennifer McCown (Blog | Twitter). I eventually decided to go with a simple DBA situation that I faced around 2005 when I now started to really get into the DBA world.

My company had a system running for a few months which collected a lot of data at a fast rate and had no real performance issues. One day they needed to run a few reports on the system's database but the reports kept timing out. Being new to the team I wanted to solve the issue and prove that they didn’t make a bad decision in hiring me. I started reading up some articles to solve the issue and eventually came across the savior known as Index on the same day. I thought the sooner I implemented some indexes the sooner I would get the issue resolved.

The number of indexes that I put on a few of the tables has been removed from my memory out of shame but the result when I ran the reports was incredible. The next day the guys started noticing weird performance issues on the database server and of course the only change that had been made before it started happening was the newly created indexes. I started to do some further reading and realized that the number of indexes created were unnecessary for the reports and totally responsible for the performance issues that we were facing. Too much of a good thing can sometimes be bad….it is all about balance.

While I learned an important lesson about indexes that day I also learned some other valuable lessons. Reading is important, while it is possible that you have to read some new materials when an incident occur you should not only wait for an issue to begin reading. Patience is important, while you have a solution in your hand you have to weigh certain factors before implementing it. In that particular situation I didn’t need to implement those indexes immediately.

This topic is a really great topic for self-assessment. Being able to notice your mistakes and correct them is a sign that you have grown in many ways. Anybody who says they don't have a situation to write about for this topic is either in denial or just new to the game and have not had enough experience and knowledge to notice the crap that they have done.

2 comments: