I'll start off with Ola Hallengren's scripts. I'm not a fan of reinventing the wheel. If someone makes gold, I tend to want to use it. Ola's scripts are fantastic. They're even already updated for 2014! You do have to contact them for the CTP version of the scripts.
These scripts cover your backups, index maintenance, verifies the integrity of your databases and logs the results of all of this for you. You can set up one part or all. This covers all of your 101 DBA requirements to keep your servers running in a safe fashion. They even give examples of what you might want to throw into the script! It doesn't get much easier than this.
Adam Machanic wrote a great script called "Who is Active". He even has a 30 part blog series on the ins and outs of this procedure found here. Just about anything you want to know can be found there. It's a fantastic script. I would familiarize yourself with it prior to relying on it for day to day operations.
Kendra Little has a great video and some sample scripts to run to view what indexes your server is wanting. As she will stress, do not just put them all into your database. Too much of a good thing can be really really bad. This is where we look for all that beautiful low hanging fruit.
On the same site as the Missing Indexes video by Kendra Little, there is a great script called SP_BlitzIndex. This was written by the same group. It's the step beyond just looking for missing indexes. Take the time to look through this when you have some time. This is not a place I'd look with a fire to put out... At first anyways. Learn about it in detail before assuming too much and jumping into it.
SP_Blitz was written by the Brent Ozar group and it helps you identify many pain points really quickly and even includes a link to what those mean and suggestions on how to handle it. Honestly, how nice is it these exist for us?!
What Can We Do?
So here we have 5 fantastic scripts. What will these accomplish?
1) We start off with Ola's script and get our backups and index fragmentation under control.
2) We find out from our users if anything in particular is slow or below SLA requirements.
3) We run the SP_Blitz and see what shows up as a major issue.
4) We get a quick break down of what the Missing Indexes are suggesting.
5) We put all this together in a solid actionable list.
6) We present our findings and come up with a solution to work on.
Yes I use the word "We" a lot. I do this on purpose. Correcting this many problems isn't a one person operation. You want the other product heads involved. You need to know what this could break or even if there's a problem you're currently trying to fix. If they need a report to respond in under a minute, spending a week trying to get it from 45 seconds to 5 seconds isn't where the focus should be unless everything else is working perfectly.
There Is More Out There!
These are just 5 of the many many scripts out there for free that have been provided just to make your job easier. I've said it before, I'll say it again... MS SQL Server has a fantastic community around it. Few come even close. These 5 scripts alone can help a DBA sustain a workshop with minimal effort. Use this not as a crutch, but as a starting point to make everything even better.
If you all know any other great scripts that can be added to this collection, I would love to hear it!
