Tuesday, October 28, 2008
SQL patching fun
After all the patching I did over the weekend, it seemed like everything was fine on my main SQL server. However, on Monday, we discovered that a couple of oddball SELECT statements weren't working anymore, returning the error "Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

To make a very long story short, I eventually figured out that I could get these queries working again by deleting all of last year's data from one of our bigger tables. The table in question had about 32 million rows in it. I cut it down to 13 million after getting rid of the 2007 data. The moral of this story, apparently, is that you shouldn't try to store 30 million rows in a single SQL Server table. I say this with tongue in cheek, since, obviously, there are other people out there with very large tables in SQL Server. Apparently, I can't be one of them, though.

I put a call in the Microsoft support this morning, but they never called me back. Maybe tomorrow. If they do call back, and I get a support guy who seems to be a bit less clueless than usual, maybe I'll try to see if we can figure out why my server chokes on this table. If I get the usual clueless support dude, I guess I'll just tell him I figured it out on my own while I was waiting for a callback. I wonder if I get my support incident credited back if I solve my own problem while I'm waiting for the callback?

Labels: ,

posted by Unknown 10:42 PM

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

I saw your post and found this page by googling the above phrase and "sqlcmd".

I have a query that ... if I create it via sqlcmd.exe..I get the error.

If I manually open up the file (SAME FILE, NO DEVIATION) ... DROP and ADD the procedure...I don't get the error.

Just giving you an FYI.

No solution found yet.
I should have waited a little longer.

I found the offending query:
(Entity Names changed to protect the innocent (haha))

BEFORE (causing the issue with sqlcmd)

--start tsql
delete dbo.Dept
from dbo.Dept
Where DeptUUID NOT IN (
Select DeptUUID
from dbo.Employee e (nolock)
--end tsql

Notice the "NOT IN" clause above:

AFTER (does not cause the issue):

--start tsql
delete dbo.Dept
from dbo.Dept d
Select DeptUUID
from dbo.Employee e (nolock)
where d.DeptUUID = e.DeptUUID
Notice the "NOT EXISTS" replacement for the earlier "NOT IN"
interesting. I just did a google search on "not in" vs "not exists" and found some interesting articles. Maybe I need to re-examine some of the queries where I'm using "not in"...
Post a Comment

This page is powered by Blogger. Isn't yours?
© 2011 Andrew Huey