February 24, 2013 at 5:29 am
Greetings,
I've got one more strange problem.
There is a big table [Indicators] in my database. It contains something like 200 million records. Fields:
bigint NOT NULL,
[KEY_Source] int NOT NULL,
[dateStart] date NULL,
[dateEnd] date NULL,
[ratePopularity] int NULL,
[rateFullness] int NULL,
[rateDataDynamic] int NULL,
[rateCorrection] int NULL,
[Key] is the primary key. Index for the primary key is a clustered one.
Please consider the query:
select * from Indicators where [Key] between 402807420000000000 and 402807429999999999
Very effective plan, runs very quickly, no records.
(Well, for different values it can return some records - very quickly and efficiently).
Now consider the following query:
delete from Indicators where [Key] between 402807420000000000 and 402807429999999999
Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan.
The same for just the seek:
select from Indicators where [Key] = 402889540000000001
KEY
402889540000000001
delete from Indicators where [Key] = 402889540000000001
Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan.
What is going on? There is a plain index scan, no rocket science. No joins, just a scan. What is wrong with me or my server?
Server version is 10.50.2811. Should we install SP2?
February 24, 2013 at 5:43 am
Oh dear. Oh dear dear. Such an idiot I am.
There are several thousand(!) FOREIGN KEYS to this table from several thousand other tables. With cascade delete.
The server tries to delete records in all those tables. And fails. There are tooo many of them.
Well. Thanks to everyone - I will never crack this case without writing the proper question.
(Let's remove these damned constraints!).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply