Keeping it Contiguous in SQL Server DBCC IndexDefrag is your Friend! by William Ryan

Quite often, databases evolve into things that their original designs never had in mind.  This usually isn't good.  There's also not much you can do about it, 'feature creep' is a fact of life in the IT field.  However, as a develoep or DBA, you often get 'deal breaker' requests that don't play well together.  Few things I've encountered fit this description better than indexes.

At first, your app has a few rows and no matter how terrible your query, it runs fast.  It makes it the whole way through the testing phase with blazing speed.  Then the dreaded production phase.  After a while, users are complaining "This thing is really slow".  Little by little, the problem gets worse.  

Now, if you are  like most IT managers who don't write any code, the solution is simple.  Add an Index! After all, what self-respecting non-programming middle manager doesn't know that Indexes are the Lord's divine cure for all performance problems? Well, if I never used a database, I might think the same thing (well not really, but I digress).  So you slap on a few indices, controls area loading like greased lightning and the manager is running around proclaiming her brilliance.  Then this annoying nuance sets in.....  Gee, why are the inserts so slow?  And why do things get bad every few days.  And why can't the stupid DBA figure out a way to not have to reindex every week?

The simple fact of life is that Indexes aren't a cure all.  They work wonders when they are used correctly, they turn into nightmares if they aren't.  Every index you add needs to be maintained.  Every time you drop and recreate an index (or use Reindex), table locks kick in which almost always means downtime.  Downtime sucks.  So what do you do?

Well, in many ways it's a case of reaping what you've worked so hard to Soe.  However, there are some things that can mitigate the problem....

Before I continue, let me emphasize something I just mentioned.  Reindexing involves downtime for one simple reason - Table Locks!  At this time, there really isn't any way around this.  After all, if you were trying to create  a book index, could you do it if the pages were changing every second?  Fragmentation is one big part of the problem (however, I'm no way implying that fragmentation is the only consideration when you are dealing with indexes).  Why does fragmentation present a problem in a database index?  Well, for the same reason that it does on a file system.  The way disk drives work, you are either accessing the data sequentially or randomly.  Obviously if your drive head isn't bobbing all over the place and can just do straight reads, it's going to perform better.  So what do you do?


Our good friends on the SQL Server development team gave us a gift, DBCC IndexDefrag!  What's the difference between IndexDefrag and Reindex?  Well, a lot. At the risk of oversimplifying the difference, allow me to sum it up in two words TABLE LOCKS.  IndexDefrag allows you to  reorgainze your indexes so that you can take advantage of sequential access, without locking everything down and pissing everyone off.

If you seach for DBCC IndexDefrag in BOL...here's the long and short of it....

DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
)

Arguments
table_name | table_id | 'view_name' | view_id
Is the table or view for which to defragment an index. Table and view names must conform to the rules for identifiers.
index_name | index_id
Is the index to defragment. Index names must conform to the rules for identifiers.
WITH NO_INFOMSGS
Suppresses all informational messages (with severity levels from 0 through 10).

This is all pretty straightforward and allows you to defragment an index without the locks. But, if you want to automate things (and what DBA doesn't), here's a little script I wrote today to iterate through your indexes  and defrag them....

CREATE PROCEDURE DailyDefrag AS DECLARE @Counter INT SET @Counter = 1 WHILE @Counter <= 7<BR>     BEGIN        DBCC IndexDefrag(JobTracking, Tbl_Job_Tracking, @Counter)     SET @COUNTER = (@COUNTER + 1)        END GO

All it is is a little T-SQL Loop that runs through your table and defrags everything.  It's easy to turn into a Job and let SQL Agent make your  life easier.

This is by no means a cure all for everything, but it may help you out if you inherit a situation where indexes rule!