Posted by Alex Kuznetsov on 07/26/07 02:29

On Jul 23, 9:03 am, "bobdu...@gmail.com" <bobdu...@gmail.com> wrote:
> We can try the clustered index, but i'm NOT convinced why this is
> going to resolve the page lock issue... i've checked the execution
> plan for some of our queries and it looks like it sorts first, then
> does a clustered index scan of the ID. Is this really going to have
> more page locks than if the index was on the date?

If your table is clustered on the criteria you use for purging, then
your historical data and your current data are physically stored on
different pages (if you provide some grace period between current and
historical). Also your purging touches as little pages as possible and
as such runs faster. This does not guarantee you will not have
deadlocks, but it might decrease their probability. Note that index
entries for current and historical data in NCIs will still intertwine,
so you still may get deadlocks.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

[Back to original message]