T-SQL Tuesday #25: My Favorite Superpowers

It's time for T-SQL Tuesday once again.  The topic this time is "What T-SQL tricks do you use today to make your job easier?".  As a superhero, I don't do "tricks", I perform feats using my amazing, incredible, awesome superpowers.  Instead of sharing "tricks" with you, I'll share with you my favorite superpowers and how/why I use them almost daily.

My greatest ability, and the one I rely on most often, is the ability to see into the past.  Quite often, some hapless soul will come running to me in a state of panic.  Usually the complaint is that some overnight process didn't finish in time, and now this person doesn't have some set of meaningless numbers to give to his or her boss.  "Please, REAL SQL Guy, you have to help me!  Why did my process take so long last night?"  Drawing strength from the radiation leaking from my computer screen, I'll summon up the spirits of the past, looking at last night's activity, comparing that to the activity from the previous few nights.  Rarely does this fail to reveal the problem, some competing process that was slamming the processors on the server or generating millions of I/O reads.

Next on the list of favorite powers is my X-ray vision.  Nearly every day, while perched above the cubicle farm scanning for trouble, I'll see someone looking around nervously, as if they're trying to "act casual".  Swooping in for a closer look, I'll approach the person, greeting them with a friendly "Wassup?".  The story is always the same - "I was writing a query, and it turned into a monster, and now it's loose in the production database.  Can you help?"  Focusing my X-ray vision on the server, I respond with "Stand back, Citizen", and begin scanning for this supposed "monster" that is terrorizing the production database server.  Yep, there it is.

Once the monster query has been identified, I'll use my Power of Persuasion to try to coerce the monster into behaving properly.  These monsters are all alike, they all want something that they can't find, and that makes them cranky, leading to their poor behavior.  Usually it's an index that they want, and it's up to me to figure out which one.  By temporarily using the query hints LOOP JOIN and MAXDOP 1 to force a single-threaded non-hashed query plan out of the monster, I can see exactly what index (if there is one) the monster is looking for.  A quick flex of my DBA muscle and we have our index, and the monster is now playing well with others.

Sadly, these stories don't all have a happy ending.  Sometimes the monster simply refuses to behave, and for the safety of the entire community, must be dispatched.  I don't like to use this power more than necessary, but sometimes the only option is to KILL the offender.

So, there you have it, my bag of tricks, errr, powers.  Every DBA out there has these same powers, although some haven't learned how to use them yet.  I hope this post changes that, even for one reader...