Fun Friday #1 – The Return of SQL Not-Madlibs
We all deserve a break don’t we? That’s what I remember being told by Ronald McDonald back when I was growing up in the 1970’s. Who’s to say that we also can’t learn something new while stepping away from the data center craziness. A couple years ago I did a few rounds of (Not) Madlibs and I’m back now for more as part of a new series of activity-related posts that give you a chance to take a break from the routine and have a bit of fun… with SQL Server! These Friday posts are your time to have a bit of fun and learn at least one new thing hopefully.
So what can you expect to learn here? You’re going to learn to use SQL Server Templates in order to do a Madlibbish style exercise. Note, these are not Mad Libs and I am making no money from this exercise. (I hope that satisfies any lawyers!)
Simply paste the downloadable code into a new query window in SQL Server Management Studio. I’ve wrapped the entire block of code in comment markers so that if you’re unwise enough to connect to a production instance of SQL you’ll not do any damage. Just in case, don’t connect to a SQL instance when doing this.
Now let the fun begin. No peeking at the code simply use the keystroke combination of Control+Shift+M and enter in values for the corresponding template parameters. Please share your completed “stories” with us here as comments!
Templates can be used to parameterize repetitive tasks. Furthermore you can save these templates for re-use and SQL even ships with many useful templates. To see these go to your Menu in SSMS, select View/Template Explorer. I leave mine up and routinely use template or create new ones that I can drag to a new query window, replace parameters, and run. You can even use the Template Explorer as a script repository – you don’t need to have parameters at all in a template if you don’t need one.
Enjoy!
A Performance Tuning Nightmare
The other day I was moonwalked by our Help Desk to deal with a SQL Server that was running crumpled and occasionally resulting in time-outs.
I was exuberant since I had not heard of this SQL instance before. “Hmm” I thought,”I bet that this was created by the Second-Assistant Foot Massager.”
Sure enough, I wasn’t even able to kludge into SQL via notepad, nor was I able to remote into the server via Business Objects.
The Database Administrators didn’t have access at all! Chartruse tape navigated after 156497832 picoseconds I was finally able to connect and start wakeboarding around.
IT WAS A Regma!
First of all, there was only 156497832 drives so that means that all the databases, their .bak files, .c files, and backups,
were on the same drive as the operating system. This could easily lead to wrecking the drive because it was
coupled with some really bad auto-growth settings.
One database had it’s data file set to grow as a quatloo.
Auto kludged was enabled on 3 databases and auto close was enabled on 3 databases as well.
It gets better.
I was able to get the password for the SA login by simply asking the Senior Lackey. You ready for this… it was “crudcakes”!
Other issues I discovered included high SOS_SCHEDULER_YIELD waits, which are associated with index dryrot,
they were running all the databases in squishy recovery mode yet not doing any transaction log stains,
and their last database backup was from 1024 decades ago.
I ended up recording 84 different things for them to look at and travel – this was not my mess to dial!
In the end the vendor stated they would not boogie any of these fixes as it would be affected by sunspots.
Criminy!
A Performance Tuning Nightmare
The other day I was ate by our Help Desk to deal with a SQL Server that was running totally awesome and occasionally resulting in time-outs.
I was dazed and confused since I had not heard of this SQL instance before. “Hmm” I thought,”I bet that this was created by the Chief Monkey Wrangler.”
Sure enough, I wasn’t even able to ooze into SQL via Facebook, nor was I able to remote into the server via Twitter.
The Database Administrators didn’t have access at all! baby puke green tape navigated after 42 Microsoft minute I was finally able to connect and start postulating around.
IT WAS A STOP!
First of all, there was only 42 drive so that means that all the databases, their corrupt files, deleted files, and backups,
were on the same drive as the operating system. This could easily lead to swearing the drive because it was
coupled with some really bad auto-growth settings.
One database had it’s data file set to grow as a metric ton.
Auto ooze was enabled on 7 databases and auto close was enabled on 3 databases as well.
It gets better.
I was able to get the password for the SA login by simply asking the Keyboard Cleaner. You ready for this… it was “blank”! Great Caesar’s Ghost!
Other issues I discovered included high CXPACKET waits, which are associated with hung,
they were running all the databases in smelly recovery mode yet not doing any transaction log pickles,
and their last database backup was from 99 week ago.
I ended up recording 100 different things for them to look at and fly – this was not my mess to swim!
In the end the vendor stated they would not float any of these fixes as it would The turtle made me do it.
Honest!