October 30, 2011 at 12:40 pm
This is a follow up post to http://qa.sqlservercentral.com/Forums/Topic1197803-1292-1.aspx
When I try to return all the record a table of 200,000,000 records, I receive and error message:
"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.". The query returned 33,500,000 rows before terminating
The operation system is Windows Server 2008 R2 and SQL is the Standard edition of SQL 2008 R2. The Server has 16 Gb of memory and no other applications are running.
Can I assume the error message means that the query used up all available memory on the server? If so is there a way to have it use a paging file or some other temporary file which will enable the query to complete? Any other ideas?
The query is:
Select I.TimePoint, I.daPrice- I.rtPrice, I.versifyID
from dbo.ECoImportedData I
inner join dbo.EcoDate D
On I.TimePoint = D.TimePoint
where D.TimePoint >= '01/01/2006 00:00:00' and d.TimePoint < '01/01/2011 00:00:00'
The ultimate objective is to use this SELECT to populate a temporary work table which might only contain 1 or 2 years of data instead of 6. The data in the temporary table will be manipulated to generate the results/reports I need. So I am trying to test the SELECT before I include the INSERT clause.
thanks,
pat
October 30, 2011 at 12:48 pm
mpdillon (10/30/2011)
When I try to return all the record a table of 200,000,000 records, I receive and error message:"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.". The query returned 33,500,000 rows before terminating
That's a .Net error. It's coming from Management Studio on your client machine. Your local machine does not have sufficient memory to store the massive result set.
Can I assume the error message means that the query used up all available memory on the server? If so is there a way to have it use a paging file or some other temporary file which will enable the query to complete? Any other ideas?
No. Nothing to do with the server, the server is fine. It's a client error.
Add more memory to your workstation (not the server) or filter the resultset.
The ultimate objective is to use this SELECT to populate a temporary work table which might only contain 1 or 2 years of data instead of 6. The data in the temporary table will be manipulated to generate the results/reports I need. So I am trying to test the SELECT before I include the INSERT clause.
This is probably the wrong approach. With decent indexing (which you now have), creating a temp table from a subset of the table for further processing is probably going to be inefficient.
I say this having spent a week cleaning just this type of temp table usage out of a client's system and getting massive performance improvements in the process.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2011 at 4:33 pm
First, thank you for following me over here. I do appreciate the replies.
Good to hear that it is a .Net error. I thought it might have something to do with the result set being displayed. I was not on a workstation, but on the server in SSMS, New query.
I do not have the final specs for the first report I am supposed to create from this table. But it will probably involve a CTE. I believe I will be comparing data from the day before to the current day and looking for the Top 10 results based on some criteria. The temp table was my attempt to make this run faster. I will try it your way and see how it performs.
As background, the table contains several thousand nodes. Each node is assigned a value every hour of the day. For a three year period I need to return the top 10 nodes with the greatest variance based on a yet undefined criteria. Part of that criteria is comparing each node for each hour to the same node the previous day.
I will probably have a few more questions that will be in a separate post but it will be later in the week.
Thanks again for your help.
pat
October 30, 2011 at 5:37 pm
mpdillon (10/30/2011)
Good to hear that it is a .Net error. I thought it might have something to do with the result set being displayed. I was not on a workstation, but on the server in SSMS, New query.
In that case, your query ran the server out of memory and probably caused problems for anyone else accessing it, but purely because of the amount of rows you were trying to display in SSMS.
Using SSMS directly on the server is not recommended in most cases, and certainly not for development purposes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 8:38 am
1) don't try to return this much data to a client tool. just won't work
2) you best have a VERY large and VERY fast tempdb IO subsystem to throw 200M rows of data into it, unless it is just a single column of tinyints or something.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 31, 2011 at 8:46 am
Thanks for the advice. I was just looking to see how long the query would take. I didn't really stop to think about the consequences of using a client display tool.
The server is not in use, except for this testing.
Thanks,
pat
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply