Today I’m going to move on to part 3 (of 4) of explaining the Maintenance Plan Wizard. Here’s a picture of the layout again, just in case you forgot!
So today we’ll start with:
Update Statistics
So first, what are statistics. Statistics are SQL Servers internal information which it uses to make sure that your Query Plans are as optimal as possible. It does by tracking how well your indexes are performing, and are the cached query plans still the fastest way to get you your data.
The Update Statistics job kicks off a manual check of your statistics. By default the statistics will update themselves when a certain amount of data chances in your table/index, but if you want to be 100% sure everything is up to date, you can set this task. If you want to have a read about the Update Statistics job a bit more in depth, have a look at this Technet article
It is non intrusive to run statistics, as opposed to index reorganize/rebuilds, however it doesn’t perform any maintenance on these objects, it just checks if they are still performing well and adjusts accordingly.
You can run this on a nightly basis just to make sure everything is ticking over if you want. There is no need to run this if you do a full index rebuild as they will update the statistics themselves and this job will only have you re-treading your tracks.
Clean Up History
This job cleans up your job history in the MSDB, and your SQL server logs from whatever point you set, backwards (default is 4 weeks). It is important to run this as when your job history starts to grow in the MSDB database, operations on these tables slow down, which in turn slows down your SQL Agent jobs. You can run it whenever you like, and as often as you like.
Execute SQL Server Agent Job
This one is fairly self explanatory! Executes an existing SQL Agent Job.
So that’s it for today, 4th and final part tomorrow covering different backup types, and the Maintenance Cleanup job. As always, any questions don’t hesitate to ask!
Stephen