Posted by Erland Sommarskog on 01/26/06 00:44

Mike Read (mar@roe.ac.uk) writes:
> 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 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.

Setting MAXDOP to 1 will avoid the CPUs being monopolized by huge query,
but a two-way box sounds a bit thin for that amount of data.

However, the main killer here is probably memory. As you scan that 1TB
table, the cached a number of times, and all those small queries must
read from disk. 4GB also sounds a bit thin, then again, 8 or 12 GB is
not going to make that much difference anyway.

A separate server, ot at least a seprate instance for those mean queries
would be a better bet.

--
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

[Back to original message]