September 24, 2008 at 8:58 am
Hi,
We have a database with around 2500 tables. Our system is multi-company so we have a Table Company with an ID and some other fields. We also have around 400 tables with the foreign key IDCompany, tables are like Products (ID, IDCompany, ...), Clients (ID, IDCompany, ...), PriceList (ID, IDCompany, ...), etc
When we want to delete a company we got the following error.
Server: Msg 8621, Level 17, State 1, Line 1
Internal Query Processor Error: The query processor ran out of stack space during query optimization.
BOL specity Foreign key table references per table to a maximum of 253. But I only have one reference to IdCompany by table. Should I didn;t understand something? What's wrong with that design? Any workaround or idea why I got this error?
September 24, 2008 at 9:48 am
Here's what I try...
I Try to delete FKs in each corresponding table with this script. I can only delete in Gen_object if the referring number of table is Below 296.
IF EXISTS (SELECT Object_Id FROM SYS.Objects WHERE Name = FK_PRICELIST _GEN_Object_IdList)
ALTER TABLE PRICELIST NOCHECK CONSTRAINT FK_PRICELIST _GEN_Object_IdList
GO
DELETE GEN_Object WHERE IdObject = 3971346541710090000 --Can't delete if more the 296 table are
--pointing on Gen_Object
GO
IF EXISTS (SELECT Object_Id FROM SYS.Objects WHERE Name 'FK_ACH_ModelProdCom_GEN_Object_IdModel')
ALTER TABLE ModelProdCom NOCHECK CONSTRAINT FK_ACH_ModelProdCom_GEN_Object_IdModel
GO
DELETE GEN_Object WHERE IdObject = 3971346541710090000
GO
Any Ideas?
September 24, 2008 at 2:39 pm
Got It,
For those who want to know
This error is a stack overflow. Another words we got too many functions on a stack during optimization.
SELECT id_ident FROM tbl1 WHERE id_ident = x
UNION
SELECT id_ident FROM tbl2 WHERE id_ident = x
...
UNION
SELECT id_ident FROM tblN WHERE id_ident = x
For the Foreign Key check
As we were running a 32 bit SQL - stack size is 0.5 Mb. On 32 bit we can't go above this because of limitation in virtual address space. With 256 worker threads that we have by default on 32 bit with less or equal to 4 CPUs we will have to have 256 * 0.5Mb = 128Mb of virtual address space just for stacks.
Total available virtual address space for user mode process - 2Gb by default.
Theoretically you may get an advantage of switching to 64 bit platform where stack size is 2Mb and that is!!
WoW
November 5, 2008 at 8:08 am
It sounds like you just have too many FK's against your company table. At this point, the FK relationship should be maintained via code, and not via the internal FK logic.
If you have more than a hand ful of FK's, for any given column, then the design needs some adjustment, or as stated above, do it via code.
The more you are prepared, the less you need it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply