Well, actually defrag, as in "should I defragment my indexes?" But, what I am really talking about is the method you use to defragment.

There are several schools of thought on this, and numerous scripts that you can snag from various places on the web that will help you do it, but ultimately it comes down to what works for you. I am talking about DBCC INDEXDEFRAG vs. DBCC DBREINDEX (SQL2005) or ALTER INDEX REBUILD vs. REORGANIZE (SQL2008+). Basically, a REORG vs. a REINDEX. Everyone will cite "Rules of Thumb", but a rule of thumb is what works for most cases. Most cases do not necessarily include my case, so I immediatly start to suspect these baseline rules - until proven. In my view,  a "Rule of Thumb" is considered a Myth until proven true. Some of my fellow SQL bloggers have based entire blog series' on busting SQL Myths. For me, I am learning to consider "Rules of Thumb" as potential myths, begging to be debunked. And this topic is no different, so on to the task at hand.

Whether your version of SQL requires the DBCC or ALTER syntax, the decision is the same: Do I rip all my indexes off and rebuild them, or do they just need tidying up? Both of these operations are expensive, but the requirements are different. A REINDEX requires that your database be out of service, as in no one using it. It is an OFFLINE operation. If you have a large database with many tables, or one with very large tables, or both, you may find yourself in a position that requires extended downtime. What if you don't have that time? Wouldn't it be nice if you didn't have to interrupt production at all? Well, you don't have to, just keep reading.

A REORGANIZE, according to Microsoft, is an online operation and can be done while the database is accepting transactions.

REORGANIZE Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

Of course there will be a performance hit while this operation is running, but some temporary system slowness is better than hours of maintenance downtime. Time is money. If the db ain't runnin', you ain't makin' money. (Sorry for that, it's how we say it in North Carolina)

Now comes the part where all the senior DBAs and MVPs will probably chime in and say I am wrong. I say Bring it. Numbers don't lie, and unless someone can prove to me I am doing damage, all I can say is that these numbers look good to me. The "Rule of Thumb" here is fragmentation threshold, and to a lesser extent, number of index pages. For example, the "Rule" will say, an index with < 30% fragmentation is a good candidate for a REORG: anything greater and its a REINDEX. I say do a REORG anyway. I have seen indexes with 99% fragmentation respond well to a REORG, sometimes falling back into single digit percentages. This is where number of pages becomes important. An index with a low number of data pages, say, below 100, will always contain some level of fragmentation after very little use. So, in this regard, it does not make sense to worry about indexes with very little data in them.

I have a script here that does all tables in a single database. This script is a compilation of 2 or three scripts, and some of my own handy work, but mainly from the one provided by Microsoft on index defragmentation. This script runs DBCC SHOWCONTIG and places the results in a temp table, runs the REORG using frag and page threshold parameters, then runs DBCC SHOWCONTIG again and compares the before and after fragmentation percentages so you can see how well it performed.

--DISCLAIMER - as with all code gleaned from the internets, test it thoroughly on non-production systems first. I am not responsible for any damage caused by use of this script.

Again, if there are any that disagree with my approach, feel free to speak up - I will listen to any and all responses. Until I see a reason not to use this method, I see no reason to cause a system outage over fragmented indexes.

This post comes as part of a challenge from the community, namely Adam Machanic [Blog] who is hosting #21 this month, centered around the T-SQL Tuesday (#TSQL2SDAY) theme. The topic for T-SQL Tuesday #21 is Crap Code. Little snippets of code we use when we are in a hurry, don't have time to 'do it right', or just simply don't know how to do it and can't take the time to learn it. It is temporary, just make it work...for now. I will go back and fix it later.

As Adam states, "As professionals, we must strive to rid ourselves of bad habits. And the only way to learn the difference is to see lots, and lots, and lots of examples." By accepting this challenge, hundreds of SQL bloggers will be exposing their crap code via independent blog posts. Once all the posts are tallied, there will be plenty of examples for us to avoid, thereby making this theme complete. So now, join me as I offer up one of my own examples for your coding displeasure.

I am a DBA for a large manufacturing company that uses Dynamics AX as their ERP. We have several developers constantly working in either the code or on reporting. This one dev is constantly cutting corners because 'he just needs to get it done now, quick and dirty'.  He's a dot Net guy, and not terribly compelling with SQL. Well, more often than not, I catch some of this quick and dirty before it makes it into my databases. (Some of you may know that AX allows the user to do just about anything to the database right through the GUI, including adding columns and even entire tables, without the DBA's knowledge or assistance) Control is nearly impossible. My expertise is usually only called upon when rescue is needed (as in "cleanup on aisle 3!").

On this particular day he was running some T-SQL to repopulate a 'custom' column in a native table that had somehow gotten populated with some NULL values and was screwing up his report (yes, he does reporting and has access to DML). And yes, it is a derived column, and yes I know about normalization. It's control I have a problem with (see above). For some strange reason, the database was denying him access to run his UPDATE statement in this DB (LOL) and he asked me to run it for him. He sent me the T-SQL in email, and this is what I got:

.

--- updates recs with correct producttype
UPDATE salesbookhist
SET producttype = (
		SELECT producttype
		FROM inventtable
		WHERE salesbookhist.itemid = inventtable.itemid
			AND inventtable.dataareaid = '004'
		)
WHERE (
		SELECT producttype
		FROM inventtable
		WHERE salesbookhist.itemid = inventtable.itemid
			AND inventtable.dataareaid = '004'
		) IS NOT NULL
GO

.

There are 1.82M rows of data in this table and he was trying to update one column. I asked him how long it ran on the DEV server and he replied, "about 3 hours". He did not believe me when I said it should take no more than a few minutes. I then proceeded to point out the flaw in his code. Again I was met with disbelief. I showed him some examples of simple JOIN clauses, but he remained adamant that his code was sound (I am not going to write it for him). I have seen similar code from him with nested SELECT statements going 5 and 6 levels deep. For those, I had mentioned using a CTE, just for readability (I hate trying to decipher someone else's crap code. The least they could do is make it readable). On this issue, however, he returned with a nice shiny CTE, but he said it still ran like a dog. Again, I suggested a simple JOIN.

An hour later he came back with a reasonably well composed statement that contained adequate JOIN syntax. It ran for 34 seconds.

I hope this post sheds some light on why we should never write (or allow in our databases) crap code.

I hereby resolve to work towards becoming a more proficient SQL Server professional. I will make opportunities to acquire knowledge, develop my skills, and grow in my profession. In working towards greater proficiency, I also resolve to enrich my relationships with family, friends and the SQL Server community. These people are my support system and are most deserving of my time and attention.