Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Isolation level and Index Expand / Collapse
Author
Message
Posted Monday, October 06, 2008 9:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:03 PM
Points: 141, Visits: 258
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 ?



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

Thanks,

Sree
Post #581140
Posted Monday, October 06, 2008 9:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 41,559, Visits: 34,481
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 2008, MVP
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

Post #581160
Posted Monday, October 06, 2008 8:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:03 PM
Points: 141, Visits: 258
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
Post #581468
Posted Tuesday, October 07, 2008 12:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 41,559, Visits: 34,481
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 2008, MVP
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

Post #581540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse