利用动态管理视图提高SQLServer索引效率

利用动态管理视图提高SQLServer索引效率我介绍了一下动态管理视图（DMV）。它是一种很有用的监控和解决SQLServer故障的工具。本文是它的续篇，我将继续和大家一起探讨其他的一些数据库管理员用来能够测定现存索引效率的动态管理视图（DMV）和分片级别。此外，我还提供了应该用来检索指定的SQLServer程序ID号（SPID）执行的最后语句。DMV提高索引效率就如同数据库DBA了解的一样，合适的索引能够提高查询性能和应用程序可测量性。但是每个附加的索引，都给系统增加了额外开销，因为随着数据从表和视图中不断增加、修改或清除，SQLServer需要维护这些索引。在安装新的索引之前，你需要检测数据库活动，保证你只有一些能提高平常执行的查询的索引。注意SQLServer并不能阻止你在相同的列上建立多个索引。它也不能提醒你你即将建立的查询并不能优化查询。复制索引对系统并没有好处。同样地，SQLServer查询优化程序不能用解决查询问题的索引也不能对系统带来什么好处。因此，在这里我们至关重要的事情就是了解索引的利用效率和它们对查询性能的影响。幸运的是，SQLServer2005和2008包括了sys.dm_db_index_usage_stats动态管理视图，我们可以用它来测量索引的效率。和所有其他的动态管理视图一样，contentsofsys.dm_db_index_usage_stats的内容在你重启SQLServer实例时就被丢弃了。所以如果你想收集索引使用统计数据，你就应该对自定义表定期复制DMV。每次用索引进行扫描时，DMV就增加了在SQLServer中搜索或查找列。例如一下的查询就在AdventureWorksDW示例数据库中检索用户表和相应视图使用统计：SELECTobject_name(a.object_id)AStable_name，COALESCE(name，'objectwithnoclusteredindex')ASindex_name，type_descASindex_type，user_seeks，user_scans，user_lookups，user_updatesFROMsys.dm_db_index_usage_statsaINNERJOINsys.indexesbONa.index_id=b.index_idANDa.object_id=b.object_idWHEREdatabase_id=DB_ID('AdventureWorksDW')ANDa.object_id1000有用的索引在user_seeks列中的总数最大。要注意user_updates这个列，这个列表示指定索引需要的维护级别。如果你注意到了一些用户搜索、扫描或查询很少用到但是还是会经常更新的索引，维护它们的成本就要比持有它们的成本要高。动态管理函数（DMF）和分片索引（fragmentedindexes）数据更改会造成索引分段，高级别的分片还会减少索引的效率。结果，SQLServer就不得不去扫描更多的索引页，甚至在用到索引时查询会变得越来越慢。为避免分片的负面影响，DBA可以重建或对索引消除碎化。在SQLServer之前的版本中，你不得不用到DBCCSHOWCONTIG语句获取索引分片级别。这个语句还有WITHTABLERESULTS这个选项，它返回的结果表格形式、有序结果。你可以想象，在一个有成千上万个表的数据库中检测每个索引肯定是一件很枯燥的工作。更不用说手动执行这项任务就等于是浪费数据库管理员的时间了。相反，许多DBA都实施了一个自动解决方案，这个解决方案上带有临时表、并且得到的结果为DBCCSHOWCONTIG。然后，你就可以根据索引的分片级别对索引进行重建或消除碎化。然是，这种方法已经过时了。虽然DBCCSHOWCONTIG仍然在SQLServer2005或2008里还存在，你还是应该用sys.dm_db_index_physical_stats动态管理函数（DMF）。DBCCSHOWCONTIG不支持最新版本中的新索引特征，可能不久就会被清除掉。有了sys.dm_db_index_physical_stats，你就不在需要创建临时表存储结果了。相反你可以在指定的时间内在定义列中用到最新的分片级别，DMF的句法如下：Sys.dm_db_index_physical_stats({database_id|NULL|DEFAULT|0}，{object_id|NULL|DEFAULT|0}，{index_id|NULL|0|-1|DEFAULT}，{partition_number|NULL|0|DEFAULT}，{mode|DEFAULT|NULL})你能够在SQLServer联机丛书上找到每个字段的详细说明。注意在指定DMF的字段时，你可以用db_id()和object_id()这两个系统函数。以下查询返回所有数据库所有索引的分片信息：SELECT*FROMsys.dm_db_index_physical_stats(NULL，NULL，NULL，NULL，NULL)第二个语句返回特定对象所有索引的索引分片级别：SELECT*FROMsys.dm_db_index_physical_stats(6，469576711，NULL，NULL，NULL)你会得到如下结果：database_idobject_idindex_idpartition_number646957671111646957671111index_type_descalloc_unit_type_descindex_depthCLUSTEREDINDEXIN_ROW_DATA3CLUSTEREDINDEXLOB_DATA1index_levelavg_fragmentation_in_percentfragment_count00.5925925938700NULLavg_fragment_size_in_pagespage_count7.75862069675NULL8396avg_page_space_used_in_percentrecord_countNULLNULLNULLNULLghost_record_countversion_ghost_record_countNULLNULLNULLNULLmin_record_size_in_bytesmax_record_size_in_bytesNULLNULLNULLNULLavg_record_size_in_bytesforwarded_record_countNULLNULLNULLNULL尽管得到的结果很庞大，该DMF还是只允许你检索你感兴趣的这些列。这是DBCCSHOWCONTIG的另一种更新，它不允许你检索这些列的子集。检索目前执行的SQL语句许多DBA已经用过SQLServer之前版本的DBCCINPUTBUFFER命令来获取已给出的链接执行的最后那个SQL语句。但是这个语句之返回了该语句最后的255个字符，可能不是整个句子。SQLServer2005和2008提供了检索该信息的几个选项。sys.dm_exec_sql_text和sys.dm_exec_requestsDMV应该通力合作，并且fn_get_sql()也应该和同一个sys.dm_exec_requestsDMV进行关联，这是另一个获取最后语句的选项。以下是示例：Tofindastatementexecutedbyaspecificsession(53):SELECTSUBSTRING(b.text，(a.statement_start_offset/2)+1，((CASEstatement_end_offsetWHEN-1THENDATALENGTH(b.text)ELSEa.statement_end_offsetEND-a.statement_start_offset)/2)+1)ASstatement_textFROMsys.dm_exec_requestsaCROSSAPPLYfn_get_sql(a.sql_handle)bWHEREa.session_id=53TogetSQLstatementssubmittedbyallrunningorsuspendedsessions:SELECTa.session_id，a.status，a.start_time，a.command，SUBSTRING(b.text，(a.statement_start_offset/2)+1，((CASEstatement_end_offsetWHEN-1THENDATALENGTH(b.text)ELSEa.statement_end_offsetEND-a.statement_start_offset)/2)+1)ASstatement_textFROMsys.dm_exec_requestsaCROSSAPPLYsys.dm_exec_sql_text(a.sql_handle)bWHEREa.statusIN('running'，'suspended')你可以在SQLServer联机丛书上找出fn_get_sql，sys.dm_exec_requests和sys.dm_exec_sql_text的详细信息。以上查询用statement_start_offset和statement_end_offset列只检索目前执行的SQL语句，即使是存储程序或自定义函数中已经付带了。如果我们已经用DBCCINPUTBUFFER，那我们应该只能获取存储程序或自定义函数名称和执行参数。