.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

MS SQL 2000 The query processor ran out of stack space during query optimization.

Posted By:      Posted Date: September 11, 2010    Points: 0   Category :Sql Server
On an insert statement with an image field I get the following error when the data to go into the image field is over a certain size. Lots of special characters. The query processor ran out of stack space during query optimization. Please help on how to fix.

View Complete Post

More Related Resource Links

Insert Column Limit from XML? The query processor ran out of stack space during query optimization e


I get the following error:

Msg 8621, Level 17, State 2, Procedure ProcessFliteTracWorkingXML_FTPax, Line 348
The query processor ran out of stack space during query optimization. Please simplify the query.

I have looked at this KB fix, but we are a newer version then in that article:

Also found this post with a similiar issue, but no solution:

Before I call Microsoft Support I thought I would try here first.

I am trying to run a stored proc that basically needs to parse xml messages coming from an outside datasource.  We are using SQL Server 2005 SP3 (9.00.4053.00 SP3 Standard Edition).  This datasource is an old pervasived database with not the best data model.  So what we wanted to do was pull the raw data as is into a staging table and then convert it to a better data model in our ODS.  There are around 300 columns in this table and I have a generic process that pulls the rows out and converts to XML and saves to a working table (this

Merge Replication - Error - Query processor ran out of stack space


Hello everyone,

I have a merge publication configured that has been running fine with no issues for many weeks.  Here is my setup... Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2, Subscriber - SQL 2008 SP1.

On Wednesday and Thursday of last week, I created a few hundred foreign keys referencing a table (user information for auditing).  Then, on Friday evening, I started to receive this message:



Msg 8621, Level 17, State 1, Procedure MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D, Line 85
The query processor ran out of stack space during query optimization. Please simplify the query.



I have traced it down, this command gets executed:


exec MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D @maxschemaguidforarticle = 'F990B1EE-D52F-4DCA-8EEF-C1783DA47A0F', @type = 6


Which executes this:



Error in SQL server 2000 after the migration (internal query processor error)

I just moved a couple of databases from one instance of SQL 2000 from different hardware to SQL 2000 instance of other hardware and after the move from the application side the app team gets an error -2147217900 - Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.', 'E', '2UA929041X. Does any one have any idea about this. I took a trace while they were testing and I found out this in the trace. exec usp_DlyPrc_LogProcessEvent '-2147217900 - Internal Query Processor Error: The query processor could not produce a query plan.  Contact your primary support provider for more information.', 'E', '2UA929041X An idea or direction on this will be greatly appreciated. Thanks hash

Oracle Linked serve Query performance in 2000 vs 2008 R2 64 Bit

Hi everyone We have started to migrate one of our reporting systems from Sql 2000 to Sql 2008 R2.  One of the steps has been to test the perforance of certain Oracle linked server queries between each server.  We are finding on average 3 fold better perforance stats (in terms of query completion time) on the old server.  This should obviously not be the case.  The new server has signifantly more CPU/Memory/IO resources to play with, and it is 64 bit (not to mention its new!).  Here's what I got so far: Old server: SQL 2000 on W2000 both fully patched.  Old Dell Dual core with 3 GB of Ram running on two soft IDE Mirrored drives (yes I know... it sucks).  It connecting via the MS OLE DB provider for Oracle (9i client) New server: ESX VMware Server with 2 CPU's assigned, 8 GB of ram connected to large HP SAN.  CPU, ram and IO's have all been ruled out as the problem.  We've tried varying network cards with different results so we havent ruled that out yet.  Its connecting via the Oracle provider for OLE DB (11G 64 bit client) The linked server is an Oracle 9i fully patched server.  All three are on the same network backbone. Running a simple select * query on both servers returns the same number of rows (~76 000) .  It takes ~1:20 on the new server and ~0:20 on the old server. In looking at the wait stati

MDX Query optimization

Hello Experts, I have a MDX query which first selects those promotions and products valid for a customer and a particular period. Then those sets are used for data extraction from cube. WITH MEMBER   [MEASURES].[Measure_Value] as Sum   ( { [MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] } ) SET   [Products_Subset] as FILTER ( {[Product].[TU Code]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) SET   [Promotion_Subset] as FILTER ( {[Promotion].[Promotion ID]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) MEMBER   [Measures].[Level2 Customer Code] AS   [Customer].[Hierarchy].CurrentMember.Properties("Level2 Customer Code") SELECT   { [Measures].[Planning Customer Code] ,[MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] ,[Measures].[Comments] } ON COLUMNS , FILTER   (( { [Promotion_Subset]} * { [Products_Subset]} * {[Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004]} * {[Time].[Calendar].[Month].&[7]&[2010]}* {[Country].[Code].&[

SQL query optimization

I have a stored procedure that is taking a long time to run. I have to optimize it. While testing I found that there is a join that is taking a long time : select t1.col1, t1.col2, count(distinct a.personid) e1, count(distinct b.personid) e2 from table1 t1 ...... join messages m with(nolock) on m.messageid = t1.messageid join messagerecipients mr with(nolock) on m.messageid = mr.messageid  left join people a with(nolock) on mr.personid = emp.personid and emp.persontypeid = 2  left join people b with(nolock) on mr.personid = con.personid  and con.persontypeid = 1   The personid in messagerecipients does not have an index. So the join are taking a long time when I find the count in the select statement. I do not intent to modify the production table for now to apply indexes. Is there a way I can optimize my query   Thanks, Reshmi

Query to list currently running queries on the sql server 2000

Hi all Who to check, what all queries are running curently on the sql server 2000, I need to list who all logged in and what queries are getting executed and how long its running. Thanks in advance. shan  

know the hard disk's SQL Server space available from SQL Query



I would be able to determine how much hard disk space is available on the Server with a SQL Query, and from an other computer on the LAN.


How can I do something?


Thanks for your help.

Query execution time from 1 second in SQL 2000 to 20 Seconds in SQL 2008 R2


When I ran this query from SQL 2000 it takes 1 second. now in SQL 2008 R2  the same query is 20 times slower.

This is the query and the numer of records and the time in SQL 2008 R2:

SELECT * FROM dbo.CxP_Saldos 
0 seconds, 923 Rows
SELECT * FROM S_Conta.Cuentas
0 seconds, 662 Rows
SELECT * FROM S_Conta.Cuentas c INNER JOIN dbo.CxP_Saldos s ON c.id = s.CtaProv
18 seconds, 923 Rows

how can I submit the actual execution Plan?

xml query optimization


following query is taking 3 second at my computer. need to optimize the query

declare @TargetResponseXML xml
set @TargetResponseXML = 

	<prospectsurveyresponses pspt_id="100" vcli_id="321" cmp_id="221">
				<surveyelements id="1">
			<page seq="1">
					<question id="2" seq="2" ismandatory="1" isvertical="0">
						<text>Travel through</text>
							<targetresponses id="4">
								<response type="matrix" id="4" seq="4" jumpto="2" isselected="0">
								<response type="matrix" id="5" seq="5" jumpto="3" isselected="0">
								<response type="matrix"

BIDS SQL 2008 sp2 "The query processor could not start the necessary thread resources for parallel q


These cubes have been working in SQL 2005 on another box.  I copied the SQL db and the SSAS Project over to this new box and a fresh install of SQL 2008, sp2 Std Ed, 32 bits. Box has Server 2003 R2 Enterprise Ed SP2,

• 2x 2.3GHZ
• 12GB RAM
• D: 120GB SSD Drive Partition For Data
• C: 73 GB Drive Partition for System
• 1 GB NIC

A few of the dimensions will not refresh, they give the message  
"The query processor could not start the necessary thread resources for parallel query execution.; 42000"
The dimension I have been testing has 34 attributes, all directly related to the key attribute, and no hierarchies. There are 246,000 unique members in the source table. If I make another dimension from the same source table, it refreshes fine as long as there are only a few attributes, but when I add the rest, I get this message again.

Drive C, where Visual Studio is installed, has 69 Gb free disk space. The SQL and SSAS data are on D also has 69 Gb free.

Why was SQL 2005 able to handle this, but SQL 2008 cannot? I am thinking of uninstalling 2008 and going back to SQL 2005. Other suggestions?





SQL 2008 SP2: adding a "persisted computed column" creates an "Internal Query Processor Error: The q

Hi, a simple (and long-existing) stored procedure started throwing this error after adding a persisted computed column on another table that is not used in the stored procedure (but is referenced by a foreign key in the table used in the stored proc.)
Msg 8624, Level 16, State 1, Procedure pSetPersonExtraAnswer, Line 26
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

I think I have traced the "culprit" to the following update.
ALTER TABLE dbo.Persons ADD fullName AS (firstName + ' ' + lastName) PERSISTED

Here is the very weird thing:

If I restore the DB, immediately add the persisted computed column and run the problematic SP call, I get the error in Management Studio.

If I restore the DB, run the problematic SP call and then add the computed column, I no longer get an error from SSMS when I rerun the SP call. But, I still get it from my ASP.NET (via enterprise library) code!

When the error happens, altering the pro

MDX Query Optimization Help!


I have the MDX query below that intermitantly takes forever to execute. Sometimes it runs in 10-20 seconds...others, 7-10 MINUTES! I am new to MDX, so I am not sure what I need to do to optimize the execution. The end result is typically only a few hundred rows of data.

Here's the query. I captured this using the Profiler from a SSRS report which is feeding the StrToSet functions with parms selected by the user.

 MEMBER [Measures].[QtyRTS] AS 
   [Measures].[Molding - Cases RTS] = 0
   ,[Measures].[Molding - Quantity RTS]
   ,[Measures].[Molding - Cases RTS]
  ,[Measures].[Molding - Quantity RTS]
  ,[Measures].[Molding - Quantity Made]
  ,[Measures].[Molding - Mach % Std (Schd Hrs)]
  ,[Measures].[Molding - Mach % Std (Run Hrs)]
  ,[Measures].[PID Labor - Labor Efficiency %]
  ,[Measures].[Downtime Hrs]
  ,[Measures].[Scheduled Hrs]
  ,[Measures].[Downtime (% Schd Hrs)]
  ,[Measures].[Run Hrs]
  ,[Measures].[Unknown Availability Hrs]
  ,[Measures].[Scrap - Scrap (% Made)]
  ,[Measures].[DMR - Quantity Rejected]
  ,[Measures].[DMR - After Rework Rejects]
  ,[Measures].[OEE %]
  ,[Measures].[OEE - Availability %]
  ,[Measures].[OEE - Performance Rate %]
  ,[Measures].[OEE - Yield %]
   [Dim Product].[Product - Family Name].MEMBERS
   ,[Dim Resources].[Machine].MEM

Executing XML query from SQL 2005 pointing to SQL 2000 server

Occurs with root command.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= ADODB.Command  Error Description: Item cannot be found in the collection corresponding to the requested name or ordinal.    Error on Line 18.  The step failed.
Executed as user: DOMAIN\SERVER01. Error Code: 0  Error Source= Microsoft OLE DB Provider for SQL Server  Error Description: Line 1: Incorrect syntax near 'ROOT'.    Error on Line 20.  The step failed.
Dim oCmd, sSQL, oDom
   Set oDom = CreateObject("Microsoft.XMLDOM")
   Set oCmd = CreateObject("ADODB.Command")
  oCmd.ActiveConnection ="Driver={SQL Native Client};Database=Validation;Server=server1;Uid=sa;Pwd=;"
   sSQL = "<?xml version=""1.0"" ?>"
   sSQL = sSQL &

Sharepoint Search Query Optimization



need some help on optimizing my query below. I wanted to do here is put more weight on other resulting items besides documents. Are there any improvement / points /performance optimization to the query?

SELECT "PageMap", "Filename", "Title",  "Created","LastModifiedTime","Author","Path", "Contentclass", "Description", "Rank" ,"ContentType","ContactsM","ContentE","IsDocument" FROM Scope()
CONTAINS("PageMap",'"*{0}*"') RANK BY WEIGHT ( 0.90 ) OR
CONTAINS("Title",'"*{0}*"') RANK BY WEIGHT ( 0.20 ) OR
CONTAINS("ContactsM",'"*{0}*"') RANK BY WEIGHT ( 0.20 )OR
CONTAINS("ContentE",'"*{0}*"') RANK BY WEIGHT ( 0.20 )


ContentType='Contacts' OR
ContentType='Content Editor' OR

   FREETEXT("Title",'"*{0}*"') RANK BY WEIGHT(0.20) OR
FREETEXT("FileName",'"*{0}*"') RANK BY WEIGHT(0.20) OR

Query Optimization


Hi i have a SP which will pull the Data  and another SP to organize that Data into proper format to fit my front end application reports.

below is the SP that we use to do formating the resultset form the original SP




Can Some one optimize this SP, i dont to use the while loop coz i have 18 such SP's and i cant do it in all the SP.

Can we get this kind of out put by using a regular query



Query optimization in sql 2005


 Hi ,

        How to optimize sql query in sql server 2005, any idea

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend