Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
На таблицу identifiers_list ссылается около 500 foreign key из других таблиц. Есть констрейнты ON CASCADE DELETE глубины на 2-3 таблицы.

Попытка выполнить удаление по первичному ключу одной записи из identifiers_list приводит к ошибке оптимизатора запросов.

Цитаты:

DELETE FROM dbo.identifiers_list lst
  WHERE lst.id_identifier = 31415;

Microsoft SQL Server Developer Edition 9.0.3159

Msg 8621, Level 17, State 2, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.


Не сталкивался ли кто-то с такой проблемой, и если да, то как решил?

И нельзя настроить/увеличить объём пресловутого "stack space" для "query processor"?

П.С.: Вариант уменьшения кол-ва констрейнтов готов обсуждать только при наличии убедительной аргументации со стороны собеседников :) ...
9 апр 08, 11:47    [5523309]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Glory
Member

Откуда:
Сообщений: 104751
BOL
Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional foreign key constraints may be expensive for the query optimizer to process.
9 апр 08, 11:49    [5523320]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Спасибо.

Речь в BOL идёт об одной таблице.
Если я "распределю нагрузку" и дополнительно к

CREATE TABLE dbo.identifiers_list(
  id_identifier INT NOT NULL,
  CONSTRAINT pk_identifiers_list PRIMARY KEY(id_identifier)
)
заведу несколько таблиц-дубликатов типа


CREATE TABLE dbo.identifiers_list_add_refs_x(
  id_identifier INT NOT NULL,
  CONSTRAINT pk_identifiers_list_add_refs_x
    PRIMARY KEY(id_identifier)
)

ALTER TABLE dbo.identifiers_list_add_refs_x
  ADD CONSTRAINT fk_idlistrefsx_idlist
    FOREIGN KEY(id_identifier)
      REFERENCES dbo.identifiers_list (id_identifier)
        ON DELETE CASCADE

А foreign keys, ссылающиеся на identifiers_list распределю между identifiers_list_add_refs_x так, чтобы на одну конкретно взятую таблицу ссылалось не больше 253 foreign keys, как думаете - это поможет? :)
9 апр 08, 12:13    [5523562]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Glory
Member

Откуда:
Сообщений: 104751
Не думаю, что что-то измменится.
Если число таблиц в запросе из-за каскадных операций не уменьшится, а наоборот возрастет, то это никак не уменьшит размер нужного оптимизатору стека
9 апр 08, 12:17    [5523609]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Glory
Не думаю, что что-то изменится.
Если число таблиц в запросе из-за каскадных операций не уменьшится, а наоборот возрастет, то это никак не уменьшит размер нужного оптимизатору стека


У меня есть только предположение, что сервер формирует именно один запрос для проверки отсутствия ссылок (ну типа,
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
)

Поймать что-то подобное в Profiler мне не удалось. То ли из-за слабых ещё навыков использования Profiler, то ли Profiler'у недоступна внутренняя кухня запросов Database Engine.

Если Ваше утверждение действительно верно, то в голове остаётся риторический вопрос "Почему бы Database Engine не предусмотреть вариант деления большого запроса на достаточно мелкие?!"

Ну и следующие варианты действий (в порядке приоритетов) :
1. Отказаться от foreign keys и нагенерировать вместо них триггера. И поддерживать потом
это "хозяйство на триггерах"

а дальше так:

Уже классика
Если запрос никак не хочет делать то, что вам нужно, попробуйте:
1. Поменять запрос
2. Поменять настройки СУБД
3. Поменять схему БД
4. Сменить СУБД
5. Поменять работу

9 апр 08, 13:08    [5524009]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Glory
Member

Откуда:
Сообщений: 104751
Процесс "during query optimization" вы в Профайлере и не увидите. Ибо составление плана выполнения есть внутренний процесс
9 апр 08, 13:44    [5524338]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Glory
Процесс "during query optimization" вы в Профайлере и не увидите. Ибо составление плана выполнения есть внутренний процесс

Я надеялся что проверка констрейнтов - это запрос/пакет сформированный ядром, и что он отправлен на выполнение через тот же вход, который отслеживает Profiler...
9 апр 08, 15:35    [5525193]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Glory
Member

