Inside SQL Server Maintenance Plans

Inside SQL Server Maintenance Plans


by Andrzej Kozlowski

 

Like a lot of database administrators who manage many SQL Server databases, I have problems with maintenance and administration tasks. Some of these are massive jobs, which have to be created and maintained to be sure that databases are robust and reliable. One of the tools I use often to make my life a little easier is the Database Maintenance Wizard, which is used to create SQL Server maintenance plans.

While the Database Maintenance Wizard, and the plans it creates are generally good, they are not always perfect. In addition, what is going on behind the scenes when a maintenance plan is running is not so obvious. So what I recently did was to find out what was going on behind the scene using the SQL Server Profiler.

To capture the Profiler trace (using SQL Server 2000), I used the predefined Profiler template called SQLProfilerStandard, with two modifications, using:

  • RPC: Starting
  • SQL: BatchStarting

instead of:

  • RPC: Completed
  • SQL: BatchCompleted

All the other trace configuration items were left the same. Those two changes to the SQLProfilerStandard template allow us easily to capture the relevant trace data.

 

Profiling Integrity Maintenance Plan Options

Next, I created a Maintenance Plan using the following Integrity options (see figure as a reference point) in order to see what SQL Server statements were used.

Then, I started the Profiler trace and ran the job created by the Maintenance Plan to see what the results would be. I then went back and changed some of the settings in the above screen to see what happened as options were changed.

Below are the results, formatted into a table for easy viewing.

Which Integrity Settings Were Used Profiler Trace Results Xp_Sqlmaint Syntax
Check database integrity, include indexes, attempt to repair any minor problems dbcc checkdb(Pubs, REPAIR_FAST) WITH NO_INFOMSGS -CkDBRepair
(not documented)
Check database integrity, include indexes Use pubs
dbcc checkdb WITH NO_INFOMSGS

Check database integrity, exclude indexes dbcc checkdb(Pubs, noindex) WITH NO_INFOMSGS -CkDBNoIdx
Perform the above tests before backing up the database or transaction log   -BkUpOnlyIfClean
(One of above CkDB depending on chosen settings)

Comments on this test run:

  1. Don’t use the option "Attempt to repair any minor problems" which causes DBCC Checkdb to be run with the option REPAIR_FAST. This option performs minor repair actions without risk of data loss. However, the given database must be in single-user mode to use this value, which presents problems if this is a production database.

  2. If database is not in single-user mode, DBCC Checkdb with REPAIR_FAST, will not be run, unfortunately the text or html report files will still report a success.

  3. By checking the option "Perform these tests before backing up the database or transaction log," SQL server modifies the backup jobs (full and log backup) by adding two option to the respective maintenance plans:

    · BkUpOnlyIfClean
    · CkDB | CkDBNoIdx | CkDBRepair

  4. Don’t use the above option if you are going to add transaction log backups to the same maintenance plan, as this will cause DBCC activity before each incremental backup, hurting performance

  5. "WITH NO_INFOMSGS" is used to suppress all informational messages


Profiling Optimization Maintenance Plan Options

In this step, I wanted to see happened under the covers when various Optimization options (see illustration below) from the Database Maintenance Wizard are traced with Profiler. As before, I will experiments with several different Optimization options, and run the resulting jobs manually in order to capture the traces.


Below are the results, formatted into a table for easy viewing.

