SQL Server Performance

How to find a frequently executed sql that causes serious blocking issues?

Discussion in 'SQL Server 2005 General DBA Questions' started by Jelly0228, Jul 22, 2010.

  1. Jelly0228 New Member

    Recently there are serious blocking issues occured in one of our product databases, though I turned ON the 1222&3605 trace flags, there are no useful information written into the error log. But I could tell from the "Blk by" field of sp_who2 that there were many sessions blocked. The number of blocked sessions were too much to find the first spid in the blocking chain.
    The problem disappeared without any efforts been made. My guess was: someone used one or more thread in the client application to process data, selecting a few rows from a table for example, after the blocking encountered, he or she stopped the thread secretly. The exection time for a single selection is very short, but the exection interval is too frequently, may 5~7 times per a second. If there are other sessions updating the same table, blocking occurs. Parts of my colleagues like such kind of ways to process data, which really make me have a headache.
    I don't know why such blockings would be recorded in the error log by SQL Server. Does anyone have a suggestion that how to find the problem session?
  2. ashish287 New Member

    hope this helps :-
    SELECT TOP 100
    [Object_Name] = object_name(st.objectid),
    total_cpu_time = total_worker_time / 1000,
    avg_cpu_time = (total_worker_time / execution_count) / 1000,
    min_cpu_time = min_worker_time / 1000,
    max_cpu_time = max_worker_time / 1000,
    last_cpu_time = last_worker_time / 1000,
    total_time_elapsed = total_elapsed_time / 1000 ,
    avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
    min_time_elapsed = min_elapsed_time / 1000,
    max_time_elapsed = max_elapsed_time / 1000,
    avg_physical_reads = total_physical_reads / execution_count,
    avg_logical_reads = total_logical_reads / execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    - qs.statement_start_offset
    ) /2
    ) + 1
    ) as statement_text
    sys.dm_exec_query_stats qs
    sys.dm_exec_sql_text(qs.sql_handle) st
    Object_Name(st.objectid) IS NOT NULL
    AND st.dbid = DB_ID()
    total_worker_time / execution_count DESC
  3. Jelly0228 New Member

    thanks a lot. But from this sub clause--- "total_worker_time / execution_count DESC", it seems it tries to find the top 100 run longest statements, not for find the statements that cause the blocking. Anyway I will have a try.
  4. satya Moderator

    You can join the above statements to get the current execution of statements on that instance, further use the following query (source: MSPSS):
    Blocking.session_id as BlockingSessionId
    , Sess.login_name AS BlockingUser
    , BlockingSQL.text AS BlockingSQL
    , Waits.wait_type WhyBlocked
    , Blocked.session_id AS BlockedSessionId
    , USER_NAME(Blocked.user_id) AS BlockedUser
    , BlockedSQL.text AS BlockedSQL
    , DB_NAME(Blocked.database_id) AS DatabaseName
    FROM sys.dm_exec_connections AS Blocking
    INNER JOIN sys.dm_exec_requests AS Blocked
    ON Blocking.session_id = Blocked.blocking_session_id
    INNER JOIN sys.dm_os_waiting_tasks AS Waits
    ON Blocked.session_id = Waits.session_id
    RIGHT OUTER JOIN sys.dm_exec_sessions Sess
    ON Blocking.session_id = sess.session_id
    CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
    CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
    ORDER BY BlockingSessionId, BlockedSessionId
  5. Luis Martin Moderator

    You can use SQL Spy (free tool) to help with your problem.

Share This Page