asked
Indexes preventing blocking issues
I came across an interesting nugget in an article and wondered if it is true. You can't trust everything on the net can you? It would appear that indexes can help prevent blocking issues. To wit "To resolve blocking by having a database reader work on the pages of a nonclustered index, while a database writer modifies other columns (not included in the nonclustered index) in the data page. In this case, the writer working on the data page won't block a reader that can get all the required column values from the nonclustered index without hitting the base table." quoted from
http://sql-plsql.blogspot.com/2010/06/non-clustered-indexes.html Have I been missing a trick all these years? As ever I rely on your collective wisdom to guide me on the path. Sincerely, (H)Alf
and that quote is quoted from the book "SQL Server 2008 Query Performance Tuning Distilled" by our very own @Grant Fritchey
I'd probably add a couple of clarifying comments to that statement, but it's essentially true. I'm on vacation at the moment or I'd look up what I have in the new version of the book coming out in September.
Once again I live up to my name! Thanks for answering that one. I had a quick look at the book and I have to admit that my inner geek really wants to read it. Cheers, (H)Alf
Be warned: this doesn't mean you have to add indexes to solve locking issues. Every index added increases your I/O, because every update to the table also updates the relating index.
16 People are following this question.
