Analysis of Guide Plans for table UK Retail$Item

Gathering Unused Index Information

SQL Server 2005 / 2008 maintains system views which contain information concerning indexes.
As these are views the information is not carried forward from a server restart
The information returned is cumulative so great care must be taken in evaluating the results.
To obtain consistent data and results we must store the information from = the system views in permanent tables for later analysis.=
The presented queries assume no partitioning

1.0 Operational cost on indexes ( assuming no RFI )

Operation	Read	Write
Select	Always	No
Insert	No	Always – every index
Update	Always	Only if row qualifies
Delete	Always	Only if row qualifies

2.0 Index Information<= o:p>

Object	Description<= /span>	Notes=
dbo.sysindexes	This is the SQL 2000 system table taken forward as a view. This table may not be supported going forward.	(Still available in SQL 2008 )
sys.dm_db_index_physica= l_stats	This is actually a function and replaces dbcc showcontig
sys.indexes	2005 system table contains property information only for each inde= x.	( unchanged in SQL 2008 )=
sys.dm_db_index_usage_stats	System view which records the access usage of every index in the database	We can view the number of times this index has been used, the type= of access and the last access date ( unchanged in SQL 2008 )=
sys.dm_db_index_operational_stats	System function which records the operational cost of access to the index	We can view information such as the number of pages, locks, latches and waits.
sys.objects=	Contains information on database objects	( unchanged in SQL 2008 )=

<= /p> sys.dm_db_missing_index_detai= ls	System view which stores information on indexes the optimiser considers are missing.
<= /p> <= /p> sys.dm_db_missing_index_group= _stats	System view which stores usage and access details for the missing indexes similar to sys.dm_db_index_usage_stats	We can also view the improvement that the index is computed to mak= e, this is essentially the same type of output as shown by the Tuning Adviso= r.
sys.dm_db_missing_index_groups	Presumably this view will be of more use in SQL2008
sys.dm_db_missing_index_columns	A system function which returns the columns for a missing index	Requires the index handle as a parameter

3.0 sys.dm_= db_index_usage_stats

h= ttp://msdn2.microsoft.com/en-us/library/ms188755.aspx=

· = The User data columns are of most interest to us <= o:p>

· = Database, Table and Index ID

· = User access counts

· = User access dates

· = System access counts ( e.g. update statistics, ind= ex rebuilds )

· = System access dates

Column name	Data type =	Description
database_id	smallint	ID of the database o= n which the table or view is defined.
object_id	int	ID of the table or v= iew on which the index is defined
index_id	int	ID of the index.
user_seeks	bigint	Number of seeks by u= ser queries.
user_scans	bigint	Number of scans by u= ser queries.
user_lookups	bigint	Number of bookmark l= ookups by user queries.
user_updates	bigint	Number of updates by= user queries.
last_user_seek	datetime	Time of last user se= ek
last_user_scan	datetime	Time of last user sc= an.
last_user_lookup	datetime	Time of last user lo= okup.
last_user_update	datetime	Time of last user up= date.
system_seeks	bigint	Number of seeks by s= ystem queries.
system_scans	bigint	Number of scans by s= ystem queries.
system_lookups	bigint	Number of lookups by= system queries.
system_updates	bigint	Number of updates by= system queries.
last_system_seek	datetime	Time of last system = seek.
last_system_scan	datetime	Time of last system = scan.
last_system_lookup	datetime	Time of last system = lookup.
last_system_update	datetime	Time of last system = update.

4.0 List unused tables/indexes

<= /p>

· = This will largely indicate empty tables as mainten= ance tasks will create entries within sys.dm_db_index_usage_stats where there is data in the table/index

o Update statistics tablename

o Dbcc dbreindex(tablename)

· = I’d normally full qualify queries with three part naming; this is omitted for this document.

Ø= = You should n= ot use this query as the primary basis for removing indexes

-- run in database to analyse

Select object_nam= e(i.object_id) as TableName,isnull(i= .name,'HEAP') as IndexName, i.index_id

from sys.indexes<= /span> i join sys.= objects o on o.object_id =3D i.object_id=

where objectprop= erty(o.object_id,'IsUserTable') =3D 1

and i.inde= x_id NOT IN

