Benchmarking SQL

When I work on improving database performance, I follow three simple steps: measure, measure and measure. Basic tool for that is simple timer. In SQL Server syntax that would read something like this:

CHECKPOINT; DBCC DROPCLEANBUFFERS;
DECLARE @StartTime DATETIME = GETDATE();

SELECT SomeField1, SomeField2 FROM SomeTable WHERE (SomeFieldN='Something');

SELECT [Duration] =  DATEDIFF(ms, @StartTime, GETDATE());

Although code is pretty much straightforward, first line requires some explanation.

SQL Server is pretty smart tool with lot of caching going around and testing on cached data is not something that gives consistent results. CHECKPOINT statement ensures that all dirty pages are written to disk and DBCC DROPCLEANBUFFERS ensures that cache is cleaned. Those two statements together basically force database to be in worst-case-scenario for performance but in best state for testing.

Of course, not all decisions can be made by this simplest of all tests. However, it will serve as pretty good rule of a thumb.

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: