|
|
Posted by bobdurie@gmail.com on 07/24/07 11:41
On Jul 23, 5:59 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> bobdu...@gmail.com (bobdu...@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, esq...@sommarskog.se
>
> 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?
Navigation:
[Reply to this message]
|