Tuesday, July 10, 2018

The code I can't live without

Today's T-SQL Tuesday topic is thought-provoking: What code couldn't you live without? (OK, OK, technically it's just titled 'Code You Would Hate to Live Without', but work with me here.)

One of the hallmarks of the dedicated DBA is that we write our own tools. I've encouraged juniors to write their own code, even if they can find things online that solve their problems (inexperienced folks blindly using code from online is a whole can of worms that I may open later). We gain so much by doing the work to learn about the systems we're interacting with, and the harder work of figuring out how to write the code, be it T-SQL, Powershell, or what have you, to really dig in there.

As a brief aside, in my experience if you don't have the curiosity and drive to begin writing your own tools, you're not going to get very far as a DBA. I'm not sure I'd WANT you to be in charge of my servers. Now, not for a second would I expect highly-polished code from juniors right out of the gate. What matters more is evidence of that curiosity and drive. What matters is that we keep learning, keep practicing, and keep improving.

On the flip side, there are times we find something out there that Just Works. Part of the wisdom we develop as we progress is knowing when to press on with writing our own, and when someone else's code does the job perfectly.

I'm finished digressing - on to the code I'd hate to live without!

Under the 'code from others' umbrella, code I'd hate to live without includes:

Of the things I've written myself, I would hate to live without these things (please note, these are all hosted in Dropbox; if you want copies and can't get to Dropbox please let me know!):
  • Replication scripts!
  • A script to get stored procedure execution information, and the query plan if you want.
    • This query gives a lot of info - when the plan was cached, how many times the plan's been looked up, how many things reference the plan, when the proc was last executed, how many times it's been executed, all sorts of worker, reads, and writes info including averages.
    • The usual caveats on the cache info apply - restarts, cache flushes, index rebuilds, plan recreations, etc. are all things to keep in mind
  • Some templates for file manipulation
I've got a lot more, but these are the most important to me.


Nate said...

Yes, a fellow replication user! :D I enjoyed your post, nicely done, welcome to the #SQLFamily. I look forward to reading more!

Tim said...

Thanks Nate! I'm glad you enjoyed it! I lived in a complex replication topology for years, and I'm glad to share some of my tools.