《转》SQL Server 2008 数据维护实务

SQL Server 2008 数据维护实务

http://blog.csdn.net/os005/article/details/7739553

http://www.cnblogs.com/xunziji/archive/2011/04/01/2002396.html

感觉蛮有用的，以前不清楚的地方，

都可以串起来了，

特别是重建索引和重新组织索引的区别。

还有邮件通知操作员的配置，正在学习。

~~~~~~~~~~~~~~~~~~~~~~~~~~

最近试用了下 Sql Server 2008 的    Maintenance Plans( 维护计划 )，感觉很不错很不错。一贯的延续了微软的风格，图形化界面操作，保准你掌握了这个，就能成为半个DBA了，哈哈，吹牛了，就是想说这个蛮不错的，特别是比这 Sql Server 2000 和 Sql Server 2005 来说，用户体验又好了一些。

废话不多说了，下面就开始详细介绍 Maintenance Plans( 维护计划 ) ：

官方对维护计划，即  Maintenance Plans 的解释：维护计划向导可以用于帮助您设置核心维护任务，从而确保数据库执行良好，做到定期备份数据库以防系统出现故障，对数据库实施不一致性检查。维护计划向导可创建一个或多个 SQL Server 代理作业，代理作业将按照计划的间隔自动执行这些维护任务。它使您可以执行各种数据库管理任务，包括备份、运行数据库完整性检查、或以指定的间隔更新数据库统计信息。创建数据库维护计划可以让SQL Server有效地自动维护数据库，保持数据库运行在最佳状态，并为管理员节省了宝贵的时间。

其实用一句话来说，Maintenance Plans( 维护计划 ) 就是来帮你方便的管理和优化数据库。

其内部，仍然是封装了部分 T-Sql 语句和 job

Maintenance Plans( 维护计划 )主要能做的工作为：

1. 备份数据库(Back Up Database)

2. 更新统计信息(Update Statistics)

3. 检查数据库完整性(Check Database Integrity)

4. 清除历史记录(History Cleanup)

5. 清除维护(Maintenance Cleanup)

6. 收缩数据库(Shrink Database)

7. 通知操作员(Notify Operator)

8. 执行 Sql Server 代理作业(Execute SQL Server Agent Job)

9. 执行 T-SQL 语句(Execute T-SQL Statement)

10. 重新生成索引(Rebuild Index Task)

11. 重新组织索引(Reorganize Index)

1. 备份数据库(Back Up Database)

最常用，也是最重要的功能了，是用起来也很见很简单，直接从左边拖拽过来

之后，在“备份数据库任务” 上右键编辑或者直接双击红色的图标，进行详细配置，详细配置信息如下图所示：

大致一看，就能明白大多配置的意思。其中有几个还是需要特殊说明一下的。

数据库：该选项选择备份Sql Server 实例中的那几个库，可以多选

备份集过期时间：是备份的失效时间，数值在 0 -99999之间，0 代表永不过气，此选项也是比较有用的，如果磁盘空间不足，新的备份文件就会覆盖掉过期的备份文件

跨一个或多个文件备份数据库：适合多文件组的数据库

2. 更新统计信息

更新 MicrosoftSQL Server 中对 表和索引的统计信息，此任务使用 UPDATE STATISTICS 语句。这个还是蛮有用的，主要影响 Sql Server 创建更好的查询计划。

上图标红的按钮，可以查看该任务封装的SQL语句

3. 检查数据库完整性(Check Database Integrity)

其实就是 DBCC CHECKDB 的 T-Sql  的封装，没什么好说的。主要用来检查数据库的完整性，DBCC CHECKDB 及相关语句通常必须从磁盘将每个已分配的页读取到内存中，才能对其进行检查，所以极消耗性能，不建议在高峰期使用。

另外，鄙人一直找不到用 DBCC CHECKDB 这个语句的地方，可能还没有碰到相应的问题把，或者由于鄙人才疏学浅啦

4. 清除历史记录(History Cleanup)

使用“‘清除历史记录’任务”对话框，可以丢弃 msdb 数据库表中旧的历史信息。 此任务支持删除备份和还原历史记录、SQL Server 代理作业历史记录和维护计划历史记录。

其实个人观察，清除的应该是 日志里面的内容

5. 清除维护(Maintenance Cleanup)

这个功能很实用，绝对的实用，并且很简单，主要用来删除备份文件或其他文件，并且图形化界面，功能也很全面，有了这个功能，绝对不会再怕备份文件把磁盘占满了。

想当初，还写的是清除文件的bat，然后放到任务计划里面，现在想起来，那种操作真不犀利了，Sql Server 已经为我们考虑好了

详细配置看下图(主要包括：删除以下类型的文件、文件位置、文件保留时间)：

6. 收缩数据库(Shrink Database)

这个功能鄙人不太常用，主要是用来减小数据库所占用的磁盘空间大小，下面就引用一段MSDN 的解释把：

使用“‘收缩数据库’任务”对话框可以创建一个任务，尝试减小所选数据库 的大小。使用下面的选项可以确定数据库收缩后在数据库中保留的未使用空间量（该百分比越大，数据库可收缩的量越小）。该数值取决于数据库中实际数据的百分 比。例如，某个 100 MB 数据库包含 60 MB 的数据和 40 MB 的可用空间，当可用空间百分比为 50% 时，则将保留 60 MB 的数据和 30 MB 的可用空间（因为 60 MB 的 50% 是 30 MB）。只会去除数据库中的多余空间。有效值为 0 到 100。

