Imagine yourself, happily computing (or whatever it is that you do with your computer). It’s a fine, sunny day, narry a cloud in the sky, and you’re happily typing along when all of a sudden you hear a rather alien sound eminating from your hard drive. Something that sounds, perhaps, like some combination of a roofer banging in a nail, and a miner’s pick as he works on releasing a stubborn piece of ore from a cave wall. 

Certainly not a good sound to hear coming from the general region of your hard drive on a nice, sunny day. Especially when you have not taken a backup in over two years…

Now consider your options. You could…

1. Turn off your computer immediately, Google “data recovery”, and call one of the multitude of companies that pops up. They will charge you in the neighborhood of $500 to get your data safely off of your damaged disk.
2. Immediately stop working, grab an external USB drive, and transfer all of your valuable data onto the external drive. Order a new internal hard disk, and keep working, confident in the knowledge that your disk is going to die soon, but you’ve already salvaged the valuable data and a new disk is on the way.
3. Try to get clever. Think to yourself “maybe if I run ‘chkdsk’ it will go through the bad areas of the disk, mark the sectors as unusable, and I can keep using this disk.”

Such was the scene at my desk last Friday. And you have probably already figured out where this is going. Yes, I ran ‘chkdsk’. The first three (of five) checks completed without error, and I thought everything was going to be fine. Then, during the fourth phase, after about an hour of intense clicking and banging noises, a message appeared on the screen, which I can only paraphrase at this point:

Not enough space is available on the disk to fix the bad sectors.

This was a concerning message, given that the disk was much less than half full.  But only a minute or two later my concerns were answered by another rather vague message:

Unspecified error has occurred. Aborting.

An unspecified error during a ‘chkdsk’ run is never a good thing. And so I rebooted, only to discover that my hard disk was no longer recognized by my system as a hard disk. I did what I should have done in the first place — called a couple of the friendly data recovery companies — and after listening to my story and, nicely enough, not laughing at me outright, they told me that my chances of data recovery were near zero percent. You see, when your disk is banging away like that, it’s the sound of the heads hitting the platters. And they’re not supposed to do that. When I ran ‘chkdsk’, I forced the heads to touch every surface of the platters, thereby scratching them into oblivion.

A quick trip to my neighborhood computer store, a new hard drive, and I’m more or less back in business, minus two years worth of documents, most of which I never bothered to back up. A bad way to start the weekend, but I actually can’t think of anything especially valuable or irreplacable that was lost.  This is more of an extreme annoyance.

So please, learn from my mistake, and next time you hear an odd clicking sound, don’t try to outsmart your already-broken hardware. Listen to your computer. It’s sending you a very clear (albiet perhaps Morse-coded) message. And back up your data now and again! I work hard to tell my customers about all of the great reasons they should back up their enterprise data; but like most people, I never think about applying those same behaviors to my personal machines.

As an aside, when I reinstalled Vista on the new drive I accidentally used the 32-bit rather than 64-bit media. After I realized my error I decided to stay on the 32-bit version for now and see how it performs. I was not happy with memory consumption before, and I suspect that the 32-bit version will be a bit leaner. Since I only have 2 GB of RAM in this machine, there is no great reason for me to run 64-bit anyway. I’ll post again in a while about my thoughts on 32-bit vs. 64-bit Vista once I’ve had a chance to work with it a bit more intensely.

Next Thursday, May 8, the New England SQL Server Users Group will have a special meeting, featuring Craig Freedman from the SQL Server development team. Craig is The Man when it comes to query optimizer internals, and wrote an incredibly detailed chapter on the topic for “Inside SQL Server 2005: Query Tuning and Optimization”.

At the meeting next week, Craig will discuss some of what he talked about in the chapter, including the basics of how the query processor works and what iterators are. He’ll cover the various operators you’ll commonly see in query plans, and describe how they actually work internally.

This should be a great meeting, and we expect it to be very well attended. In order to help us figure out food and drink, in addition to securing enough chairs for the meeting room, we need you to RSVP if you’re planning to attend. In order to RSVP, sign up for our mailing list. I will send out an e-mail next Tuesday, and you can RSVP by replying to it. Only attendees who RSVP will be eligible for our prize draw at the end of the night, so make sure to sign up for our list by Monday in order to guarantee that you don’t get left out.

We would like to thank Red Gate Software, who made a very generous donation to the group that allowed us to have this special meeting. Red Gate makes some of my favorite SQL Server tools and provides a huge amount of community support in the SQL Server and .NET space, and we hope that you will give their products a try.

I was just reviewing my calendar for the next several weeks and noticed that the Toronto SQLTeach conference is now only a few weeks away.  This conference includes quite a few interesting SQL Server-related sessions, on topics ranging from best practices, to performance, to some of the new SQL Server 2008 features. I fully expect this to be a great show.

I am doing two breakout sessions during the main conference:

