Post reply

Tempdb Contention - sys.dm_os_waiting_tasks

  • January 30, 2011 at 8:03 pm

    #244387

    Hello All,

    I am trying to analyze the tempdb contention in SQL Server 2005. I am running the below script against SQL Server 2005 instance using SSIS package every 10 seconds. I go this information from Microsoft white paper

    -- get the current timestamp

    declare @now datetime

    select @now = getdate()

    -- insert data into a table for later analysis

    insert into analysis..waiting_tasks

    select

    session_id,

    wait_duration_ms,

    resource_description,

    @now

    from sys.dm_os_waiting_tasks

    where wait_type like ‘PAGE%LATCH_%’ and

    resource_description like ‘2:%’

    After running the above query for more than 4 days I noticed that when DBCC CheckDB runs on the server, the value wait_duration_ms column is close to 20 Sec on some servers. Is it a normal thing to happen during the DBCC CheckDb run ?

    There are some sporadic instances during the day time when the value wait_duration_ms column is recorded close to 15 ms. What is the acceptable value of wait_duration_ms column?

    Any help will be greatly appreciated.

    Thanks in advance.

  • January 31, 2011 at 7:48 am

    #1280433

    dbcc checkdb is one of MANY things that started using tempdb in SQL 2005. You REALLY need a good tempdb on SQL systems.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • January 31, 2011 at 7:24 pm

    #1280718

    Hello Kevin,

    Thanks for your reply.

    I understand that DBCC CheckDB uses TempDB heavily. But is it a normal thing to observe the wait duration of 20 ms during the DBCC CheckDB run as I am experiencing?

    Cheers

  • February 1, 2011 at 7:16 am

    #1280915

    It is quite common if/when your tempdb IO subsystem is poor - which 99% of the clients I help have.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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