OutOfMemoryException when query large data from db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

OutOfMemoryException when query large data from db

Author

Topic

Ekkapop
Starting Member

6 Posts

Posted - 2008-09-25 : 23:05:16

Windows OS: Windows 2003 server enterprise for itanium sevice pack 2 and last security patch on 14 Aug 08
SQL: MS SQL server 2005 enterprise edition, server service pack 2, version 9.00.3042.00

Hardware: Itanium Server with 24GB of memory.

Query: SELECT KeyNo, Data1 FROM TableA
TableA total records: 50,000,000 rows

Exception throw when result = 33,554,432 (2 ^ 25)
Exception message: System.OutOfMemoryException

I know that microsoft release [url=http://support.microsoft.com/kb/943656/LN/]new hotfix[/url] that i have not download it yet. I want to know that my problem is bug or not? If this problem is bug of Sql Server or Sql Management Studio, is micorsoft release hot fix or patch for this problem?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-25 : 23:59:09

Why don't you have a WHERE clause on your query? You can't expect to return 50 million rows from a query and not have memory issues.

The bug is in your query. It is missing a filter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ekkapop
Starting Member

6 Posts

Posted - 2008-09-26 : 00:11:12

I want to test for server and database performance. I know that this query is not make sence but i can't tell that to my manager :). My manager expect that it must retuen 50 million rows of record then i must research for solution or find out that is problem from os / sql server or management studio.

ps. from task manager the management studio have * 32 after it name.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-26 : 00:13:39

There is no solution to your issue except to add a WHERE clause. There is no problem as no application will return this amount of data to the screen.

The SQL client tools are 32-bit.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ekkapop
Starting Member

6 Posts

Posted - 2008-09-26 : 00:46:22

It always exception at 33,554,432 (2 ^ 25) rows of data is this number, is this number have some significant or it can predict in a manner?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-26 : 14:18:50

It has to do with memory. You don't have enough memory to return that much data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ekkapop
Starting Member

6 Posts

Posted - 2008-09-30 : 01:55:02

This Itanium server has 24GB of memory that i think it's enough to hold 10GB of data (my 50 million is about 10 GB; include log file), and while i run this command memory is use not more than 10GB (i'm looking in task manager). I believe that memory is not problem.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 13:00:29

You are missing my point. The client doesn't have enough memory to receive this result set back from the server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources