I took the SQL Server 2000 Best Practices Analyzer Beta for a spin this evening in a virtual PC. The download and install worked without problems. The installation needs to create a database to store rules and report results – the default name is sqlbpa.
After registering a SQL Server to analyze I went to the Best Practices screen, where I selected “Create Best Practice” and began to look through the available rules. Rules fall into the following categories:
Each category contains a set of rules, many of which you can parameterize. For example, you can check to see if databases have successfully backed up in the last X days (where X defaults to 30, but you can enter your own value).
Here are some of the interesting rules you can run:
Index fragmentation: you can specify the maximum value for fragmentation, and the minimum number of pages an index must have to be included in the scan.
File placement: ensures data and log files are not on the same drive.
Unexpected shutdowns: looks in the NT event log and flags any unexpected shutdowns.
Object Prefixes / Object Suffixes: enforces naming standards. You can select a prefix / suffix for each object type, although it is not clear to me how to setup more than one type of object to scan. Perhaps this is a beta issue.
NULL comparisons: scans stored procedures, views, triggers, and functions to find equality and inequality comparison with NULL constants.
Temp table usage: looks for opportunities to replace temp tables with table variables.
Once I had a set of best practices defined I could move them into the execution area and scan one more or SQL servers. Reports are saved into the database for viewing through the UI.
This looks like an extremely productive tool for watching databases both in development and production. There are managed code assemblies in the bin directory, and possibly room for extensions, as a quick peek in the BPA database shows assembly names. Perhaps custom rules can be put into a custom assembly and registered in the database also.
Definitely going to add this tool to the toolkit.
I never realized you bypass the network protocol stack when connecting to SQL Server on the the same physical machine. At least you CAN bypass the protocol stack if you use the correct settings. I learned this from Ken Henderson's SQL Server Connection Basics on MSDN. Excerpt:
You can indicate that the shared memory Net-Library should be used by specifying either a period or (local) as your machine name when connecting. You can also prefix your machine\instance name with lpc: when connecting to indicate that you want to use the shared memory Net-Library.
I regularly connect to a SQL instance on my desktop at work by using SALLEN\dbs1, meaning I am using the protocol stack instead of shared memory. Shared memory is generally quicker, but not always. As Ken says, make sure to test first for your specific environment.
Of course, for development, I'm sure it won't make a noticable difference at all, but this is a good tip to remember. I always assumed SQL would not optimize for a local connection, but then my assumptions always come back to bite me.
Yesterday I was assuming I was in the correct departure gate for a flight out of Toronto. To make a long story short – I discovered with 5 minutes to go before boarding that I was in the wrong area. The conversation went something like this
Wondering Woman : Is this the flight to Pittsburgh?
Me: I think this is the gate for a flight to Baltimore
Man in another seat: I’m here for a flight to Indianapolis.
I knew two of us had to be wrong, and one of those two was probably me. For some reasons airports do their best to confound me. I had to move from gate T to gate E in YYZ’s Terminal 2, which, as one airport employee described to me with a grin, is “one heck of a walk”. Yep. I felt like I was trying to break the 4 minute mile with two carry on bags in tow. It also involves a shuttle bus ride. The kind of shuttle bus ride where you want to pound on the glass separator and tell the driver to floor it but are restrained just enough by the thought of being arrested as an airport lunatic. Fortunately, I made the plane and was on enough of a post-panic comedown to actually nap for a bit.
Transmissible data flowing out violet blue ports,
My types are all hidden, only schemas export.
Pixilated scenes replaced by vertex shaders,
My side-bar includes fancy blog aggregators.
With left angle brackets I declare gradient fills,
We are long past the choice between red or blue pills.
Longhorn release: how long will you be?
I need to start planning my new hardware spree.
Every time I write multithreaded code I worry. Perhaps it is because I read stories like the final report on the U.S. / Canadian blackout of August 2003. The problem was a race condition:
"There was a couple of processes that were in contention for a common data structure, and through a software coding error in one of the application processes, they were both able to get write access to a data structure at the same time," says Unum. "And that corruption led to the alarm event application getting into an infinite loop and spinning."
Seems simple, but consider:
"We text exhaustively, we test with third parties, and we had in excess of three million online operational hours in which nothing had ever exercised that bug," says Unum. "I'm not sure that more testing would have revealed that“.
Stories like the above, and about the Therac-25 machine which killed three people, always make me worry I’m missing something. I’ve never worked on radiation therapy machines or power grid software, but years ago I did some development for a machine that would take your blood pressure using a real cuff, and I’ll admit to being apprehensive about sticking my arm in for the squeeze.
The other reason I worry is because it is nearly impossible to test for race conditions.
Functional testing and stress testing may catch race conditions with some luck, but these bugs are hard to bring to the surface. Black box testing in general, with no knowledge of the implementation, has little chance to uncover a race condition except as a side-effect.
Unit testing is also little help. By definition, unit testing focuses on a small and specific piece of code and there is no interaction from other areas of the software. In addition, the exact timing conditions required to trigger a race condition bug have little chance of happening during a single-run pass or fail test.
In the end, a thorough code analysis (more sets of eyeballs) is the best tool to uncover race conditions. I remember a recent jaybaz blog entry showing code I’ve seen and written many times now in .NET and I’ve never thought about the potential race condition there. Sigh. Just goes to show race conditions are the most subtle bugs I know of.
I’ve been playing around with the web service API of Reporting Services again and I seem to have stumbled across a little bug. I plan on posting some code soon to show how to programmatically create a subscription – it took much longer to get something to work than I had originally thought and only part of that time was wasted on the bug.
While working on this I downloaded the Reporting Service Documentation Update to help out. I’ve noticed a number of documentation updates for 2004 products released this month (Biztalk 2004 has 1, 2, 3 updates this month). I’m wondering when every product will move to the MSDN style quarterly updates, or something akin to the Office 2003 Online help. Help is easy to update, unlike anti-lock brakes….
If you could pick one brand of vehicle on the road with a “software anomaly” in the braking system, would it be:
a) The Mini Cooper (like Rory Blyth drives)
b) The Cadillac Escalade (like Queen Latifah drives)
My choice would be A, because of course, a Mini would probably sustain more damage in a collision with my mailbox than my mailbox would, but the real vehicle with a brake bug is the Escalade, which could probably drive through the side of my house with no damage to the occupants. Thats 12,329 SUVs to recall.
Ah well, sleep beckons.
Maybe 10 months ago I downloaded Orca to tinker with an MSI file on a setup and installation project. While it was nice to twiddle inside of the MSI tables with Orca (ever read the story behind Orca?), the experience is nothing like Wix, which can decompile an MSI file into XML. This is great, because I can now check installations into a source repository as a text file and do DIFFs if needed.
Of course, you can also compile an XML Wix file into an MSI, which means no more fiddling around in a GUI to make changes in an opaque file for you. For some reason Visual Studio setup projects always worry me because I don’t know exactly what I’m changing when I do a save. I always feel there is something sinister at work in the background.
Even more interersting: the project has been open sourced by Microsoft. It is definitely a strange feeling to go to SourceForge to download MS bits.
That leaves DTS packages as the only other binary pieces under source control that I'd rather see as XML.