SQL Server Performance

Transaction Isolation Level

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by atulgoswami, Jan 13, 2011.

  1. atulgoswami New Member

    Hi,
    I just came across one scenario,
    In a stored procedure, where transaction isolation level is set to READ UNCOMMITTED at the beginning of the SP. and then internally this SP calls another SP. In the Child SP, Transaction Isolation level is set to SNAPSHOT at beginning and at the end it set back to READ UNCOMMITTED.
    Now, I am thinking if transaction isolation level is set in parent SP then would it be changed if changed to some other isolation level in child SP?
    In my case, for child SP, would it run under SNAPSHOT or would it continue to use READ UNCOMMITTED?
    and another thing...Would there be any impact on query performance as it is changed during the course of execution?
    Thanks
  2. satya Moderator

  3. atulgoswami New Member

    Thanks Satya and i saw the comments in old post and was helpful..However, the earlier post for some different scenario. This time, i was cusrious to know how transaction isolation level works for parent/child SP. I was under impression that if it is set in parent SP then child SP will also run in the same isolation level even it is changed in child SP but i think every SP runs in its own isolation level even it is called from within SP.
    Thanks
  4. satya Moderator

    True in some sense and it depends on default isolation levels too, from SQL 2005 onwards you can control this in the beginning of stored procedure to take desired affect.
  5. SQL2K8Dude New Member

    We have an application which has hundreds of thousands of Chat Messaging users who are sending millions of SMS messages to each other on a daily basis. This application is a classic faux multi-user application where each record in the Messages table should theoretically not need locking of any kind since a stored message is read-only by the intended recipient(s). In this environment would response times be improved and disk cache requirements be minimized or helped substantially by the presence of the TRANSACTION ISOLATION LEVEL READ UNCOMMITTED setting or not? What other tuning settings could be made in order to improve overall performance, reduce memory consumption and minimize CPU consumption within this type of environment under these constraints? In rare cases, the use of a contact list could cause 2 or more users to try to read the same message simultaneously.

Share This Page