Should I REORGANIZE or REBUILD?

As I work on adding new stuff to existing database there is good chance that indexes will get fragmented. On quite a few occasions I got them in 90% fragmentation range. And that affects performance.

SQL Server 2008 has two main ways to fight fragmentation. One is REORGANIZE command (e.g. “ALTER INDEX ALL ON MyTable REORGANIZE”). This will do simple shuffling of existing data. No new allocations will be done. Unfortunately this also means that, especially in case of multiple files, some things will stay unoptimized.

For worst-case-scenarios there is REBUILD (e.g. ALTER INDEX ALL ON MyTable REBUILD). This command creates new index instead of current one. This will probably cause some new allocations and thus it will take a little longer, but final result will be impeccable.

Microsoft has really nice page that explains both how to check fragmentation and what can be done to solve it. Rule of thumb there is that you should REBUILD once fragmentation is over 30% and REORGANIZE when fragmentation is lower. I view this as quite good rule if you have SQL Server Enterprise. However, if Enterprise is not your edition of choice, things change a little.

Since I mostly work with SQL Server Express, only question that I ask my self is whether database can be offline. Below Enterprise edition, REBUILD operation CAN NOT be done while other operations are pending. If database has to be accessible, only option is REORGANIZE.

I do not mind that much since REORGANIZE is usually sufficient. And, if changes are large enough that they would cause real fragmentation mess, they are also big enough to allow for offline REBUILD.

I find that Microsoft has right to limit some functionality (in this case online rebuilds) to certain editions of their software. However, I do hate when they don’t find it necessary to document this “small” difference. On other had, if Microsoft did all documenting, I would have nothing to write about.

P.S. This represents my view as a programmer, database administrator might not approve this. :)

Wireless Wake on LAN

While roaming through TechNet (yes, I do have weird habbits), I stumbled upon explanation how wireless Wake-On-LAN (WoWLAN) can be used with Windows 7.

Since I often connect to my network from far-far-away, I do have a way to wake networked computers. Using MagiWOL and having them connected via cable is perfectly fine. Issue here is that not all my computers are connected via cable.

This article gave me perfect solution. Unfortunately, Broadcom card in my laptop (HP 6730b) was not capable of this. There is some power management included but not a waking ability. This came to me as surprise.

This is rather new computer and Broadcom does create network cards that have this ability (just check Apple’s computers). I would bet that even my card internally supports this. However, PC manufacturers are lazy bunch. If nobody is forcing them, there is no way that they will include new stuff in driver by them-self.

Where that leaves us? Well, my personal opinion is that Microsoft should mandate wireless wake-on-LAN for it’s WHQL driver testing. Only once they start to require it, some improvement will be done.

And, now, historical moment - first question ever on this blog!

Does anybody have a Windows 7 computer with Wireless wake-on-LAN capable network adapter?

VHD Attach 1.70

VHD Attach just got new version. It is mostly maintenance release with few bug-fixes and some internal rearranging. Without further ado, I will just tell that download is available.

QText 2.31

Of course I messed-up.

QText 2.30 could not be installed on clean system. Upgrade procedure worked just fine, but initial install would not create it’s directories.

This is sorted in this release. Sorry for any problems you had.

Download:

• Installation
• Zipped executable

QText 2.30

After more than a year, new version of QText is finally out of beta. That time was spent mostly on rearranging internals which is not really visible but I do have few changes that should make you want upgrade.

Probably most desired request was to be able to hide tabs. This is something that I postponed for quite a while, but time has come. Since QText tries to keep underlying files readable with any program, this change was done via flipping file’s hidden attribute.

Second big change is ability to detect changes in open files. It was implemented in different ways through-out beta until I finally settled on this implementation.

Additional improvement for those running QText in corporate environment is that, when QText is started without installation, no settings are saved to registry. Program should leave no traces on computer. Unfortunately this means that no settings can be saved but this is necessary trade-off.

Program can be downloaded as full installation and as zip archive (for no-trace setup).