Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

High latch wait time on SQL 2008 cluster

Posted on 2012-03-28
4
Medium Priority
?
914 Views
Last Modified: 2016-11-23
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
Comment
Question by:sherryfitzgroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1500 total points
ID: 37778062
0
 
LVL 2

Accepted Solution

by:
sherryfitzgroup earned 0 total points
ID: 37805008
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
 
LVL 2

Author Closing Comment

by:sherryfitzgroup
ID: 37820960
Awarding points, as lcohan was the only one to help me.
0
 
LVL 2

Author Comment

by:sherryfitzgroup
ID: 37968931
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

636 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