[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

High numbers of latches on one index

Posted on 2009-12-29
9
Medium Priority
?
406 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:anushahanna
  • 5
  • 3
9 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26141046
is this a composit index?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26145469
tigin44,
It is Unique Clustered PK.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 26145472
just one column.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Author Comment

by:anushahanna
ID: 26180965
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26398527
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
 
LVL 6

Author Comment

by:anushahanna
ID: 26518719
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 26521749
>> 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
 
LVL 6

Author Comment

by:anushahanna
ID: 26837223
Thanks for your helpful post.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26839846
Welcome..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based developeā€¦
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question