This month's T-SQL Tuesday is hosted by Jens Vestergaard (b|t) and he wants to know what the essential SQL Server tools are in my stack.

Aside from being nosy, this is a great topic to get a poll of some obvious, and not so obvious, productivity tools used by much smarter people than myself.

Hopefully someone learns of a new tool here and I'm sure I will learn a whole lot!

Now,  onto pancakes!

When I think of stacks I think of sweet delicious stacks of pancakes. I can't help it. Stacks of anything else are inferior. I am also hungry.

I have broken out my go-to tools into a few different stacks.

• Administrative/Maintenance
• Automation
• Performance/Troubleshooting
• Benchmark/Testing
• Productivity

The Admin stack is probably the most important stack here. You still using maintenance tasks via SSMS? stop doing that. Rebuilding indexes every night? Maybe rethink that.

How you keep track of, monitor and do basics DBA tasks? 

CMS server
Ok so this can involve SSMS, but a feature not a lot of people may not use. We use it to keep track of all of our instances and push things..oh baby baaaby!  It also allows me to combine PoSh to do work against instances, gather data (historical, dmv...etc) and do a boat load of admin stuff without pointing and clicking. Heck I don't even have to open SSMS to use my CMS server at all.

SentryOne SQLSentry 
SQLSentry can automatically defrag indexes for you and update stats. You could use this instead of the below choices for this aspect if desired. Although not free, it's an option we have in our environment and I love me some options.

Ola hallengren/Minionware
Both amazing options for backup, reindexing and checkdb. Although most places i've worked use Ola's scripts by default. HOWEVER.... Minion has some pretty nice options that are FAR more configurable than Ola's. We have mitigated some large DB issues by rolling our own code on top of Ola's scripts. We could avoid this by simply using Minionware!

The automation stack is arguably the most delicious of the stacks. I mean, c'mon look at it....Loooook at iiiit (shaking fist)

Nothing is more satisfying than automating a task.

Taking something, that normally would consume hours, and transforming it into an afterthought, which are some of my favorite kind of thoughts.

PowerShell 
The peanut butter to my jelly. The syrup to my pancakes! I don't always automate things, but when I do it's with PoSh. Ok that's a lie I do always automate things. Do I want to manually do something or do I want to automate it and drink a beer while it happens....automatically.

dbatools  
Ah it's like a warm fuzzy blanket. I can always trust dbatools to provide me warmth and comfort when I want do almost anything DBA related at scale. Need to migrate instances? No problem! Move logins, with passwords and realign SIDS (sql logins only of course) no problemo! There aren't enough words to express how much I love and appreciate all the hard work that has gone into this module. It really helped opened my eyes to what can be done with powershell + SQL Server. If you haven't played with it yet, stop reading and do it right MEOW!

dbachecks  
The new kid on the block and latest edition to the dbatools family. It's a completely separate module focusing on checking yourself before you wreck yourself. I have just started to play with this and it is amazing. We have separate CMS jobs that we push to check for a lot of these issues:
- Backups have been run
- Orphaned users
- VLF counts
- Failed Jobs
- Cluster Health
- AG Health ....and many more

Want to sleep better at night? Start using this TODAY!

Dynamic Restore Script  
This is a custom script I wrote for our specific environment. However, it can be used by anyone. It allows us to grab the last full,diff and logs from MSDB metdata tables backupset and backupmediafamily. You can use variables to limit to only full, diff or all backups and set to norecovery for log ship re-seeding. It can also alter the UNC location for full/diff as well as capture only a single DB output. Having  Full/Diff and logs in different locations is a pain and this helped mitigate that pain for team members that weren't comfortable with PoSh. I am currently using this as a SP to refresh a customer environment by taking down application services(with PoSh), creating each DB restore script, capturing tier specific data, restoring DBs, restoring tier data, then starting services again on all application servers. boom goes the dynamite

The performance/Troubleshooting stack is probably my favorite. Maybe not as delicious as automation, but definitely more satisfying. 

Taking a query that runs for over 15 minutes and optimizing it to run in under 15 seconds is my kind of satisfying. 

I not going to mention Extended Events here since they're built into SSMS and pretty much standard. Although I just did.

SentryOne - SQLSentry 
Even though monitoring tools seem obvious, some people fail to realize the time and effort they save. One of my last positions didn't have a monitoring tool and I wasted a lot of time and effort chasing baselines, combing through weekly profile numbers and writing lots of DMV queries to look for potential issues. I am a much happier person now. SQL sentry is a game changer for me. I am able to look at the high level dashboard, configure alerts, look at deadlock graphs, most expensive queries and jump straight into SentryOne's Plan Explorer to resolve issues. 

SentryOne - Plan Explorer 
It's freeeee! I love free. Sometimes I go to Costco on the weekend for the free samples. And by sometimes I mean always. Move over kid I'm trying to score my second helping of that mini dumpling in a paper cup. If you're not using this free tool, you need to. I can jump between estimated and actual costs visually, look at the histogram and data distribution without switching to SSMS for dbcc show_statistics and look at large plans much easier.  This is an actual plan I had to fix. See if you can spot the issue.  Bottom line is that this really shouldn't be free, but don't tell that to SentryOne.

sp_whoisactive 
This is my crutch. The wind beneath my wings written by Adam Mechanic (b|t). It smashes a lot of my separate DMV queries and steps into one awesome swiss army knife. I use this while an issue is happening to look at SPID waits, blocks, reads, writes, and getting plans, all while writing to a table for later review and analysis. This is invaluable when parachuting into an onsite customer emergency or troubleshooting something during off hours. I don't have to email a customer 37 times to run different queries, especially if they don't have any monitoring. boo.

dbo.check_blocking 
A great little SP written by Andy Mallon (b|t) to get way more information on complicated blocking scenarios. I've only had to use this once, but it was extremely helpful in helping me diagnose the root cause.

Not the most exciting stack to deal with but a necessity nonetheless.

I want to know numbers! Show me how a new environment will perform based on an old, or current, benchmark.

Query Stress
Another Adam Machanic(b|t) contribution. Seriously I need to buy this guy a beer. This is now maintained by Erik Ejlskov Jensen (b|t) I Use this to really stress out my instances, like really stress out. Think night before a big presentation and you haven't finished your PPT stressed out! Want to demo latch contention? Query Stress!

HammerDB
Transactional benchmark testing. Although it doesn't have the latest TCP tests, it's still a decent option. Just don't focus on the actual numeric value, simply compare results. I used this during a private preview of Azure managed instance with excellent results!

DiskSPD
Amazingly easy I/O benchmarking utility. You can use this to show a storage admin where the real bottleneck is ammiright?!

The last stack is a misc productivity stack. I use the following apps to make my daily life easier. I also ran out of pancakes. sorry.

Ultimately whatever tool you use, make sure you use it in the right situation.

I am not going to use PoSh for anything set-based and I won't monitoring with sp_whoisactive and dbachecks. 

Lightshot
when reading things I like to look at pictures. This also makes documents easier to follow for some poor soul at 3am when things break. lightshot saves me time by snipping screen areas and allowing me to annotate, and highlight specific aspects.

Dashlane - 
How many passwords do you have? 4? 7? I have a bazillion and having them all 12345 just wasn't secure anymore. Now they all are totally randomly generated and stored by dashlane for me. It automatically signs me into important websites like reddit, so I can look at cat pictures, and allows me to save custom secure notes. I love the 2 factor authentication, actually I could be 3 if you add a pin to your phone and not have the auth on your laptop.

All of the tools together are greater than their sum and I'm excited to see what I am missing!