|
Posted by Robert Klemme on 01/25/06 12:49
Mike Read wrote:
> Hi
>
> On our SQl Server (2000) we can have queries that take at least
> 10-20mins (require full table scan fo billion row table). When one of
> these queries is running it substantailly slows down very quick
> queries (sub second queries take several seconds).
I don't know your env and its requirements but to me the difference
between sub 1 sec and several seconds doesn't sound worth the effort
changing anything - unless, of course, you have lots of these short
queries executed in sequence and the difference sums up dramatically.
> I believe there is no way to set priorities but was wondering if there
> are other configuration settings that could help. The server is dual
> processor so maybe setting maxdop to 1 will help. Memory size is
> dynmaically assigned up to 4Gb but as the DB size is > 1Tb I'm not
> sure if allowing this much memory is actually decreasing performance
> when the quick query trys to get through.
>
> Any suggestions.
A random list that comes to mind:
- scheduling: make sure the long runners are done during the night or
other time when the DB is mostly idle.
- distribution of data: either via some form of replication or by moving
data from one DB to a complete different system
- optimizing SQL: additional indexes, different query conditions etc.
Cheers
robert
Navigation:
[Reply to this message]
|