《sqlserver2005怎样评估和管理索引》由会员分享，可在线阅读，更多相关《sqlserver2005怎样评估和管理索引（10页珍藏版）》请在金锄头文库上搜索。
1、SQLServer2005怎样评估和管理索引？SQLServer2005怎样评估和管理索引？-王成辉翻译整理，转贴请注明出处问题：SQLServer2005怎样评估和管理索引？（1）怎样知道索引是否有用？它们是怎样使用的？（2）哪些表和索引是没用或者很少用的？（3）索引维护的成本与它的性能比多少合适？（4）存在索引争夺吗？（5）更多的索引好还是更少的索引好？回答：SQLServer2005动态管理视图(DMVs)返回会话、事务、请求的服务器状态信息。它可用于诊断、内存和过程调优、监控（SQLServer2000不支持）。SQLServer引擎跟踪详细的资源使用情况，用select语句从DMVs
2、中可查到，但是这些信息不会长期驻留在磁盘上。由于索引提供了代替表扫描的一个选择，且DMVs返回索引使用计数，所以可以比较索引的成本和其性能。这个比较包括保持索引最新的成本，与使用索引而不是表扫描读数据的性能之比。谨记一个更新或删除操作先要读数据从而定位数据，然后对定位的数据进行写操作。一个插入操作在所有的索引上只是写操作。因此，一个大量的插入将使写操作次数超过读操作次数。一个大量的更改操作（包括更新和删除），读和写的次数通常很接近（假定没有记录找不到的情况发生）。一个大量的读操作，读的次数将超过写。引用约束如外键还要求额外的读操作（对于插入、更新、删除而言）去确保引用完整性得到维护。（1）怎样
3、知道索引是否有用？它们是怎样使用的？首先来看看索引是否是有用的。DDL是用来创建对象（如索引）且更新目录的。创建索引不是使用索引，所以在索引相关的DMVs不会有记录，除非索引真正被使用。当一个索引被Select、 Insert、 Update或者 Delete引用时，会被sys.dm_db_index_usage_stats捕获。如果运行一个典型的SQLServer使用周期后，所有的有用的索引都会记录在sys.dm_db_index_usage_stats中。这样，任何一个在sys.dm_db_index_usage_stats中找不到的索引就是没用的索引（在最近的一个SQLServer使用周
4、期里）。未使用的索引可通过下面的方式找到：（2）哪些表和索引是没用或者很少用的？- 未使用的表和索引。表都有一个索引ID，如果是0则为堆表，1则为聚集索引Declare dbid intSelect dbid = db_id(Northwind)Select objectname=object_name(i.object_id) , indexname=i.name, i.index_id from sys.indexes i, sys.objects o where objectproperty(o.object_id,IsUserTable) = 1and i.index_id NOT I
5、N (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id and i.index_id=s.index_id and database_id = dbid )and o.object_id = i.object_idorder by objectname,i.index_id,indexname asc使用很少的索引和频繁使用的索引一样，都会记录在sys.dm_db_index_usage_stats中。为了找出这些索引，需要查看诸如user_seeks、 user_scans、
6、user_lookups和user_updates的列。- 使用很少的索引排在最先declare dbid intselect dbid = db_id()select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id , user_seeks, user_scans, user_lookups, user_updatesfrom sys.dm_db_index_usage_stats s, sys.indexes iwhere database_id = dbid and object
7、property(s.object_id,IsUserTable) = 1and i.object_id = s.object_idand i.index_id = s.index_idorder by (user_seeks + user_scans + user_lookups + user_updates) asc（3）索引维护的成本与它的性能比多少合适？ 如果一个表是频繁更改的而又有很少用到的索引，那么维护索引的成本将超过索引带来的好处。为了比较成本和其好处，可以如下使用表值函数sys.dm_db_index_operational_stats：- sys.dm_db_index_op
8、erational_statsdeclare dbid intselect dbid = db_id() select objectname=object_name(s.object_id), indexname=i.name, i.index_id , reads=range_scan_count + singleton_lookup_count , leaf_writes=leaf_insert_count+leaf_update_count+ leaf_delete_count , leaf_page_splits = leaf_allocation_count , nonleaf_wr
9、ites=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count , nonleaf_page_splits = nonleaf_allocation_countfrom sys.dm_db_index_operational_stats (dbid,NULL,NULL,NULL) s, sys.indexes iwhere objectproperty(s.object_id,IsUserTable) = 1and i.object_id = s.object_idand i.index_id = s.index_
10、idorder by reads desc, leaf_writes, nonleaf_writes- sys.dm_db_index_usage_statsselect objectname=object_name(s.object_id), indexname=i.name, i.index_id ,reads=user_seeks + user_scans + user_lookups ,writes = user_updatesfrom sys.dm_db_index_usage_stats s, sys.indexes iwhere objectproperty(s.object_i
11、d,IsUserTable) = 1and s.object_id = i.object_idand i.index_id = s.index_idand s.database_id = dbidorder by reads descgosys.dm_db_index_usage_stats和sys.dm_db_index_operational_stats不同之处在于：前者是每次访问加1，而后者依赖于操作、页、或行来计数。（4）存在索引争夺吗？ 可以在sys.dm_db_index_operational_stats中查看索引争夺（如等待锁）。列row_lock_count, row_loc
12、k_wait_count, row_lock_wait_in_ms, page_lock_count, page_lock_wait_count, page_lock_wait_in_ms, page_latch_wait_count, page_latch_wait_in_ms, pageio_latch_wait_count, pageio_latch_wait_in_ms详细描述了锁和在等待期间的锁争夺。可以通过比较锁和阻塞等待的计数来得到均值，如下：declare dbid intselect dbid = db_id()Select dbid=database_id, objectn
13、ame=object_name(s.object_id), indexname=i.name, i.index_id -, partition_number, row_lock_count, row_lock_wait_count, block %=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2), row_lock_wait_in_ms, avg row lock waits in ms=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_c
14、ount) as numeric(15,2)from sys.dm_db_index_operational_stats (dbid, NULL, NULL, NULL) s, sys.indexes iwhere objectproperty(s.object_id,IsUserTable) = 1and i.object_id = s.object_idand i.index_id = s.index_idorder by row_lock_wait_count desc下面的报告显示在表Order Details的锁，OrdersOrder_Details表上的索引。虽然锁出现的时间小于
15、2，但当它发生时，平均的锁时间是15. 7秒。使用事件探查器跟踪下面的阻塞进程报告是很重要的。你可以使用sp_configure Blocked Process Threshold,15设置锁进程报表的阈值为15 。然后，超过15秒后运行跟踪去捕获锁。事件探查器能跟踪锁和阻塞。跟踪结果可以保存到跟踪文件里以便进行分析。你可以找到锁产生的原因。本例中锁是由存储过程NewCustOrder引起的，阻塞是由存储过程UpdCustOrderShippedDate引起的。本例中事件探查器的锁进程跟踪报告显示是由存储过程引起的，你不能查看存储过程里引起锁的实际语句。然而你可以用stmtstart和stmtend找到过程NewCustOrder里引起阻塞的语句。使用上面的报告，你能够通过提供sqlhandle、stmtstart和stmtend得到存储过程NewCustOrder的阻塞语句，如下
