|
|
Posted by bobdurie@gmail.com on 07/24/07 11:39
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
Thank you very much for the insight!! More followups:
For this query/purge idead:
SELECT @loadid = MAX(LoadID)
FROM loads
WHERE CreateDate < dateadd(SECOND, -40, CURRENT_TIMESTAMP);
DELETE loads WHERE LoadID <= @loadid;
Why would i need an index on CreateDate,LoadiD ? Is it just for the
first query, or would the DELETE actually need it too? I'd be tempted
to do the first query with READ_UNCOMMITTED transaction level, then do
the DELETE on another default transaction isolation level connection.
The performance of the DELETE can be quite bad/slow, etc.., just so
long as it doesn't lock the system up.
To add some color i'm scared to add indexes because the system has
been thoroughly tested and minus the problems we're discussing here
its functioning quite well. Further to that, we support sql server
2000, 2005, and oracle, and have same/similar schemas for all, hence
its testing*3. Let me know if you think i could get away with NOT
adding the index you describe here.
To the point about the SNAPSHOT isolation, i didn't know about either
of those... we could experiment with those, but we'd still be in
trouble with sqlserver2000. Ahhh the joys of a database 'agnostic'
application!!! :)
Thanks again!!!
Navigation:
[Reply to this message]
|