6

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

  • Are you running this in SSMS when you are getting OOM? You are using collate command does the two tables have different collation – Shanky Jun 11 '15 at 17:24
  • Yes. It's running in a job, But I saw that it was never ending ( sometimes it ends...but since 3 days ago i'm having this problem ), and for tests I used SSMS. The collate is because there are databases with different collates, and because the loop, we use it. – Racer SQL Jun 11 '15 at 17:26
  • 1
    The collate command will force query to not use index this might probably be reason for long running. SSMS has capacity to produce rows its memory is limited to 2 G if you force it to produce more result set many a times it gives OOM error – Shanky Jun 11 '15 at 17:29
  • Epic...Didn't know about the collate with indexes. I'm trying to change collate of those databases for months, making them use the same collate, but i'm not getting. This is a really good answer. Thank you very much. WIll study a little more to fix different collates. – Racer SQL Jun 11 '15 at 17:34
  • 1
    Shanky...Just changed collate to Latin1_General_CI_AS...it's running in 1 second now. Dumb me. Thank you for your support. It helped me a lot. I fell SMARTER ! – Racer SQL Jun 12 '15 at 11:22
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.