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!
