Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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([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!

Total article views: 10402 | Views in the last 30 days: 8
 
Related Articles
FORUM

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...

FORUM

Update Script to select

Update Script to select

FORUM

Maintenance Plan reporting

Select Report Option

BLOG

RYO Maintenance Plan – Update Statistics

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

FORUM

Update and Select

Update and Select

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones