Showing posts with label win. Show all posts
Showing posts with label win. Show all posts

Monday, March 26, 2012

Error: 8624 Internal Query Processor Error: The query processor could not produce a query plan.

SQL Server 2005 9.0.3161 on Win 2k3 R2

I receive the following error:

"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I have traced this to an insert statement that executes as part of a stored procedure.

INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@.journal_id, @.acct_id, @.acct_recv_id, @.amount)

There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.

Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

I'd suggest calling PSS. It does sound like a bug here.

|||

I ran into this exact problem today, and it turned out to be related to a catalog being inconsistent, when we performed a migration from a SQL 2000 server to a SQL 2005 server, by restoring a backup . There was a foreign key constraint that was not functioning correctly following the migration and that was causing the error to show up.

We had restored the SQL 2000 backup to a 2005 server, then changed the compatability mode from 80 to 90, then updated statistics. We did not however, run the DBCC following all of that. We had run tests prior to the migration, but this hadn't showed up as an issue until the day of the migration. As a matter of fact, we had run DBCC's on the SQL 2000 database and things were fine. We checked the backup file, and that was fine. The problem was in some part of the page migrations that happen within the engine itself. Maybe this was a page alignment issue, whereby a given catalog page was in a decent state for migration when we were testing, but changed it's alignment on a given page between the time we tested and the time that we migrated. Who know....that's just my speculation.

The error from running a simple insert statement looks like this:

Msg 8624, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

The error from the DBCC CHECKDB looks like this:

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=238623893,key_index_id=3) of row (object_id=951674438) in sys.foreign_keys does not have a matching row (object_id=238623893,index_id=3) in sys.indexes.

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

This lead us to the conclusion that we could drop the FK and recreate it, and have everything work. And, it did.

The moral of the story, like it's been said many times...run DBCC's after restores when going from SQL 2000 to SQL 2005.

Hope this helps someone else in the future.

-- Don

|||

I consulted tech support. It was finally classified as a bug. The database was in 80 compatibility and changing it to 90 made the problem go away. The other work around was to set arithabort on. This explained why it worked from management studio and not anywhere else. Apparently management studio has arithabort set to on by default. The following is the case closure confirmation from the MS engineer:

PROBLEM:

=======

An insert query to a table that has foreign key references cannot generate a plan with error 8624 when arithabort is set to be off.

Server: Msg 8624, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

AGREED UPON CRITERIA FOR RESOLUTION:

===========================

Investigate root cause

CAUSE:

=====

This issue appears to be related to the fact database compatibility is set to 80.I have filed a product bug for tracking purpose

RESOLUTION:

===========

Setting database compatibility to be 90 resolved the issue

Error: 8624 Internal Query Processor Error: The query processor could not produce a query plan.

SQL Server 2005 9.0.3161 on Win 2k3 R2

I receive the following error:

"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I have traced this to an insert statement that executes as part of a stored procedure.

INSERTINTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES(@.journal_id, @.acct_id, @.acct_recv_id, @.amount)

There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.

Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

I'd suggest calling PSS. It does sound like a bug here.

|||

I ran into this exact problem today, and it turned out to be related to a catalog being inconsistent, when we performed a migration from a SQL 2000 server to a SQL 2005 server, by restoring a backup . There was a foreign key constraint that was not functioning correctly following the migration and that was causing the error to show up.

We had restored the SQL 2000 backup to a 2005 server, then changed the compatability mode from 80 to 90, then updated statistics. We did not however, run the DBCC following all of that. We had run tests prior to the migration, but this hadn't showed up as an issue until the day of the migration. As a matter of fact, we had run DBCC's on the SQL 2000 database and things were fine. We checked the backup file, and that was fine. The problem was in some part of the page migrations that happen within the engine itself. Maybe this was a page alignment issue, whereby a given catalog page was in a decent state for migration when we were testing, but changed it's alignment on a given page between the time we tested and the time that we migrated. Who know....that's just my speculation.

The error from running a simple insert statement looks like this:

Msg 8624, Level 16, State 1, Line 1

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

The error from the DBCC CHECKDB looks like this:

