latch waits

Question

latch waits

Achtang

SSCommitted

Points: 1602
More actions
April 5, 2009 at 7:27 am

#132459

Hi,
Does anybody know what do you mean by latches and latch waits? I am really not getting any idea about any practical scenario? Please guide.
thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Latches are light-weight synchronisation constructs that are designed to protect the physical integrity of a page in a similar way to how locks protect the logical consistency of rows. They're taken any time something wants to modify a page, be it moving the page from disk to memory or vis versa, writing a record onto a page or changing a page's metadata.

Why do you ask?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Achtang SSCommitted Points: 1602 More actions · Answer 2

Thanks on ur reply Gail 🙂

I am reading on performance tuning and i found about latch waits and it says more latch waits can be a bottleneck on your server.

if you know can you explain to me please?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It is possible to have excessive latch waits. Normally they fall into two categories:

1) Contention in TempDB

2) Slow IOs

Contention in TempDB

On SQL 2000 it was very likely, if TempDB was heavily used to get excessive contention on the allocation pages. This manifested as long latch waits on the resource 2:1:3 (Database 2, file 1, page 3) This is the first of the allocation pages in TempDB and lots of temp table usage could result in long waits for exclusive latcheson this page.

The solution was to add additional files to TempDB (files, not filegroups) so that there were a number of files for TempDB equal to the number of processors or a ratio of the number of processors (8 processors, 2, 4 or 8 tempDB files)

The allocation algorithms were greatly improved in SQL 2005 and this contention is less likely to appear. It may, and if it does the solution is the same as for SQL 2000.

Slow IOs

If there are large numbers of PAGEIOLATCH waits, it usually means that the IO subsystem is not keeping up with the requests and it's taking a log time to get pages from disk into memory.

This could be because there are non-optimal queries or indexes and hence more reads are requested than actually necessary for the workload. It may also mean there's latency in the IO subsystem.

Solutions here include tuning queries or optimising the IO subsystem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Achtang SSCommitted Points: 1602 More actions · Answer 4

Wow Gail thanks that's awesome!!! You would be my Guru 🙂

so for example your server is slow and you are finding out the problem. and one of methods is to look for waits and queues.

you use dmv sys.dm_os_wait_stats and in this if you find many threads at PAGEIOLATCH then you can confirm that there is a problem with I/O subsystem and then you investigate more into why I/O contention. but, we can confirm that there is pressure on I/O subsystem? is it?

Sorry, I am learning about Performance Tuning , I want to really have clarity into it, i might ask many more questions i hope you guys dont mind.:-)

thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Achtang (4/5/2009)
you use dmv sys.dm_os_wait_stats and in this if you find many threads at PAGEIOLATCH then you can confirm that there is a problem with I/O subsystem

No.

If you find lots of PAGEIOLATCH waits then it suggests a contention problem with the IO subsystem. You would then examine the disk related perfmon counters and any other disk-level diagnostics to confirm if it is the case.

For info on perfmon, see this - http://sqlinthewild.co.za/index.php/2009/02/14/do-you-know-how-to-use-perfmon/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Achtang SSCommitted Points: 1602 More actions · Answer 6

Thank you Gail.

So, I just want to know hwether i am thinking in the right way:

do you set up Perfmon or SQL Trace? I read ur article while i was searching through this forum and find it very useful but there is says u have already identified that there is some problem with indexes. But what if in the middle of the day u are sitting there as a DBA and users compain server is slow, how do you go about it? You identify the current queries that are running, but in those current queries how you identify that some of these queries are costlier?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Achtang (4/5/2009)
do you set up Perfmon or SQL Trace?

As I said, Perfmon. SQL Trace won't tell you if you have an IO bottleneck. The disk counters within perfmon will do that

I read ur article while i was searching through this forum and find it very useful but there is says u have already identified that there is some problem with indexes.

Actually, the premise of the article was that the app is very slow, users are constantly complaining and I suspect that there's a problem with lack of indexes.

But what if in the middle of the day u are sitting there as a DBA and users compain server is slow, how do you go about it? You identify the current queries that are running, but in those current queries how you identify that some of these queries are costlier?

Check for blocking

Check for excessive waits

Check for something that's running that shouldn't be.

Check for other applications that are running.

Basically, if the slowness is abnormal, check for something abnormal on the server.

If there are constant complaints that it's slow, that's when you haul SQL Trace and start looking for slow queries so that you can optimise them.

The tool you use depends on what you're trying to find.

Looking to see if there are hardware bottlenecks, perfmon or other system monitoring tools

Looking for long running queries, SQL Trace

Looking for blocking or waits, use the DMVs

There's no single tool for the job.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Achtang SSCommitted Points: 1602 More actions · Answer 8

Hi Gail,

Thanks again for your reply.

Have a question: I read having high page life expectancy is good? how to achieve this? with proper indexes or more memory?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Achtang (4/5/2009)
with proper indexes or more memory?

Yes.

It depends where the problem is. If your indexing is not good, look to the indexes. If the indexes are good, add memory.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

maechismo_8514 SSCoach Points: 17510 More actions · Answer 10

Achtang (4/5/2009)
Have a question: I read having high page life expectancy is good? how to achieve this? with proper indexes or more memory?

Should not be lesser than 300s. Yes proper indexing majority of the times its the root cause.