Isolation level and Index

  • Hi,

    Is there any relationship between isolation level and indexes ?

    How indexes affect the use of isolation level ?

    Is there any performance boost-up obtained during data retrieval using where clause when isloation level is implemented on a table with indexes ?

    :alien: :alien: :alien:

    Can any one explain in detail ? Links and resources would be of great help :hehe:

    Thanks,

    Sree

  • No relationship.

    Isolation levels affect how long locks are kept within a transaction and how isolated changes made by one transaction are from another transaction.

    Indexes speed up data retrieval by giving the query processor a fast way to find rows affected by queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi GilaMonster,

    Thanks for the reply 🙂

    Can u plz go thro the below paragraphs and offer me your valuable guidance/suggestions.

    When i tried to explore isolation levels i found the below stuff.

    The following statements are executed to test READCOMMITTED isolation level

    CREATE TABLE Consultants (

    ConsultantID INT IDENTITY,

    ConsultantName VARCHAR(20),

    Technology VARCHAR(15),

    HireDate DATETIME,

    HourlyRate MONEY)

    ---------------------------------------------------------------------

    -- Fill some data

    ---------------------------------------------------------------------

    INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)

    SELECT 'Jacob', 'SQL Server', '2000-10-03', 120

    INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)

    SELECT 'Smith', 'ASP.NET', '2005-01-01', 140000

    INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)

    SELECT 'Michael', 'C#.NET', '2002-10-30', 80000

    I executed the following statements in Query Window 1

    BEGIN TRAN

    UPDATE c SET

    HourlyRate = 250

    FROM Consultants c

    WHERE ConsultantName = 'Smith'

    When execcuted the following statements from Query Window 2

    select * from Consultants

    the query blocks and does not return. This query blocks with the transaction running on

    the other session. Now move back to Query Window 1 and COMMIT the transaction.

    COMMIT TRAN

    Now switch back again to Query Window 2. You will notice that the query returned the following.

    /*

    ConsultantID ConsultantName Technology HireDate HourlyRate

    ------------ -------------------- --------------- ----------------------- ---------------------

    1 Jacob SQL Server 2000-10-03 00:00:00.000 120.00

    2 Smith ASP.NET 2005-01-01 00:00:00.000 250.00

    3 Michael C#.NET 2002-10-30 00:00:00.000 100.00

    */

    I then created a clustered index on the ConsultantID column.

    ALTER TABLE Consultants

    ADD CONSTRAINT PK_Consultants PRIMARY KEY (ConsultantID)

    Now move to Query Window 1 and update the record of Smith.

    BEGIN TRAN

    UPDATE c SET

    HourlyRate = 250

    FROM Consultants c

    WHERE ConsultantName = 'Smith'

    Now, Move to Query Window 2 and run the following queries.

    SELECT *

    FROM Consultants

    WHERE ConsultantID = 3

    /*

    ConsultantID ConsultantName Technology HireDate HourlyRate

    ------------ -------------------- --------------- ----------------------- ---------------------

    3 Michael C#.NET 2002-10-30 00:00:00.000 100.00

    */

    Now, the thing i want to know is that "is still something

    there to know more about isolation levels in connection with indexes ?"

    N.B: the above code and observations were obtained from the following URL

    http://www.sqlserverandxml.com/2008/08/sql-server-transaction-isolation-level.html

    Thanks and Regards,

    Sree

  • Without indexes SQL has to do table scans and hence will take table-level locks. With appropriate indexes, SQL can take more granular locks and hence the two connections don't block each other.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply