SQLServer维护计划内幕

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址

象其他管理很多SQLServer数据库的许多管理员一样，我对维护和管理任务也有疑问。其中一些是不得不创建和维护的大的作业，以确保数据库是健壮的、可靠的。我经常使用的一个使我的工作变得更容易的工具使数据库维护向导，它可以用来创建SQLServer维护计划。

虽然数据库维护向导和它创建的计划通常是好的，但它们不总是很完美。另外，维护向导运行背后所进行的一切都不是显而易见的。所以最近我所做的就是用事件探查器去找出背后所做的事情。

为了捕捉事件探查器的跟踪（使用SQLServer2000），我使用了预定义的跟踪模板叫做SQLProfilerStandard，不过有两处更改：

• RPC: Starting
• SQL: BatchStarting

替换为：

• RPC: Completed
• SQL: BatchCompleted

所有其他的跟踪配置条目保持相同。这两个对SQLProfilerStandard模板的改变允许我们很容易的去捕捉相应的跟踪数据。

维护计划的完整性选项

接下来，为了了解使用了什么SQLServer语句我使用下面的完整性选项创建了一个维护计划（如下图所示）。

然后，我启动事件探查器跟踪并运行维护计划创建的作业，看看结果会是什么。然后再回去更改上面图片里的一些设置来看看选项更改时发生了什么。

结果如下，用一个表来显示比较好看一些：

完整性选项	事件探查器跟踪结果	Xp_Sqlmaint语法
检查数据库完整性, 包含索引, 尝试修复所有小问题	dbcc checkdb(Pubs, REPAIR_FAST) WITH NO_INFOMSGS	-CkDBRepair (非文档化)
检查数据库完整性, 包含索引	Use pubs dbcc checkdb WITH NO_INFOMSGS
检查数据库完整性, 排除索引	dbcc checkdb(Pubs, noindex) WITH NO_INFOMSGS	-CkDBNoIdx
在备份数据库或事务处理日志之前执行测试		-BkUpOnlyIfClean (依赖于选择的设置，上面CkDB中的一个)

对这个测试运行的解释：

1、不要使用选项“尝试修复所有小问题”，这会运行带REPAIR_FAST选项的DBCC Checkdb。在选项会在不冒数据丢失的风险下执行小的修复动作。然而，给定的数据库必须处于单用户模式去使用该值，如果是一个产品数据库的话会出现问题。

2、如果数据库不是单用户模式，带REPAIR_FAST的DBCC Checkdb不会运行，不幸的是文本或HTML报告文件仍会报告成功。

3、通过选中选项“在备份数据库或事务处理日志之前执行测试”，SQLServer通过添加两个选项到各自的维护计划来更改备份作业（全备和日志备份）。

· BkUpOnlyIfClean
· CkDB | CkDBNoIdx | CkDBRepair

4、如果你要添加事务日志备份到同一个维护计划的话不要使用上面的选项，因为这将在每隔增量备份之前引起DBCC活动，影响性能。

5、用“WITH NO_INFOMSGS”来禁止所有的信息报告。

维护计划的优化选项

在这个步骤里，我要从用事件探查器来跟踪的数据库维护向导里查看不同的优化选项到底发生了什么（下图所示）。之前，我要用几个不同的优化选项来试验一下，为了捕捉这些跟踪，手动的来运行能产生结果的作业。

结果如下，用一个表来显示比较好看一些：

