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.

ANNOUNCING THE AVAILABILITY OF WINDOWS SBS 7 PREVIEW:

I am very pleased to announce that Windows Small Business Server 7 (SBS7) Public Preview is available for download.

Built on Windows Server 2008 R2, this exciting new edition of the all-in-one solution server for small businesses will include Microsoft Exchange Server 2010 SP1, Microsoft SharePoint Foundation 2010, and Windows Software Update Services. Small business customers will find significant security and management enhancements over previous versions, as well as much richer features for providing file-and-print, email and Internet services to employees.

Together with Windows SBS code name Aurora, SBS7 is dedicated to providing small business customers with a highly manageable, low complexity solution to help cut costs, save time and be more efficient. SBS7 offers on premise email and collaboration suite functionalities that Auroras customers can add through online services.

SBS7 also offers a great opportunity for small businesses with prior versions to upgrade their servers and to simultaneously take advantage of the advancements in security, reliability, and connectivity technology. For this reason, SBS7 offers enhanced migration tools, extensive pre-migration checks, and prescriptive guidance included in the software.

Furthermore, with SBS7, small businesses gain the freedom to securely access all of their communications from virtually any Web-browser or mobile device -- getting more done wherever they are with the integrated Exchange Server 2010 and Microsoft Outlook Web Access (OWA) capabilities.

Customers using SBS7 will also have the opportunity to view, edit, and share Microsoft Office documents online with SharePoint Foundation 2010 and Microsoft Office Web Apps. As the online companion to Microsoft Word, Microsoft Excel(R), Microsoft PowerPoint(R), and Microsoft OneNote(R), Office Web Apps gives users the freedom to access documents from virtually anywhere.

To learn more about Windows Small Business Server 7, you can visit our SBS site.

There have bee a lot of rumors today about the death of rock legend Ronnie James Dio, but it appears to be true now according to the official web site http://www.ronniejamesdio.com/

Rest in peace, Ronnie, and may you always be our Rainbow in the Dark.  \m/

Sorry for such a delay, but things have gotten really hectic at work.

The surgery went fine, she emerged feeling instantly better. The physical mass she was feeling is now gone, the heartburn is 10% of what it was, her appetite is back to normal (and then some). She has been sore, still has some pain every now and again, and her system is starting to figure out how to process food sans one organ. She was back to work in 4 days and has been working ever since.

I am unsure if we should keep her appointment with the specialist our local doctor scheduled for her a month ago. It's in July. At the time we were told of the appointment in July, I told the doctor she would miss the appointment...that she would be dead by then. And I was deadly serious. She was failing that fast. The doctor did not like my comment, but was unsure how to respond.

Thank heaven for the one doctor we found that was not affraid of a lawsuit and went ahead and operated, doing what he knew was the right thing. I asked him if he was concerned about operating on a patient that had no visible stones. He said, "If I was worried about every little lawsuit, I chose the wrong profession." He got my vote immediately.

Pathology revealed that there were multiple stones. Either they were tiny, which was not indicated in the report, or they ran all the tests wrong, or they can't read them from shit. Either way you look at it, someone (just about all of them) is incompetent. Stones are calcified...they show up. A word to the wise: if you think your doctor is operating in his best interests rather than your own, find another doctor. YOU'RE FIRED!

When asked about Win7, I find myself often speaking in the simplest terms to describe the product. I usually say "It has the eye-candy of Vista and the stability of XP". But really, it seems even more stable than XP ever was, except for Internet Explorer 8; that's just a dog. But I believe I have mentioned that here before.

Sometimes an even better part of something new is discovering some new trick that you can perform with it that you didn't know it would do when you bought it. Older Office users will know this as 'Easter Eggs', neat stuff embedded in the application that can only be accessed by special keystrokes or methods. That was Office: this is Win7. A little different concept, but cool none the less.

I have found two cool things that you can do in Win7 to gain extra functionality that MS did not advertise. I am hoping to find more, and if I do you will see it here, so stay tuned.

See attached files for God Mode and Restoring the Quick Launch Tool Bar from XP. Enjoy.

UPDATE: I have fixed the problem by adding the docs to a zip file.

Win7Goodies.zip (448.95 kb)

UPDATE 1/12/2010: Apparently, this is a developer tool that has been around for years. To see all their tricks, go here

About a month ago, Microsoft contacted me via email and asked me to take a survey giving feedback on their Partner Program, of which I am a member.
The benefit to me was that I would be entered into a drawing to win one of 5 HP Mini netbooks. As luck would have it, I won!

Congratulations! You’ve been selected as one of 5 winners in the Microsoft Partner Survey Sweepstakes!
Thank you for providing your valuable feedback about conducting business with Microsoft.
All partners who submitted feedback via the partner survey were entered to win a HP 5101 Netbook Computer
with Microsoft Windows 7 Professional and Microsoft Office Professional Plus 2010 Beta and you’ve won!

That kinda made my day. I will let you all know how this machine performs once I receive it.

UPDATE 02/22/2010: The HP Mini is a fantastic machine. Runs Office 2010 great, runs Skype great, fits in your pocket...well, almost. Get one, you won't be disappointed.

I have been using Win7 now for a few weeks and I have really put it through it's paces. I have decided I like it so much that I have upgraded every workstation or laptop in my possession worth using to Win7. I also have a few little tweaks that will add some familiarity of XP and Vista to your Win7 installation...look for them here soon. (hint: Quick launch bar and disabled services)

If I had to give a simple answer on how Win7 stacks up, I would have to say the it has the eye candy of Vista and the performance and stability of XP. So far, and as I have mentioned before, the only Reservation I have is IE8...it's total crap. I work with Sharepoint A LOT, so the single sign-on afforded by IE is pretty much a must, but if it were not for that it would be Firefox or Chrome, without a doubt. IF you can deal with a non-native browser (and most people prefer to), then consider moving to Win7. It is much lighter weight than Vista, and before long XP will no longer be supported. You can actually use older machines to run Win7, ones that Vista ran like a dog on, provided they at least meet the Win7 minimum requirements.

All told Win7 is a hit. Microsoft needed to hit one out of the park after the Vista debacle, and this one is outta here. Users can finally be productive again, IT staff can quit fretting about an out of date OS and how they will keep it safe. CIOs can finally get on with the budget and focus on enterprise apps rather than on what they should do about desktop deployment (sit and stay or jump and gamble).

Yes, the verdict is in: Windows 7 is your next operating system. Get up on it.

Okay, so I updated my main workstation yesterday from x64 Vista to x64 Win7. Only one issue found so far. The connection entries in my terminal services applet got deleted. No worries, I rebuilt the 48 or so server connections for here at work and I am on my way. This OS is just too cool. It took almost 3 hours to do the upgrade as opposed to the 40 minutes or so for a clean install, but to be fair it does have a lot to do in an upgrade. And this is no lightweight PC. It is a dual quad core 2Ghz, with 8Gb of RAM and a RAID disk system.

I only have one other complaint and that is IE8 - absolutely lame. It looks like MS could make a browser. The only reason I have to use it is Sharepoint and SSRS. Other than that it looks like I will be getting Firefox very soon.