Msg 8992, Level 16, State 1, Line 1

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=238623893,key_index_id=3) of row (object_id=951674438) in sys.foreign_keys does not have a matching row (object_id=238623893,index_id=3) in sys.indexes.

CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.

This lead us to the conclusion that we could drop the FK and recreate it, and have everything work. And, it did.

The moral of the story, like it's been said many times...run DBCC's after restores when going from SQL 2000 to SQL 2005.

Hope this helps someone else in the future.

-- Don

|||

I consulted tech support. It was finally classified as a bug. The database was in 80 compatibility and changing it to 90 made the problem go away. The other work around was to set arithabort on. This explained why it worked from management studio and not anywhere else. Apparently management studio has arithabort set to on by default. The following is the case closure confirmation from the MS engineer:

PROBLEM:

=======

An insert query to a table that has foreign key references cannot generate a plan with error 8624 when arithabort is set to be off.

Server: Msg 8624, Level 16, State 1, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

AGREED UPON CRITERIA FOR RESOLUTION:

===========================

Investigate root cause

CAUSE:

=====

This issue appears to be related to the fact database compatibility is set to 80.I have filed a product bug for tracking purpose

RESOLUTION:

===========

Setting database compatibility to be 90 resolved the issue

Error: 701 Insufficient Memory - Nightly Data Update

