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

[Back to original message]