<cffunction name="getMostBlockingIndexes" output="No" returntype="any" description="lists indexes with the most contention (blocking)">
<cfargument name="DBName" default="master" type="any">
<cfargument name="DBObjName" default="" type="any">
<cfset var getMostBlockingIndexes = "">
<cfquery name="getMostBlockingIndexes" datasource="yourdsn" username="youruserName" password="yourpasswd">
use #DBName#
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname=i.name, i.index_id
, 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_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
,sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
<cfif Len(Trim(DBObjName))>
and s.object_id = object_id('#DBObjName#')
</cfif>
order by row_lock_wait_count desc
</cfquery>
<cfreturn getMostBlockingIndexes>
</cffunction>
Posted At : Mar 21, 2007 4:48 AM
| Posted By : Ed Tabara
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL
Related Categories: ColdFusion, My Projects, cfSQLMaster, SQL
To list all idexes wit the most blocking in MSSQL 2005 you may use the following code:
| 6540 Views
| 8% / 0% Popularity
Comments