Which Optimization Settings Were Used Profiler Trace Results Xp_Sqlmaint Syntax
Reorganize data and index pages, reorganize pages with the original amount of free space dbcc dbreindex(dbo.Table1,'', 0, sorted_data_reorg) 
(for each table in database)
-RebldIdx 100
Reorganize data and index pages, change free space per page percentage to 30% dbcc dbreindex(dbo.Table1', '', 70, sorted_data_reorg) 
(for each table in database)
-RebldIdx 30
Update the statistics used by the query optimizer, percentage of database to sample 30% UPDATE STATISTICS dbo.table1 WITH all, SAMPLE 30 PERCENT (for each table in database) -UpdOptiStats 30
Remove unused space from database files DBCC shrinkdatabase(test, 10, TRUNCATEONLY ) -RmUnusedSpace 2048 10


Comments on this test run:

  1. DBCC DBREINDEX uses free space as the inverse of the fill factor. So in the first row in the above table, free space = 0%, which is the same as a fill factor of 0. In second position we can see a value of 30% for free space, which is the same as a 70% fill factor.

  2. If you choose DBREINDEX for a database, an UPDATE STATISTIC is done automatically

  3. Even though the UPDATE STATISTIC command gives us the possibility to choose either a SAMPLE option of PERCENT or number of ROWS, the maintenance plan allows you only to specify PERCENT.

Profiling Other Maintenance Plan Options

I will not describe the database and log backup options of the Database Wizard because they are very straight-forward, and work like you would expect them to. However, I would like you to discuss some other maintenance plan options which are accessible only from xp_sqlmaint utility and not from the Maintenance Plan Wizard:

  • HtmlRpt "e:\mssql7\LOG\ak_test.html": Creates an HTML version of the database maintenance report.

  • DelHtmlRpt <time period>: Sets the retention cycle for HTML files, e.g. 3 WEEKS.

  • CkCat: Runs DBCC CHECKCATALOG.

  • CkAl, CkAlNoIdx: Runs DBCC NEWALLOC.

  • CkTxtAl: Runs DBCC TEXTALL.


Comments on These Commands:

  1. Be careful with the syntax of the above switches. They must be exactly as I listed above. For example if you type Htmlrpt (instead of HtmlRpt) the job will fail.

  2. Don’t use NEWALLOC and TEXTALL. They are included for backward compatibility. CHECKALLOC replaced NEWALLOC and CHECKTABLE TEXTALL. It’s better to use CHECKDB, which performs CHECKTABLE for each table in a database to check the integrity of the data, and CHECKALLOC to check allocation of all pages.

 

Maintenance Plan Tables

Now for some details on how maintenance plans are stored inside SQL Server. The more you know about this, the easier it will be for you to automate the gathering of useful information about your maintenance plans, if you so desire.

There are four tables in the msdb database which are store maintenance plan information:

  • sysdbmaintplan_databases: Contains one row for each database. Note that you can use special strings for group of databases like in the Database Maintenance Plan Wizard.


Sample Data From sysdbmaintplan_databases

plan_id  database_name
36D539DF-7DE3-11D6-9855-00508BB3C376 WWW_ak
36D539E1-7DE3-11D6-9855-00508BB3C376 All Databases
36D539E2-7DE3-11D6-9855-00508BB3C376 All System Databases
36D539E3-7DE3-11D6-9855-00508BB3C376 All User Databases
  • sysdbmaintplans: Contains one row for each maintenance plan.

Sample Data From sysdbmaintplans (partial table)

Plan_id Plan_name date _created owner max _history _rows
00000000-0000-0000-0000-000000000000 All ad-hoc plans 11/13/98 3:10 REDMOND\_sqlbld 0
36D539DF-7DE3-11D6-9855-00508BB3C376 ak_test_www_ak 11/14/98 3:10 NT\TEST 1000
36D539E1-7DE3-11D6-9855-00508BB3C376 ak_test_all_db 11/15/98 3:10 NT\TEST 1000
36D539E2-7DE3-11D6-9855-00508BB3C376 ak_test_sys_db 11/16/98 3:10 NT\TEST 1000
36D539E3-7DE3-11D6-9855-00508BB3C376 ak_test_user_db  11/17/98 3:10 NT\TEST 1000
  • sysdbmaintplan_history: Maintain the history information for each maintenance plan.

  • sysdbmaintplan_jobs: Maintains the relationship between the maintenance plan id and the job id.


Comments on These System Tables:

Notice than there is one predefined maintenance plan named: "All ad-hoc plans" with a strange owner: "REDMOND\_sqlbld". It is used for all ad-hoc jobs using the xp_sqlmaint utility. The max_history_rows for this plan is set to 0, however there are history entries in the sysdbmaintplan_history table for it.

To demonstrate how this "special" entries works, I created a custom job with following command:

EXECUTE master.dbo.xp_sqlmaint N'-D WWW_ak -Rpt "e:\mssql7\LOG\www_ak.txt" -WriteHistory -CkDB '

After running it twice, here's what the history table looks like:

plan_id plan
_name
database
_name
server
_name
activity duration
00000000-0000-0000-0000-000000000000 All ad-hoc plans WWW_ak test Check Data and Index Linkage 77
00000000-0000-0000-0000-000000000000 All ad-hoc plans WWW_ak test Check Data and Index Linkage 77

 

Creating a Custom Maintenance Plan

As you can see, the SQL Server Database Maintenance Plan Wizard doesn’t allow us to include all the potentially useful options (HTML report, some DBCC stuff). As workaround, I propose that you follow one these two options when creating your own maintenance plan:

  1. Create each new maintenance plan using the wizard, then modify each job manually for those options not available directly from the wizard.

  2. Avoid the wizard completely, and create all jobs manually using the xp_sqlmaint utility.

I recommend the following strategy for SQL Server maintenance jobs:

  1. Create jobs (ad-hoc or using the wizard) for full backups of your databases with DBCC CHECKDB and CHECKCATALOG, and set up the option to perform the backup only if they didn’t report any errors. Create this job for all databases which don’t demand transaction log backups.

  2. Create separate jobs for only transaction log backups for all databases which need it.

  3. Create separate job(s) for data optimizations, depending on your demands.

  4. For all of your jobs, add the HTML report option and direct it to a share point for easy access with a browser.

All information in this article was provided to help you simplify the management of our SQL Server environment. Please send all comments and questions to: akozlowski@amadeus.net



posted on 2006-06-01 16:22 【Xine】中文站 阅读(525) 评论(0)  编辑  收藏 所属分类: SQL Server


只有注册用户登录后才能发表评论。


网站导航:
 
<2024年11月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(8)

随笔分类(40)

随笔档案(40)

文章分类(33)

文章档案(34)

相册

BLOG 联盟

搜索

最新评论

阅读排行榜

评论排行榜