Откуда:
Сообщений: 104751
АнатоЛой
Glory
Процесс "during query optimization" вы в Профайлере и не увидите. Ибо составление плана выполнения есть внутренний процесс

Я надеялся что проверка констрейнтов - это запрос/пакет сформированный ядром, и что он отправлен на выполнение через тот же вход, который отслеживает Profiler...

У вас до выполнения дело не доходит, потому что серверу не хватает ресурсов построить план этого самого выполнения.
9 апр 08, 15:37    [5525216]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
Вот, исправлено в Cumulative update #5
9 апр 08, 16:36    [5525795]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Богдан Гоцкий
Вот, исправлено в Cumulative update #5

Спасибо, рассматривать как вариант уже начал (чуть раньше). Но пока нет особых успехов в получении от Microsoft хоть CU5, хоть CU6...
9 апр 08, 19:26    [5526824]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
АнатоЛой
Богдан Гоцкий
Вот, исправлено в Cumulative update #5

Спасибо, рассматривать как вариант уже начал (чуть раньше). Но пока нет особых успехов в получении от Microsoft хоть CU5, хоть CU6...

На здоровье
9 апр 08, 19:59    [5526937]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Богдан Гоцкий
На здоровье

Спасибо большое! (это 7+ что-ли? :). Я как раз перед этим нашёл и установил CU5
НО:
Ни
SQLServer2005-KB943656-x86-ENU.exe (9.0.3215)
ни предложенный на здоровье SQLServer2005-KB949959-x86-ENU.exe (9.0.3232)
положение не исправили...
10 апр 08, 13:28    [5530073]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
В связи с официальным ограничением в документации про 253 foreign key на таблицу, я конечно не сильно надеялся на CU7 (тем более что в списке исправлений ничего похожего нет), но всё же надежда умирает последней...

CU7 ведёт себя аналогично...

MS SQL 2005 DE 9.0.3239

The query processor ran out of stack space during query optimization. Please simplify the query.

Заодно выяснил, что сервер ведёт себя аналогичным образом и на попытку удаления 0 записей (по заданному условию а-ля
DELETE FROM dbo.identifiers_list lst WHERE lst.id_identifier = -5;
записей в БД нет), что почти смешно

Возник почти праздный вопрос: ну неужели никто не сталкивался с потребностью заполучить больше 253 констрейнтов на таблицу?
15 апр 08, 21:17    [5551298]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
ГСА
Guest
С энфорсом и тем более каскадами? В топку.
15 апр 08, 21:55    [5551411]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
ГСА
С энфорсом и тем более каскадами? В топку.

Спасибо.
Встречный вопрос - а с обеспечением необходимой целостности, но на триггерах, подводные камни будут/были? :) В смысле, если кто-то таки сталкивался с подобной структурой БД. То есть вместо 500 констрейнов получим N триггеров INSERT, UPDATE, DELETE при N таблиц, которые будут обеспечивать целостность при удалении для parent и целостность для вставки/обновлений для child'ов...
16 апр 08, 15:16    [5555570]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
У вас что, для каждой записи в главной таблице есть соотвтветствующие во всех остальных пятиста? Или, как правило в 1й, 2х?
16 апр 08, 15:18    [5555589]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
SkyWalker
Member

