SQL Server 使用 T-SQL 管理数据库

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第 12 章,使用 T-SQL 管理数据库,1,管理索引碎片,索引碎片,检测索引碎片,索引碎片整理选项,管理统计信息,收缩数据库,使用 DBCC CHECKDB,2,管理索引碎片：,索引是保证优化查询执行中最基本的元素之一。它不像 SQL Server 2005 中其他的对象，合理地建立索引只是关键的第一步。对于索引还要进行长时间合理的维护。,对索引维护不当，构成索引的索引页中就会包含过多的碎片，失去它的功效。索引碎片的程度以及 DBA 对索引的维护程度决定着哪些索引会在 SQL Server 查询优化器中进行使用。,知识点：,学完本节后，你应能够了解：,什么是索引碎片；,索引碎片的类型；,检测索引碎片；,索引碎片的整理选项。,3,索引碎片：,索引碎片是由于索引内的页使用不充分而造成的。数据的修改，碎片会不断的产生。,索引碎片发生,当数据被更改的时候，,SQL Server,重新整理索引页，对索引页进行拆分,碎片类型,内部 碎片,由于每页内存储的数据量低于该数据页可包含的数据量，因此造成索引内的,页使用不充分。,外部 碎片,由于页的逻辑顺序错误造成索引内页的使用不充分。,检测碎片,SQL Server Management Studio ,索引属性窗口,系统函数,sys.dm_db_index_physical_stats,4,使用,SQL Server Management Studio,（索引属性窗口）检测索引碎片：,打开 SSMS 图形化工具，查看索引碎片的详细信息。 打开查看索引的属性对话框，单击碎片选项。,5,使用,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,表上所有索引的平均碎片率。,程序清单如下：,6,-,获取 Production.Product 表上所有索引的平均碎片率,SELECT a.index_id, name,avg_fragmentation_in_percent,FROM sys.dm_db_index_physical_stats (DB_ID(NAdventureWorks),OBJECT_ID(N,Production.Product,)NULL,NULL,NULL)AS a JOIN sys.indexes AS b ON a.object_id=b.object_id AND a.index_id = b.index_id;,index_id,name,avg_fragmentation_in_percent,1,PK_Product.ProductID,23.31,2,AK_Product.ProductNumber,50,3,AK_Product.ProductName,66.6666666666667,代码执行结果表：,7,索引碎片整理选项：,整理碎片的选项：,重新组织：重新组织索引将对表的聚集索引和非聚集索引的页级进行碎片整理；,重新生成：重新生成索引将删除索引然后创建一个新的索引。,重新组织：,它是对页级页重新进行物理排序以匹配页节点的逻辑顺序（从左至右），使页有序排列将改善索引扫描性能。,重新组织索引还将压缩索引页。这种缩所产生的空页将被删除，提供了额外的磁盘空间。,重新生成：,执行此操作时，碎片将被删除。磁盘空间通过对页的压缩得以回收，并且索引行将记录在连续页中。通过减少获取请求数据所需要的页读取次数，来提高磁盘的性能。,8,重新组织索引与重新生成索引：,在决定对于索引是重新组织还是重新生成来消除碎片时，应基于索引中现有的碎片程度。可通过以下两种情况进行报告：,使用,SSMS,图形化工具；,使用,sys.dm_db_index_physical_stats,函数。,avg_fragmentation_in_percent,操作,30%,重新生成,碎片的处理方法：,注意：,若碎片高于,30,，重新组织索引将不会带来改善，应尝试重新生成索引。,9,重新组织索引：,使用,ALTER INDEX,语句的,REORGANIZE,子句来整理索引碎片。,举例：,用,T,SQL,代码使用,ALTER INDEX,语句重新组织了,Production.Product,表上,AK_Product.ProductName,索引,。,程序清单如下：,ALTER INDEX AK_Product.ProductName ON Production.Product,REORGANIZE,重新生成索引：,使用,ALTER INDEX,语句的,REBUILD,子句来整理索引碎片。,举例：,用,T,SQL,代码使用,ALTER INDEX,语句重新生成了,Production.Product,表上,AK_Product.ProductName,索引,。,程序清单如下：,ALTER INDEX AK_Product.ProductName ON Production.Product,REBUILD,10,管理统计信息：,SQL Server 创建的统计信息是实现最佳查询性能的一个重要方面，这些统计信息展示了列中数值的分布情况。在对查询进行评估时，查询优化器可以使用这些统计信息来预估使用索引优化器而产生的代价。,自动生成统计信息：,当,DBA,创建索引时，查询优化器将针对被索引的列存储相应的统计信息，以优化查询的速度。,在,数据库属性,选项对话框中（如下图：），,自动创建统计信息,为,true,，数据库引擎将为不包含索引的列创建统计信息，这些列会在查询中用到。,不同于索引，统计信息不包含列中的全部信息它通常只会从列中抽取,200,个数据来构成统计信息。,11,如上图所示：自动更新统计信息选项，它可以使得查询优化器根据数据库的变化自动更新统计信息。,12,用,T-SQL,语句生成统计信息：,可使用,sp_createstats,系统存储过程为所有的表中所有符合条件的列创建统计信息。,举例,：,用代码为当前数据库中全部用户表的所有合格的列创建统计信息。,程序清单：,EXEC,sp_createstats,；,可使用,CREATE STATISTICS,语句为特定的表或视图创建统计信息。,举例：,用 代码创建,NamePurchase,统计信息的集合。此示列对,ContactID,和,EmailAddress,列中的所有行计算统计信息。,程序清单如下：,CREATE STATISTICS NamePurchase,ON AdventureWorks.Person.Contact (ContactID, EmailAddress),WITH FULLSCAN, NORECOMPUTE;,13,可以使用 UPDATE STATISTICS 或,sp_updatestats,系统存储过程来更新统计信息,；也可使用 DROP STATISTICS 来删除统计信息。,查看统计信息：,可使用 不同的方法来查看统计信息：,sp_autostats,系统存储过程,sys.stats,目录视图,sys.stats,_column,目录视图,STATS_DATE,函数,DBCC SHOW_STATISTICS,语句,收缩数据库：,在 SQL Server 中，一些操作可能会导致数据库文件的大小超过其实际的大小。数据库管理员可以通过收缩文件，移出未使用的数据页，以释放磁盘空间。,14,自动收缩数据库文件：,SQL Server 2005,允许通过设置,数据库属性,中的,自动收缩,选项，使数据库引擎可以自动对数据库进行收缩。,数据库的持续增长和自动收缩，会导致文件碎片的产生。,手动收缩数据库文件：,当需要收缩数据库文件时，更好的方法是手动收缩法。可以决定收缩操作的时机，不会给服务器造成持续的负担。,使用,SHRINKDATABASE,语句或者,SHRINKFILE,语句来执行数据库或者数据库文件的收缩。,注意：,无法将数据库收缩到小于创建时的大小。,举例：用下列代码表明以上两个语句的使用格式。,15,- 减小 UserDB 用户数据库中数据文件和日志文件的大小，以便在数据库中留出10的可用空间,DBCC SHRINKDATABASE (UserDB, 10);,GO,-,将 UserDB 用户数据库中名为 DataFile1 的数据文件的大小收 缩到7 MB,USE UserDB;,GO,DBCC SHRINKFILE (DataFile1, 7);,GO,程序清单如下：,收缩的最佳实践：,执行可能会产生很多未使用空间的操作后，可进行收缩操作；,不要反复收缩数据库，大多数数据库需要一些可用空间，用来日常操作时使用；,16,收缩操作不会保留索引碎片状态，还会增加一定的碎片；,若无特定需要，不应将,AUTO_SHRINK,数据库选项设置为,ON,。,使用,DBCC CHECKDB,：,作为数据库维护操作的一部分，需要定期检查数据库的完整性。可以使用 DBCC CHECKDB 语句完成任务。需要了解该语句的输出，明确数据库的状态。,DBCC CHECKDB 语句的命令语法如下：,DBCC CHECKDB , ( 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 , ,17,参数的含义如下：,database_name,|,database_id,| 0,：运行完整的数据库名称或,ID,；,NOINDEX,：指定不应对用户表的非聚集索引执行可能引起很大系统开销的检查，这将有效减少总执行时间；,REPAIR_ALLOW_DATA_LOSS,：,尝试修改报告的所有错误；,REPAIR_FAST,：,保留语法是为了向后兼容，未执行修复操作；,REPAIR_REBUILD,：,执行次要、快速修复以及耗时修复，执行这些修复不会丢失数据；,ALL_ERRORMSGS,：显示针对每个对象报告的所有错误。指定了它，则只为每个对象显示前,200,条错误消息。按对象,ID,对错误进行排序，,tempdb,数据库生成消息除外；,NO_INFOMSGS,：取消显示所有信息性消息；,TABLOCK,：使,DBCC CHECKDB,获取锁，并且使其在负荷较重的数据库上运行更快，会减少数据库上可获得的并发性；,18,ESTIMATEONLY,：显示运行包含所有其他执行选项的,DBCC CHECKDB,时所需的,tempdb,空间估计数量，但不执行实际数据库检查。,PHYSICAL_ONLY,：用较小的开销数据库的物理一致性、硬件故障等；,DATA_PURITY,：检查数据库中是否存在无效或越界的值。,举例：,下面的代码将对当前数据库和AdventureWorks 数据库执行DBCC CHECKDB 命令。,-,检查当前数据库,DBCC CHECKDB,GO,-,检查 AdventureWorks 数据库，不检查其中的非聚集索引,DBCC CHECKDB （AdventureWorks，NOINDEX ）;,GO,19,当指定不同参数时，命令将返回不同的结果集，结果集中会包含大量的结论和警告信息。在 SQL SERVER 2005 中要进行全面、复杂的检查将运行不带任何参数的,DBCC CHECKDB 。,20,