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:
- Ola Hallengren's maintenance solution. Oh my god you guys, if you need a robust backup and maintenance tool it's well worth your time to consider Ola's. I've used Ola's solution with great success in both small and large environments.
- Glenn Berry's diagnostic information queries. These are priceless. They cover almost anything you need to gain insight into your server's health and status.
- Adam Machanic's sp_whoisactive. What's going on, in detail, RIGHT NOW!
- sp_who2 filtered, from SQL Matters. Simply putting sp_who2's output into a table is brilliant, and one of those things that I wished I'd thought of myself when I came across this.
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 quick script to get publication information.
- This returns publishing database, publication name, the source article name, subscriber instance, and the immediate sync setting.
- A trio of scripts I wrote to get replication agent history - distribution, snapshot, and log reader.
- These scripts return agent names, agent IDs, status info, and the most recent 100 history records. Adjust the value as necessary.
- A script to identify the jobs associated with various agents.
- 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.