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_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

• Ray Laubert

  SSCrazy

  Points: 2910

  More actions

  May 13, 2009 at 1:04 pm

  #994008

  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

  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

  May 14, 2009 at 6:07 am

  #994401

  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

  May 14, 2009 at 8:16 am

  #994558

  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]