High wait stats?

  • 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_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms

    WRITELOG144998829 107838531 4875 4279281

    LOGBUFFER5675 15421 1203 187

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

    Thanks,

    Andy

  • 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

  • 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

  • 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 :(.....

  • 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]

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

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