SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Maintenance Plans - Behind the Scenes

By Andy Warren,

Last week I wrote an article that looked at some of what happens behind the scenes with maintenance plans. That article was a follow up to yet another article I wrote discussing maintenance plans in general and why I believe they are a good tool to use. This week I'd like to follow up by profiling some more of the actions generated by various maintenance plan options. The end result - I hope - will be that you're both more comfortable with using a "black box" for routine maintenance AND that you'll be learning to fish - I'm just applying some time and Profiler to see what's going on.

So, preamble done, let's get started. I do my testing on a workstation with no other processes running, saves a lot of effort filtering out all the extra stuff. I set up Profiler using the default filter and data columns. For events, I use the ones in the following image:

Depending on what you're trying to find you may need to make changes, but this is a great place to start. Now with Profiler running, I can set up a new plan. For the first test I want to look at what happens when I check the 'Update the Statistics' box, so I create the plan with that checked, then run the job 'Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan1'.

Now let's see what happened:


SELECT statman([CategoryName],[CategoryID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CategoryName],[CategoryID] FROM [dbo].[Categories] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [CategoryName],[CategoryID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1) 


Well, that's a subset of it anyway. The first two selects are a little cryptic (anyone know what Statman does?), but the meat and potatoes is the last line that runs update statistics. I'd guess that the first two lines are involved in determining if the statistics need to be updated - but it's only a guess. If so, how much time/effort does it save compared with just running sp_updatestats? Unknown! Overall it gets the job done.

Next I changed the job to just remove unused space from the db, using the default options. Here is what we get (after I added 100m to the space allocated to Northwind):

DBCC shrinkdatabase(N'Northwind', 10, TRUNCATEONLY ) 


The DBCC shrinkdatabase does the work, DBCC SQLPerf returns information about how much space is being used (probably used for reporting?). No surprise here at all how the shrinking gets done.

To finish up for this week, let's look at how rebuilding indexes is accomplished. You'll notice that when you select the option for rebuilding, the option for updating stats is dimmed - it gets done as part of the rebuild.

We end up with a lot of SQL, but most of it looks something like this:

dbcc dbreindex(N'[dbo].[Suppliers]', N'', 90, sorted_data_reorg)

It's processing each object using a standard DBCC. One thing I'd like to see added is support for the Defrag option. There are times when you can't afford a total index rebuild but you need to increase performance if you can - defrag can be a good solution in some cases.

So, have I changed any minds yet?

Whether you use maintenance plans or despise them, I hope you've gained a little insight into how they do that thing they do. After all, at your next job they might be using them!

Total article views: 10412 | Views in the last 30 days: 5
Related Articles

In 'Select Maintenance Task Order' the move up and move down options are dimmed.

Maintenance Plan Wizard(SQL 2005 Standard Edition) on the 'Select Maintenance Task Order' the move u...


Update Script to select

Update Script to select


Maintenance Plan reporting

Select Report Option


RYO Maintenance Plan – Update Statistics

Another key component of any good maintenance plan is updating statistics. Statistics are what help ...


Update and Select

Update and Select