Random Prefetching

In my last post, I explained the importance of asynchronous I/O  and described how SQL Server uses sequential read ahead to boost the performance of scans.  In this post, I’ll discuss how SQL Server uses random prefetching.  Let’s begin with a simple example of a query plan that performs many random I/Os.  As in my…


Query Processing Presentation

Last week, I had the opportunity to talk to the New England SQL Server Users Group.  I would like to thank the group for inviting me, Adam Machanic for organizing the event, and Red Gate for sponsoring it.  My talk was an introduction to query processing, query execution, and query plans in SQL Server.  I’ve…


Halloween Protection

In a prior post, I introduced the notion that update plans consist of two parts: a read cursor that identifies the rows to be updated and a write cursor that actually performs the updates.  Logically speaking, SQL Server must execute the read cursor and write cursor of an update plan in two separate steps or…


Maintaining Unique Indexes with IGNORE_DUP_KEY

A few months ago, I wrote a post describing how SQL Server maintains unique indexes while avoiding false uniqueness violations.  In this post, I’m going to look at how SQL Server maintains unique indexes that were created with the WITH IGNORE_DUP_KEY clause.  Normally, if we attempt to insert a duplicate key into a unique index,…


Maintaining Unique Indexes

Consider the following schema: CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT)CREATE INDEX TA ON T(A)CREATE UNIQUE INDEX TB ON T(B) INSERT T VALUES (0, 0, 0)INSERT T VALUES (1, 1, 1) Now suppose we run the following update statement: UPDATE T SET A = 1 – A This update statement affects…


Optimized Non-clustered Index Maintenance in Per-Index Plans

In my last post, I showed how SQL Server 2005 only updates non-clustered indexes when the data in the index actually changes.  For my example, I used a simple update statement that results in a per-row or narrow plan.  In this post, I’ll show how this optimization works in a per-index or wide update plan….


Optimized Non-clustered Index Maintenance

Insert, update, and delete plans consist of two parts.  The first part or read cursor identifies the list of rows to be inserted, update, or deleted.  The second part or write cursor performs the actual insert, update, or delete.  Let’s look at a simple example: CREATE TABLE T (PK INT, A INT, B INT, C…



If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.  For example, the following update statement yields the following plan: CREATE TABLE T (A INT)INSERT T VALUES (0)INSERT T VALUES (1)INSERT T VALUES (2) UPDATE…


Read Committed and Updates

Let’s try an experiment.  Begin by creating the following simple schema: create table t1 (a int, b int)create clustered index t1a on t1(a)insert t1 values (1, 1)insert t1 values (2, 2)insert t1 values (3, 3) create table t2 (a int)insert t2 values (9) In session 1, lock the third row of table t1: begin tranupdate…