Maintenance Plans - Behind the Scenes

,

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([CategoryID],@PSTATMAN) FROM (SELECT TOP 100 PERCENT [CategoryID] FROM [dbo].[Categories] WITH(READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [CategoryID]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)

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) 

UPDATE STATISTICS [dbo].[Categories] WITH all, SAMPLE 10 PERCENT

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 ) 

DBCC SQLPERF(LOGSPACE)

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!

Rate

5 (1)

Share

Share

Rate

5 (1)