Optimizing for Ad hoc Workloads

One of the nifty features available in SQL Server 2008 is the "Optimize for Ad hoc Workloads" option, which is at the instance level.  If your workload involves a high number of ad hoc queries that are used only once, your plan cache (also known as the "procedure cache") may benefit from enabling this feature.  (There are, of course, other solutions to this issue as well, a primary one being to use less ad-hoc SQL and more stored procedures, but that may not always be possible.) What it does SQL Server has an upper limit on the size of its…
Read More

Disaster Recovery Strategies for the Home

A key function of a DBA is to plan, test, and implement disaster recovery policies for the databases they maintain.  DBAs lacking these skills may also find themselves lacking a job if they are unable to recover data when necessary.  For all these skills we apply in the workplace, I have still heard via Twitter a few tales of woe regarding lost data in the home. Having a disaster recovery strategy for your personal data is every bit as important as having one at work - maybe even more so because your precious memories are at stake.  I don't have…
Read More

Transactions: Taking The Plunge

A few days ago Tim Ford (blog | twitter) put forth a challenge to describe a transaction in a non-technical fashion.  I could have replied to the post directly, but I figured it would be rather wordy.  Instead I decided to follow the lead of Janice Lee (blog | twitter) and write a post about it, providing Tim with a neat and tidy hyperlink instead. One thought I had is that committing a transaction is like jumping off a diving board. It's Atomic, meaning it happens or it doesn't, there is no in-between.  If you jump off the board, you'll…
Read More

More Than An Output

No more than an output to me, but it sure was to SQL Server!  This issue kept me busy over the weekend, and most definitely gave me a case of Saturday Night SQL Fever (as opposed to "SQL Saturday Night Fever", for which the only cure is more karaoke). When executing a stored procedure with an OUTPUT parameter, I always believed that the pre-execution state of a variable being written to by the output parameter didn’t matter, as it would be overwritten. A quick poll on Twitter showed me that I’m not alone, however I recently discovered this isn’t always…
Read More

Why My Newest Laptop Is A Mac

I've never been a fan of Apple.  Despite making a living off SQL Server, I don't consider myself to be a Windows fanboy by any stretch of the imagination either, but I never imagined that I would buy a Mac. One of the easiest arguing points back in the day was the availability of software.  Games on a mac?  There was that sliding apple puzzle thing, that's about it.  (Ok, there were more than that, but if I couldn't play Doom II on it I wasn't interested!)  Then they switched to Intel processors and OSX got better and better.  The…
Read More