SQL Server Performance

Creating self blocking in sql server

Discussion in 'Performance Tuning for DBAs' started by contactjimmys, Jun 12, 2007.

  1. contactjimmys New Member

    I have created a procedure that logs the blocking information on the server to a table.I have tested it by creating a blocking on QA using scripts .I need to check it for self blocking ie same process blocking the same .How I can create a self blocking on sql server using sql server.

    Thanks

    DBA
    SHAMS
  2. thomas New Member

    One spid can't block itself, why do you need this?
  3. contactjimmys New Member


    I need to track the blocks that are happening on the server.I have seen few spids with same number in current activity .It says spid 61 blocking spid 61. How to track this type of blocking .

    Thanks

    DBA
    SHAMS
  4. MohammedU New Member

    quote:Originally posted by thomas

    One spid can't block itself, why do you need this?

    In rare circumustances on multi proc and parallalism enabled servers may cuase self blocking...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. thomas New Member

    Yes I have seen this in 2000 SP3. Maybe try running a very big query which is executed in parallel and see what happens?

    But jimmy, this is not true blocking. It's just the way SQL Server tracks parallel query execution. So if you're concerned about blocking, you can ignore this kind of blocking.
  6. satya Moderator

    You could take help of DMVs and referring the wait types within SQL 2005.
    The list of wait types is available from the sys.dm_os_wait_stats dynamic management view (DMV). So when we talk about Wait types these can occur only at times when they do not affect user activity, such as during initial server startup and shutdown, and are not visible to users.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. MohammedU New Member

    One more thing...SQL SERVER 2000 in SP4, MS introduced new way of troubleshooting...

    The blocked column in the sysprocesses table is populated for latch waits after you install SQL Server 2000 SP4
    http://support.microsoft.com/default.aspx/kb/906344


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. satya Moderator

    I think we need to be clear whether SHAMS need for 2000 or 2005 [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  9. contactjimmys New Member


    Currently iam testing for sql 2000

    DBA
    SHAMS

Share This Page