by Denise Crabtree | Senior DBA at Taos
I admit it: I am a copycat. I am not willing to waste my time re-designing wheels or solving the already solved. While I love writing a good script to aid my work, finding useful, effective, kicking SQL tools is a better plan. I have found some SQL Server giants and I regularly stand on their shoulders.
There are some brilliant and generous people in the SQL Server world. They share their knowledge and experience. I have used scripts, blogs, videos and white papers to increase my knowledge and help me do my job. Using tools from good sources, gives me time to dig in quicker and be more effective.
Once, I got “the call” – poor performance in a daily processes was causing a data jam in their system. I could see lots of processes with the sp_whoisactive – it did look like there were major problems. I used sp_BlitzCache from the “First Responder Kit” to get the performance plan. SentryOne’s free plan evaluation tool showed me just where there were a large number of reads. The default SQL plan only shows detail if you “hover” this one shows detail and color codes problem spots in the detail of the results set. So easy to find the real problem. Nine million reads to return ten thousand rows is a bad ratio. It was using an index scan to return one row at a time. That is where I used the sp_BlitzIndex to create a covering index. The recommended index made all the difference. The turnaround time from the report of the problem to the solution was greatly compressed because I had some good tools on-hand.
Some years ago, I wrote an index maintenance tool. I had to keep tweaking it and changing as any automation effort. Quickly, and so, quite quickly, it was different across the servers I managed. My automation had done the xkcd thing. (https://xkcd.com/1319/ ) This is a situation where the Ola Hallengren tools shine. Database Maintenance is a big part of keeping the engine running smoothly. I can consistently apply the tools across servers and the jobs look the same. And, they work dependably. I started using his tools instead of mine and my consistency improved dramatically. Even with new releases, applying the new set of tools does not break existing jobs. Using this tool saved time from my automation effort because the problem is solved.
The caveats. Learn from the scripts. Make sure you are installing something that you understand.
Here are my top four for daily use:
- First Responder Kit from Brent Ozar: http://www.brentozar.com/
- Database maintenance tool set from Ola Hallengren: https://ola.hallengren.com/
- “sp_whoisactive” plus more from Adam Machanic: http://sqlblog.com/blogs/adam_machanic/default.aspx
- ShowPlan tools from SentryOne: https://www.sentryone.com/
There are many forums, bloggers, specialist, and aggregators that contribute to the SQL knowledge base. I hope your find some favorites of your own; you are not alone.