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

Откуда: Moscow
Сообщений: 610
Доброго времени суток!

СУБД:Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Есть процедура A в кторой есть следующий код:

DELETE
    Table1
    FROM Table1 p WITH (ROWLOCK INDEX = XPKTable1)
    WHERE p.SPID = @@SPID


Когда с этой процедурой работают много пользователей возникают длительные блокировки по таблице Table1.
Вопрос: Можно ли как нибудь оптимизировать удаление? И почему конструкция ROWLOCK накладывает блокировку на целую таблицу?

К сообщению приложен файл. Размер - 19Kb
18 дек 12, 12:52    [13648106]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
aleks2
Guest
Да.
1. Сделать кластерный индекс Table1 по SPID и
2. ликвидировать все остальные индексы
3. или обеспечить, чтобы все они начинались в поля SPID.
18 дек 12, 12:58    [13648154]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
offtop это не Диасофт с "p" таблицами ?
18 дек 12, 13:01    [13648183]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
_djХомяГ,

именно он
18 дек 12, 13:07    [13648217]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
1 Уверенны что это случается только при DELETE , а не при наполнении и изменении данных (UPDATE) "p" таблицы
2 Было такое именно при UPDATE, отказались как от p таблицы так и штатной процедуры рассчета - начитывали и обрабатывали времянку
18 дек 12, 13:12    [13648251]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
_djХомяГ
Уверенны что это случается только при DELETE , а не при наполнении и изменении данных (UPDATE) "p" таблицы


судя по мониторинга 97% времени блокировок происходит именно из за DELETE

aleks2
1. Сделать кластерный индекс Table1 по SPID и
2. ликвидировать все остальные индексы
3. или обеспечить, чтобы все они начинались в поля SPID.

в таблице только 1 кластреный индекс по полю SPID
18 дек 12, 13:41    [13648466]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
все же интересно почему при использовании ROWLOCK блокируется вся таблица?
18 дек 12, 13:43    [13648478]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
andrew shalaev
И почему конструкция ROWLOCK накладывает блокировку на целую таблицу?

