SQL Server Performance

Which process is blocking

Discussion in 'T-SQL Performance Tuning for Developers' started by devteam, Feb 24, 2006.

  1. devteam New Member

    Hi,

    i am using sql server 2000. while running my application, sometimes the row is locked..

    i have given my locking status result.. can u tell me which process is blocking other process... what is the reason...

    Result
    spid --> 52 ( Blocked by 57) --> dbcc inputbuffer(52) --> select error_key,error_parent_key from errors where error_parent_key = 24478

    spid-->57 (Blocking) --> dbcc inputbuffer(57) --> select property_value from application_property where system_code='DEFCRTNAM' and relation_key=21234

    spid-->65 (Blocked by 52) --> dbcc inputbuffer(65) -->sp_prepexec;1

    spid-->70 (Blocked by 52) --> dbcc inputbuffer(70) -->sp_prepexec;1

    thanks


  2. ghemant Moderator

    Hi,
    process id 57 is causing blocking, i suggest to read related topics in Forum,and articles
    http://vyaskn.tripod.com/anti_blocking_strategies.htm

    http://www.sql-server-performance.com/blocking.asp

    http://www.sql-server-performance.com/reducing_locks.asp

    http://www.sql-server-performance.com/sf_block_prevention.asp

    http://www.sql-server-performance.com/q&a26.asp




    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  3. devteam New Member


    Hi,

    why the lock is placed in the first place? why the other transactions are not executed.
    In the previous reply process id 57 is blocking and executed query is
    select property_value from application_property where system_code='DEFCRTNAM' and relation_key=21234

    how the select statement performs blocking?



    quote:Originally posted by ghemant

    Hi,
    process id 57 is causing blocking, i suggest to read related topics in Forum,and articles
    http://vyaskn.tripod.com/anti_blocking_strategies.htm

    http://www.sql-server-performance.com/blocking.asp

    http://www.sql-server-performance.com/reducing_locks.asp

    http://www.sql-server-performance.com/sf_block_prevention.asp

    http://www.sql-server-performance.com/q&a26.asp




    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

  4. Madhivanan Moderator

    How many rows are in that table?

    Madhivanan

    Failing to plan is Planning to fail
  5. devteam New Member

    Hi,

    In that blocking process executed query is
    select property_value from application_property where system_code='DEFCRTNAM' and relation_key=21234

    there are 1836 rows are available. is this because of any problem?



    quote:Originally posted by Madhivanan

    How many rows are in that table?

    Madhivanan

    Failing to plan is Planning to fail
  6. ghemant Moderator

    Hi,
    read "eadlocks, overview" in BOL for more about DEADLOCK.

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

Share This Page