SQL Server Haunt 2017
Halloween is a great time of year. It is unfortunate that it is just one day of the year. That said, I do like to think of the month of October as Halloween Month. I have several posts over the years that geek out over the cross-over between Halloween and SQL Server.
The undead of Halloween are now upon us. Among the hordes of data zombies roaming the streets in lab-coats and fishnet stockings, few of us are still scrambling to remove the hexes we have looming over our data.
As chance would have it, these hexidecimals, err hexes, have a more profound effect on us than we first thought. Many may have yet to even recognize the impact of the hexes placed along with the monsters that now lurk in the data after having been summoned via those hexes.
DB and Fun Related
Seeing as I am a really big fan of this holiday I have a few Halloween posts over the years. If you are interested in the previous Halloween posts, here is a list of a few of them:
That list is my Halloween treat this year. Now for a bit of a trick with a very strong warning. Because of this warning, I am not posting any code showing how to perform the trick.
The contents of this post are for the intent of HUMOR!
I can hear you clamoring from half a world away right now – “There is nothing called ‘Freddy Kreuger’ in SQL Server. What in the world are you talking about?”
You would be very accurate in your exclamation there. But this is not necessarily a strict exercise in feature names within SQL Server. I want you to think a little further outside the norms for a while.
Do you currently or have you ever needed to shred XML? XML shredding via TSQL can be a monstrously bloody killer to your database performance. As it turns out, Mr. Kreuger was also a monstrously bloody shredder.
Yet another beast that is not truly in SQL Server, or is it? A not so new but new feature in SQL Server is called JSON. This feature does actually perform better than XML in some regards. That said, we do have a very common problem between the two of these features – blobs.
If you are not familiar with what that means -here you go. A blob is an overly large item being stored in the database. If you wish, you could correlate that to the other well known Halloween beast – “The Blob”.
Over time, this blob acts like sludge and just slows down your database queries. In addition, like the creature, the blob in your database tends to continue to grow in size and is seemingly never able to be put in check.
Then again, when I run into the situation as described recently, in this post, I may get a bit of a startle and get just a wee bit concerned.
Overall though, I am rarely startled or frightened by any skeletons in the database. These are really just the supporting structures of a nice secure database and are called “schemas”. See, not really all that frightening here if we think about it just a bit.
This next one however, might be a little harder and should be nearly enough to cause some heart pain.
Surely there are no wizarding type of people in the database, right? Warlocks? I know for absolute certainty that there is no such feature or anything remotely close to a warlock, witch or wizard within the database. That is unless my database is about mystical creatures and people.
Alas, I urge you again to expand the box of perception a little bit and become just a tiny bit imaginative. This one, truth be told, does require a fair amount of explanation and imagination though.
The problem comes in part from some magical data issues that can occur due to this particular feature. In addition, this also comes from the wonderful grammatical errors from various blog posts and forums out there mis-spelling “which” as “witch”. Since “manwich” is really close to “man-witch”, I am calling it a warlock.
Now, since I am calling it a warlock, that leads us to the next strong hint about the feature. “Lock” in this case is the key. Now which magical, imaginative feature might there be that is related to “lock”? That would be the “nolock” directive and all of the data quality issues that it presents. Here is a really really good recap (by Aaron Bertrand) on this feature along with reference to it being “magic” – at this site.
This is probably the easiest of the day by far. For all the data loving geeks out there, SQL Server has this pirate flavored way for you to get your drool on. This feature is called “R”. Yup – just like what a pirate says matey.
R is a tool to be used by data scientists or data geeks in general to try and throw together many different flavors of statistical analysis about your data.
This condition is something rare but it is very real. The split brain syndrome is pretty much a multiple identity personality disorder in your database. If you have multiple nodes in a cluster, mirror or availability group, it is possible for more than one of those nodes to believe it is the master node and then for different transactions to become hardened in each of those nodes.
When this happens, you will not be able to use bleach to clean up the mess. Instead, you will be required to spend a grundle of time with your database cuddling it and nursing it back to data consistency and good mental health.
Last but not least, HAPPY HALLOWEEN!