Непредсказуемый update (MS SQL 2000)

Nosferatu · Post by **Nosferatu** » 10 Jun 2004 23:13

Помогите, господа! Может кто-нибудь на такие грабли натыкался...

Есть update:

UPDATE i
SET Domain='C'
FROM tblFacilityInventory i
JOIN tblFacilityHierarchy h ON h.FaxId=i.FaxId
JOIN rcvSystemInventory s ON h.Position LIKE s.Position+'.%'
WHERE s.Domain='C'

tblFacilityInventory = ~ 2,200,000 rows
tblFacilityHierarchy= ~ 5,600,000 rows
rcvSystemInventory= ~ 19,000 rows

Ни одна таблица не имеет никаких на нее глядящих foreign keys.

На всех машинах отрабатывает за 5-10 секунд.
На одной машине (гораздо более мощной) не работает вообще - отжирает все 4 CPU под 100% и не дышит.
Данные на всех машинах приблизительно одинаковые. +/- 100,000 rows в первых двух таблицах.

Попробовали разбить так:

SELECT h.FaxId
INTO #tempinv
FROM tmpFacilityHierarchy h
JOIN rcvSystemInventory s ON h.Position LIKE s.Position+'.%'
WHERE s.Domain='C'

UPDATE i
SET Domain='C'
FROM tmpFacilityInventory i
JOIN #tempinv t ON t.FaxId=i.FaxId

Выполняется за 8-10 секунд...

Встречаю подобную аномалию уже третий или четвертый раз.
Это я что-то не так делаю или SQL server?

P.S. Когда выполняется UPDATE или вернее пытался выполниться, врубается параллелизм и в основном процессе CXPACKET может висеть минутами и часами.
P.P.S. Размеры всех баз и транзакшн логов более чем достаточные...

Буду благодарен за любые идеи...

Strannik223 · Post by **Strannik223** » 10 Jun 2004 23:23

Сравнить план выполнения на аномальной и нормальной машине

Nosferatu · Post by **Nosferatu** » 10 Jun 2004 23:35

Strannik223 wrote:Сравнить план выполнения на аномальной и нормальной машине

Estimated Execution Plan - одинаковый. А реальный сравнить не могу, потому что не могу его получить от висящего update'a.

Dmitry67 · Post by **Dmitry67** » 11 Jun 2004 09:01

Мое предположение
На мощной машине используется парралелизм
Может используется на всех, Но на одной это происходит неудачно
Hintами отключите парралелизм для этого запроса

YellowMan · Post by **YellowMan** » 11 Jun 2004 10:23

Вы ничего не написали про индексы, ничего - про статистику, вернее ее актуальность.

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

Dmitry67 · Post by **Dmitry67** » 11 Jun 2004 11:09

Блокировакой это быть не может
Написал человек что 4 CPU под 100%
Я такое много раз видел при парралелизме

Nosferatu · Post by **Nosferatu** » 11 Jun 2004 15:22

YellowMan wrote:Вы ничего не написали про индексы, ничего - про статистику, вернее ее актуальность.

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

Да, я забыл написать, что индексы на всех табицах на разных машинах одинаковые.
К таблицам кроме этого update вообще никто не обращается - блокировок там быть не может. Да и вообще, машина которая не может выполнить этот update практически не используется - активность на ней минимальная...

Nosferatu · Post by **Nosferatu** » 11 Jun 2004 15:25

Dmitry67 wrote:Мое предположение
На мощной машине используется парралелизм
Может используется на всех, Но на одной это происходит неудачно
Hintами отключите парралелизм для этого запроса

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

Nosferatu · Post by **Nosferatu** » 11 Jun 2004 15:35

Dmitry67 wrote:Мое предположение
На мощной машине используется парралелизм
Может используется на всех, Но на одной это происходит неудачно
Hintами отключите парралелизм для этого запроса

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

Dmitry67 · Post by **Dmitry67** » 11 Jun 2004 15:44

