High numbers of latches on one index

There is an index in which there is 1M Page Latch Wait Count and 100M Page IO Latch Wait Count. What could this indicate, and what would be appropriate actions to reduce this high number of latches, on this index/table?

Thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> The index is rebuilt everytime the fragmentation is <25%.

Reorganizing index would suffice if the fragmentation is less than 30 or 25 %..
And Index Rebuild is recommended if fragmentation is higher than 30 or 25 %.

Check out "D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes" in the link below:

http://msdn.microsoft.com/en-us/library/ms188917.aspx

>> Auto shrink is always false.

That's good.

>> Bottom line, if the statistics are updates, latches should be minimal, right?

If the statistics are up to date, then latches should be minimal..
Also check whether your Auto-update statistics is set to true or not..
0
 
tigin44Commented:
is this a composit index?
0
 
anushahannaAuthor Commented:
tigin44,
It is Unique Clustered PK.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anushahannaAuthor Commented:
just one column.
0
 
anushahannaAuthor Commented:
also, apart from the above table/index, there is also latches all over system tables (under user databases)- is this something to be concerned about?

for example:
spt_fallback_db
spt_fallback_usg
sysallocunits
sysclsobjs
syscolpars
sysdbfiles
sysfiles1
sysguidrefs
syshobtcolumns
syshobts
sysidxstats
sysiscols
sysmultiobjrefs
sysnsobjs
sysobjkeycrypts
sysobjvalues
sysowners
sysprivs
sysqnames
sysrowsetcolumns
sysrowsets
sysscalartypes
sysschobjs
sysserefs
syssingleobjrefs
syssqlguides
sysxprops
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
What about the index statistics on those objects..
Check whether index defragmentation is high on those indexes..

Also check whether Auto shrink database is enabled on that database and if so kindly set it to False..
0
 
anushahannaAuthor Commented:
Jegan
The index is rebuilt everytime the fragmentation is <25%. (That also does the statistics). Would you recommend a better way for the statistics, or the existing setup ok.

Auto shrink is always false.

Bottom line, if the statistics are updates, latches should be minimal, right?

thanks
0
 
anushahannaAuthor Commented:
Thanks for your helpful post.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.