(select s.index_id f= rom sys.dm_db_index_usage_stats s

where s.object_id=3Di.object_id and i.index= _id=3Ds.index_= id

and database_id = =3D db_id() )

order by object_name(i.object_id),i.index_i= d;

Sample output

TableName	IndexName	index_id
$ndo$srvproperty	HEAP	0
DBAObjMon	HEAP	0
dtproperties	pk_dtproperties	1
fragreport	PK_fragreport	1
spt_values	spt_valuesclust	1
spt_values	ix2_spt_values_nu_nc	2
tblIndex	HEAP	0

5.0 Query to filter by= user access

<= /p>

· = This sorts by lack of user access

· = This is a count of the times there was a seek, sca= n or lookup on the index/table

· = I’ve discounted updates from my criteria as = an update will occur regardless of the index being used for selects=

· = Essentially any index with zero as a value for see= k, scan and lookup will be unused.

-- run in database to be analysed

select object_nam= e(s.object_id) as TableName,isnull(i= .name,'HEAP') as IndexName,

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end,

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups, user_updates as<= /span> Updates

from sys.dm_db_in= dex_usage_stats s join sys.= indexes i on i.object_id =3D s.object_id=

and i.inde= x_id =3D s.index= _id

where database_id = =3D db_id() and objectprope= rty(s.object_id,'IsUserTable') =3D 1

order by (user_seeks + user_scans + user_lookups ) asc;

<= /p>

Sample ouput

TableName	IndexName	Type	Seeks	Scans	Lookups	Updates
OutgoingMessages	Sender_ID=	NC=	14	0	0	0
OutgoingMessages	PK_OutgoingMessages<= o:p>	CLUS	0	0	14	0
OutgoingMessages	Date_Sent=	NC=	0	14	0	0

· = Remember that these values are cumulative since st= art up so we must snapshot the data on a regular basis to enable proper analysis

· = We must also collect data over a suitable time per= iod based upon the usages of your database.

· = In an environment which runs, say, month end processes, it is vital that we capture the full work life cycle.=

· = This is especially important if the CEO or MD has a favourite report they run once a month and you remove the index(es) which m= ake this run quickly.

6.0 Capture snapshot of index activity

-- Run this in the database to be analysed

Insert into d= bo.Unused_indexes

SELECT getdate(),* FROM <= span style=3D'color:green'>sys.dm_db_index_usage_stats

WHERE database_id = =3D DB_ID()

and (last_= user_seek < conv= ert(char(11),getdate()-1) or last_u= ser_seek is null)

and (last_user_scan < convert(= char(11),getdate()-1) or last_user_scan = is null);

· = I would schedule this to run every night just after midnight

· = I’m collecting information where an index has not been used in the preceding 24 hours for a user seek or user scan. ( see Working with snapshots for explanation of this )

· = The collection date is added for clarity.

· = A primary key could be defined on the columns TheDate,database_id,object_id,index_id

· = The table is a Heap

6.1 Table to capture i= ndex activity

CREATE TABLE = dbo.Unused_Indexes

(

= ; TheDate datetime NOT= NULL,

= ; database_id smallint NOT= NULL,

= ; [object_id] int NOT NULL,

= ; index_id int NOT NULL,

= ; user_seeks bigint NOT NULL,

= ; user_scans bigint NOT NULL,

= ; user_lookups bigint NOT NULL,

= ; user_updates bigint NOT NULL,

= ; last_user_seek datetime NUL= L,

= ; last_user_scan datetime NUL= L,

= ; last_user_lookup datetime NUL= L,

= ; last_user_update datetime NUL= L,

= ; system_seeks bigint NOT NULL,

= ; system_scans bigint NOT NULL,

= ; system_lookups bigint NOT NULL,

= ; system_updates bigint NOT NULL,

= ; last_system_seek datetime NUL= L,

= ; last_system_scan datetime NUL= L,

= ; last_system_lookup datetime NUL= L,

= ; last_system_update datetime NUL= L

)

7.0 sys.dm_= db_index_operational_stats=

h= ttp://msdn2.microsoft.com/en-us/library/ms174281.aspx=

· = There are groups of data columns which interest us=

o click on the link ( above ) for full table details=

· = This is a very important table which contains a we= alth of performance information

· = We can view waits, page splits, and volume of acce= ss at leaf and non-leaf levels

· = Just like sys.dm_db_index_usa= ge_stats the information stored in this table is cumulative, howev= er unlike sys.dm_db_index_usage_stats the data may not be persisted so i= f we want to use the information in this view we must capture the data and store= it in a user table.

· = The function returns too many columns to li= st them here, see the link, there’s enough data to keep an analyst busy = for many days for a busy database.

8.0 View index usage b= y work - Selects

<= /p>

· = This query shows all indexes, reads to top , write= s to bottom

-- Run this in the database to be analysed

select object_nam= e(s.object_id) as TableName, isnull(i= .name,'HEAP') as IndexName,=

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType

, reads=3Drange_scan_count + singleton_lookup_count

, 'leaf_writes'<= /span>=3Dleaf_insert_count+leaf_update_count+ leaf_delete_count

, 'leaf_page_spl= its' =3D leaf_allocation_count

, 'nonleaf_write= s'=3Dnonleaf_insert_count + nonleaf_update_count + nonleaf_delete_c= ount

, 'nonleaf_page_= splits' =3D nonleaf_allocation_count=

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id =3D s.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

order by rea= ds desc, leaf_= writes, nonleaf_writes;

Sample output=

TableName	IndexName	Index Type	reads	Leaf writes	Leaf page splits	Nonleaf writes	Nonleaf page splits
StoreMovements<= /o:p>	PK_StoreMovements	CLUS	275339681=	7887418	87128	97844	451
DefaultDimension	PK_Default Dimension=	CLUS	30773523<= /span>	1031	18=	18=	0<= /p>
Item	UK_Item	CLUS	24011949<= /span>	279596	2761	3766	9<= /p>
SystemPreaction=	idx2	NC=	1973394	292886	204	408	26=
Actions	idx1	NC=	1084490	1033114	285	3065	1<= /p>
Actions	idx2	NC=	943652	7851301	250	19607	68=
Accessories	PK_Accessories<= /o:p>	CLUS	907502	907514	212	212	3<= /p>

· = Top page splits are for the StoreMovements table

· = High reads for DefaultDimension but low writes and splits probably indicates a table which does not change very much

· = Remember that these figures are ( maybe ) cumlative since server startup.

8.1 This query only sh= ows those indexes which have reads

-- Run this in the database to be analysed

-- This query has been updated since original publicati= on

select object_nam= e(s.object_id) as TableName, isnull(i= .name,'HEAP') as IndexName,=

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType

, reads=3Drange_scan_count + singleton_lookup_count

, 'leaf_writes'<= /span>=3Dleaf_insert_count+leaf_update_count+ leaf_delete_count

, 'leaf_page_spl= its' =3D leaf_allocation_count

, 'nonleaf_write= s'=3Dnonleaf_insert_count + nonleaf_update_count + nonleaf_delete_c= ount

, 'nonleaf_page_= splits' =3D nonleaf_allocation_count=

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id =3D s.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

and (range= _scan_count + singleton_lookup_count)>0

and (leaf_= insert_count+leaf_update_count= + leaf_delete_count+nonleaf_insert_count = + nonleaf_update_count + nonleaf_delete_count)=3D0

order by reads desc, leaf_writes, nonleaf_writes;

Sample output=

TableName	IndexName	Index Type	reads	Leaf writes	Leaf page splits	Nonleaf writes	Nonleaf page splits
Object	PK_Object=	CLUS	1889069	0<= /p>	0<= /p>	0<= /p>	0<= /p>
JnlAllocation	UK_JnlAllocation	CLUS	1349098	0<= /p>	0<= /p>	0<= /p>	0<= /p>
AccountingPeriod	PK_AccountingPeriod<= o:p>	CLUS	561972	0<= /p>	0<= /p>	0<= /p>	0<= /p>
dbproperty	HEAP	HEAP	206860	0<= /p>	0<= /p>	0<= /p>	0<= /p>
User	PK_User	CLUS	205741	0<= /p>	0<= /p>	0<= /p>	0<= /p>
Object	Idx_1	NC=	195688	0<= /p>	0<= /p>	0<= /p>	0<= /p>

· Tables/index= es without writes may be candidates for read only filegroups=

= ; &n= bsp; = ; &n= bsp; = ; &n= bsp; = ; &n= bsp; = ;

8.2 Indexes without re= ads but with high writes may be being maintained unnecessarily