• “SQL Server 2005: Authorization, Privilege, and Access Control”.  In this talk I cover SQL Server 2005’s enhancements around granting permissions via stored modules (i.e., stored procedures, views, functions)
• “Designing Highly Concurrent Database Applications”.  In this talk I get into the business requirements behind supporting concurrent processes, and the areas where SQL Server (and every other database product) falls short. I then go on to show how to solve the problems in the database programmatically.

I am also doing a full-day post-conference session on SQLCLR programming. This will be the first time that I will be presenting all of my SQLCLR material in a single day; should be fun. I will take attendees from the basics all the way through some advanced applications and techniques, so if you’re interested in becoming a SQLCLR expert I highly recommend attending.

The conference starts in just three weeks, but it is not too late to register.

If you’ve read many of my blog posts, you know that I consider lack of procedure cache control to be a major SQL Server pain point. Badly written apps that use non-parameterized ad hoc queries can quickly flood SQL Server’s memory pools and bring the server to its knees.

SQL Server 2005 brought some relief in the form of the Forced Parameterization database option, and SP2 took things one step further with better throttling of the cache… but it’s still not enough. We want a knob!

The bad news: We’re not getting quite the knob I was hoping for.

The good news: SQL Server 2008 will include an sp_configure option called ”optimize for ad hoc workloads“.  This option will cause the procedure cache to only cache the parameterized stubs for ad hoc queries, rather than the full query with parameters.  This means that applications passing a large number of non-parameterized batches should see much lower procedure cache memory utilization and, therefore, better overall throughput.  I’m really looking forward to seeing this in action; this feature should be added with the next pre-release drop.

Remember, there is simply no substitute for properly designing your application’s data access layer, but hopefully this will help for those applications that simply can’t be changed…

How creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [<my last name> @ pythian.com]. Make sure to include an explanation of why it works, as well as your mailing address. The best two solutions/explanations win a free copy of Expert SQL Server 2005 Development, a wonderful feeling of accomplishment, plus eternal fame and glory when I reveal your solutions here on the blog.

Run the following T-SQL to create two tables in TempDB:

USE TempDB
GO

CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
GO

INSERT b1
SELECT LEFT(AddressLine1, 5) AS blat1
FROM AdventureWorks.Person.Address

INSERT b2
SELECT AddressLine1 AS blat2
FROM AdventureWorks.Person.Address
GO

Now consider the following query:

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

This query takes around three minutes to run on my notebook, and does over 1.8 million logical reads. Can you figure out a way to re-write it so that it performs better? No modification of the base tables or addition of any other objects is allowed (sorry, no indexed views!) — the challenge is to tune this by doing nothing more than re-writing the query.

Good luck! I’ll leave the contest open for submissions until May 1.

SQL Server has a variety of features that let developers and DBAs take advantage of minimally logged inserts and leverage the Bulk Copy API. This is an extremely important area to understand when doing large data loads or moving data between tables, databases, or servers, but I have found that many DBAs don’t understand what’s actually happening under the covers, and/or don’t realize that taking full advantage of these interfaces requires playing by a couple of special rules.

In short:

1. There is no such thing as a “non-logged” transaction in SQL Server (a very common myth)
2. Minimal logging means that only extent allocations will be logged, rather than the actual data copied to the destination. This is a huge plus when moving enough data to fill many new pages… but again, it’s not a lack of logging, just less logging
3. In order to take advantage of minimal logging (as of SQL Server 2005), the bulk copy operation must use a table lock, and the table must have either no rows or no indexes

For more detail on all of this, please see the following two recent posts from the SQL Server Storage Engine blog:

• What are the Bulk Import Optimizations?
• Bulk Import Optimizations (Minimal Logging)

And all of that is great, but it’s not what prompted me to post today.

Background: SQL Server has long had a feature called SELECT INTO that lets you do a minimally logged insert via a SELECT, but it requires that the target table does not exist (it will be created by the SELECT). This feature is a bit limiting; you can’t target a specific filegroup, you need to use somewhat awkward CASTs or CONVERTs to make sure the target columns have the right scale/precision (if you’re working with variable-length types), and you can’t pre-create indexes or constraints.

After spending a long time on a project for a client that does a huge amount of data movement using SELECT INTO, I posted a Connect item asking for an enhancement where a minimally-logged insert would be available from a SELECT doing an INSERT into an existing table. And I waited. And I waited some more. And then one day the item was updated with a comment: “This is targetted to be available in SQL Server 2008.”

I wasn’t sure exactly what this meant until the last few days, when Sunil Agarwal posted a series of three posts (one, two, three) describing the enhancements to minimal logging in SQL Server 2008 — including the ability to do a minimally logged insert via a SELECT, to an existing table!

This is a fantastic enhancement, and one that I am certain some of my customers will be able to make great use of. I have been kind of lukewarm on many of the SQL Server 2008 enhancements, but this one goes immediately into my top 5 and is one of those things I will bring out when certain customers ask me whether it’s worth their time to upgrade. Cheers to the Storage Engine team for making this happen!