SQL Server Performance

Error: 8623, Severity: 16, State: 1 The query processor ran out of internal resources and could not produce a query plan

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Narine, Feb 5, 2008.

  1. Narine New Member

    Hi all,
    We're getting the following error intermittently on one of our SQL2K5 servers:
    Error: 8623, Severity: 16, State: 1.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.
    I'm trying to capture the query that's causing this in the profiler, but so far no luck.
    How can I filter the profiler to only display the SQL statement that's causing it and not get all the other transactions and processes.
    Many Thanks for the help in advance!!
    Narine
  2. satya Moderator

  3. Narine New Member

    It's got SP2 and Cumulative Update 2 on it.
    Thanks for the link. According to this article, post SP2 Cumulative Update 5 should fix the problem.
    Boy, they really've got a lot of cumulative updates following SP2.
  4. satya Moderator

  5. FORSQL New Member

    Hi all,
    We have SP3 installed on our Production server. Even then we are getting same error.
    what could be the reson??
    Regards,
    Gayaz Ahamed Shaik
  6. cwilson310 New Member

    We also just got this error message, and we are current in our service packs.
    Is it possible that a patch to fix this error did not get applied?
    Or does this mean that our query truly is too complex?
  7. kuraliniyan New Member

    Hi ,
    The same error message we for SQL server 2005 SP3 installed server.May i know what would be the cause?Please help me.
  8. Narine New Member

    Unfortunetely, we haven't resolved the issue. We've upgraded to 2008, and the problem did not go away. Recenly, I stumbled upon the following post and believe this might be the issue in our case where the insert statement to table with the computed columns is causing this. But I haven't tried the solution mentioned here. Let me know if this works for you:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=422368
  9. Adriaan New Member

    Hm - that last link is about a script that does this:
    INSERT INTO tbl (col1, col2, col3)
    VALUES (@var1, @var2, @var3)
    I seem to remember that an insert with variables in a VALUES clause can indeed have this effect. If you concatenate the variable values into dynamic SQL, it may solve the issue.
    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = 'INSERT INTO tbl (col1, col2, col3) '
    + 'VALUES (''' + @var1 + ''', ''' + @var2 + ''', ''' + @var3 + ''')'
    EXEC (@SQL)
    HIH

Share This Page