-- Run this in the database to be analysed

select object_nam= e(s.object_id) as TableName, isnull(i= .name,'HEAP') as IndexName,=

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType

, reads=3Drange_scan_count + singleton_lookup_count

, 'leaf_writes'<= /span>=3Dleaf_insert_count+leaf_update_count+ leaf_delete_count

, 'leaf_page_spl= its' =3D leaf_allocation_count

, 'nonleaf_write= s'=3Dnonleaf_insert_count + nonleaf_update_count + nonleaf_delete_c= ount

, 'nonleaf_page_= splits' =3D nonleaf_allocation_count=

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id =3D s.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

and (range= _scan_count + singleton_lookup_count)=3D0

and (leaf_= update_count+ leaf_delete_count+ nonleaf_update_count + nonleaf_delete_c= ount)>0

order by leaf_writes desc, nonleaf_writes d= esc;

Sample output

TableName	IndexName	Index Type<= /p>	reads	Leaf writes=	Leaf page splits	Nonleaf writes<= /b>	Nonleaf page splits
StoreMovements	idx_9	NC	0	208269892	12415	1031410	5931
OrderingLines	idx_2	NC	0	67615494	873	195135	694
RetailAction	idx_1	NC	0	9464172	176	14118	42
InfocodeEntry	idx_3	NC	0	6666664	1372	54032	568
InfocodeEntry	idx_7	NC	0	6618063	826	39885	270
RetailTransaction	idx_5	NC	0	987210	10906	13190	146

· = This is a good indication of unused secondary inde= xes

9.0 Order by writes to= find your most heavily updated index

<= /p>

· = The inserts have been separated as we cannot avoid this io

· = Indexes without writes are excluded

· = Secondary indexes appearing high on the list may benefit from being placed on a separate filegroup

· = High levels of leaf writes on a HEAP may indicate = the possibility of fragmentation which cannot be removed

-- Run this in the database to be analysed

select object_nam= e(s.object_id) as TableName, isnull(i= .name,'HEAP') as IndexName,=

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType

,'total_writes'=3Dleaf_update_count+ leaf_delete_count+nonleaf_update_count = + nonleaf_delete_count

,'total_insert_writes'=3Dleaf_insert_count+nonleaf_insert_count

, 'leaf_writes'<= /span>=3Dleaf_update_count+ leaf_delete_count

, 'nonleaf_write= s'=3Dnonleaf_update_count + nonleaf_delete_count

, 'insert_leaf_w= rites'=3Dleaf_insert_count

, 'insert_nonlea= f_writes'=3Dnonleaf_insert_count

, 'leaf_page_spl= its' =3D leaf_allocation_count

, 'nonleaf_page_= splits' =3D nonleaf_allocation_count=

, reads=3Drange_scan_count + singleton_lookup_count

from sys.dm_db_index_operational_stats (db_id(),NULL,NULL,NULL) s join sys.indexes i

on i.object_id =3D s.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

and (leaf_= update_count+ leaf_delete_count+nonleaf_update_count + nonleaf_delete_count+leaf_insert_count+nonleaf= _insert_count)>0

order by total_writes desc,total_insert_writes desc;

Sample output<= /span>

TableName=	IndexName=	Index Type	Total writes	Total insert writes<= o:p>	Leaf writes	Nonleaf writes<= /o:p>	Insert leaf writes	Insert nonleaf write= s	Leaf page splits	Nonleaf page splits<= o:p>	reads
Transaction	PK_Transaction	CLUS	4574565	108649228=	4573740	825	101322359=	7326869	204092	31691	17503128<= /span>
StoreMovements	UK_StoreMovements	CLUS	4142934	4187560	4132202	10732	4093101	94459	94459	493	310032497=
MoveToBase	PK_MoveToBase	CLUS	3790353	35743	3790353	0<= /p>	31244	4499	4499	94=	3821451
MoveFromBase	PK_MoveFromBase	CLUS	3412263	24757	3412098	165	24218	539	704	29=	3436101
Actions	Idx_1	NC=	943651	116651	940871	2780	116296	355	355	1<= /p>	1120269
Actions	Idx_2	NC=	943650	6951381	941298	2352	6934056	17325	320	68=	943652

· = Refer to the table description within msdn to interpret the results from your own data

