The strange case of missing memory

Question

Post reply

The strange case of missing memory

Elliott Berkihiser

SSCarpal Tunnel

Points: 4068
More actions
August 23, 2010 at 10:53 am

#148456

SQL Server seems to have "lost" memory between two operations:
I was experimenting with making cross joins of a table (AsciiChars) consisting of 95 ascii characters. I created an empty "receiving" table and then cross-joined the original table with itself 4 times to generate the result-set for insertion into the receiving table.
POWER(95,4) yields some 81 million records and the process worked fine. However when I try to repeat the process: Select a.c + b.c + c.c + d.c from AsciiChars a cross join AsciiChars b cross join AsciiChars c cross join AsciiChars d, the Query runs and then reports an Out of Memory error.
In fact, if I simply try to select all the 81 million records that were created in the receiving table, the same error occurs.
Any ideas on what has occurred?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Can you post the test scripts you used for the test so that the scenario can be reproduced in-house?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 2

Create Table AsciiChars(Acode int, C Char(1))

Declare @cmd varchar(200)

Declare @code smallint

Set @code=32

WHILE @code<127

BEGIN

Set @cmd='

Insert into AsciiChars

Select + CAST(' + @code + ' as varchar(3)), CHAR(' + CAST(@code as varchar(3)) + ')'

Exec(@cmd)

Set @code=@code+1

END

Select a.C + b.C + c.C + d.C -- create 4 character "words"

From AsciiChars a cross join AsciiChars b cross join AsciiChars c cross join AsciiChars d

Does this help?

Thanks,

Elliott

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

Okay. So, the problem is not in the query. The problem is in returning results back to SSMS.

The following works:

Select a.C + b.C + c.C + d.C AS 'AsciiChars'-- create 4 character "words"

INTO AsciiChars2

From AsciiChars a

cross join AsciiChars b

cross join AsciiChars c

cross join AsciiChars d

and so does using the "Results to File" option (Query -> Results to -> Results to File OR Ctrl+Shift+F)

Precisely 81450625 rows are affected.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What was the exact error you got? Was it a SQL error (appearing in the messages pane) or an SSMS error (appearing as a message box)?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

The error was a OutOfMemory exception in the messages pane.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 6

Gail,

It appears to be an OS error:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

(This was from a query to select from a large table).

As the previous poster suggested, I was able to run the cross join query by sending the results to a file, but then (of course?), there was not enough memory for SQL to read the file.

Am I missing something here?

Thanks,

Elliott

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 7

I agree. This definitely looks like a buffer/memory setting of some sort. I ran this today on a much powerful server and workstation (as compared to my previous attempt) and I was able to go about 3 times more before encountering the exception.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

This is really strange case. As per my view, whenever we try to insert the result set into another table, SQL Server is pushing/pumping the data in batch and release the buffer so it has available memory during that operation. Same way when we redirect the result set to file, it is doing the same and operation completes successfully.

If you check, when you fire "select * from table" and table has millions of row then SSMS starts displaying the results once it has few records (means 500) and continue the process till it completes. Same like streaming the data & displaying it.

Thanks

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 9

Hello!

Here's more food for thought:

I got a 64-bit Intel Xeon dual CPU box (2x2GHz), with 4GB RAM running Windows Server 2008 R2 and SQL Server 2008 R2. The system went as far as around 30000000 rows (out of an expected 81450625) before choking up.

The other system (which is what I had used yesterday for the test) is: 64-bit, Intel Pentium Dual CPU (2x1.6GHz) with 4GB RAM running Windows Server 2008 R2 and SQL Server 2008 R2.

Here's the most interesting point - both systems generated the exact same execution plan!

The activity monitor in SQL does not show any pressure, however the query does seem to be taking up a lot of drive space - maybe the expansion of the tempdb is what is causing the I/O bottleneck. I am not sure, but am just speculating.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 10

Nice observation Nakul.

We should post the query to Microsoft forums using this link as reference, so those who knows SQL Server internal in deep can answer.

Shall we require any approval to give reference for this post's link while posting the query to MS forums?

Thanks