此任务执行 DBCC SHRINKDATABASE 语句。

7. 通知操作员(Notify Operator)

使用的也是比较少，不过随着数据库重要性的上升，这个任务的重要程度也会提升的

摘自MSDN：使用“‘通知操作员’任务”对话框可以向此维护计划中添加自动通知。若要使用此任务，必须启用数据库邮件并将 MSDB 正确配置为邮件主机数据库，而且还要具有一个带有有效电子邮件地址的 MicrosoftSQL Server 代理操作员。

此任务使用 sp_notify_operator 存储过程。

8. 执行 Sql Server 代理作业(Execute SQL Server Agent Job)

感觉比较鸡肋的功能，主要用来执行 job 的，不知道怎么用，还不如在 SQL Server Agent 里面设置 Job。

9. 执行 T-SQL 语句(Execute T-SQL Statement)

蛮有用的，设置也比较简单，超时时间最好设置下

10. 重新生成索引(Rebuild Index Task) 和 重新组织索引(Reorganize Index)

这两个比较重要，又比较常用，同时又有可比性，所以就放在一起说。其实，在维护中，一般只选择这两个中的其中一个，对于如何选择，则是表的大小和db请求压力等决定的。

重新生成索引比重新组织索引更彻底，更给力，但是对于在线DB来讲，影响也比较大.。下面是详细的解释：

无论何时对基础数据执行插入、更新或删除操作，SQL Server 数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数 据文件中的物理排序不匹配时，就存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。

您可以通过重新组织索引或重新生成索引来修复索引碎片。对于基于分区方案生成的已分区索引，可以在完整索引或索引的单个分区上使用下列方法之一。

重新组织索引：

若要重新组织一个或多个索引，可以使用带 REORGANIZE 子句的 ALTER INDEX 语句。此语句可以替代 DBCC INDEXDEFRAG 语句。若要重新组织已分区索引的单个分区，可以使用 ALTER INDEX 的 PARTITION 子句。

重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序 （从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织， 而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。

重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于 sys.indexes 目录视图中的填充因子值。

重新组织进程使用最少的系统资源。而且，重新组织是自动联机执行的。该进程不持有长期阻塞锁，所以不会阻止运行查询或更新。

索引碎片不太多时，可以重新组织索引。请参阅上面的表，了解有关碎片的指导原则。不过，如果索引碎片非常多，重新生成索引则可以获得更好的结果。

大型对象数据类型压缩

重新组织索引时，除了重新组织一个或多个索引外，默认情况下还将压缩聚集索引或基础表中包含的大型对象数据类型 (LOB)。数据类型 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 都是大型对象数据类型。压缩此数据可以改善磁盘空间使用情况：

重新组织指定的聚集索引将压缩该聚集索引的叶级别（数据行）包含的所有 LOB 列。

重新组织非聚集索引将压缩该索引中属于非键（包含性）列的所有 LOB 列。

如果指定 ALL，将重新组织与指定的表或视图相关联的所有索引，并压缩与聚集索引、基础表或带有包含列的非聚集索引相关联的所有 LOB 列。

如果 LOB 列不存在，则忽略 LOB_COMPACTION 子句。

重新生成索引：

重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。

可以使用下列方法重新生成聚集索引和非聚集索引：

带 REBUILD 子句的 ALTER INDEX。此语句将替换 DBCC DBREINDEX 语句。

带 DROP_EXISTING 子句的 CREATE INDEX。

每个方法执行的功能都相同，但如下表所示，也都各有优缺点需要考虑。

功能	ALTER INDEX REBUILD	CREATE INDEX WITH DROP_EXISTING
可以通过添加或删除键列、更改列顺序或更改列排序顺序来更改索引定义。*	否	是**
可以设置或修改索引选项。	是	是
可以在单个事务中重新生成多个索引。	是	否
可以联机重新生成大部分索引类型，而不会阻止运行查询或更新。	是	是
已分区索引可以重新分区。	否	是
可以将索引移动到另一个文件组中。	否	是
需要额外的临时磁盘空间。	是	是
重新生成聚集索引的操作将重新生成相关的非聚集索引。	否 除非指定关键字 ALL。	否 除非更改索引定义。
可以重新生成强制 PRIMARY KEY 和 UNIQUE 约束的索引，而不用删除并重新创建这些约束。	是	是
可以重新生成单个索引分区。	是	否

* 通过在索引定义中指定 CLUSTERED，可以将非聚集索引转换成聚集索引类型。执行此操作时必须将 ONLINE 选项设置为 OFF。不管将 ONLINE 设置成什么，都不支持从聚集索引到非聚集索引的转换。

** 如果通过使用相同的名称、列和排序顺序重新创建索引，则可以省略排序操作。重新生成操作将检查行是否在生成索引时进行了排序。

您也可以先使用 DROP INDEX 语句删除索引，然后使用一个单独的 CREATE INDEX 语句重新创建该索引，通过这种方式重新生成索引。将这些操作作为单独的语句执行有许多缺点，因此不推荐这样做。