High wait stats?

Question

Post reply

High wait stats?

j.a.c

SSCrazy

Points: 2283
More actions
May 13, 2009 at 12:28 pm

#130858

Hi,

Should I be concerned about these wait stat numbers? How high is to high for the wait_time_ms value?

Running this:

SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN ( 'WRITELOG' , 'LOGBUFFER' )

gives these results:

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms

WRITELOG 144998829 107838531 4875 4279281

LOGBUFFER 5675 15421 1203 187

We are running SQL Server 2005 9.00.3042.00 (X64) Enterprise Edition.

Thanks,

Andy

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ray Laubert SSCrazy Points: 2910 More actions · Answer 1

I am going to assume that you do not have a base line of performance values yet. If that is the case, I would capture this information around the same time each day for several weeks. At that time, export to Excel and create a graph. This will show you over time any trends with your performance values. Take the average of these values as your baseline. Then periodically compare the current values with the baseline.

Over time you will see trends that can indicate upcoming problems and performance issues. As major changes in the system, database, no of users etc occur you will want to update the baseline.

If you already have a baseline and these figures are way off, then you may want to look at what has changes. Increases in users, no of concurrent queries etc. But without a base line and more information, there is no way I could say good or bad. Even the type of hardware IE CPU, # of CPU, Memory etc would be required to tell if these are out of line or not.

Best I could say, if are the users complaining about performance? If not, these are ok 🙂

Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g

j.a.c SSCrazy Points: 2283 More actions · Answer 2

j.a.c

SSCrazy

Points: 2283

May 13, 2009 at 1:32 pm

#994034

Thanks for your reply Ray.

There are no complaints and the system is running well. But seeing numbers that high always get me worried as I have no baseline yet.

Thanks again,

Andy

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

Andy (5/13/2009)

Thanks for your reply Ray.

There are no complaints and the system is running well. But seeing numbers that high always get me worried as I have no baseline yet.

Thanks again,

Andy

You should create a baseline for your servers then. That is the first step in troubleshooting any server. Create a Baseline and get it approved by your management and I am sure your management will love you when you show these graphs with full of colours :-).....

There is a great advantage if you have a Baseline created you can quickly narrow down to the problemetic areas which will save you time and some sleepless nights :(.....

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Those absolute numbers itself don't mean anything until you compare them to total wait time. The longer your server is online, the higher the values will get.

This query will tell you how many percent of the total time your server is waiting on Logwrite and Logbuffer:

SELECT



wait_type,

	wait_time_ms,

	wait_time_ms * 1.00/(SELECT SUM( wait_time_ms)

					FROM sys.dm_os_wait_stats) *100

	AS [%TotalWaittime]

FROM

sys.dm_os_wait_stats

WHERE wait_type IN ( 'WRITELOG' , 'LOGBUFFER' )

[font="Verdana"]Markus Bohse[/font]