none
The query processor ran out of internal resources

    Question

  • Hey all!

    I have two similar SQL statements (bold represents a different param):

    1)

    SELECT REC_CI.CashItem, REC_CI.Position, REC_CI.DisplayName, REC_CI.Parent, REC_CI.OrderBy,  
    PivotData.* FROM (SELECT PivotCashItem, Month_Period, Amount / 1 AS Amount 
    FROM dbo.V35_FORCAST_DIFF_Get_PL_PG_Forcast_Actual_Difference (500, 400, 16, 41, 7, 'Aug  1 2013 12:00AM', 'Oct 31 2013 12:00AM', NULL, 'ILS', 14, 1)) Data 
    PIVOT(MAX(Amount) FOR Month_Period IN ([Forcast 8/2013],[Actual 8/2013],[Differences 8/2013],[Forcast 9/2013],[Actual 9/2013],[Differences 9/2013],[Forcast 10/2013],[Actual 10/2013],[Differences 10/2013])) PivotData 
    RIGHT OUTER JOIN dbo.V35_CASHITEMS_Get_SubCashItems(500, NULL) REC_CI ON REC_CI.CashItem = PivotData.PivotCashItem 

    2)

    SELECT REC_CI.CashItem, REC_CI.Position, REC_CI.DisplayName, REC_CI.Parent, REC_CI.OrderBy,  
    PivotData.* FROM (SELECT PivotCashItem, Month_Period, Amount / 1 AS Amount 
    FROM dbo.V35_FORCAST_DIFF_Get_PL_PG_Forcast_Actual_Difference (500, 400, NULL, 41, 7, 'Aug  1 2013 12:00AM', 'Oct 31 2013 12:00AM', NULL, 'ILS', 14, 1)) Data 
    PIVOT(MAX(Amount) FOR Month_Period IN ([Forcast 8/2013],[Actual 8/2013],[Differences 8/2013],[Forcast 9/2013],[Actual 9/2013],[Differences 9/2013],[Forcast 10/2013],[Actual 10/2013],[Differences 10/2013])) PivotData 
    RIGHT OUTER JOIN dbo.V35_CASHITEMS_Get_SubCashItems(500, NULL) REC_CI ON REC_CI.CashItem = PivotData.PivotCashItem 

    Each statement runs as expected and returns 129 rows!

    The problem begins when I try to insert these rows into a temp table:

    IF OBJECT_ID('tempdb..##V3_TMP_ACTUAL_MATRIX_06D18928_AA8F_45AB_A518_FE88AE40B2B0') IS NOT NULL DROP TABLE ##V3_TMP_ACTUAL_MATRIX_06D18928_AA8F_45AB_A518_FE88AE40B2B0
    CREATE TABLE ##V3_TMP_ACTUAL_MATRIX_06D18928_AA8F_45AB_A518_FE88AE40B2B0(
    [CashItem] [int] NOT NULL,
    [Position] [int]  NULL,
    [DisplayName] [nvarchar](50)  NULL,
    [Parent] [int] NOT NULL, 
    [OrderBy] [int], 
    [PivotCashItem] [int],[Forcast 8/2013] [decimal](18, 2) NULL,[Actual 8/2013] [decimal](18, 2) NULL,[Differences 8/2013] [decimal](18, 2) NULL,[Forcast 9/2013] [decimal](18, 2) NULL,[Actual 9/2013] [decimal](18, 2) NULL,[Differences 9/2013] [decimal](18, 2) NULL,[Forcast 10/2013] [decimal](18, 2) NULL,[Actual 10/2013] [decimal](18, 2) NULL,[Differences 10/2013] [decimal](18, 2) NULL CONSTRAINT [PK_##V3_TMP_ACTUAL_MATRIX_06D18928_AA8F_45AB_A518_FE88AE40B2B0] 
    PRIMARY KEY CLUSTERED 
    ([CashItem] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = OFF, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]) ON [PRIMARY]  
    INSERT INTO ##V3_TMP_ACTUAL_MATRIX_06D18928_AA8F_45AB_A518_FE88AE40B2B0 

    ... (1/2)

    I get the following error for inserting statement number 1:

    Msg 8623, Level 16, State 1, Line 9
    The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

    Any ideas why it only happens for the insert statement?

    Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) (VM) 
    • Edited by Solo Polo Sunday, September 08, 2013 9:26 AM
    Sunday, September 08, 2013 9:24 AM

Answers

  • Hi Solo,

    This issue can occur if there are an extremely large number of values (many thousands) in an IN clause, please check the insert commands where the issue occurs to determine whether we can split the insert command into several commands. Additionally, please post the full insert commands here for analysis.

    IN (Transact-SQL) (Error 8623)
    http://msdn.microsoft.com/en-us/library/ms177682.aspx


    Allen Li
    TechNet Community Support

    Tuesday, September 10, 2013 6:34 AM
  • There can be many reasons for  8623 errors. May be mixing of table valued function and PIVOT operator and temp table cause this issue. Is the function so complex...? Test by simplifying the query. Just remove the PIVOT for the time being, and insert the data to temp table. If this works that means problem is with the mixing of temporary objects. Temp table, table valued functions, PIVOT etc. use tempdb and query plans may be complex.

    Try to apply latest service pack and try the original query...


    Krishnakumar S

    Tuesday, September 10, 2013 7:17 AM
  • It seems like 'too many' WHERE conditions across few table unions caused the problem. 

    I removed a table or two from the union, and it worked out.

    Mystery...

    Tuesday, September 10, 2013 2:58 PM

All replies

  • How much have you memory on server and allocate to SQL Server(in MB) ?

    Sunday, September 08, 2013 9:32 AM
  • Around 12 GB, 4 Cores
    Sunday, September 08, 2013 12:27 PM
  • ok

    and the memory dedicated to SQL Server ?

    SELECT value, value_in_use 
    FROM sys.configurations
    WHERE name = 'max server memory (MB)'

    Sunday, September 08, 2013 2:50 PM
  • Hi Solo,

    This issue can occur if there are an extremely large number of values (many thousands) in an IN clause, please check the insert commands where the issue occurs to determine whether we can split the insert command into several commands. Additionally, please post the full insert commands here for analysis.

    IN (Transact-SQL) (Error 8623)
    http://msdn.microsoft.com/en-us/library/ms177682.aspx


    Allen Li
    TechNet Community Support

    Tuesday, September 10, 2013 6:34 AM
  • There can be many reasons for  8623 errors. May be mixing of table valued function and PIVOT operator and temp table cause this issue. Is the function so complex...? Test by simplifying the query. Just remove the PIVOT for the time being, and insert the data to temp table. If this works that means problem is with the mixing of temporary objects. Temp table, table valued functions, PIVOT etc. use tempdb and query plans may be complex.

    Try to apply latest service pack and try the original query...


    Krishnakumar S

    Tuesday, September 10, 2013 7:17 AM
  • It seems like 'too many' WHERE conditions across few table unions caused the problem. 

    I removed a table or two from the union, and it worked out.

    Mystery...

    Tuesday, September 10, 2013 2:58 PM