Не томите
Проверить без парралелизма это одна минута

Nosferatu · Post by **Nosferatu** » 11 Jun 2004 16:01

Dmitry67 wrote:Не томите
Проверить без парралелизма это одна минута

Обязательно проверю. Сейчас пока не могу, т.к. на машину данные загружаются...
Отключить параллелизм, я так понимаю, надо с помощью (MAXDOP 1) ?

Niky · Post by **Niky** » 11 Jun 2004 16:21

Вот тут похожая ситуация описана.

YellowMan · Post by **YellowMan** » 11 Jun 2004 16:37

MAXDOP 1 совершенно верно...
Но блокировками все-же не пренебрегайте - я тоже много раз видел как на базах с выставленными auto growth для лога или данных попытка откусить еще места для базы и одновременный insert/update вгоняли базу в кому на неопределенное время...

Nosferatu · Post by **Nosferatu** » 11 Jun 2004 17:51

YellowMan wrote:MAXDOP 1 совершенно верно...
Но блокировками все-же не пренебрегайте - я тоже много раз видел как на базах с выставленными auto growth для лога или данных попытка откусить еще места для базы и одновременный insert/update вгоняли базу в кому на неопределенное время...

Да... Но 100% на всех CPU без особой дисковой активности?

alex_127 · Post by **alex_127** » 12 Jun 2004 03:37

Nosferatu wrote:Да... Но 100% на всех CPU без особой дисковой активности?

нехорошо это как-то... а можно схему табличек, план и так далее? Можно PM - попробуем разобраться... На тестовом сервере у вас это тоже случается?

Nosferatu · Post by **Nosferatu** » 12 Jun 2004 13:53

Dmitry67 wrote:Не томите
Проверить без парралелизма это одна минута

Вчера, на неработавшей машине, UPDATE неодиданно заработал. Занял около 15 секунд. Попробовали отключить параллелизм - 3 секуды. Повторили несколько раз и то и другое. Параллелизм - 15 секунд, разбитый Update - 7 сек, без параллелизма - 3 сек. Вот такие дела...

Nosferatu · Post by **Nosferatu** » 12 Jun 2004 13:56

alex_127 wrote:
Nosferatu wrote:Да... Но 100% на всех CPU без особой дисковой активности?

нехорошо это как-то... а можно схему табличек, план и так далее? Можно PM - попробуем разобраться... На тестовом сервере у вас это тоже случается?

Нет, на тестовом сервере такого не было, как и на других серверах. Но подобные этому случаи были пару раз на разных серверах.

Схемку нарисую скоро. А вот что такое PM, извините, не знаю...

Dmitry67 · Post by **Dmitry67** » 12 Jun 2004 14:08

Nosferatu wrote:Вчера, на неработавшей машине, UPDATE неодиданно заработал. Занял около 15 секунд. Попробовали отключить параллелизм - 3 секуды. Повторили несколько раз и то и другое. Параллелизм - 15 секунд, разбитый Update - 7 сек, без параллелизма - 3 сек. Вот такие дела...

Надо было на бутылку спорить

Тут недавно был у нас консультант из M$
Так вот он понизив голос сказал что знаете, там много ошибок при паралелизме... если какие проблемы будут отключите... я ему сказал что я уже отключил... он скащал, ну и слава Богу

Nosferatu · Post by **Nosferatu** » 12 Jun 2004 15:39

Dmitry67 wrote:
Nosferatu wrote:Вчера, на неработавшей машине, UPDATE неодиданно заработал. Занял около 15 секунд. Попробовали отключить параллелизм - 3 секуды. Повторили несколько раз и то и другое. Параллелизм - 15 секунд, разбитый Update - 7 сек, без параллелизма - 3 сек. Вот такие дела...

Надо было на бутылку спорить
Тут недавно был у нас консультант из M$
Так вот он понизив голос сказал что знаете, там много ошибок при паралелизме... если какие проблемы будут отключите... я ему сказал что я уже отключил... он скащал, ну и слава Богу

