SQLServer使用T-SQL管理数据库.ppt

第12章使用T SQL管理数据库 管理索引碎片索引碎片检测索引碎片索引碎片整理选项管理统计信息收缩数据库使用DBCCCHECKDB 管理索引碎片 索引是保证优化查询执行中最基本的元素之一 它不像SQLServer2005中其他的对象 合理地建立索引只是关键的第一步 对于索引还要进行长时间合理的维护 对索引维护不当 构成索引的索引页中就会包含过多的碎片 失去它的功效 索引碎片的程度以及DBA对索引的维护程度决定着哪些索引会在SQLServer查询优化器中进行使用 知识点 学完本节后 你应能够了解 什么是索引碎片 索引碎片的类型 检测索引碎片 索引碎片的整理选项 索引碎片 索引碎片是由于索引内的页使用不充分而造成的 数据的修改 碎片会不断的产生 索引碎片发生当数据被更改的时候 SQLServer重新整理索引页 对索引页进行拆分碎片类型内部碎片 由于每页内存储的数据量低于该数据页可包含的数据量 因此造成索引内的页使用不充分 外部碎片 由于页的逻辑顺序错误造成索引内页的使用不充分 检测碎片SQLServerManagementStudio 索引属性窗口系统函数 sys dm db index physical stats 使用SQLServerManagementStudio 索引属性窗口 检测索引碎片 打开SSMS图形化工具 查看索引碎片的详细信息 打开查看索引的 属性 对话框 单击 碎片 选项 使用sys dm db index physical stats系统函数 检测索引碎片 可以使用sys dm db index physical stats函数查看一个特定索引的碎片 表或索引视图中所有索引的碎片 一个数据库中所有索引的碎片等等 sys dm db index physical stats函数的参数包括评估的数据库 表 索引和分区的ID 该函数的结果集中 avg fragmentation in percent列 它显示了平均索引碎片的百分比 avg page space used in percent列 它显示了页的填充度的百分比 可以通过检查它们 验证索引好用与否 举例 用T SQL代码演示如何使用sys dm db index physical stats函数获取Production Product表上所有索引的平均碎片率 程序清单如下 获取Production Product表上所有索引的平均碎片率SELECTa index id name avg fragmentation in percentFROMsys dm db index physical stats DB ID N AdventureWorks OBJECT ID N Production Product NULL NULL NULL ASaJOINsys indexesASbONa object id b object idANDa index id b index id 代码执行结果表 索引碎片整理选项 整理碎片的选项 重新组织 重新组织索引将对表的聚集索引和非聚集索引的页级进行碎片整理 重新生成 重新生成索引将删除索引然后创建一个新的索引 重新组织 它是对页级页重新进行物理排序以匹配页节点的逻辑顺序 从左至右 使页有序排列将改善索引扫描性能 重新组织索引还将压缩索引页 这种缩所产生的空页将被删除 提供了额外的磁盘空间 重新生成 执行此操作时 碎片将被删除 磁盘空间通过对页的压缩得以回收 并且索引行将记录在连续页中 通过减少获取请求数据所需要的页读取次数 来提高磁盘的性能 重新组织索引与重新生成索引 在决定对于索引是重新组织还是重新生成来消除碎片时 应基于索引中现有的碎片程度 可通过以下两种情况进行报告 使用SSMS图形化工具 使用sys dm db index physical stats函数 碎片的处理方法 注意 若碎片高于30 重新组织索引将不会带来改善 应尝试重新生成索引 重新组织索引 使用ALTERINDEX语句的REORGANIZE子句来整理索引碎片 举例 用T SQL代码使用ALTERINDEX语句重新组织了Production Product表上AK Product ProductName索引 程序清单如下 ALTERINDEXAK Product ProductNameONProduction ProductREORGANIZE 重新生成索引 使用ALTERINDEX语句的REBUILD子句来整理索引碎片 举例 用T SQL代码使用ALTERINDEX语句重新生成了Production Product表上AK Product ProductName索引 程序清单如下 ALTERINDEXAK Product ProductNameONProduction ProductREBUILD 管理统计信息 SQLServer创建的统计信息是实现最佳查询性能的一个重要方面 这些统计信息展示了列中数值的分布情况 在对查询进行评估时 查询优化器可以使用这些统计信息来预估使用索引优化器而产生的代价 自动生成统计信息 当DBA创建索引时 查询优化器将针对被索引的列存储相应的统计信息 以优化查询的速度 在 数据库属性 选项对话框中 如下图 自动创建统计信息 为true 数据库引擎将为不包含索引的列创建统计信息 这些列会在查询中用到 不同于索引 统计信息不包含列中的全部信息它通常只会从列中抽取200个数据来构成统计信息 如上图所示 自动更新统计信息 选项 它可以使得查询优化器根据数据库的变化自动更新统计信息 用T SQL语句生成统计信息 可使用sp createstats系统存储过程为所有的表中所有符合条件的列创建统计信息 举例 用代码为当前数据库中全部用户表的所有合格的列创建统计信息 程序清单 EXECsp createstats 可使用CREATESTATISTICS语句为特定的表或视图创建统计信息 举例 用代码创建NamePurchase统计信息的集合 此示列对ContactID和EmailAddress列中的所有行计算统计信息 程序清单如下 CREATESTATISTICSNamePurchaseONAdventureWorks Person Contact ContactID EmailAddress WITHFULLSCAN NORECOMPUTE 可以使用UPDATESTATISTICS或sp updatestats系统存储过程来更新统计信息 也可使用DROPSTATISTICS来删除统计信息 查看统计信息 可使用不同的方法来查看统计信息 sp autostats系统存储过程sys stats目录视图sys stats column目录视图STATS DATE函数DBCCSHOW STATISTICS语句 收缩数据库 在SQLServer中 一些操作可能会导致数据库文件的大小超过其实际的大小 数据库管理员可以通过收缩文件 移出未使用的数据页 以释放磁盘空间 自动收缩数据库文件 SQLServer2005允许通过设置 数据库属性 中的 自动收缩 选项 使数据库引擎可以自动对数据库进行收缩 数据库的持续增长和自动收缩 会导致文件碎片的产生 手动收缩数据库文件 当需要收缩数据库文件时 更好的方法是手动收缩法 可以决定收缩操作的时机 不会给服务器造成持续的负担 使用SHRINKDATABASE语句或者SHRINKFILE语句来执行数据库或者数据库文件的收缩 注意 无法将数据库收缩到小于创建时的大小 举例 用下列代码表明以上两个语句的使用格式 减小UserDB用户数据库中数据文件和日志文件的大小 以便在数据库中留出10 的可用空间DBCCSHRINKDATABASE UserDB 10 GO 将UserDB用户数据库中名为DataFile1的数据文件的大小收缩到7MBUSEUserDB GODBCCSHRINKFILE DataFile1 7 GO 程序清单如下 收缩的最佳实践 执行可能会产生很多未使用空间的操作后 可进行收缩操作 不要反复收缩数据库 大多数数据库需要一些可用空间 用来日常操作时使用 收缩操作不会保留索引碎片状态 还会增加一定的碎片 若无特定需要 不应将AUTO SHRINK数据库选项设置为ON 使用DBCCCHECKDB 作为数据库维护操作的一部分 需要定期检查数据库的完整性 可以使用DBCCCHECKDB语句完成任务 需要了解该语句的输出 明确数据库的状态 DBCCCHECKDB语句的命令语法如下 DBCCCHECKDB database name database id 0 NOINDEX REPAIR ALLOW DATA LOSS REPAIR FAST REPAIR REBUILD WITH ALL ERRORMSGS NO INFOMSGS TABLOCK ESTIMATEONLY PHYSICAL ONLY DATA PURITY 参数的含义如下 database name database id 0 运行完整的数据库名称或ID NOINDEX 指定不应对用户表的非聚集索引执行可能引起很大系统开销的检查 这将有效减少总执行时间 REPAIR ALLOW DATA LOSS 尝试修改报告的所有错误 REPAIR FAST 保留语法是为了向后兼容 未执行修复操作 REPAIR REBUILD 执行次要 快速修复以及耗时修复 执行这些修复不会丢失数据 ALL ERRORMSGS 显示针对每个对象报告的所有错误 指定了它 则只为每个对象显示前200条错误消息 按对象ID对错误进行排序 tempdb数据库生成消息除外 NO INFOMSGS 取消显示所有信息性消息 TABLOCK 使DBCCCHECKDB获取锁 并且使其在负荷较重的数据库上运行更快 会减少数据库上可获得的并发性 ESTIMATEONLY 显示运行包含所有其他执行选项的DBCCCHECKDB时所需的tempdb空间估计数量 但不执行实际数据库检查 PHYSICAL ONLY 用较小的开销数据库的物理一致性 硬件故障等 DATA PURITY 检查数据库中是否存在无效或越界的值 举例