SQLServer怎样评估和管理索引.docx
- 文档编号:25796060
- 上传时间:2023-06-14
- 格式:DOCX
- 页数:6
- 大小:18.46KB
SQLServer怎样评估和管理索引.docx
《SQLServer怎样评估和管理索引.docx》由会员分享,可在线阅读,更多相关《SQLServer怎样评估和管理索引.docx(6页珍藏版)》请在冰豆网上搜索。
SQLServer怎样评估和管理索引
SQLServer2005怎样评估和管理索引?
SQLServer2005怎样评估和管理索引?
--王成辉翻译整理,转贴请注明出处 问题:
SQLServer2005怎样评估和管理索引?
(1)怎样知道索引是否有用?
它们是怎样使用的?
(2)哪些表和索引是没用或者很少用的?
(3)索引维护的成本与它的性能比多少合适?
(4)存在索引争夺吗?
(5)更多的索引好还是更少的索引好?
回答:
SQLServer2005动态管理视图(DMVs)返回会话、事务、请求的服务器状态信息。
它可用于诊断、内存和过程调优、监控(SQLServer2000不支持)。
SQLServer引擎跟踪详细的资源使用情况,用select语句从DMVs中可查到,但是这些信息不会长期驻留在磁盘上。
由于索引提供了代替表扫描的一个选择,且DMVs返回索引使用计数,所以可以比较索引的成本和其性能。
这个比较包括保持索引最新的成本,与使用索引而不是表扫描读数据的性能之比。
谨记一个更新或删除操作先要读数据从而定位数据,然后对定位的数据进行写操作。
一个插入操作在所有的索引上只是写操作。
因此,一个大量的插入将使写操作次数超过读操作次数。
一个大量的更改操作(包括更新和删除),读和写的次数通常很接近(假定没有‘记录找不到’的情况发生)。
一个大量的读操作,读的次数将超过写。
引用约束如外键还要求额外的读操作(对于插入、更新、删除而言)去确保引用完整性得到维护。
(1)怎样知道索引是否有用?
它们是怎样使用的?
首先来看看索引是否是有用的。
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使用周期里)。
未使用的索引可通过下面的方式找到:
(2)哪些表和索引是没用或者很少用的?
------未使用的表和索引。
表都有一个索引ID,如果是0则为堆表,1则为聚集索引
Declare@dbidint
Select@dbid=db_id('Northwind')Select objectname=object_name(i.object_id) ,indexname=i.name,i.index_idfromsys.indexesi,sys.objectsowhereobjectproperty(o.object_id,'IsUserTable')=1andi.index_idNOTIN(selects.index_id fromsys.dm_db_index_usage_statss wheres.object_id=i.object_idand i.index_id=s.index_idand database_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的列。
---使用很少的索引排在最先
declare@dbidintselect@dbid=db_id()
selectobjectname=object_name(s.object_id),s.object_id,indexname=i.name,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.object_idandi.index_id=s.index_idorderby(user_seeks+user_scans+user_lookups+user_updates)asc
(3)索引维护的成本与它的性能比多少合适?
如果一个表是频繁更改的而又有很少用到的索引,那么维护索引的成本将超过索引带来的好处。
为了比较成本和其好处,可以如下使用表值函数sys.dm_db_index_operational_stats:
---sys.dm_db_index_operational_stats
declare@dbidint
select@dbid=db_id()
selectobjectname=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_writes'=nonleaf_insert_count+nonleaf_update_count+nonleaf_delete_count ,'nonleaf_page_splits'=nonleaf_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),indexname=i.name,i.index_id ,reads=user_seeks+user_scans+user_lookups ,writes= user_updatesfromsys.dm_db_index_usage_statss, sys.indexesiwhereobjectproperty(s.object_id,'IsUserTable')=1ands.object_id=i.object_idandi.index_id=s.index_idands.database_id=@dbidorderbyreadsdescgo
sys.dm_db_index_usage_stats和sys.dm_db_index_operational_stats不同之处在于:
前者是每次访问加1,而后者依赖于操作、页、或行来计数。
(4)存在索引争夺吗?
可以在sys.dm_db_index_operational_stats中查看索引争夺(如等待锁)。
列row_lock_count,row_lock_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@dbidintselect@dbid=db_id()Selectdbid=database_id,objectname=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)asnumeric(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, sys.indexesiwhereobjectproperty(s.object_id,'IsUserTable')=1andi.object_id=s.object_idandi.index_id=s.index_idorderbyrow_lock_wait_countdesc
下面的报告显示在表[OrderDetails]的锁,OrdersOrder_Details表上的索引。
虽然锁出现的时间小于2%,但当它发生时,平均的锁时间是15.7秒。
使用事件探查器跟踪下面的阻塞进程报告是很重要的。
你可以使用sp_configure‘BlockedProcessThreshold’,15设置锁进程报表的阈值为15。
然后,超过15秒后运行跟踪去捕获锁。
事件探查器能跟踪锁和阻塞。
跟踪结果可以保存到跟踪文件里以便进行分析。
你可以找到锁产生的原因。
本例中锁是由存储过程NewCustOrder引起的,阻塞是由存储过程UpdCustOrderShippedDate引起的。
本例中事件探查器的锁进程跟踪报告显示是由存储过程引起的,你不能查看存储过程里引起锁的实际语句。
然而你可以用stmtstart和stmtend找到过程NewCustOrder里引起阻塞的语句。
使用上面的报告,你能够通过提供sqlhandle、stmtstart和stmtend得到存储过程NewCustOrder的阻塞语句,如下:
declare@sql_handlevarbinary(64),@stmtstartint,@stmtendint
Select@sql_handle=0x3000050005d9f67ea8425301059700000100000000000000Select@stmtstart=920,@stmtend=1064selectsubstring(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) as"blockedstatement" ,s.statement_start_offset ,s.statement_end_offset ,batch=qt.text ,qt.dbid ,qt.objectid ,s.execution_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
你能使用下面的存储过程实时的捕获在存储过程里实际引起锁的语句:
createprocsp_block_info
as
selectt1.resource_typeas[locktype] ,db_name(resource_database_id)as[database] ,t1.resource_associated_entity_idas[blkobject] ,t1.request_modeas[lockreq] ---lockrequested ,t1.request_session_idas[waitersid] ---spidofwaiter ,t2.wait_duration_msas[waittime] ,(selecttextfromsys.dm_exec_requestsasr ---getsqlforwaiter crossapplysys.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=-1 thenlen(convert(nvarchar(max),qt.text))*2 elser.statement_end_offsetend-r.statement_start_offset)/2) fromsys.dm_exec_requestsasr crossapplysys.dm_exec_sql_text(r.sql_handle)asqt wherer.session_id=t1.request_session_id)aswaiter_stmt ---statementblocked ,t2.blocking_session_idas[blockersid] --spidofblocker ,(selecttextfromsys.sysprocessesasp ---getsqlforblocker crossapplysys.dm_exec_sql_text(p.sql_handle) wherep.spid=t2.blocking_session_id)asblocker_stmt from sys.dm_tran_locksast1, sys.dm_os_waiting_tasksast2where t1.lock_owner_address=t2.resource_addressgoexecsp_block_info
(5)更多的索引好还是更少的索引好?
记住索引既有维护的成本又可提高读的性能,所有索引的成本和性能可以通过比较读和写而得到。
读索引可以避免表扫描,然而索引的维护以保持最新需要成本。
可以很容易的找到索引是否使用还是很少使用。
在最后的这个分析中,索引的成本性能是比较主观的问题。
原因在于大量的读与写较高的依赖于工作量和频率。
另外,超过大量读写的定性因素是要考虑的第二个因素:
包括重要程度很高的每月一次的管理报告或每季的VP报告。
insert会执行所有索引的写操作,但是没有无关联的读(除非有引用约束)。
除了select外,update和delete都会执行读操作,限定行的写操作也会执行。
OLTP存在大量的小事务,还有频繁的查询、插入、更新、删除操作。
数据仓库典型地包括高度集中的批量写和在线的读。
SQL语句 读操作 写操作
select Yes No insert No Yes(所有的索引)
update Yes Yes(特定行)
delete Yes Yes(特定行)
一般说来,在一个高事务的OLTP环境下,保持索引的功能最小化,因为高事务伴随索引维护的成本和潜在的阻塞。
相反,对于数据仓库而言,在更新时执行的批处理仅维护一次索引,这样对于在线用户来说有更多的索引比较好。
总而言之,SQLServer2005的一个重要的新特点包括动态管理视图(DMVs)。
DMVs可用于诊断、内存和过程调优、监控(SQLServer2000不支持)。
DMVs在回答诸如索引使用、索引的成本性能、索引争夺等实际问题时是有帮助的。
最后使用select语句可以查询DMVs,但是这些信息不会长期驻留在磁盘上。
这样它们反映了最后一个SQLServer周期服务器状态信息的变化。
还有它提供了一些新的特性,比如文件流支持、T-SQL改进(TOP子句等)、数据库镜像、透明客户端重定向、新的居于架框安全模式、内建HTTP服务器等。
拒绝代溝
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 怎样 评估 管理 索引