Causes for Latch Wait Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Causes for Latch Wait Time

Author

Topic

MagnumVP
Starting Member

2 Posts

Posted - 2008-01-06 : 01:43:39

I setup a SQL Agent to send me an email when the Average Latch Wait Time is greater than 300ms. Now I receive an email every 15 seconds stating that the current ALWT is 3916ms. That value never changes with the emails. However, the perfmon shows nothing at all (shows zero).

I also have a Buffer cache hit ratio of 2848.00.

These numbers are when there is NOBODY on the DB at all It is just sitting there. When I reboot the server, as soon as SQL starts it starts to send the emails again.

Server: Intel Xeon Quad Core 2.66
RAM: 4GB (with /3GB in the boot.ini)
RAID 1: OS
RAID 1: Data (DB and logs)
CPU Utilization: 0-1%
RAM Utilization: 527MB
OS: Server 2003 R2 With SP2
SQL: 2005 Standard with SP2

How can I determine if the ALWT is really 3916?

I executed 'Select * from sysprocesses where SPID>50 and waittime>0'

Which showed;

BROKER_RECEIVE_WAITFOR
waittime=110640ms
waittype=0x0075
status=suspended
program_name=DatabaseMail90 - Id<3780>

Any assistance is greatly appreciated.

Thank You

Magnum

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-06 : 15:25:23

In this case, I'll check why only uses 500mg memory on the server and find out what the db mail waiting for.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-06 : 15:27:39

BROKER_RECEIVE_WAITFOR is of no concern as far as i know

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

MagnumVP
Starting Member

2 Posts

Posted - 2008-01-07 : 10:26:03

The sqlservr.exe is now up to 818,104KB so it does grow as the DB is used. How do I find out what the DB Mail is waiting for? As far as I know it's nothing.

Could it have anything to do with using the "Average" in "Average Latch Wait Time (ms)". In my mind the average will never be 0 once it rises above it. I want to get the CURRENT latch and lock times.

Thank You

Magnum

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-07 : 22:51:10

Check sql instance memory usage in perfmon, you can find latch and lock related counters there too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources