Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как оптимизировать подобного рода процедуру ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Привет.
Подскажите пожалуйста в чем может быть дело.

Имеется процедурка в БД, которая чистит информацию.

ALTER PROCEDURE [Schema1].[DeleteData]
	@id int
AS
BEGIN
	DELETE FROM [Schema1].[Tab1]
	WHERE [Field1] IN (SELECT  id  FROM [Schema1].[Field2] WHERE Field = @id)
	
	DELETE FROM [Schema1].Tab2
	WHERE  FieldId = @id

        .... еще полтора десятка подобных команд

END



Вызывается она внутри транзакции, которая и сама по себе длится какое-то время.
Все это отваливается по таймауту - на этой процедуре :(

Подскажите пожалуйста в каком направлении копать, чтобы найти причину проблемы. Можно ли как-то оптимизировать подобную процедуру, которая удаляет некие данные из десятка таблиц по ID ?
1 авг 16, 12:04    [19481227]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4823
ProBiotek,

Для начала получить план всех удалений -- посмотреть, где не хватает индексов
1 авг 16, 12:17    [19481318]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
ProBiotek,

может порциями проще удалять и вызывать процедуру в цикле?
1 авг 16, 12:42    [19481440]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4823
Pavel1211
ProBiotek,

может порциями проще удалять и вызывать процедуру в цикле?


Я так понят идёт удаление данных связанных с какой-то сущностью (пользователем, например). Данные надо удалить в транзакции. Если это занимает много времени -- можно делать это синхронно в транзакции с большим таймаутом.
1 авг 16, 12:44    [19481458]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
ProBiotek
Member

Откуда: Moscow
Сообщений: 907
Ну да, удаление сущности и дерево всех ее зависимостей.
1 авг 16, 12:45    [19481465]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8494
ProBiotek,

а что не так? Вам просто не хватает времени на все удаления. Увеличьте таймаут.
1 авг 16, 16:11    [19482860]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47066
Может, определить FK с каскадным удалением?
Удалять из основной таблицы небольшими порциями в цикле, пока удалять будет нечего.
1 авг 16, 16:13    [19482872]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47066
iap
Может, определить FK с каскадным удалением?
Удалять из основной таблицы небольшими порциями в цикле, пока удалять будет нечего.
Да, и надо помнить, что FK сами по себе не заменят правильных индексов.
1 авг 16, 16:15    [19482892]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4985
a_voronin
ProBiotek,

Для начала получить план всех удалений -- посмотреть, где не хватает индексов

+ поудалять возможные лишние индексы, на перестройку которых при удалении тоже тратится процессорное время.
1 авг 16, 16:49    [19483095]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
o-o
Guest
Megabyte
a_voronin
ProBiotek,

Для начала получить план всех удалений -- посмотреть, где не хватает индексов

+ поудалять возможные лишние индексы, на перестройку которых при удалении тоже тратится процессорное время.

и давно индексы у нас ПЕРЕСТРАИВАЮТСЯ при удалении данных из таблицы?
и поди, если не энтерпрайз, так они еще и оффлайново ребилдятся, да?
1 авг 16, 17:10    [19483229]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8494
o-o,

ну все равно есть накладные расходы на обновление страниц с индексами. Пусть автор и неточно выразился. Однако, удалять - это слишком сурово, можно индекс отключить и перестроить после того.
1 авг 16, 17:19    [19483271]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
o-o
Guest
Владислав Колосов
o-o,

ну все равно есть накладные расходы на обновление страниц с индексами.

а я с этим не спорю.
просто надо ж следить за выражениями,
одно дело обновить индекс, другое дело перестроить
1 авг 16, 17:23    [19483297]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Владислав Колосов
можно индекс отключить и перестроить после того

Ну как бы... когда индекс отключается - все страницы которые он занимал помечаются свободными. Фактически бы делаем DROP INDEX. Только метаданные о параметрах индекса остаются. А когда заново включаем, то он заново создается.
1 авг 16, 17:25    [19483308]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
Владислав Колосов
ну все равно есть накладные расходы на обновление страниц с индексами.
Практически отсутствуют в транзакции, где происходит удаление - за счет механизма ghost records/ghost cleanup
Владислав Колосов
Однако, удалять - это слишком сурово, можно индекс отключить и перестроить после того.
А отключить и перестроить не слишком сурово по отношению к конкурирующим процессам, где этот индекс задействован?
1 авг 16, 17:49    [19483407]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4074
Владислав Колосов
ProBiotek,

а что не так? Вам просто не хватает времени на все удаления. Увеличьте таймаут.


Можно получить блокировки, если кто-то или что-то работает в это время.
Вообще-то, все от задачи зависит. Можно удалять и асинхронно, когда минимальная нагрузка, через джобы, можно кусками и т.д..
1 авг 16, 17:58    [19483449]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
msLex
Member

Откуда:
Сообщений: 8856
[quot invm]
Владислав Колосов
ну все равно есть накладные расходы на обновление страниц с индексами.
Практически отсутствуют в транзакции, где происходит удаление - за счет механизма ghost records/ghost cleanup
[quot Владислав Колосов]

Только вот записи в этих индексах, которые нужно пометить как ghost, сначала нужно найти, а это +1 index seek на каждую удаляемую строку умноженное на количество индексов.
1 авг 16, 19:04    [19483726]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
msLex
Member

Откуда:
Сообщений: 8856
квотирование поехало

invm
Владислав Колосов
ну все равно есть накладные расходы на обновление страниц с индексами.
Практически отсутствуют в транзакции, где происходит удаление - за счет механизма ghost records/ghost
cleanup


Только вот записи в этих индексах, которые нужно пометить как ghost, сначала нужно найти, а это +1 index seek на каждую удаляемую строку умноженное на количество индексов.
1 авг 16, 19:05    [19483731]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
msLex
Только вот записи в этих индексах, которые нужно пометить как ghost, сначала нужно найти
Ну так чтобы удалить или изменить, или просто выбрать, их тоже нужно найти ;)
1 авг 16, 19:47    [19483847]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
msLex
Member

