Memory problems while selecting many rows

Question

I am retrieving rows from large table (1.8 GB, 20 milions of records) with DataReader.

The SQL Server (2008 R2) consumes a lot of memory and (sometimes) doesn't survive this query. It is probably holding the whole result in memory and returning the rows from this buffer to client.

The select is quite simple - it just returns all rows from table with simple where condition, date stored in column is smaller than actual date. There are no blobs or strings in columns.

Am I right with my estimation about the cause of memory usage? And what can I do in this situation - I need all rows, the query doesn't have to be fast, but memory efficient.

Thanks

Updated info - select is in stored procedure. Code:

CREATE PROCEDURE [get_current_records]
with recompile
AS
BEGIN

declare @currentDate datetime = getdate()
SELECT 
   [id]
  , name
  , description
  , number
  ,[valid_from]
  ,[valid_to]
  from ui_parcela
  where valid_from < @currentDate and (valid_to is null or valid_to > @currentDate )
END

No, client and server are different machines. Rows are stored to stream using XmlWriter. It was the SQL Server that had problems with memory. — user2900951, Oct 20 '13 at 20:33
What kind of errors do you see? I can't see why querying a 1gb table should be a problem. — Andrew, Oct 20 '13 at 21:16
Errors are: There is insufficient memory available in the buffer pool. — user2900951, Oct 21 '13 at 06:15

score 0 · Answer 1 · answered Oct 20 '13 at 20:35

0

Its important to know what are you doing with those rows? Are you storing them in the memory , or you can use each row and release them from the memory

So I am suggesting you try using async reader

answered Oct 20 '13 at 20:35

Izikon

902
11
23

But his SQL server is crashing out, not the client machine. – Science_Fiction Oct 20 '13 at 20:40
ohhh then maybe his setting are not correct. http://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx – Izikon Oct 21 '13 at 07:46

score 0 · Answer 2 · answered Oct 20 '13 at 20:43

0

It sounds like you need to look at your SQL query if the server is running out of memory. Have you Indexed correctly?

Check the SQL execution plan to see what is expensive.

answered Oct 20 '13 at 20:43

Science_Fiction

3,403
23
27

I will do that tomorrow at work and post the exact select and info about execution plan – user2900951 Oct 20 '13 at 20:48

Memory problems while selecting many rows

2 Answers2