Да как-то страшно выключать... Довольно большие объемы данных загружаются, да и читаются тоже. Неужели он настолько плох, что только мешает, а не помогает совсем?

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

Dmitry67 · Post by **Dmitry67** » 12 Jun 2004 19:15

Это зависит от workload
При OLTP, много маленьких запросов, особенно WEB, лучше отключать
При OLAP, при построении сложных запросов лучше бывает с паралелизмом
Но не всегда

Пусть у Вас есть RAID, который поделен на буковки D: и E:
У вас данные разбиры между байлами лежащими на D и E
Ура ! думает SQL server. Два устройства, с ними может работать одновременно и строит план с парралелизмом с одновременным чтением из разных мест
Но так как физически это одно устройство с одним каналом то получает только хуже, вместо одного длинного table scan получится например смесь двух с хаотическим дерганием головки дисков

YellowMan · Post by **YellowMan** » 14 Jun 2004 11:02

Dmitry67 wrote:Надо было на бутылку спорить
Тут недавно был у нас консультант из M$
Так вот он понизив голос сказал что знаете, там много ошибок при паралелизме... если какие проблемы будут отключите... я ему сказал что я уже отключил... он скащал, ну и слава Богу

Надо было

Мне из французских коньяков ближе всего Камю, ибо как было сказано:

Nosferatu wrote:Вчера, на неработавшей машине, UPDATE неодиданно заработал.

АКА блокирующий ресурс транзакция отвалилась

А консультантов Вы слушайте - они вам всякого наговорят, по секрету и строго конфидинциально ...

Dmitry67 · Post by **Dmitry67** » 14 Jun 2004 12:24

Заработала потому что ошибка с паралелизмом плавающая, возникает при некотором неудачном стечении обстоятельств, race conditions итд.
А чтобы транзакция чтото блокировала часами и днями... надо очень стараться чтобы это не увидеть... потом опять таки никак не объясняет CPU 100%

P.S.
Я предпочитаю белые вина, особо дорогое, так уж и быть, не покупайте

YellowMan · Post by **YellowMan** » 14 Jun 2004 16:28

Заработало потому что на выходных обновилась статистика, или по базе прошелся какой-нибудь maintenance job и вправил индексам мозги, слетела блокирующая транзакзия (клерк пришел в понедельник на работу и закрыл окошко на клиенте, транзакция и отвалилась) или еще из-за 123 различных причин.
Индекс скан и деадлок - вот вам и 100% процессора(ов).
Заметить конечно действительно несложно - но непонятно было ли вообще телодвижение в эту сторону.
Так что Дмитрий, взялись за гуж, не говорите что ингуш - высылайте коньяковского. Мне Камю в форме книжки - чтобы на таком двухсмысленном предмете объяснять знакомым философам как бытие определяет сознание

Могу даже подсказать лично проверенное и одобренное место в Париже где затовариваться

Dmitry67 · Post by **Dmitry67** » 14 Jun 2004 16:35

Table scan вызывает IO, а CPU как раз бадает
Зачем процессору думать если он читает ?
deadlock детектируется в течение секунды
ПРосто блокировка вызывает падение CPU
Так что винишко то с Вас... За базар, как говорится

YellowMan · Post by **YellowMan** » 14 Jun 2004 17:27

Неа, не с меня - совсем наоборот

Пока что никаких убедительных доказательств что виновата паралельность а не к примеру, лок или старая статистика не прозвучало. Мы же не принимаем всерьез замечание некого непонятного консультанта, владеющего ИСТИНОЙ (!) и вскользь выдавшего Вам страшный секрет о жутких багах в коде сервера, правда ?

Так что с винишком мы пока повременим - а хороший коньяк может и закончиться...возле собора Парижской Богоматери, прямо через улицу от метро был отличный магазин с коньяком - хозяина толи Жан толи Жак звали