Index reduces blocking

We know that indexes improve query performance by SEEKING the records instead of table SCAN. Because in case of index seek maximum number of comparisons is equal to the number of index page levels while in scanning its equal to the total number of records in table. But reduced number of comparison is not the only reason for improved performance. There is another reason because of that not only the performance of query improves but blocking is also reduces. Let us understand the behavior of table scanning and index seeking with following scenario.

Suppose a table has 8 records with id 1 to 8. Table does not have any index on ID column. We want to update record of ID 6. Record of id 3 is already locked by another process. Here our query to update record ID 6 should complete without waiting for the release of record ID 3. But actually our query would waits for the release of record ID 3. Because table is scanned and scanning block if any of record is locked.

Now let us see the change if table has an index in ID column. Now the record ID 6 would be searched through index level traversal. So the record ID 3 would not come in the way and query would complete without waiting for the release of record ID 3.


Let us test it. In SSMS execute the below batch:

 

CREATE TABLE dbo.TestDeadlock(ID INT IDENTITY, Name VARCHAR(25))

GO

INSERT INTO dbo.TestDeadlock VALUES('Ajay')

INSERT INTO dbo.TestDeadlock VALUES('Vijay')

BEGIN TRAN

UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Rachit' WHERE ID = 1

WAITFOR DELAY '00:00:20'

COMMIT TRAN

Now in another query window (to start another session) execute the below query:

UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Mahesh' WHERE ID = 2

Notice that second query does not complete until the first query completes.

Now create an index on ID column as below:

CREATE CLUSTERED INDEX ci_ID ON dbo.TestDeadlock (ID)

Execute the queries in both sessions again. Notice that second query completes instantly.

So this is another reason because of that index improves query performance and reduces blocking.