SQLServer2005怎樣評估和管理索引

1、SQLServer2005怎樣評估和管理索引？SQLServer2005怎樣評估和管理索引？-王成輝翻譯整理，轉(zhuǎn)貼請注明出處問題：SQLServer2005怎樣評估和管理索引？(1)怎樣知道索引是否有用？它們是怎樣使用的？(2)哪些表和索引是沒用或者很少用的？(3)索引維護的成本與它的性能比多少合適？(4)存在索引爭奪嗎？(5)更多的索引好還是更少的索引好？回答：SQLServer2005動態(tài)管理視圖(DMVs)返回會話、事務、請求的服務器狀態(tài)信息。它可用于診斷、內(nèi)存和過程調(diào)優(yōu)、監(jiān)控(SQLServer2000不支持)。SQLServer引擎跟蹤詳細的資源使用情況，用select語句從DMVs

2、中可查到， 但是這些信息不會長期駐留在磁盤上。 由于索引提供了代替表掃描的一個選擇，且DMVs返回索引使用計數(shù)，所以可以比較索引的成本和其性能。 這個比較包括保持索引最新的成本， 與使用索引而不是表掃描讀數(shù)據(jù)的性能之比。謹記一個更新或刪除操作先要讀數(shù)據(jù)從而定位數(shù)據(jù)，然后對定位的數(shù)據(jù)進行寫操作。一個插入操作在所有的索引上只是寫操作。因此，一個大量的插入將使寫操作次數(shù)超過讀操作次數(shù)。一個大量的更改操作(包括更新和刪除)，讀和寫的次數(shù)通常很接近(假定沒有記錄找不到的情況發(fā)生)。一個大量的讀操作，讀的次數(shù)將超過寫。引用約束如外鍵還要求額外的讀操作（對于插入、更新、刪除而言）去確保引用完整性得到維護。（

3、1）怎樣知道索引是否有用？它們是怎樣使用的？首先來看看索引是否是有用的。DDL是用來創(chuàng)建對象（如索引）且更新目錄的。創(chuàng)建索引不是使用索引，所以在索引相關的DMVs不會有記錄，除非索引真正被使用。當一個索引被Select、Insert、Update或者Delete引用時，會被sys.dm_db_index_usage_stats捕獲。如果運行一個典型的SQLServer使 用 周 期 后 ， 所 有 的 有 用 的 索 引 都 會 記 錄 在sys.dm_db_index_usage_stats中。這樣，任何一個在sys.dm_db_index_usage_stats中找不到的索引就是沒用的索引

4、（在最近的一個SQLServer使用周期里）。未使用的索引可通過下面的方式找到：(2)哪些表和索引是沒用或者很少用的？-未使用的表和索引。表都有一個索引ID,如果是0則為堆表，1則為聚集索引DeclaredbidintSelectdbid=db_id(Northwind)Selectobjectname=object_name(i.object_id),indexname=,i.index_idfromsys.indexesi,sys.objectsowhereobjectproperty(o.object_id,IsUserTable)=1andi.index_idNOTIN(s

5、elects.index_idfromsys.dm_db_index_usage_statswheres.object_id=i.object_iddatabase_id=dbid)ando.object_id=i.object_idorderbyobjectname,i.index_id,indexnameasc使 用 很 少 的 索 引 和 頻 繁 使 用 的 索 引 一 樣 ， 都 會 記 錄 在sys.dm_db_index_usage_stats中。 為了找出這些索引， 需要查看諸如user_seeks、user_scans、user_lookups和user_updates的歹!J

6、。-使用很少的索引排在最先declaredbidintselectdbid=db_id()selectobjectname=object_name(s.object_id),s.object_id,indexname=,i.index_id,user_seeks,user_scans,user_lookups,user_updatesfromsys.dm_db_index_usage_statss,sys.indexesiwheredatabase_id=dbidandobjectproperty(s.object_id,IsUserTable)=1andi.object_id=s

7、.object_idandi.index_id=s.index_idorderby(user_seeks+user_scans+user_lookups+user_updates)asc(3)索引維護的成本與它的性能比多少合適？如果一個表是頻繁更改的而又有很少用到的索引，那么維護索引的成本將超過索引帶來的好處。為了比較成本和其好處，可以如下使用表值函數(shù)sys.dm_andi.index_id=s.index_idand-sys.dm_db_index_operational_statsdeclaredbidintselectdbid=db_id()selectobjectname=object

8、_name(s.object_id),indexname=,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_writes=nonleaf_insert_count+nonleaf_update_count+nonleaf_delete_count,nonleaf_page_splits=nonl

9、eaf_allocation_countfromsys.dm_db_index_operational_stats(dbid,NULL,NULL,NULL)s,sys.indexesiwhereobjectproperty(s.object_id,IsUserTable)=1andi.object_id=s.object_idandi.index_id=s.index_idorderbyreadsdesc,leaf_writes,nonleaf_writes-sys.dm_db_index_usage_statsselectobjectname=object_name(s.object_id)

10、,indexname=,i.index_id,reads=user_seeks+user_scans+user_lookups,writes=user_updatesfromsys.dm_db_index_usage_statss,sys.indexesiwhereobjectproperty(s.object_id,IsUserTable)=lands.object_id=i.object_idandi.index_id=s.index_idands.database_id=dbidorderbyreadsdescgosys.dm_db_index_usage_stats和sys

11、.dm_db_index_operational_stats不同之處在于：前者是每次訪問加1,而后者依賴于操作、頁、或行來計數(shù)。(4)存在索弓I爭奪嗎？可以在sys.dm_db_index_operational_stats中查看索引爭奪(如等待鎖)。歹！Jrow_lock_count,rowlockwaitcount,rowlockwaitinms,pagelockcount,page_lock_wait_count,page_lock_wait_in_ms,page_latch_wait_count,page_latch_wait_in_ms,pageio_latch_wait_count

12、,pageio_latch_wait_in_ms詳細描述了鎖和在等待期間的鎖爭奪。可以通過比較鎖和阻塞等待的計數(shù)來得到均值，如下：declaredbidintselectdbid=db_id()Selectdbid=database_id,objectname=object_name(s.object_id),indexname=,i.index_id-,partition_number,row_lock_count,row_lock_wait_count,block%=cast(100.0*row_lock_wait_count/(1+row_lock_count)asnumer

13、ic(15,2),row_lock_wait_in_ms,avgrowlockwaitsinms=cast(1.0*row_lock_wait_in_ms/(1+row_lock_wait_count)asnumeric(15,2)fromsys.dm_db_index_operational_stats(dbid,NULL,NULL,NULL)s,exesiwhereobjectproperty(s.object_id,IsUserTable)=landi.object_id=s.object_idandi.index_id=s.index_idorderbyrow_lock_wait_co

14、untdesc下面的報告顯示在表OrderDetails的鎖，OrdersOrder_Details表上的索引。雖然鎖出現(xiàn)的時間小于2%,但當它發(fā)生時，平均的鎖時間是15.7秒。使用事件探查器跟蹤下面的阻塞進程報告是很重要的。你可以使用sp_configureBlockedProcessThreshold,1般置鎖進程報表的閾值為15。然后，超過15秒后運行跟蹤去捕獲鎖。事件探查器能跟蹤鎖和阻塞。跟蹤結(jié)果可以保存到跟蹤文件里以便進行分析。你可以找到鎖產(chǎn)生的原因。本例中鎖是由存儲過程NewCustOrder引起的，阻塞是由存儲過程UpdCustOrderShippedDate引起的。 本例中事件

15、探查器的鎖進程跟蹤報告顯示是由存儲過程引起的， 你不能查看存儲過程里引起鎖的實際語句。然而你可以用stmtstart和stmtend找到過程NewCustOrder里引起阻塞的語句。使用上面的報告，你能夠通過提供sqlhandle、stmtstart和stmtend得到存儲過程NewCustOrder的阻塞語句，如下：declaresql_handlevarbinary(64),stmtstartint,stmtendintSelectsql_handle=0 x3000050005d9f67ea8425301059700000100000000000000Selectstmtstart=92

16、0,sys.ind1064selectsubstring(qt.text,s.statement_start_offset/2,(casewhens.statement_end_offset=-1thenlen(convert(nvarchar(max),qt.text)*2elses.statement_end_offsetend-s.statement_start_offset)/2)asblockedstatement,s.statement_start_offset,s.statement_end_offset,batch=qt.text,qt.dbid,qt.objectid,s.e

17、xecution_count,s.total_worker_time,s.total_elapsed_time,s.total_logical_reads,s.total_physical_reads,s.total_logical_writesfromsys.dm_exec_query_statsscrossapplysys.dm_exec_sql_text(s.sql_handle)asqtwheres.sql_handle=sql_handleands.statement_start_offset=stmtstartands.statement_end_offset=stmtend你能使

18、用下面的存儲過程實時的捕獲在存儲過程里實際引起鎖的語句：createprocsp_block_infoasselectt1.resource_typeaslocktype,db_name(resource_database_id)asdatabase,t1.resource_associated_entity_idasblkobject,t1.requeststmtendmodeaslockreq-lockrequested,t1.request_session_idaswaitersid-spidofwaiter,t2.wait_duration_msaswaittime,(selectte

19、xtfromsys.dm_exec_requestsasr-getsqlforwaitercrossapplysys.dm_exec_sql_text(r.sql_handle)wherer.session_id=t1.request_session_id)aswaiter_batch,(selectsubstring(qt.text,r.statement_start_offset/2,(casewhenr.statement_end_offset=-1thenlen(convert(nvarchar(max),qt.text)*2elser.statement_end_offsetend-

20、r.statement_start_offset)/2)fromsys.dm_exec_requestsasrcrossapplysys.dm_exec_sql_text(r.sql_handle)asqtwherer.session_id=t1.request_session_id)aswaiter_stmt-statementblocked,t2.blocking_session_idasblockersid-spidofblocker,(selecttextfromsys.sysprocessesasp-getsqlforblockercrossapplysys.dm_exec_s=t2.resource_addressgoexec（5）更多的索引好還是更少的索引好？記住索引既有維護的成本又可提高讀的性能，所有索引的成本和性能可以通過比較讀和寫而得到。讀索引可以避免表掃描，然而索引的維護以保持最新需要成本?？梢院苋菀椎恼业剿饕欠袷褂眠€是很少使用。在最后的這個分析中，索引的成本性能是比較主觀的問題。原因在于大量的讀與寫較高的依賴于工作量和頻率。另外，超過大量讀寫的定性因素是要考慮的第二個因素：包括重要程度很高的每月一次的管理報告或每季的VP報告。insert會執(zhí)行所有索引的寫操作，但是沒有無關聯(lián)的讀（除非有引用約束）。除了se