· = High counts of splits may indicate where a fill fa= ctor adjustment may be of use.

· = In SQL 2000 it was almost impossible to tie in page split perfmon counter to actual objects, now you can.

10.0 Simple query for = user access reads and writes

-- Run this in the database to be analysed

-- This query has been updated since original publicati= on

select object_nam= e(s.object_id) as TableName, isnull(i= .name,'HEAP') as IndexName,

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType

,reads=3Duser_seeks + user_scans + user_lookups

,writes =3D user_updates

from sys.dm_db_in= dex_usage_stats s join sys.= indexes i

on s.object_id =3D i.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

and s.data= base_id =3D db_id= ()

order by (user_seeks + user_scans + user_lookups+user_updates) desc;

Sample output

TableName	IndexName	Index Type<= /p>	reads	writes
DefaultDimension	PK_DefaultDimension	CLUS	112242160	113093516
LocalStores	PK_LocalStores	CLUS	80399895	96585318
Movements	PK_Movements	CLUS	74547363	75067450
StoreItem	PK_StoreItem	CLUS	127471081	4160<= /span>
UserDetails	PK_UserDetails	CLUS	61368230	61416593
ItemLookup	PK_ItemLookup	CLUS	76185253	45033431
OpenDates	UK_OpenDates	CLUS	55227530<= o:p>	55227530<= o:p>

In much the same way as ::fn_virtualfilestats or sys_dm_io_virtual_file_s= tats return information concerning file activity sys.dm_db_index_operat= ional_stats allows us to view simil= ar information for our indexes

h= ttp://msdn2.microsoft.com/en-us/library/ms190326.aspx=

h= ttp://msdn2.microsoft.com/en-us/library/ms187309.aspx

11.0 Query to display = waits and blocks

· = this query is cumulative so is of questionable val= ue

-- Run this in the database to be analysed

Select object_nam= e(s.object_id) as TableName

,isnull(i.name<= span style=3D'color:gray'>,'HEAP') as IndexN= ame

,case i.index_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as IndexType =

,row_lock_count= , row_lock_wait_count

,[block %]=3Dcast (10= 0.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))

, row_lock_wait_in_ms

, [avg row lock waits in ms]=3Dcast = (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15<= span style=3D'color:gray'>,2))

from sys.dm_db_index_operational_stats (db_id(), NULL, <= span style=3D'color:gray'>NULL, NULL)= s join sys.indexes<= /span> i

on i.object_id =3D s.object_id and i.index= _id =3D s.index= _id

where objectprop= erty(s.object_id,'IsUserTable') =3D 1

order by row_lock_wait_count desc ;

11.1 sample output

Table

Index

Index Type

Row lock count

Row lock wait count

block %

Row lock wait in ms

avg row lock waits in ms<= /p>

Sales

Idx_21

NC=

32679

34=

0.10

104076

2973.60

Employees=

PK_Employees

CLUS

958510

32=

0.00

1055219

31976.33<= /span>

Invoices<= /span>

PK_Invoices

CLUS

100332

27=

0.03

19049

680.32

Returns

Clx_Returns_Date

CLUS

1771122

21=

0.00

168156

7643.45

Areas

PK_Areas<= /span>

CLUS

50762

12=

0.02

937

72.08

·      =     In general terms as this is from start up the valu= es are likely diluted, however we can see that on the Employees table ( none of these tables are real, although the data is ) we have some serious waits wh= en a wait does occur.

·      =     You would want to snapshot the data and compare to extract operational data values.

12.0 Find your worst s= cans

<= /p>
·      =     An index scan may not be an issue so we’ll concentrate on table scans

·      =     A clustered index scan is a table scan so we’= ;ll select on index id 0 and 1

--

-- run in database to be analysed

--

select object_nam= e(s.object_id) as TableName,isnull(i= .name,'HEAP') as IndexName,

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType,

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups

from sys.dm_db_in= dex_usage_stats s join sys.= indexes i on i.object_id =3D s.object_id=

and i.inde= x_id =3D s.index= _id

where database_id = =3D db_id() and objectprope= rty(s.object_id,'IsUserTable') =3D 1

and user_scans>= ;0 and i.index_id<2

order by user_scans desc;

Sample output=

TableName

IndexName

Index Type<= /p>

seeks

