Come, my fine friends, and gather around! I shall impart to you a tale from not so long ago of an epic victory forged in the heat of battle with a nameless SQL beast!
One fine morning our fearless database leader approached me with an issue. “SQL Princess, we are in need of your assistance with an issue of grave importance to our kingdom. Some time ago, our keeper of the royal documents approached us with a request to fix the royal document system. A wizard from long ago set up this system and in it placed a strange curse. The enchantment that had been put into place to fix the mess caused by this curse inadvertently created a vicious SQL beast! When the enchantment is invoked, the foul beast is unleashed wreaking havoc for hours on end bringing the server to its knees placing it into a deep slumber from which it does not respond. I need you to work with the keeper to slay this SQL beast once and for all in addition to fixing the havoc wreaked by this curse.”
I nervously replied, “Oh, my dear Lady! I am but a mere princess! How can I possibly be of help? I’ve been in your court for barely one year!”
She sagely countered, “I have the utmost confidence in your ability to help our keeper in his time of need. Look upon the original enchantment in the test arena and perhaps there lies the answer to slaying this SQL beast.”
“I will not let you down!” I exclaimed. Resolute in my mission, I contacted the keeper. “What can you tell me about this curse?” I inquired.
He explained, “We have four main databases that appear to have been cursed. In each database, there reside two tables – Cloak and Dagger. Cloak should contain the document’s location (i.e. the path). Instead, it contains only a part of the path. Dagger should contain the document file name. Instead, it has the missing portion of the path attached to the file name. Even though Cloak contains 95 other fields, the only one that is of concern right now is the path. These two tables are tied together by a document number. What is strange is that the system still appears to work fine for the most part. We only noticed the problem when we went to remove a document from the system. Since the path isn’t complete, records end up being orphaned during the removal process. We need you to combine together the full path in Cloak and leave just the file name in Dagger. This system stores our kingdom’s precious documents. Please fix this! ”
That doesn’t sound so bad, I mused. I promptly sat down at my desk and gazed into my mystical SQL portal intent on finding a solution to this dilemma. Once I located the system, I was a bit taken aback to discover it was SQL circa 2000, the year of the slammer worm! Hmm. With 2 out of 3 Gigabytes of memory dedicated to SQL, this could indicate prime breeding ground for SQL beasts, I reflected.
Continuing on, I cast some “counts” to determine how many documents were cursed. “Holy SQL Logs!” I cried. “The curse has affected over 12 million out of 20 some million records in one database alone!” Now curious about the enchantment that had been set to fix this, I set my portal to gaze upon it.
I soon discovered the enchantment was comprised of just one procedure. The procedure creates a temporary table consisting of three fields – a document number, a path, and a file name. Records are inserted into it from a joining of the Cloak and Dagger tables based upon a starting and ending document number set by the caster of the enchantment. “Wait a minute,” I mused. “How does the caster know which document numbers to use considering there are millions of records that were not affected by this curse? Not only that, but criteria was not set for the insertion query. So it’s inserting all records regardless if they’ve been affected by the curse or not!” Scratching my head, I trudged on. Next was a selection of all the records from the temporary table. No insert or update – just a selection. “That may not be a good sign,” I reflected, “considering the vast number of records in the system.” Could this be the start of what unleashes the SQL beast? Even more curious, I continued.
Following the selection query was an update to the temporary table in its entirety (i.e. no where clause). The path and file name fields are then set to new values using a custom merge function and a custom parse function. Following the update are two more updates – one for the Cloak table and one for the Dagger table – using the updated temporary table. It seems simple enough. Wondering about the two functions, I looked them up. They appear to be fairly simple – nothing to worry about just yet. I peered at the Cloak and Dagger tables– no indexes were to be seen! Not even a primary key! “Well, that explains quite a bit”, I muttered to myself.
Ensuring my portal was gazing upon the test arena where a fresh copy of the databases resided, I readied myself to invoke the enchantment and to gaze upon the SQL beast with my own eyes. Wiping my sweaty unsteady hands, I calmly called forth the execute command invoking the enchantment.
At first, it was eerily quiet. I heard not a peep from the test arena. I kept an eye on the server vitals whilst the enchantment worked its magic. So far so good. After a very boring hour, I began to wonder. Where is this so-called beast? Perhaps it dares not show its ugly head whilst I’m around. So I turn my attention away to other matters hoping to lure it out. Many goblets of coffee later towards the end of the day, out of the corner of my eye I spied something dark slithering across the test arena. Could this be it? Could the beast finally be coming around?
And there I saw it! The ugly, vicious, havoc-wreaking SQL beast had finally reared its ugly head! Gasp! The arena suddenly went still. I quickly cast sp_who2 to look for blocking. It wasn’t responding! What the hell? The server housing the test arena was dying! The beast was slowly choking it to death! I had to do something and quickly! “Kill! Kill! Kill!” I commanded through my mystic portal! The beast roared in agony as it rolled back! I waited in breathless anticipation hoping the beast would disappear. Soon it was quiet, the server responsive, and the beast was nowhere to be seen. “Whew! That was close”, I breathed. I trudged on to modify the enchantment hoping to fix the mess caused by the curse without unleashing the beast once again.
After consulting the execution plan, I added a couple of indexes and then replaced the temporary table with a view adding criteria to limit the records and removed all references to the temporary table. Unfortunately, I wasn’t able to use an indexed view due to limitations. Next, I tackled revising the update statements to join with the new view. I continued weaving my new enchantment which was soon complete!
Nervous about the beast reappearing, I found a small range of document numbers to test with. Much to my astonishment, it successfully updated 125,000 records in less than 2 minutes! 1 million records? No problem! It ran in 8 minutes. So by my calculations, 12 million records should be updated in 3 hours which, at the time, seemed much better than bringing the server to its knees after over 8 hours!
At the last minute, I performed some calculations to determine how to split the documents into three fairly equal batches. I then invoked the new enchantment for each batch. After the second batch ran successfully without the beast appearing, I bellowed “Beware, thou foul beast! Your reign of terror has come to an end!” I quickly ran the last batch of documents hoping I had finally repaired the mess the curse had caused and had destroyed the SQL beast once and for all!
After the hour was complete and the disaster caused by the curse successfully fixed, I jumped out of my seat and cried, “I Vanquished the SQL Beast!” The royal court blissfully rejoiced and celebrated for days on end! Okay, it was more like minutes than “days” but close enough.
Epilogue: Afterwards, the keeper of the royal documents verified the results were indeed correct. Unfortunately, the new enchantment was not set to run in the “live” arena because an upgrade was being planned to eradicate the curse in the system that had caused this ruckus in the first place. Roughly one year later, the upgrade to SQL circa 2008 R2 for the system began. Once the new system is up and running, the enchantment will only be needed once for the initial cleanup prior to migrating to the new server. The whole kingdom continues to rejoice confident the royal court will happily slay any SQL beast that dares to rear its ugly head!