Post reply

Collecting statistical information

  • May 7, 2010 at 2:03 pm

    #218661

    Hi,

    I am looking forward to collect statistical information based on wait types.

    Eg. select * from sys.sysprocesses where lastwaittype = 'LOGMGR_QUEUE' and waittime > 10

    If this query returns results, then collect details of SPID using DBCC input buffer and write results to a table.

    Please let me know if any one of you have implemented such a job or stored proc already.

    M&M

  • May 7, 2010 at 2:28 pm

    #1164443

    Since you're on SQL 2005, rather use the 2005 DMVs - sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_sql_text. You can do it as one query. Also, have you looked at sys.dm_os_wait_stats?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 7, 2010 at 3:47 pm

    #1164486

    I tried something like the one below.

    SELECT wt.session_id,wt.wait_duration_ms,wt.wait_type,

    st.text

    FROM sys.dm_os_waiting_tasks wt LEFT JOIN sys.dm_exec_requests er

    ON wt.waiting_task_address = er.task_address

    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

    where wt.wait_type ='LOGMGR_QUEUE'

    But how come I am not getting for the processes belonging to all wait types

    Eg. ASYNC_NETWORK_IO

    M&M

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

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