scans

lookups

LedgerEntry

PK_LedgerEntry

CLUS

1

5794646

92167=

dbproperty

HEAP

HEAP

0

4039823

0

PromotionalItems

PK_PromotionalItems

CLUS

585372

3412710

2519<= /span>

PromoAction

PK_PromoAction

CLUS

3232308

3069131

228015

DocumentActions

PK_DocumentActions

CLUS

97178=

2309198

28267=

Members

PK_Members

CLUS

2002967

2003253

0

Users

PK_Users

CLUS

2005736

2003116

0

·      =     Lookups typically indicate bookmark lookups

·      =     High scans and no lookups may indicate a lack of appropriate secondary indexes

12.1 Add a calculation= and sort by proportion of scans to seeks

--

-- run in database to be analysed

--

select object_nam= e(s.object_id) as TableName,isnull(i= .name,'HEAP') as IndexName,

case i.inde= x_id

when 0 then= 'HEAP'

when 1 then= 'CLUS'

else 'NC'

end as Ind= exType,

user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups

,cast((user_scans*1.0/(user_seeks+user_scans))*100 as <= span style=3D'color:blue'>numeric(5,2)) as '%age'

from sys.dm_db_in= dex_usage_stats s join sys.= indexes i on i.object_id =3D s.object_id=

and i.inde= x_id =3D s.index= _id

where database_id = =3D db_id() and objectprope= rty(s.object_id,'IsUserTable') =3D 1

and user_scans>= ;0 and i.index_id<2

order by '%age' desc;

Sample output

Table Name<= /p>

Index Name<= /p>

Index Type<= /p>

Seeks

Scans

Lookups

%age

ShippingServices

PK_ShippingServices

CLUS

0

1031<= /span>

0

100.00

HREmployees

HEAP

HEAP

0

80321=

0

100.00

AccountingPeriod

PK_ AccountingPeriod

CLUS

0

57

0

100.00

PrintGroup

PK_ PrintGroup

CLUS

0

139

0

100.00

VendorEntry

PK_ VendorEntry

CLUS

598

74621=

7724155

99.20=

SystemAction

PK_ SystemAction

CLUS

126

14941=

0

99.16=

MacroLine

UK_ MacroLine

CLUS

553

63497=

0

99.14=

Allocation

PK_ Allocation

CLUS

2333<= /span>

172550

0

98.67=

ReportList

PK_ ReportList

CLUS

13486=

309580

0

95.83=

ReportSelections

PK_ ReportSelections

CLUS

2739<= /span>

62509=

0

95.80=

AnalysisView

PK_ AnalysisView

CLUS

Table	Index	Index Type	Row lock count	Row lock wait count	block %	Row lock wait in ms	avg row lock waits in ms<= /p>
Sales	Idx_21	NC=	32679	34=	0.10	104076	2973.60
Employees=	PK_Employees	CLUS	958510	32=	0.00	1055219	31976.33<= /span>
Invoices<= /span>	PK_Invoices	CLUS	100332	27=	0.03	19049	680.32
Returns	Clx_Returns_Date	CLUS	1771122	21=	0.00	168156	7643.45
Areas	PK_Areas<= /span>	CLUS	50762	12=	0.02	937	72.08

Table Name<= /p>	Index Name<= /p>	Index Type<= /p>	Seeks	Scans	Lookups	%age
ShippingServices	PK_ShippingServices	CLUS	0	1031<= /span>	0	100.00
HREmployees	HEAP	HEAP	0	80321=	0	100.00
AccountingPeriod	PK_ AccountingPeriod	CLUS	0	57	0	100.00
PrintGroup	PK_ PrintGroup	CLUS	0	139	0	100.00
VendorEntry	PK_ VendorEntry	CLUS	598	74621=	7724155	99.20=
SystemAction	PK_ SystemAction	CLUS	126	14941=	0	99.16=
MacroLine	UK_ MacroLine	CLUS	553	63497=	0	99.14=
Allocation	PK_ Allocation	CLUS	2333<= /span>	172550	0	98.67=
ReportList	PK_ ReportList	CLUS	13486=	309580	0	95.83=
ReportSelections	PK_ ReportSelections	CLUS	2739<= /span>	62509=	0	95.80=
AnalysisView	PK_ AnalysisView	CLUS