Posted by Erland Sommarskog on 10/01/19 11:39

Paul (paulwragg2323@hotmail.com) writes:
> It turns out that we have a table that is over 1GB in size. We only had
> the server configured to allow SQL Server to use 800MB.
>
> Therefore, when we were querying using a query resulting in a full
> table scan there was not enough memory to perform the query. Instead of
> returning an error it basically just stopped (or maybe it was doing the
> query but it would take years!).

There is no reason why SQL Server would failed to return all rows from
a table that is larger than the available memory. However, it should
not comes as any surprise that returning so many rows will take a
very long time, and will saturate both server and client.

Or are you saying that you get a problem if you do:

SELECT * FROM tbl WHERE nonindexedcol = oddvalue

While it will take some load as the table is scanned, and both brought
into cache and out again, it should not send SQL Server into complete
nirvana.


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