The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for

The following exception occurs when trying to browse a tag:

[ suspect >> searchSQL generated at SearchBarrelProvider.cs:95 in searchSQL is over 130k in size.... ]

 

Server Error in '/' Application.

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.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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.

Source Error:

Line 100:
Line 101:                connection.Open();
Line 102:                using(reader = command.ExecuteReader(CommandBehavior.CloseConnection))
Line 103:                {
Line 104:

Source File: C:/websites/CS_2.1.60809.935_SDK/source/CommunityServerSearchBarrel/SearchBarrelProvider.cs    Line: 102

Stack Trace:

[SqlException (0x80131904): 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.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857450
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735062
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62
   CommunityServer.Components.SearchBarrel.SearchBarrelProvider.GetSearchResults(SearchBarrelCriteria barrelCriteria) in C:/websites/CS_2.1.60809.935_SDK/source/CommunityServerSearchBarrel/SearchBarrelProvider.cs:102
   CommunityServer.Components.SearchBarrel.SearchBarrelProvider.Search(SearchQuery query) in C:/websites/CS_2.1.60809.935_SDK/source/CommunityServerSearchBarrel/SearchBarrelProvider.cs:40
   CommunityServer.Components.CSSearch.Search(SearchQuery query) in C:/websites/CS_2.1.60809.935_SDK/source/Components/Search/CSSearch.cs:25
   CommunityServer.Components.Tags.GetPostsMatchingTags(String[] tags, Int32 pageIndex, Int32 pageSize, Boolean andTags, Boolean cacheable) in C:/websites/CS_2.1.60809.935_SDK/source/Components/Tags.cs:296
   CommunityServer.Components.Tags.GetPostsMatchingTags(String[] tags, Int32 pageIndex, Int32 pageSize, Boolean andTags) in C:/websites/CS_2.1.60809.935_SDK/source/Components/Tags.cs:268
   CommunityServer.Controls.TagPostResults.get_SearchResults() in C:/websites/CS_2.1.60809.935_SDK/source/Controls/Utility/TagPostResults.cs:133
   CommunityServer.Controls.TagPostResults.DataBind() in C:/websites/CS_2.1.60809.935_SDK/source/Controls/Utility/TagPostResults.cs:177
   CommunityServer.Controls.TagPostResults.OnLoad(EventArgs e) in C:/websites/CS_2.1.60809.935_SDK/source/Controls/Utility/TagPostResults.cs:149
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

 


Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Filed under:
  •  
  • Post Points: 50
  • 09-17-2006 4:12 PM In reply to
    • X.Static
    • Top 25 Contributor
    • Joined on 10-07-2004
    • 127.0.0.1 (Charlotte NC)
    • Posts 1,517
    • Points 26,370
    • MVPs
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...
    Hmm, that's a new one for me. Try updating the statistics in your CS databae (see books online and look up sp_updatestats) to see if that will fix the issue.
    •  
    • Post Points: 5
  • 09-18-2006 1:38 AM In reply to
    • X.Static
    • Top 25 Contributor
    • Joined on 10-07-2004
    • 127.0.0.1 (Charlotte NC)
    • Posts 1,517
    • Points 26,370
    • MVPs
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...
    There is a KB over on MS.com about this as well: http://support.microsoft.com/?kbid=917888 however I do not know if it applies in this case. Regardless, to get this hotfix you'll need to call Microsoft (it's a free call if it's related to a hotfix) and they'll be able to help you determine if the hotfix is appropriate in this case.
    •  
    • Post Points: 35
  • 09-19-2006 10:11 AM In reply to
    • dagaci
    • Not Ranked
    • Joined on 10-01-2005
    • Posts 20
    • Points 385
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    Thanks for the quick reply. Unfortunately the update statistics method didn't solve the problem.

    The unreleased HotFix page you linked to point might be a solution, but really it seems the problem is with the generated sql query itself which is actually a single line of dynamic sql 120k wide.

    If 100 people click on a TAG does that mean SQL server has to decode and create a plan for 120k of dynamic, 100 times?

     

    Filed under: , , ,
    •  
    • Post Points: 20
  • 09-19-2006 2:20 PM In reply to
    • X.Static
    • Top 25 Contributor
    • Joined on 10-07-2004
    • 127.0.0.1 (Charlotte NC)
    • Posts 1,517
    • Points 26,370
    • MVPs
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    I see what you're saying. I'm going to venture a guess and recommend that you give that hotfix a shot; I've been involved with community server for almost 2 years now and this is the first time I've seen this error. If you want to try and narrow it down to SQL Server 2005 only before trying out the hotfix export your db to SQL 2000 (if that's a possibility of course) and see if you still get that exception.

    The hotfix isn't unreleased, it's Microsoft policy to not publicly distribute hotfixes related to MS developer products so you'll have to call them (it's a free call since it's related to a hotfix). The engineer will verify that you're having this exact issue and then send you information as to how to obtain the hotfix (usually a password protected FTP site).
     

    •  
    • Post Points: 20
  • 09-19-2006 3:32 PM In reply to
    • iso3200
    • Top 100 Contributor
    • Joined on 10-22-2004
    • Vancouver, Canada
    • Posts 195
    • Points 2,890
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    would it be possible for the OP to find the query being issued using a trace or profiler? e.g. see this thread: http://communityserver.org/forums/thread/533670.aspx

     I'm willing to bet the SQL is dynamically being generated with a huge "WHERE IN" clause.  This was a problem before where sprocs were taking in query text as nvarchar(4000) and EXEC'ing them.  Now it looks like the sprocs have been updated to accept a ntext parameter.

    I'm not sure how well SQL Server performs with huge WHERE IN clause.  Isn't there a way to pass a comma-separated list of SectionIDs and generate a temp table out of that and then INNER JOINing to that temp table?

    Edit to add: http://www.sommarskog.se/arrays-in-sql.html 

    •  
    • Post Points: 20
  • 09-20-2006 6:25 AM In reply to
    • dagaci
    • Not Ranked
    • Joined on 10-01-2005
    • Posts 20
    • Points 385
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    What is OP?  -- yes there is a very huge WHERE IN clause. I "fixed" that by modifying the SearchQueryBuilder.cs to exclude empty sections from the query, this reduces the query to something SQL2005 can handle. Is there a need to include Sections with 0 posts (in SearchQueryBuilder.cs)?

    sb.Append(" AND P.SectionID IN (");
    for(int i =0; i < list.Count; i++)

     if (((Section)list[ i ]).TotalPosts > 0) 
     { 
      sb.Append(((Section)list[ i ]).SectionID.ToString()); 
      if ((i + 1) < list.Count) 
      { sb.Append(","); }
     }
    }
    sb.Append(")");

     

    •  
    • Post Points: 20
  • 09-20-2006 9:33 AM In reply to
    • iso3200
    • Top 100 Contributor
    • Joined on 10-22-2004
    • Vancouver, Canada
    • Posts 195
    • Points 2,890
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...
    OP = Original Poster
    •  
    • Post Points: 5
  • 09-29-2006 9:56 PM In reply to
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    Same problem here.  I didn't do anything special with this at all, right out of the can and it's broken. 

    I'm sure the hotfix will fix it, but I'm more concerned about why it's building a query so complex and so large?  Surely this is a mistake?

    Cheers,

     Paul

    •  
    • Post Points: 5
  • 09-29-2006 10:04 PM In reply to
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...
    Not sure if it's significant, but my site has ~100,000 sections.
    •  
    • Post Points: 20
  • 10-03-2006 9:20 AM In reply to
    • dagaci
    • Not Ranked
    • Joined on 10-01-2005
    • Posts 20
    • Points 385
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

     

    deadlyviper:
    Not sure if it's significant, but my site has ~100,000 sections.

     

    100,000 sections is ~10X bigger than my site, your sql queries are probably in the multi-megabytes zone.

    •  
    • Post Points: 5
  • 10-03-2006 11:30 AM In reply to
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    Just to throw my problems in here too... I have 10K sections and the generated SQL is way too long.

    http://communityserver.org/forums/thread/549534.aspx 

    CarKnee's Blog
    •  
    • Post Points: 5
  • 12-14-2006 10:58 PM In reply to
    • dagaci
    • Not Ranked
    • Joined on 10-01-2005
    • Posts 20
    • Points 385
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...
    Answer

     

    X.Static:
    There is a KB over on MS.com about this as well: http://support.microsoft.com/?kbid=917888 however I do not know if it applies in this case. Regardless, to get this hotfix you'll need to call Microsoft (it's a free call if it's related to a hotfix) and they'll be able to help you determine if the hotfix is appropriate in this case.

     

    You can download the hotfixes from here: http://support.microsoft.com/kb/918222

    •  
    • Post Points: 20
  • 04-10-2007 4:29 AM In reply to
    • Maria51
    • Not Ranked
    • Joined on 04-10-2007
    • Posts 1
    • Points 5
    Re: CS 2.1 Tags: The query processor ran out of internal resources ...

    Hi everybody Smile

    I have same error 

    "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."

    when I run my package . for more explanation.....  I have one server with win2003 os  and a database system in .Net 2003 and sql server 2000.

    I changed sql200 to sql2005 and saw this error,when I run my package to connect to first table. I searched in the internet.....and installed sql server 2005 sp2 and  execute sp_updatestates on database after restore...... can anyone help?

    Thanks in advance

    Maria 

     

     
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值