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!
This one to search within job steps often comes in handy since that's an association that doesn't get tracked in SQL. It would be great if RedGate would add this to their already nifty search tool too.
ReplyDeleteSELECT DISTINCT
sj.name JobName,
st.step_name StepName
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobsteps st ON sj.job_id = st.job_id
WHERE st.command LIKE '%StoredProcNameHere%'
OR st.command LIKE '%AnotherStoredProcNameHere%';
I'm not familiar with this script. I'll have to test it out. Thanks for the addition!
DeleteHi Marbry,
DeleteI'm the Project Manager for Red Gate's SQL Search. I'm pleased to say we currently have a beta available for the next version, which does include searching jobs and job steps. You can get the beta from http://documentation.red-gate.com/display/SS1/SQL+Search+beta+program
Please send us your feedback on it.
Dom.
Works a charm, seems very snappy. I noticed that you also enabled copying text from the preview window. This was a minor annoyance, but definitely comes in handy. Thanks!
DeleteI'm looking for a little SP I once saw that accepted a column name and returned a list of every SP, trigger, view, etc. that utilized it.
ReplyDeleteThat could be difficult since different tables can have the same column name. I would be interested to see how someone would build that one.
DeleteYou reminded me of the second input. You fed the SP the table and column name, it returned everything that referenced that table+column. Thank you for spurring my memory. As I recall you could launch it and go have lunch before it was done, but was very useful. It saved the company I was working for some serious heartburn when my pointy haired boss directed me to remove a column from a table because she didn't need it. The column in question controlled posting to the GL of all processes in an ERP system.
DeleteI would add Glenn Berry's scripts (http://sqlserverperformance.wordpress.com/) and its HTML output version by Nirav Jashi (http://www.sqlservercentral.com/scripts/baselines/96797/)
ReplyDeleteI mention his scripts enough you'd think I wouldn't have forgotten. It is a fantastic set of scripts.
Delete