Re: Ongoing purging of active records causes deadlocks
- From: "bobdurie@xxxxxxxxx" <bobdurie@xxxxxxxxx>
- Date: Tue, 24 Jul 2007 11:41:10 -0000
On Jul 23, 5:59 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
bobdu...@xxxxxxxxx (bobdu...@xxxxxxxxx) writes:
- Sorry again, the type >= is just a way of getting more data, those
queries are typically = based, and theres never more than 20 unique
values for type.
For a query like:
SELECT LoadID, Type, CreateDate, OtherDate, ObjectID
FROM loads
WHERE Type = @P0
ORDER BY loads.LoadID DESC
An index on (Type, LoadID DESC) appears to be a good idea. If the
query is selective as you say, the optimizer would use the index and
bookmark lookups. And with the ID in the index, the result is already
sorted.
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?
The sort is usually performed at the end of the query plan - you should
read it from right to left. Sorts can appear earlier in the plan, if
the optimizer settles for a merge join, which requires sorted input.
I cannot guarantee that the clustered index on CreateDate saves the day,
but I think it will be difficult without it. The way the query is written,
without a parameter, the optimizer will assume a 30% hit-rate which is far
beyond what is good NC-index seek + bookmark lookup. Of course, you could
force the index with an index hint, but I don't think that would be a
good thing.
What is a possible alternative, though, is that the purge first performs:
SELECT @loadid = MAX(LoadID)
FROM loads
WHERE CreateDate < dateadd(SECOND, -40, CURRENT_TIMESTAMP)
And the issues "DELETE loads WHERE LoadID <= @loadid". For this to be
successful, you still need an index on (CreateDate, LoadID DESC).
One final thing that i don't understand at the db level is transaciton
models - using jdbc we're normally using READ_COMMITTED - we could use
SERIALIZABLE but i'm not sure how either of these translates at the db
level. If someone has a pointer to this info with respect to SQL
server (and/or oracle) i'd love to hear it.
READ_COMMITTED means that once you have read data, you release the lock
for it. Thus, if you read the same data twice in the same transaction,
you can get different result.
SERIALIZABLE means that you do not tolerate that anyone adds or changes
rows that could affect data you have read. I don't think serializable
will work here. First of all, things will slow down, because they are
just that: serialized. Second, serializable appears to often lead to
deadlocks, because SQL Server is not very good at always taking the
range locks in the same order, or so it seems.
There are a few more possibilities: On SQL 2005, you can use any of the
two varities of SNAPSHOT isolation. With this isolation level, readers
read from the version store, and cannot block writers. Yes, just like
on Oracle. But watch out! If you issue a SELECT COUNT(*) while the purge
is running, do you want the number of rows before or after the purge?
Snapshot will give you the former, that is old and out-dated data.
Then you can go for READ UNCOMMITTED, and do dirty reads. Then your
SELECT COUNT(*) will give you something between before and after. Finally
there is the READPAST hint which means that you skip locked rows. In
this case two, you may also get something in between before and after,
although it's more likely to be closer to after.
...and a completely different approach is to use partitioned tables
(requires SQL 2005 Enterprise). In that case, you would only know and
then alter your partition scheme and partition function. A purge would
be a pure metadata operation. Switch out the old data, and then drop
that table. In this case, you would purge far less often, but when you
do it, it would be snap.
--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
One other thing that i don't understand - whats with the DESC
indexes? I would understand for queries where you'r doing a DESC
orderby, but what other purpose to they serve?
.
- References:
- Ongoing purging of active records causes deadlocks
- From: bobdurie@xxxxxxxxx
- Re: Ongoing purging of active records causes deadlocks
- From: Alex Kuznetsov
- Re: Ongoing purging of active records causes deadlocks
- From: bobdurie@xxxxxxxxx
- Re: Ongoing purging of active records causes deadlocks
- From: Erland Sommarskog
- Re: Ongoing purging of active records causes deadlocks
- From: bobdurie@xxxxxxxxx
- Re: Ongoing purging of active records causes deadlocks
- From: Erland Sommarskog
- Ongoing purging of active records causes deadlocks
- Prev by Date: Re: Ongoing purging of active records causes deadlocks
- Next by Date: Free SQL Server 2005 Books
- Previous by thread: Re: Ongoing purging of active records causes deadlocks
- Next by thread: Re: Ongoing purging of active records causes deadlocks
- Index(es):
Relevant Pages
|
