http://www.mssqltips.com/tip.asp?tip=1485

Just wondering if there was any reason that the table hint WITH(NOLOCK) could not have been used ? That way you will use existing indexes and can retrieve all of the data, and not need to create another index. I try to only create indexes where a performance gain is required, especially if it is a busy table.

Using the with(nolock) hint means you can/will get dirty reads that are not transactionally consistent. The other common warning against it is the query may error out due to data movement if the page your query is processing moves. I always use with nolock hint when running big / long running reports that don't need to be 100% accurate.

Indexes certainly increase overhead of dml so like everything in sql the decision of whether or not to add one is 'it depends'. If it is a busy table with lots of reads then certainly it can increase query throughput.

I think the article fails to bring up the important point that the update *will* take a lock on the index if the update changes any of the columns that the index covers. Here is an example that demonstrates this:

create table t1 (id int, lastAgg int, pending int, currentAgg as lastAgg+pending)
insert t1 values (1, 20, 2)
insert t1 values (2, 30, 3)
insert t1 values (3, 40, 4)
go
--create clustered index t1_cidx_id_agg on t1(id)
--go
create unique nonclustered index t1_idx_id_lastAgg on t1(id) include (lastAgg)
go
/*in session 1 update a column not in the covering index*/
begin tran
update t1 set pending = 1 where id = 2;

/*in session 2 following, using index hint since table and data is compact so query plan may otherwise use the clustered index and invalidate the test*/
select lastAgg from t1 with (index = t1_idx_id_lastAgg) where id = 2
/*result: not blocked by session 1*/
go

/*rollback or commit the previous session before starting the next test*/
/*in session 1 update the column that is covered by the index*/
begin tran
    update t1 set lastAgg = 21 where id = 2;
/*in session 2 following, using index hint since table and data is compact so query plan may otherwise use the clustered index and invalidate the test*/
select lastAgg from t1 with (index = t1_idx_id_lastAgg) where id = 2
/*result: session 2 is blocked by session 1*/
go

This table/index pattern can be used to implement a deferred update aggregation table. All of the readers that can afford a time lag query the lastAgg column. All readers that require up to date info query the currentAgg column and take the hit that they will be blocked by concurrent writers. All of the updates write to the pending column. A scheduled task or other background process occassionally goes through the table and does: set lastAgg=lastAgg+pending, pending=0, dirty=0.

Side note 1: Note that it does not matter if the base table has a clustered index or is a heap. 

Side note 2: I don't know if this always holds true, but I observe that if your query does an update/set on a column that is covered by the index but does *not* actually change the value (i.e. set to value 3 and current value is already 3), then the index is not locked.

I agree with the comment that the data will not be transactionally consistent with WITH(NOLOCK), however as the point of this was to read columns that were not changed then I don't see the reason for using the index. As you quite rightly point out if the data changed is on a column in the index then the index will be locked anyway. Also, the point of the article I thought was to enable you to read rows that are locked for a long period of time, and if this data affects the index you are using then it looks to me that it doesn't answer the problem - getting the data consistently.

 I've not heard of queries erroring on a WITH(NOLOCK) and have not come across it on any of the databases I've written. I guess if the data is highly used, and the query you are executing is very slow then you will get discrepancies (data updated whilst query takes place not sure what that would do), but I would then suggest that you are writing heavy reports against a transactional database so should think about creating a replicated reporting database and remove the load from the data entry.

 I think I must be missing something here, because I can't really see how this will help over WITH(NOLOCK)

I think the point can be summed up as:

IF

Access pattern of column A is lots of DML, and access pattern of column B is lots of reads and few DML

THEN

IF you have a covering index, the reads from column B will experience zero blocking from DML to column A because the DML operation will only lock the base table and not the index. Note that you still have full transactional consistency in your reads -- if there is an infrequent DML to column B you will see any appropriate isolation.

ELSE IF you do not have a covering index, column B will experience significant blocking from DML to column A because the DML operation will lock the whole row of the base table and the reads against column B must also use the base table.

I had a need for this kind of pattern for a project so I made a blog entry with an overview

re WITH(NOLOCK) 605 error : http://support.microsoft.com/default.aspx?scid=kb;en-us;235880

Reading this, I think that this would only occur if the data is being moved due to an update of a clustered index. I tend to make my clustered indexes non-updatable so that may answer why I have not come across this problem.

However, I can see merit in using the covering index from a performance perspective as the server will read the index tree and not look at the table. And this is probably why a covering index is not subject to the row lock. I will certainly incorporate this idea into future designs should adding an index be a viable possibility. I think, in actual fact, I probably do exactly this but didn't realise that the covering index was the reason behind it all.

As a side thought, would using WITH(NOLOCK) and the covering index increase the performance again (no intent locks will be created for a start) and should not suffer from the 605 error also ? Might be worth a look.

Hi,

As mentioed above (by  ahains) using NOLOCK hint affects transaction read consistency i.e. when using it you can never be sure that you are reading correct information; I specially chose quota example since we do not like to see our qouta incorrect (think of you private bank account quota ;)...).

Anyway the point of the article was not to "the point of this was to read columns that were not changed" but rather give an example of how we can bypass a lock created because of a writer even not having update on our "going to be read" data, still blocks it. In the other case if the writer would have updated our "going to be read" data we still want SQL Server to lock it and not let us read it until writer is done; using the NOLOCK hint would not give us this and we may be reading wrong information.

 Hope this helps,

 --Tal (tal.olier@gmail.com)

 mentioned and quota (sorry, was too enthusiastic to complete my answer)

:)

--Tal.

Hello ahains,

First I'd like to thank you for commenting my article it is a great pleasure knowing all this "writing stuff" do make any difference (also read your blog post...).

Second, well, your observation (i.e. "update *will* take a lock on the index if the update changes any of the columns that the index covers") is correct but a bit confusing from the reader POV (I have a case with a given update and cannot change that...), the thing I wanted to show here was that update will "lock" your reading statement even if no data update occurred and to show there is a cure for that (also imply some of us practice this cure by adding covering index without knowing it solves locking issues - I did) .

So when I built the article I noticed it should cover the reader POV i.e. first we have an update, then we have a read that is blocked. So I actually tried embedding your statement in two phrases in my article:
1. When performing a write operation, SQL Server does not lock related indexes (e.g. our Session B did not lock customers_ix1; note it would not have locked it regardless if we used it or not!), only the relevant data row.
2. By adding an index that will cover all columns of the table that are required for Session A's specific query (A.K.A. Covering Index), we will have a solid bypass without requiring specific access to the actual data row.

Regarding the update of a value to the same value (i.e. 3=3), noticed that also and do think it is intentionally done by SQL Server, great idea for a future research :)

hope I covered all points you've raised.

Thanks,

--Tal (tal.olier@gmail.com)