Откуда:
Сообщений: 320
Sorry for English (don't have russian keyboard)

This error is stack overflow. Another words we got too many functions on a stack during optimization.
As I undersatnd you have 32 bit SQL - stack size is 0.5 Mb. On 32 bit we can not 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.

If you will upload here repro script I can test it for you on x64 bit.
16 апр 08, 18:51    [5557390]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Гавриленко Сергей Алексеевич
У вас что, для каждой записи в главной таблице есть соотвтветствующие во всех остальных пятиста? Или, как правило в 1й, 2х?

Как правило в 1й...
Так называемый mutually exclusive inheritance, как его обзывает PowerDesigner...
17 апр 08, 11:46    [5559809]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
АнатоЛой
Гавриленко Сергей Алексеевич
У вас что, для каждой записи в главной таблице есть соотвтветствующие во всех остальных пятиста? Или, как правило в 1й, 2х?

Как правило в 1й...
Так называемый mutually exclusive inheritance, как его обзывает PowerDesigner...
Так вот и расскажите, нафига в плане запроса, который удаляет записи из 2х таблиц, будет проверка еще по пятистам?

Я так делал как-то, уже с 50ю не понравилось. Пришлось вырубать и каскады и даже енфорс, и кодить удаление в процедурах. Модификации, к счастью, не было.
17 апр 08, 12:09    [5560018]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
Гавриленко Сергей Алексеевич
АнатоЛой
Гавриленко Сергей Алексеевич
У вас что, для каждой записи в главной таблице есть соотвтветствующие во всех остальных пятиста? Или, как правило в 1й, 2х?

Как правило в 1й...
Так называемый mutually exclusive inheritance, как его обзывает PowerDesigner...
Так вот и расскажите, нафига в плане запроса, который удаляет записи из 2х таблиц, будет проверка еще по пятистам?

Сорри, но я немного неправильно выразился.

Для foreign key c CASCADE DELETE - соответствующая запись будет только в 1-ой из child-таблиц (в одном из "наследников"), и таких ссылок на первом уровне - штук 20. У "наследников" есть свои наследники, кол-во которых тоже может исчисляться десятками...

А для обычных foreign key (без CASCADE DELETE) - записи со ссылками теоретически могут находится во всех таблицах - на то они и foreign key... И там их счёт идёт сотнями...

Гавриленко Сергей Алексеевич

Я так делал как-то, уже с 50ю не понравилось. Пришлось вырубать и каскады и даже енфорс, и кодить удаление в процедурах. Модификации, к счастью, не было.


А до скольки дошло, если не секрет?
17 апр 08, 15:45    [5561984]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
АнатоЛой
Member

Откуда: Киев, Украина
Сообщений: 2897
Блог
И в электронном BOL 2008 те же ограничения...

риторический вопрос
Нет, ну почему в Informix этой проблемы нет, а?
18 апр 08, 04:07    [5564239]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Nick74
Member

Откуда:
Сообщений: 215
Аналогичная проблема. 311 FK на одну таблицу.
Сегодня поставим SP4, проверю изменится ли что-то.
21 июн 11, 11:13    [10845210]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Nick74
Аналогичная проблема. 311 FK на одну таблицу.
Сегодня поставим SP4, проверю изменится ли что-то.


OFFTOP

311 FK - помоему таки консерваторию править таки надо
21 июн 11, 12:04    [10845651]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
Nick74
Member

Откуда:
Сообщений: 215
Maxx
OFFTOP

311 FK - помоему таки консерваторию править таки надо

Опишу ситуацию.

Внутренний документооборот компании.
Около 500 таблиц на данный момент, в большинстве своем содержащих поля а-ля "Автор", "Менеджер", "Пропонент", "Аппрувер" и т.п. - все ссылаются на одну таблицу сотрудников компании.
Слава богу, из таблицы удаление крайне редко, уволенные просто помечаются как уволенные.
Удаление нужно было только для дубликатов.

Как-то, не особо задумываясь, делал FK, т.к. считаю, что он вроде бы как для этого и предназначен.
Предложите более правильный с вашей точки зрения способ.
21 июн 11, 12:14    [10845735]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: большое количество foreign key приводит к ran out of stack space  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Nick74
Maxx
OFFTOP

311 FK - помоему таки консерваторию править таки надо

Опишу ситуацию.

Внутренний документооборот компании.
Около 500 таблиц на данный момент, в большинстве своем содержащих поля а-ля "Автор", "Менеджер", "Пропонент", "Аппрувер" и т.п. - все ссылаются на одну таблицу сотрудников компании.
Слава богу, из таблицы удаление крайне редко, уволенные просто помечаются как уволенные.
Удаление нужно было только для дубликатов.

Как-то, не особо задумываясь, делал FK, т.к. считаю, что он вроде бы как для этого и предназначен.
Предложите более правильный с вашей точки зрения способ.
Если эти сотни таблиц - совершенно одинаковые сущности, то почему это не одна таблица???
Возможно, с полем, по которому различаются записи, сейчас находящиеся в разных таблицах.
21 июн 11, 12:32    [10845856]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить