Saturday, February 28, 2009

Index Maintenance

I'm prepping a presentation for SSWUG on index maintenance. I have had experience with this in most of my jobs. some, I have even had to create systems to perform the maintenance. One such shop was ready to purchase hardware to upgrade their servers, but I started maintaining indexes periodically at night, the worst performing indexes, and it improved performance enough, that we didnt have to upgrade hardware, like they had previously intended. This little system was improved upon over the years, but would basically perform ShowContigs to get the status of indexes, then maintain them as needed.
In my current job, we have the need for maintenance after some ETL processes on a monthly basis. Tonight is one of those nights. After a couple parts of the process have been run, then we have to maintain the indexes. I've written an automated script that helps me out by dumping the results of the ShowContig to a temp table, performss maintenance on the worst performing indexes, and then runs another ShowContig. These results are gathered, and shared with the team to prove that things are in good working order.
Back to the SSWUG presentation. I have a virtual server that i am creating temp data for, so i can show off the steps of this system. I created a table, and was adding random names to cause an index on the names to get out of whack. Initially I found a sql script that had a ton of names in it, that I had used before to create tons of random names. After all that effort, I realized that adding my families names, in order, would produce an fragmented index.
My name is TJay. Starts with 'T'. My wife is named Christy. 'C' is before 'T'. So, alphabetically, these are not in order. Next is Kylie. Then Alyssa. And finally Treyson.
I created a simple table at work. Inserted 100 records for each name, in that order, and check the table afterwards, and its density, as reported by ShowContig, is not near 100, and indicates its out of whack.
I just thought it was pretty cool that the names of my family was in such an order that it could be easily used for a scenario for testing fragmentation. Obviously, its a better test scenario to add tons of distinct names. I just thought it was cool.

1 comment:

Topsy said...

You totally lost me, but I still love you the best smarty pants!