Introduction

This document shows how the presence or absence of indexes will impact the outcome of scenarios where different clients do concurrent access on SQL tables.

Why is this interesting?

Most SQL developers understand how indexes impact the performance of simple queries. The simplest and most direct consequences of indexes have to do with what happens when one client issues a query that has a WHERE clause. The presence or absence of the appropriate index can cause dramatic differences in performance if large table are involved. In one case SQL Server will scan the entire table. In other it will be able to directly access the rows targeted by the query.

There is also a different aspect of indexes which has to do with how concurrent clients will impact each other when accessing the same tables. The presence or absence of indexes will change the way SQL Server parses data from tables. Without indexes not only the operations that a client does will cause more data to be parsed but that client may be locked and have to wait needlessly until another client finishes its own operations. By needlessly I mean that the same results would be achieved if no locking and waiting was imposed. Without indexes, the SQL Server does not have a way of figuring that that is the case and the approach that is taken will cause one client to wait for another.

Read the full article.