locked
Unable to generate execution plan RRS feed

  • Question

  • An event log entry is made by SQL Server saying “Unable to generate execution plan”.
    The SP has a dynamic SQL statement with parameters.
    Within the SP we Execute sp_Executesql @SQL, @ParamDefinition, @fromDate,
          @toDate, @objectIds, @pageno, @pagecount, @objecttypecode

    The eventlog does not have any details regarding SP or Database.The eventlog entry happens whenever we execute the web application that calls this SP. So I assume its due to the SP.
    The issue occurs only in Prod environment and not in UAT/Stage/Dev environments.
    Network service account is being used to call the SP.


    Dinesh
    Wednesday, February 23, 2011 6:03 AM

Answers

  • Yep could be , what if  you rewrite the query to use UDF split objectid list

     

    ALTER function [dbo].[ListString] (@List varchar(8000))
    returns table
    as return (
    select  substring(List, [Number] + 1,
                      charindex(',', List, [Number] + 1) - ([Number] +1 )) Item
    from   (select ',' + @List + ',' List) OL
            join numbers on substring(List, [Number], 4000) like ',_%'
            and [Number] between 1 and len(List))

     

    With CTE as
       (SELECT rn = row_number()
       over (order by [MA] desc)
         ,[ID]
         ,[MA]    
       FROM
       [TBL]
      WHERE
       MA between @fromDate and @toDate
       AND OT = @objecttypecode  AND ID in IN (SELECT Item FROM [ListString](@objectist))  

     Select [ID],[MA], from CTE
    Where    
      rn between @pageno * @pagecount  and @pageno * @pagecount
      order by [MA] desc'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi N Friday, February 25, 2011 1:03 AM
    • Marked as answer by KJian_ Tuesday, March 1, 2011 5:08 AM
    Wednesday, February 23, 2011 9:46 AM
    Answerer

All replies

  • Dinesh

    Interesting, can you post sample data + table structure to test that behaviour


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, February 23, 2011 7:36 AM
    Answerer
  • Set @SQL ='SET NOCOUNT ON;
       With CTE as
       (SELECT rn = row_number()
       over (order by [MA] desc)
         ,[ID]
         ,[MA]    
       FROM
       [TBL]
      WHERE
       MA between @fromDate and @toDate
       AND OT = @objecttypecode  AND ID in ('+@objectIds+'
       )
      Select [ID],[MA], from CTE
    Where    
      rn between @pageno * @pagecount  and @pageno * @pagecount
      order by [MA] desc'

    Is this due to some permission difference in prod environment ?

    Or due to bad query or timeout(longer time to exectute) as there is a lot of data ?


    Dinesh
    Wednesday, February 23, 2011 9:10 AM
  • Yep could be , what if  you rewrite the query to use UDF split objectid list

     

    ALTER function [dbo].[ListString] (@List varchar(8000))
    returns table
    as return (
    select  substring(List, [Number] + 1,
                      charindex(',', List, [Number] + 1) - ([Number] +1 )) Item
    from   (select ',' + @List + ',' List) OL
            join numbers on substring(List, [Number], 4000) like ',_%'
            and [Number] between 1 and len(List))

     

    With CTE as
       (SELECT rn = row_number()
       over (order by [MA] desc)
         ,[ID]
         ,[MA]    
       FROM
       [TBL]
      WHERE
       MA between @fromDate and @toDate
       AND OT = @objecttypecode  AND ID in IN (SELECT Item FROM [ListString](@objectist))  

     Select [ID],[MA], from CTE
    Where    
      rn between @pageno * @pagecount  and @pageno * @pagecount
      order by [MA] desc'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Naomi N Friday, February 25, 2011 1:03 AM
    • Marked as answer by KJian_ Tuesday, March 1, 2011 5:08 AM
    Wednesday, February 23, 2011 9:46 AM
    Answerer
  • Thanks Uri :)

    Will let you know the results


    Dinesh
    Wednesday, February 23, 2011 12:32 PM
  • Hi Uri,

    I optimized the logic so that objectids that are getting passed is minimal

    and increased the timeout.

    Now things are fine.

    Thanks for your reusable function.


    Thank you Dinesh
    Saturday, April 30, 2011 6:52 AM