Откуда:
Сообщений: 8856
invm
msLex
Только вот записи в этих индексах, которые нужно пометить как ghost, сначала нужно найти
Ну так чтобы удалить или изменить, или просто выбрать, их тоже нужно найти ;)

Если честно, не совсем понятно о чем вы.

Основной поинт был в том, что дополнительные индексы уменьшат скорость удаления записи.
Как я понял, вы утверждали, что нет, т.к. есть отложенный ghost cleanup.
Я объяснил почему отложенный ghost cleanup, не отменяет замедления процесса удаления записей из таблицы при увеличении числа индексов.
1 авг 16, 19:57    [19483873]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
invm
Member

Откуда: Москва
Сообщений: 9688
msLex
Основной поинт был в том, что дополнительные индексы уменьшат скорость удаления записи.
Как я понял, вы утверждали, что нет, т.к. есть отложенный ghost cleanup.
Зависит от того как трактовать фразу "обновление страниц с индексами".
В моем понимании, подразумевалось физическое удаление строк в индексе. Поэтому и был упомянут ghost cleanup.
1 авг 16, 20:43    [19483991]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4985
Владислав Колосов
o-o,

ну все равно есть накладные расходы на обновление страниц с индексами. Пусть автор и неточно выразился. Однако, удалять - это слишком сурово, можно индекс отключить и перестроить после того.

Я имел ввиду лишние в прямом смысле индексы, которые мог сделать предыдущий не совсем грамотный в этом разработчик.
Я на текущем месте уже, наверное, не одну сотню лишних(по полям, не используемым в поиске)\пересекающихся\дублирующих индексов удалил.
Сами знаете, много индексов - это так же плохо, как и их отсутствие...
1 авг 16, 21:41    [19484115]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Megabyte
Member

Откуда: ближайшее заМКАДье
Сообщений: 4985
o-o
Владислав Колосов
o-o,

ну все равно есть накладные расходы на обновление страниц с индексами.

а я с этим не спорю.
просто надо ж следить за выражениями,
одно дело обновить индекс, другое дело перестроить

Прошу прощения, оговорился)
1 авг 16, 21:42    [19484117]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
msLex
Member

Откуда:
Сообщений: 8856
invm
msLex
Основной поинт был в том, что дополнительные индексы уменьшат скорость удаления записи.
Как я понял, вы утверждали, что нет, т.к. есть отложенный ghost cleanup.
Зависит от того как трактовать фразу "обновление страниц с индексами".
В моем понимании, подразумевалось физическое удаление строк в индексе. Поэтому и был упомянут ghost cleanup.

А пометка рекорд ghost разве не является обновлением страниц с индексами?

ЗЫ
Само удаление рекорды несильно отличается от пометки ее как ghost, а вот удаление последней записи со страницы, это да, в этом случае требуется обновить минимум 4 страницы в самом индексе (саму страницу, слева, справа и "сверху"), и это если повезет, а если совсем не повезет, то есть шанс "попасть" на уменьшение глубины дерева.
1 авг 16, 23:05    [19484303]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать подобного рода процедуру ?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8494
o-o
Владислав Колосов
o-o,

ну все равно есть накладные расходы на обновление страниц с индексами.

а я с этим не спорю.
просто надо ж следить за выражениями,
одно дело обновить индекс, другое дело перестроить


Хорошее замечание, т.к. математика - точная наука :)
Что касается отключение индекса и перестройки, то это удобнее для ДБА - не надо думать о метаданных.
А что за шум вокруг "повлияет на запросы"? Никаких запросов не должно быть при ТО базы, для этого выделяются сервисные часы. Для 24/7 выделяются сервера. Всё решаемо.
2 авг 16, 14:20    [19487016]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить