SQL Server Performance

Weird Values of Waittypes

Discussion in 'Performance Tuning for DBAs' started by rlahoty, Feb 17, 2005.

  1. rlahoty New Member

    HI guys

    When catching waittime for waittypes from sysprocesses, sometimes I see values as high as "2005188812" for WRITELOG and some of these kinds of enormous values for other waittypes. They don't look right to me.

    What does that might mean? Any pointers would help.

    Thanks
    -Rajeev Lahoty
  2. satya Moderator

    This means waiting for write requests to the transaction log to complete
    Refer tohttp://sqldev.net/misc/waittypes.htm for brief explanation of wait types.
    Run DBCC SQLPERF(waitstats) for results.

    Heavy rollback activity adds additional log and disk I/O contention, and such activity has been observed as contributing to writelog timeouts. Refer to the event viewer for any errors/information on hardware and use PERFMON to collect few counters for further assessment.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. rlahoty New Member

    I haven't seen any write log timeout. I have run DBCC SQLPERF('waitstats') but what does that have to analyzing this high value of waittime for writelog waittype. This is an enormous number. I dont' think it is right by any chance. It would mean that it took almost 5500 hours to write to log. I thought someone else might have noticed these high numbers.

    This also occurs for CPU column of sysprocesses. And by no means within a span of 2 minutes, a SPID can take so much CPU. There is something happening in the background that I dont' know. Thats it. Not that it is causing some problem, just wondering if someone else had any idea.

    Thanks
    -Rajeev Lahoty
  4. joechang New Member

    presumable the waittypes are stored as 32-bit integers allowing a range on -2B to + 2B,
    since the units are milli-sec, is not difficult to acculumate 2B ms or 2M sec of wait time, especially if there are multiple SPIDs acculumate waittime concurrently .
    you can always reset the wait time counters, before taking a measurement,
    other wise, take the difference between two measurements
  5. rlahoty New Member

    Thats exactly I did. I did take the difference for CPU for a particular spid after 10 seconds. And this high number came up. Waittimes I can clear but not the cumulative CPU till that connection closes. And I am not talking of multiple SPIDs.

    I simply ran this:

    SELECT spid, loginame, lastwaittype, waitresource, waittime, GETDATE()
    FROM master.dbo.sysprocesses
    WHERE waittime > 0
    AND spid > 50
    AND lastwaittype NOT LIKE '%waitfor%'
    ORDER BY waittime DESC

    And thats where I saw that. So the number was for a particular spid. And this particular SPID never appeared before the time when I ran this query. I run this query every second to find out what is waiting on what.
  6. satya Moderator

    Heavy rollback activity adds additional log and disk I/O contention, and such activity has been observed as contributing to writelog timeouts. Refer to the event viewer for any errors/information on hardware and use PERFMON to collect few counters for further assessment.
    ... any information on the above lead.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page