| |||
|
Filed under: Tags Bug
|
-
09-17-2006 4:12 PM In reply to 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 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 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: bugs, TAGs, SQL, Performance- Post Points: 20
-
09-19-2006 2:20 PM
In reply to 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 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 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-29-2006 9:56 PM
In reply to 
deadlyviper 
- Joined on 10-25-2005
- Posts 24
- Points 360
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 - deadlyviper
-
-
- Joined on 10-25-2005
- Posts 24
- Points 360
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 
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 - Sean Kearney (CarKnee)
- Joined on 08-23-2004
- Long Island, NY
- Posts 301
- Points 4,790
Just to throw my problems in here too... I have 10K sections and the generated SQL is way too long.
- Post Points: 5
-
12-14-2006 10:58 PM
In reply to
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 Hi everybody
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
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
