|
|
Posted by Erland Sommarskog on 07/23/07 21:59
bobdurie@gmail.com (bobdurie@gmail.com) 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|