The strange case of missing memory

  • 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?

  • 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

  • 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

  • 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

  • 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
  • The error was a OutOfMemory exception in the messages pane.

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

    Follow me on
    Twitter: @sqltwins

  • 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

  • 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

  • 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

  • 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

  • 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

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

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