Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 946
  • Last Modified:

High latch wait time on SQL 2008 cluster

I'm seeing an issue on my production db cluster. It's a SQL 2008 R2 cluster. It is connected to a Dell Equallogic SAN. The servers in the cluster connect to the SAN via the MS iSCSI initiator.  
I notice the latch wait time is always between 500ms and 3000ms. This is the case even when the server is not processing much. The guide value for this counter from MS is <300ms. I ran a query which showed me most of the waits are on the buffer latch_class.
I have another DB cluster connected to the same SAN, and latch wait time is usually 0, but very occasionally going above this.  I also have a backup environonment, where the same code and databases are present. Here, the latch wait time is always 0 too. The only difference from this environment is that there is no cluster, and it using local storage.
The end sympotoms for end users are that processing time is slow between the app server and the db server.
When I run perfmon, disk queue length looks ok. Any other areas where the bottleneck may be?
0
sherryfitzgroup
Asked:
sherryfitzgroup
  • 3
2 Solutions
 
lcohanDatabase AnalystCommented:
0
 
sherryfitzgroupAuthor Commented:
The problem ended up being with the application server that was writing to the db. I narrowed it down by setting up disk, cpu and memory counters on the DB server. I could see that there was no bottleneck anywhere that coincided with the latch waits.
I took a look at the SAN for the app server, and could see that there were a lot of vms on the same link to the SAN, and the response time was quite slow (used free storage response monitor). So I moved the appp vm to a different datastore, and also changed the SAN so that each datastore had a different preferred path/controller.
0
 
sherryfitzgroupAuthor Commented:
Awarding points, as lcohan was the only one to help me.
0
 
sherryfitzgroupAuthor Commented:
I only realised after a month that I hadn't resolved the long latch time by moving the App server.
The actual solution was down to SAN paths. I only had one active path to the SAN from the DB server. I configured SAN multipathing, with Dell Equallogic HIT kit.
Now I have 2 active paths, and my latch time is down to about 150ms.
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now