优化选项	Profiler Trace Results	Xp_Sqlmaint Syntax
重新组织数据和索引页,使用原有可用空间重新组织页面	dbcc dbreindex(dbo.Table1,'', 0, sorted_data_reorg) (对数据库的每一个表)	-RebldIdx 100
重新组织数据和索引页,将每页的可用空间百分比更改为30%	dbcc dbreindex(dbo.Table1', '', 70, sorted_data_reorg) (对数据库的每一个表)	-RebldIdx 30
更新查询优化器使用的统计, 数据库的采样百分比为30%	UPDATE STATISTICS dbo.table1 WITH all, SAMPLE 30 PERCENT (对数据库的每一个表)	-UpdOptiStats 30
删除数据库文件中不使用的空间	DBCC shrinkdatabase(test, 10, TRUNCATEONLY )	-RmUnusedSpace 2048 10

对这个测试运行的解释：

• DBCC DBREINDEX使用填充因子的反面作为可用空间。所以在上表的第一行里，可用空间＝0％，等同于0％的填充因子相同。在第二行里我们看到可用空间为30％，等同于70％的填充因子。
• 如果你为数据库选择DBREINDEX，UPDATE STATISTIC会自动完成。
• 即使UPDATE STATISTIC命令给了我们一个选择百分比或行数的样本的可能性，维护向导仅允许你指定百分比。

维护计划的其他选项

我不会描述数据库向导的数据库和日志备份选项，因为它们非常显而易见，就像你期望的那样去工作。然而，我会讨论一些其他的仅能从xp_sqlmaint工具不能从维护计划向导访问的维护计划选项：

• HtmlRpt "e:\mssql7\LOG\ak_test.html":创建一个HTML版本的数据库维护报告
  

• DelHtmlRpt <time period>: 为HTML文件设置一个可保持的周期，比如3周
  

• CkCat: 运行DBCC CHECKCATALOG
  

• CkAl, CkAlNoIdx: 运行DBCC NEWALLOC
  

• CkTxtAl:运行DBCC TEXTALL

这些命令的解释：

• 小心上面的命令语法。它们必须正确拼写如上所列。例如如果你键入了Htmlrpt（而不是HtmlRpt），那么作业会失败。
• 不要使用NEWALLOC和TEXTALL。它们是用来向后兼容的。CHECKALLOC代替了NEWALLOC和CHECKTABLE TEXTALL来检测数据库的每个表数据完整性，并且CHECKALLOC还检测所有页面的分配。

维护计划系统表

现在讨论维护计划是怎样存在在SQLServer内的。你对此了解得越多，你就越容易去自动收集你维护计划的有用的信息，如果你愿意的话。
在MSDB数据库里有4个表来存储维护计划信息：

• sysdbmaintplan_databases: 每个数据库存一行。注意你能为在数据库维护计划向导里的数据库组使用特定的字符串。

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: 每个维护计划存一行。

sysdbmaintplans示例数据（表的一部分）

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: 维护每个维护计划的历史信息。
• sysdbmaintplan_jobs: 维护维护计划ID和作业ID之间的关系。

这些系统表的解释：

注意这里有一个预定义的维护计划名称“All ad-hoc plans”，它有一个奇怪的拥有者REDMOND\_sqlbld（译者注：SQLServer2000 SP4的这个值为sa）。它是为所有使用xp_sqlmaint的特别的作业准备的。该计划的max_history_rows列设为0，然而在表sysdbmaintplan_history里有历史记录（译者注：SQLServer2000 SP4也没有记录）。

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 '

运行两次后，历史表信息如下：

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

创建一个自定义的维护计划

正如你所看到的，SQLServer数据库维护计划向导不允许我们选择所有潜在的有用的选项（HTML报告、一些DBCC命令）。作为替代方法，我建议你创建你自己的维护计划时参照下面两个选项：

• 使用向导创建每一个新的维护计划，然后手动更改每个作业里那些不能直接从向导里使用的选项。
• 完全不用向导，使用xp_sqlmaint工具手工创建所有的作业。

为SQLServer维护作业我提供下面的策略：

• 为你的数据库全备份创建带DBCC CHECKDB和CHECKCATALOG的作业（或者特别使用向导），仅当它们不报告任何错误时设置选项去执行备份。为所有的不要求事务日志备份的数据库创建该作业。
• 仅为所有需要事务日志备份的数据库创建独立的作业。
• 为数据库优化创建独立的作业，这依赖于你的需求。
• 对所有的作业，添加HTML报告选项并放到一个容易用浏览器访问的共享的地方。