Change My Mind… T-SQL Tuesday #146

Andy Yun (t | b) is this month’s host. Our challenge this month is:

 …to think about something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something.

I’m going to give a twofer for this one, since he’s also allowing non-technical answers.

8K Blocks

“SQL Server writes everything in 8K blocks.” I recall learning this probably close to 2 decades ago. And, it makes sense, at a lot of levels. And it was “confirmed” when we reformatted the disks on one of our production servers into 64K blocks so SQL Server could read/write 8 blocks at a time. Performance definitely improved. But, then I learned from Argenis Fernandez that this isn’t necessarily true. SQL Server will write what it wants to write. And if you think about it, that makes sense. If you update one record and it’s a single value you’re updating, SQL Server isn’t going to simply sit there and not write your 1 byte change to disk. And it’s not going to make up random 8191 bytes just so it can satisfy this rule of 8K. Yes, SQL Server will try to consolidate disk I/O and be efficient about it, but even then, it may not matter. Gone are the days where we’re writing data to bare metal (some of us are old enough to recall an option in early versions of SQL Server where one could create a database on a “raw” partition to get a performance boost). No, today we’re probably writing through multiple layers, more than we realize. For one client for example, a disk write from SQL Server will pass through an encryption layer, then to the OS, which will pass it through a disk driver layer that will then pass it on to the VM which will have its own layers. At that point, even if SQL Server were trying to only write 8K blocks, it’s quite possible every other layer has its own rules.

Yes, changing our disk formatting from 8K blocks to 64K blocks helped. It helped us. But, your requirements and situation may be different and ultimately you may end up writing more or less than 8K blocks all the time. (and I hope I summed up Argenis’s position accurately.)

Toss the Rope Down

As many know, I’m a caver. I’ve been caving for decades. Early in my caving career I learned vertical caving and back then we still used what was known as a “3-knot” system or “prussiks”. That hardware has improved and changed. But one habit took longer. It was (and unfortunately still is) common to tie one end of the rope to a tree or other rigging point, and drop the rest down the pit. Sure, you ended up with a pile of rope at the bottom, but no one really cared, as long as you didn’t step on it (which is another myth for another time). This helped guarantee that your rope actually reached the bottom. The only thing that sucks more than rappelling down a pit and reaching the knot at the end of the rope 50′ from the bottom of the pit is rappelling down a pit and realizing 50′ from the bottom of the pit that you forgot to put a knot in your rope.

But somewhere along the line, folks started to realize, “hey, that rope at the bottom of the pit is useless. It’s FAR more useful if we can leave it at the top of the pit.” As the depth of most pits are known, it’s actually not that hard to measure out the rope you think you need (plus a bit extra) and then rig the rope so that you have extra at the top. Why is this important? Some call this “rigging for rescue” (or more accurately, one part of the bigger concept).

Imagine the scenario where you’re ascending the rope and have an equipment failure. You can’t go up and can’t go down. If all the extra rope is below you, it doesn’t do you any good. You can’t do anything with it. But, if that extra 10′ or 20′ (or more) is at the top and you’ve rigged correctly, someone at the top can, without too much effort, safely change the rigging (with you still on the rope) to a 3:1 or if nothing else, a 2:1 haul system. Suddenly that extra rope sitting at the top of the pit becomes useful.

Beginners will still often toss the extra rope to the bottom of the pit, but more experienced cavers will rig it to stay at the top and this may literally save lives.

Conclusion

Stop and think about practices that you do now that you may have learned that could be wrong or no longer applicable. And more importantly, do those bad practices interfere with doing something that’s better or safer? With SQL Server, over the past few decades, a lot has changed and improved, but are you still doing something you were taught 2 decades ago because “well that’s the way things are done.” A lot has changed in 2 decades. Make sure your knowledge is still valid!

1 thought on “Change My Mind… T-SQL Tuesday #146

  1. Pingback: T-SQL Tuesday #146 Round-up! Upending Preconceived Notions | Every Byte Counts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s