Blog
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?
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?
Comments:
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 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
Where NOT EXISTS (
Select DeptUUID
from dbo.Employee e (nolock)
where d.DeptUUID = e.DeptUUID
)
Notice the "NOT EXISTS" replacement for the earlier "NOT IN"
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
Where NOT EXISTS (
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
© 2011 Andrew Huey
