Sql-server – 6GB Table – 37Mi Rows – ‘System.OutOfMemoryException’ – SQL 2008 R2

performancequery-performancesql-server-2008-r2

I have a proc, looping through databases( the insert is in a fix database…the select uses the loop ), making inserts (Will hide names for security):

insert into Main_Database..Table(16 Fields)  
    select ( there are 16 fields here, DateDiff(s, DtTrabalhoInF8, DtTrabalhoFi) as TempoSegundos  
     from  [Database_name].dbo.View as T1 with (nolock)  
     where DtTrabalho >= '2015/06/06' and   
     not exists(select 1 from PainelControle.dbo.tblLogProjetos T2 Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS and t2.codlog = t1.codlog)

The problem is:

If I run this, it runs in 1 second:

insert into Main_Database..Table(16 Fields)  
        select ( there are 16 fields here, DateDiff(s, DtTrabalhoInF8, DtTrabalhoFi) as TempoSegundos  
         from  [Database_name].dbo.View as T1 with (nolock)  
         where DtTrabalho >= '2015/06/06'

The problem is here:

and   
         not exists(select 1 from Database_Name.dbo.tblLogProjetos T2 Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS and t2.codlog = t1.codlog)

This tblLogProjetos has 37Million rows. 6GB ( The simple select 1 from tbllog trhowing the message) and the job never ends( running the code manually, not inside the job, after 10min it shows me the error ):

'System.OutOfMemoryException'

I would like ot know how to troubleshoot this. Is this SERVER problem? ( we have 32GB ). Is this MANAGEMENT STUDIO problem:

name                    minimum      maximum       config_value     run_value
max server memory (MB)    16       2147483647        28000           28000

Or is this query problem? One thing that I notice, When I use a query to see unused indexes, every index of this table has 0 reads. How can this be possible?

enter image description here

By the way…tblLogProjetos is a VIEW that uses a linked server to another server. It's just a select ( fields ) from [server5].painelcontrole.dbo.tbllogprojetos

Best Answer

System.OutOfMemory exception is a .NET exception which is basically telling you that the grid control experienced an OOM condition. I had blogged about this in detail in the past.

You could try the recommendations to trim down the result set which is definitely required in the intermediate levels of the procedure. But to circumvent the OOM condition in SSMS, you could either try with output to TEXT or output to a file or use SQLCMD to execute the same T-SQL procedure/command.

Related Question