We are running a relatively small data warehouse on a 2 CPU Dell server w/ 4
GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other load
on this server is IIS running our reporting application.
Our nightly data updates are a collection of relatively simple stored
procedures that extract data from DB2 on an AS/400 into the DW in a series o
f
stages. We're moving approximately 1 GB of data each night using the job
scheduler in SQL Server agent.
These nightly jobs fail at various steps with the following error:
"Executed as user: sa. There is insufficient system memory to run this
query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
terminated. [SQLSTATE 01000] (Error 3621). The step failed."
I am eventually able to get each job to complete by restarting SQL Server
and SQL Server Agent after failure, and then restarting the jobs from the
last failed step. This is obviously an unacceptable solution.
I've read some of the other posts on similar problems, but none of the
solutions provided have been applicable to our problem.
This appears to be a memory leak. We have increased the memory available to
SQL Server as well as the virtual memory. These steps have simply delayed th
e
problem so that it occurs later in the process.
Can anyone provide any insights or guidance on how to address this problem?
--
Thanks, KenFirst hopefully you have enabled AWE, are running SQL Enterprise Ed. and
have applied the memory hotfix. But none of that would help directly with
error 1750 if it is the root cause.
Second hopefully, you don't have all your data files on the same disk on a
RAID 5 array. If you do, watch your CPUs when your loading. They will go
high and stay there if you are waiting on the disk. If you are waiting on
the disk, the DB2 connection could be trying to push when your system
doesn't have the resources.
some thoughts that might offer some leads.
d.
"Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> We are running a relatively small data warehouse on a 2 CPU Dell server w/
4
> GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other
load
> on this server is IIS running our reporting application.
> Our nightly data updates are a collection of relatively simple stored
> procedures that extract data from DB2 on an AS/400 into the DW in a series
of
> stages. We're moving approximately 1 GB of data each night using the job
> scheduler in SQL Server agent.
> These nightly jobs fail at various steps with the following error:
> "Executed as user: sa. There is insufficient system memory to run this
> query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
> previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621). The step failed."
> I am eventually able to get each job to complete by restarting SQL Server
> and SQL Server Agent after failure, and then restarting the jobs from the
> last failed step. This is obviously an unacceptable solution.
> I've read some of the other posts on similar problems, but none of the
> solutions provided have been applicable to our problem.
> This appears to be a memory leak. We have increased the memory available
to
> SQL Server as well as the virtual memory. These steps have simply delayed
the
> problem so that it occurs later in the process.
> Can anyone provide any insights or guidance on how to address this
problem?
> --
> Thanks, Ken|||Thanks D,
My problem is still not solved, but I thought I would give an update for
anyone else experiencing similar problems.
We are running SQL 2000 Enterprise Edition SP4; we have AWE enabled; data
files spread across the RAID array; and I did install the hotfix described i
n
the article: http://support.microsoft.com/kb/899761. In fact I installed a
cumulative hotfix that includes all hotfixes for SP4
(http://www.microsoft.com/downloads/...&displaylang=en).
I did discover that we had set 'Max degree of Parallelism' to 1, which means
we are only using 1 of the 2 processors available. It turns out that prior t
o
the 701 "Insufficient Memory" error, our jobs were failing with the error:
"The query processor could not start the necessary thread resources for
parallel query execution. [SQLSTATE 42000] (Error 8642)."
We circumvented this problem by using only 1 CPU. I would love to know if
anyone else has handled this error before. I can't find anything on Technet
or other SQL Server forums relating to this error.
I currently have a case opened with MSFT support. They have directed me to
disable AWE since it is not needed for only 4GB RAM. I've run several MSFT
analysis, profiling, and perf monitoring tools which have so far not
identified the root cause of the problem.
One thing I recently realized is that we are running Windows 2003 Standard
edition, which has more memory limitations than Enterprise edition. I'll be
researching this more tomorrow. Meanwhile, if any experts can shed light on
how this may relate to my problem. I would greatly appreciate your ideas.
--
Thanks, Ken
"d" wrote:

> First hopefully you have enabled AWE, are running SQL Enterprise Ed. and
> have applied the memory hotfix. But none of that would help directly with
> error 1750 if it is the root cause.
> Second hopefully, you don't have all your data files on the same disk on a
> RAID 5 array. If you do, watch your CPUs when your loading. They will go
> high and stay there if you are waiting on the disk. If you are waiting on
> the disk, the DB2 connection could be trying to push when your system
> doesn't have the resources.
> some thoughts that might offer some leads.
> d.
> "Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
> news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> 4
> load
> of
> to
> the
> problem?
>
>

Error: 701 Insufficient Memory - Nightly Data Update

We are running a relatively small data warehouse on a 2 CPU Dell server w/ 4
GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other load
on this server is IIS running our reporting application.
Our nightly data updates are a collection of relatively simple stored
procedures that extract data from DB2 on an AS/400 into the DW in a series of
stages. We're moving approximately 1 GB of data each night using the job
scheduler in SQL Server agent.
These nightly jobs fail at various steps with the following error:
"Executed as user: sa. There is insufficient system memory to run this
query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
terminated. [SQLSTATE 01000] (Error 3621). The step failed."
I am eventually able to get each job to complete by restarting SQL Server
and SQL Server Agent after failure, and then restarting the jobs from the
last failed step. This is obviously an unacceptable solution.
I've read some of the other posts on similar problems, but none of the
solutions provided have been applicable to our problem.
This appears to be a memory leak. We have increased the memory available to
SQL Server as well as the virtual memory. These steps have simply delayed the
problem so that it occurs later in the process.
Can anyone provide any insights or guidance on how to address this problem?
--
Thanks, KenFirst hopefully you have enabled AWE, are running SQL Enterprise Ed. and
have applied the memory hotfix. But none of that would help directly with
error 1750 if it is the root cause.
Second hopefully, you don't have all your data files on the same disk on a
RAID 5 array. If you do, watch your CPUs when your loading. They will go
high and stay there if you are waiting on the disk. If you are waiting on
the disk, the DB2 connection could be trying to push when your system
doesn't have the resources.
some thoughts that might offer some leads.
d.
"Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> We are running a relatively small data warehouse on a 2 CPU Dell server w/
4
> GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other
load
> on this server is IIS running our reporting application.
> Our nightly data updates are a collection of relatively simple stored
> procedures that extract data from DB2 on an AS/400 into the DW in a series
of
> stages. We're moving approximately 1 GB of data each night using the job
> scheduler in SQL Server agent.
> These nightly jobs fail at various steps with the following error:
> "Executed as user: sa. There is insufficient system memory to run this
> query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
> previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621). The step failed."
> I am eventually able to get each job to complete by restarting SQL Server
> and SQL Server Agent after failure, and then restarting the jobs from the
> last failed step. This is obviously an unacceptable solution.
> I've read some of the other posts on similar problems, but none of the
> solutions provided have been applicable to our problem.
> This appears to be a memory leak. We have increased the memory available
to
> SQL Server as well as the virtual memory. These steps have simply delayed
the
> problem so that it occurs later in the process.
> Can anyone provide any insights or guidance on how to address this
problem?
> --
> Thanks, Ken|||Thanks D,
My problem is still not solved, but I thought I would give an update for
anyone else experiencing similar problems.
We are running SQL 2000 Enterprise Edition SP4; we have AWE enabled; data
files spread across the RAID array; and I did install the hotfix described in
the article: http://support.microsoft.com/kb/899761. In fact I installed a
cumulative hotfix that includes all hotfixes for SP4
(http://www.microsoft.com/downloads/details.aspx?familyid=4773BF7E-21AE-4F1E-AD48-6CA739E10217&displaylang=en).
I did discover that we had set 'Max Degree of Parallelism' to 1, which means
we are only using 1 of the 2 processors available. It turns out that prior to
the 701 "Insufficient Memory" error, our jobs were failing with the error:
"The query processor could not start the necessary thread resources for
parallel query execution. [SQLSTATE 42000] (Error 8642)."
We circumvented this problem by using only 1 CPU. I would love to know if
anyone else has handled this error before. I can't find anything on Technet
or other SQL Server forums relating to this error.
I currently have a case opened with MSFT support. They have directed me to
disable AWE since it is not needed for only 4GB RAM. I've run several MSFT
analysis, profiling, and perf monitoring tools which have so far not
identified the root cause of the problem.
One thing I recently realized is that we are running Windows 2003 Standard
edition, which has more memory limitations than Enterprise edition. I'll be
researching this more tomorrow. Meanwhile, if any experts can shed light on
how this may relate to my problem. I would greatly appreciate your ideas.
--
Thanks, Ken
"d" wrote:
> First hopefully you have enabled AWE, are running SQL Enterprise Ed. and
> have applied the memory hotfix. But none of that would help directly with
> error 1750 if it is the root cause.
> Second hopefully, you don't have all your data files on the same disk on a
> RAID 5 array. If you do, watch your CPUs when your loading. They will go
> high and stay there if you are waiting on the disk. If you are waiting on
> the disk, the DB2 connection could be trying to push when your system
> doesn't have the resources.
> some thoughts that might offer some leads.
> d.
> "Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
> news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> > We are running a relatively small data warehouse on a 2 CPU Dell server w/
> 4
> > GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other
> load
> > on this server is IIS running our reporting application.
> >
> > Our nightly data updates are a collection of relatively simple stored
> > procedures that extract data from DB2 on an AS/400 into the DW in a series
> of
> > stages. We're moving approximately 1 GB of data each night using the job
> > scheduler in SQL Server agent.
> >
> > These nightly jobs fail at various steps with the following error:
> >
> > "Executed as user: sa. There is insufficient system memory to run this
> > query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
> > previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
> > terminated. [SQLSTATE 01000] (Error 3621). The step failed."
> >
> > I am eventually able to get each job to complete by restarting SQL Server
> > and SQL Server Agent after failure, and then restarting the jobs from the
> > last failed step. This is obviously an unacceptable solution.
> >
> > I've read some of the other posts on similar problems, but none of the
> > solutions provided have been applicable to our problem.
> >
> > This appears to be a memory leak. We have increased the memory available
> to
> > SQL Server as well as the virtual memory. These steps have simply delayed
> the
> > problem so that it occurs later in the process.
> >
> > Can anyone provide any insights or guidance on how to address this
> problem?
> > --
> > Thanks, Ken
>
>sql

Error: 701 Insufficient Memory - Nightly Data Update

We are running a relatively small data warehouse on a 2 CPU Dell server w/ 4
GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other load
on this server is IIS running our reporting application.
Our nightly data updates are a collection of relatively simple stored
procedures that extract data from DB2 on an AS/400 into the DW in a series of
stages. We're moving approximately 1 GB of data each night using the job
scheduler in SQL Server agent.
These nightly jobs fail at various steps with the following error:
"Executed as user: sa. There is insufficient system memory to run this
query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
terminated. [SQLSTATE 01000] (Error 3621). The step failed."
I am eventually able to get each job to complete by restarting SQL Server
and SQL Server Agent after failure, and then restarting the jobs from the
last failed step. This is obviously an unacceptable solution.
I've read some of the other posts on similar problems, but none of the
solutions provided have been applicable to our problem.
This appears to be a memory leak. We have increased the memory available to
SQL Server as well as the virtual memory. These steps have simply delayed the
problem so that it occurs later in the process.
Can anyone provide any insights or guidance on how to address this problem?
Thanks, Ken
First hopefully you have enabled AWE, are running SQL Enterprise Ed. and
have applied the memory hotfix. But none of that would help directly with
error 1750 if it is the root cause.
Second hopefully, you don't have all your data files on the same disk on a
RAID 5 array. If you do, watch your CPUs when your loading. They will go
high and stay there if you are waiting on the disk. If you are waiting on
the disk, the DB2 connection could be trying to push when your system
doesn't have the resources.
some thoughts that might offer some leads.
d.
"Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> We are running a relatively small data warehouse on a 2 CPU Dell server w/
4
> GB RAM running Win 2003 Server and SQL Server 2000 SP4. The only other
load
> on this server is IIS running our reporting application.
> Our nightly data updates are a collection of relatively simple stored
> procedures that extract data from DB2 on an AS/400 into the DW in a series
of
> stages. We're moving approximately 1 GB of data each night using the job
> scheduler in SQL Server agent.
> These nightly jobs fail at various steps with the following error:
> "Executed as user: sa. There is insufficient system memory to run this
> query. [SQLSTATE 42000] (Error 701) Could not create constraint. See
> previous errors. [SQLSTATE 42000] (Error 1750) The statement has been
> terminated. [SQLSTATE 01000] (Error 3621). The step failed."
> I am eventually able to get each job to complete by restarting SQL Server
> and SQL Server Agent after failure, and then restarting the jobs from the
> last failed step. This is obviously an unacceptable solution.
> I've read some of the other posts on similar problems, but none of the
> solutions provided have been applicable to our problem.
> This appears to be a memory leak. We have increased the memory available
to
> SQL Server as well as the virtual memory. These steps have simply delayed
the
> problem so that it occurs later in the process.
> Can anyone provide any insights or guidance on how to address this
problem?
> --
> Thanks, Ken
|||Thanks D,
My problem is still not solved, but I thought I would give an update for
anyone else experiencing similar problems.
We are running SQL 2000 Enterprise Edition SP4; we have AWE enabled; data
files spread across the RAID array; and I did install the hotfix described in
the article: http://support.microsoft.com/kb/899761. In fact I installed a
cumulative hotfix that includes all hotfixes for SP4
(http://www.microsoft.com/downloads/d...displaylang=en).
I did discover that we had set 'Max Degree of Parallelism' to 1, which means
we are only using 1 of the 2 processors available. It turns out that prior to
the 701 "Insufficient Memory" error, our jobs were failing with the error:
"The query processor could not start the necessary thread resources for
parallel query execution. [SQLSTATE 42000] (Error 8642)."
We circumvented this problem by using only 1 CPU. I would love to know if
anyone else has handled this error before. I can't find anything on Technet
or other SQL Server forums relating to this error.
I currently have a case opened with MSFT support. They have directed me to
disable AWE since it is not needed for only 4GB RAM. I've run several MSFT
analysis, profiling, and perf monitoring tools which have so far not
identified the root cause of the problem.
One thing I recently realized is that we are running Windows 2003 Standard
edition, which has more memory limitations than Enterprise edition. I'll be
researching this more tomorrow. Meanwhile, if any experts can shed light on
how this may relate to my problem. I would greatly appreciate your ideas.
Thanks, Ken
"d" wrote:

> First hopefully you have enabled AWE, are running SQL Enterprise Ed. and
> have applied the memory hotfix. But none of that would help directly with
> error 1750 if it is the root cause.
> Second hopefully, you don't have all your data files on the same disk on a
> RAID 5 array. If you do, watch your CPUs when your loading. They will go
> high and stay there if you are waiting on the disk. If you are waiting on
> the disk, the DB2 connection could be trying to push when your system
> doesn't have the resources.
> some thoughts that might offer some leads.
> d.
> "Have Mercy" <HaveMercy@.discussions.microsoft.com> wrote in message
> news:4369847C-CC04-4762-9B75-5A2D393F4241@.microsoft.com...
> 4
> load
> of
> to
> the
> problem?
>
>