Blocking

  • Hi All,

    we are using SQL Server 2005 Working with Service broker technology along with replication.

    we are getting timeout errors at application side, after my analysis i came to know that it is with blocking.

    Here SPID 27 (system process :Task manager ) is blocking.

    we are unable to find what is happening with this process (SPID :27).

    we have used DBCC Inputbuffer and Opentran methods but we did nt get any information.

    We have only one clue Opentran is showing :deleting from queue.

    -- pls share if any one having information.

  • Try this

    --########## Common Significant Wait types with BOL explanations ##################--------------

    -- *** Network Related Waits ***

    -- ASYNC_NETWORK_IO Occurs on network writes when the task is blocked behind the network

    -- *** Locking Waits ***

    -- LCK_M_IX Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock

    -- LCK_M_IU Occurs when a task is waiting to acquire an Intent Update (IU) lock

    -- LCK_M_S Occurs when a task is waiting to acquire a Shared lock

    -- *** I/O Related Waits ***

    -- ASYNC_IO_COMPLETION Occurs when a task is waiting for I/Os to finish

    -- IO_COMPLETION Occurs while waiting for I/O operations to complete.

    -- This wait type generally represents non-data page I/Os. Data page I/O completion waits appear

    -- as PAGEIOLATCH_* waits

    -- PAGEIOLATCH_SH Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

    -- The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

    -- PAGEIOLATCH_EX Occurs when a task is waiting on a latch for a buffer that is in an I/O request.

    -- The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.

    -- WRITELOG Occurs while waiting for a log flush to complete.

    -- Common operations that cause log flushes are checkpoints and transaction commits.

    -- PAGELATCH_EX Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.

    -- The latch request is in Exclusive mode.

    -- BACKUPIO Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

    -- *** CPU Related Waits ***

    -- SOS_SCHEDULER_YIELD Occurs when a task voluntarily yields the scheduler for other tasks to execute.

    -- During this wait the task is waiting for its quantum to be renewed.

    -- THREADPOOL Occurs when a task is waiting for a worker to run on.

    -- This can indicate that the maximum worker setting is too low, or that batch executions are taking

    -- unusually long, thus reducing the number of workers available to satisfy other batches.

    -- CX_PACKET Occurs when trying to synchronize the query processor exchange iterator

    -- You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

    WITH Waits AS

    (

    SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

    FROM sys.dm_os_wait_stats

    WHERE wait_type NOT IN

    (

    'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

    ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

    ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

    ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

    ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'

    )

    )

    SELECT

    W1.wait_type,

    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

    FROM Waits AS W1

    INNER JOIN Waits AS W2

    ON W2.rn <= W1.rn

    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

    HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

    --Check IO bottlenecks by PAGEIOLATCH_EX or PAGEIOLATCH_SH

    select *

    from sys.dm_os_wait_stats

    where wait_type like 'PAGEIOLATCH%'

    order by wait_type asc

    select database_id,

    file_id,

    io_stall,

    io_pending_ms_ticks,

    scheduler_address

    from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,

    sys.dm_io_pending_io_requests as iopior

    where iovfs.file_handle = iopior.io_handle

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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