Это может произойти из-за эскалации блокировок (оно же "укрупнение блокировок", в терминологии Microsoft. Укрупнение блокировки

По сколько записей может удаляться за раз?
18 дек 12, 13:46    [13648504]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
1 Поправка: вроде бы Диасофт не взлюбил кластерные индексы - поэтому индекс скорее всего некластерный
2 Блокировки долговременные - если да то не уверен, что они из за DELETE Попробуйте ради эксперимента оставить в функционале только DELETE и закомментировать остальную часть кода (в тч хранимки) чтобы быть уверенными на 100% Может анализируете не тот Delete - мало ли что
18 дек 12, 13:47    [13648507]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
andrew shalaev
все же интересно почему при использовании ROWLOCK блокируется вся таблица?


блокируется только ключ (раз есть кластерный) Х-ом, а страница и таблица блокировкой намеренья IX
18 дек 12, 13:51    [13648538]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
_djХомяГ
1 Поправка: вроде бы Диасофт не взлюбил кластерные индексы - поэтому индекс скорее всего некластерный


совершенно верно, я не внимательно посмотрел. В таблице единственный некластерный индекс по полю SPID
18 дек 12, 13:58    [13648578]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Гость333
По сколько записей может удаляться за раз?


Сколько пользователь выделит счетов, столько и удалится, но не думаю что речь идет о сотнях или тысячах записей.
18 дек 12, 14:00    [13648594]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
andrew shalaev
_djХомяГ
1 Поправка: вроде бы Диасофт не взлюбил кластерные индексы - поэтому индекс скорее всего некластерный


совершенно верно, я не внимательно посмотрел. В таблице единственный некластерный индекс по полю SPID


тогда в любом случаи блокировка Х должна быть только на РИД и ключ некластерного, а на страницах и таблице IX...у вас на картинке так и есть
18 дек 12, 14:04    [13648643]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
1 Так в том то и дело что такие тормоза (опять таки если блокировка долговременная) из за DELETE по SPID маловероятны
2 Если считаете остатки по счетам/субконто лучше пользуйтесь не процедурой а выборками по tOperPart (и их производными)
18 дек 12, 14:05    [13648649]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а табличка-то небось активно изменяемая? случаем не из хранимок? дайте, плиз, результаты sp_lock для того, кто ждет и для того, кого ждут + выборку из master..sysprocesses (да, устарело, но привычнее, извините уж) где будет виден факт ожидания. есть 1 мысль.
18 дек 12, 14:07    [13648677]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
Crimean
а табличка-то небось активно изменяемая? случаем не из хранимок? .

идеалогия Диасофт: удалить все из постоянной таблицы разделямеой по SPID, затем начитать данные в нее (что обычно быстро) включая SPID а затем вызвать хранимку (соответственно транзакция/ции) которая рассчитает необходимые величины
18 дек 12, 14:13    [13648745]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
_djХомяГ
Crimean
а табличка-то небось активно изменяемая? случаем не из хранимок? .

идеалогия Диасофт: удалить все из постоянной таблицы разделямеой по SPID, затем начитать данные в нее (что обычно быстро) включая SPID а затем вызвать хранимку (соответственно транзакция/ции) которая рассчитает необходимые величины

Охренеть
И че, они это ещё и за деньги продают?
18 дек 12, 14:17    [13648784]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
2 Crimean
ТС молчит
А какая у Вас была мысль , просто интересно

Спасибо!
18 дек 12, 17:40    [13650252]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2566
Гость333
_djХомяГ
пропущено...

идеалогия Диасофт: удалить все из постоянной таблицы разделямеой по SPID, затем начитать данные в нее (что обычно быстро) включая SPID а затем вызвать хранимку (соответственно транзакция/ции) которая рассчитает необходимые величины

Охренеть
И че, они это ещё и за деньги продают?
Не только продают, поддержкой всего ЭТОГО целые отделы в банках занимаются, сам видел
18 дек 12, 17:47    [13650300]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
_djХомяГ,

была мысль что там беда в том числе и в компиляциях. ибо повторить описанное поведение "просто так" не получается. а хранимки, работающие с такой полу-времянкой будут компилить нужные стейтменты постоянно с причиной 2 - смена статистик. а на время компиляции схема лочится монопольно
18 дек 12, 18:14    [13650437]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
_djХомяГ
Guest
Crimean
Спасибо!
18 дек 12, 18:20    [13650445]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
p.s.
а та утиль, что блокировки так красиво показывает систему, между нами, грузит некисло
по крайней мере если процессов больше чем 100
18 дек 12, 20:12    [13650852]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Гость333
Member

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

А какая это утилита?
19 дек 12, 09:20    [13652001]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Гость333
Crimean,

А какая это утилита?


http://sqlblocks.narod.ru/
19 дек 12, 09:27    [13652025]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать?  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Таблица на которой возникают блокировки - полу временная, и раз в сутки очищается, но достаточно 2-х часов работы пользователей и таблица набивается до десятков миллионов записей. Логика работы процедуры такова, что когда пользователь разворачивает дерево счетов, в таблицу начинает активно записываться информация по SPID. Перед началом же работы процедуры старая записанная информация по SPID удаляется... Т.е. если имеем ситуацию когда пользователь развернул большое дерево то получаются монопольные блокировки на таблицу. Поговорив с нашим отделом по обслуживанию Диасофт пришли к мнению, что оптимизировать подобную логику нет смысла. т.к. при проливке обновлений все изменения затрутся.

Crimean
а та утиль, что блокировки так красиво показывает систему, между нами, грузит некисло
по крайней мере если процессов больше чем 100

По поводу утилиты MS SQL Blocks - да, нагрузка на систему есть. но в моем случае пока не критичная и пользы от нее пока больше чем вреда :) И процов у нас значительно меньше чем 100
19 дек 12, 10:22    [13652327]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить