SQL Server Performance

Open transactions

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

  1. devteam New Member

    Hi,
    I am using sql server 2000. some times when i was executing a simple select query against in the database, the select query is keep on running.

    is this because of row lock ? and transactions are opened before executing a query? what are the situation open tranasctions are occured...

    please explain me what is meant by open tranasction and when it is occured? how to close the transction?

    Thanks
  2. vbkenya New Member

    A few things to check for:

    1. Even simple select queries run against a large/unindexed/fragmented database can run for a very long time.
    2. Open transactions can block other SQL statements depending on the transaction isolation level of that open transaction. By default, there shouldn't be any issue with isolation levels.

    An open transaction? : Basically a transaction that has not executed its COMMIT or ROLLBACK statement (or has not been chosen as a deadlock victim.)

    Closing it? : First run sp_lock and sp_who2 to determine which processes and statements are blocking(if any) your SELECT statement. You can choose to KILL them (because more often than not, you cannot issue a COMMIT/ROLLBACK).






    Nathan H. Omukwenyi
  3. devteam New Member

    Hi,

    Thanks for ur information. onething i did not get it... open transaction has not been chosen as a deadlock victim.. can u explain abt what are situations deadlock can occured?
    if i kill the process then i cannt retrieve the value for the select statement or i did wrong.

    Thanks



    quote:Originally posted by vbkenya

    A few things to check for:

    1. Even simple select queries run against a large/unindexed/fragmented database can run for a very long time.
    2. Open transactions can block other SQL statements depending on the transaction isolation level of that open transaction. By default, there shouldn't be any issue with isolation levels.

    An open transaction? : Basically a transaction that has not executed its COMMIT or ROLLBACK statement (or has not been chosen as a deadlock victim.)

    Closing it? : First run sp_lock and sp_who2 to determine which processes and statements are blocking(if any) your SELECT statement. You can choose to KILL them (because more often than not, you cannot issue a COMMIT/ROLLBACK).






    Nathan H. Omukwenyi

  4. ranjitjain New Member

    run this command in QA to know about transactions in current database

    dbcc opentran
  5. devteam New Member

    Hi,

    i have ran this command .. it show the given below result

    Oldest active transaction:
    SPID (server process ID) : 64
    UID (user ID) : 1
    Name : implicit_transaction
    LSN : (1607:65:1)
    Start time : Feb 24 2006 12:24:06:560PM

    so can u tell me, 64 is the blocking process ?

    and i have used dbcc inputbuffer(64), it displays a query... is this query blocking the other processes?

    i have checked the lock type is LCK_M_S ... why this lock is occured? what are situations they are occured?



    quote:Originally posted by ranjitjain

    run this command in QA to know about transactions in current database

    dbcc opentran
  6. mmarovic Active Member

    1. Simple select query can run forever even without locking or fragmentation. It is easy to write long running query.
    2. Active transaction doesn't mean it blocks anything.
  7. mmarovic Active Member

    To examine locks run sp_lock stored procedure.

Share This Page