Блог

Хорошо, я сделаю все возможное, описывая это. У меня есть SP, который передает XML и обновляет и вставляет другую таблицу. Вчера это работало. Все, что я изменил сегодня, это загрузка временной таблицы с помощью OPENXML vs xml.nodes. Я даже изменил его обратно, и я все еще получаю эту интересную проблему. У меня есть обновление и вставка в одной транзакции. Обновление работает, а затем вставка зависает, никаких ошибок, ничего… продолжается 9 минут. Обычно это занимает 10 секунд. Нет процессов блокировки в соответствии с master.sys.sysprocesses . Забавно, что выбор вставки не возвращает строк, поскольку они уже есть в базе данных. Обновление обновляет 72438 в

 SQL Server Execution Times:
   CPU time = 1359 ms,  elapsed time = 7955 ms.

ROWS AFFECTED(72438)
  

У меня нет идей относительно того, что может быть причиной моей проблемы? Разрешения, я так не думаю? Пробел, я так не думаю, потому что будет возвращена ошибка?

запросы:

 UPDATE [Sales].[dbo].[WeeklySummary]
SET [CountryId] = I.CountryId
    ,[CurrencyId] = I.CurrencyId
    ,[WeeklySummaryType] = @WeeklySummaryTypeId       
    ,[WeeklyBalanceAmt] = M.WeeklyBalanceAmt   I.WeeklyBalanceAmt
    ,[CurrencyFactor] = I.CurrencyFactor
    ,[Comment] = I.Comment
    ,[UserStamp] = I.UserStamp
    ,[DateTimeStamp] = I.DateTimeStamp
FROM 
    [Sales].[dbo].[WeeklySummary] M
INNER JOIN
    @WeeklySummaryInserts I
    ON M.EntityId = I.EntityId
    AND M.EntityType = I.EntityType
    AND M.WeekEndingDate = I.WeekEndingDate
    AND M.BalanceId = I.BalanceId
    AND M.ItemType = I.ItemType
    AND M.AccountType = I.AccountType
  

и

 INSERT INTO [Sales].[dbo].[WeeklySummary]
    ([EntityId]
    ,[EntityType]
    ,[WeekEndingDate]
    ,[BalanceId]
    ,[CountryId]
    ,[CurrencyId]
    ,[WeeklySummaryType]
    ,[ItemType]
    ,[AccountType]
    ,[WeeklyBalanceAmt]
    ,[CurrencyFactor]
    ,[Comment]
    ,[UserStamp]
    ,[DateTimeStamp])
SELECT
    I.[EntityId]
,   I.[EntityType]
,   I.[WeekEndingDate]
,   I.[BalanceId]
,   I.[CountryId]
,   I.[CurrencyId]
,   @WeeklySummaryTypeId
,   I.[ItemType]
,   I.[AccountType]
,   I.[WeeklyBalanceAmt]
,   I.[CurrencyFactor]
,   I.[Comment]
,   I.[UserStamp]
,   I.[DateTimeStamp]
FROM 
    @WeeklySummaryInserts I
LEFT OUTER JOIN
    [Sales].[dbo].[WeeklySummary] M
    ON I.EntityId = M.EntityId
    AND I.EntityType = M.EntityType
    AND I.WeekEndingDate = M.WeekEndingDate
    AND I.BalanceId = M.BalanceId
    AND I.ItemType = M.ItemType
    AND I.AccountType = M.AccountType
WHERE M.WeeklySummaryId IS NULL
  

Обновить

Пытаясь воспользоваться приведенным здесь советом, который некоторое время работал, я запускаю следующее перед вызовом моей хранимой процедуры

 UPDATE STATISTICS Sales.dbo.WeeklySummary;
UPDATE STATISTICS Sales.dbo.ARSubLedger;
UPDATE STATISTICS dbo.AccountBalance;
UPDATE STATISTICS dbo.InvoiceUnposted
UPDATE STATISTICS dbo.InvoiceItemUnposted;
UPDATE STATISTICS dbo.InvoiceItemUnpostedHistory;
UPDATE STATISTICS dbo.InvoiceUnpostedHistory;
EXEC sp_recompile N'dbo.proc_ChargeRegister'
  

Все еще зависает в инструкции Insert, которая снова вставляет 0 строк.

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

ШАГ 1: Вручную создайте набор XML для запуска, который произведет ровно одну вставку и никаких обновлений, чтобы вы могли, так сказать, «вернуться к основам» и убедиться, что код по-прежнему выполняет то, что вы ожидаете, и результат в точности соответствует вашим ожиданиям. Это может показаться глупым или ненужным, но вам действительно нужно начать проверку на реальность.

ШАГ 2: Вручную создайте набор XML, который создаст набор вставок среднего размера, по-прежнему без обновлений. Основываясь на вашем опыте работы с процедурой, попробуйте найти что-нибудь, что будет выполняться через 3-4 секунды. Возможно, 5000 строк. Продолжает ли оно работать так, как ожидалось?

ШАГ 3: Предполагая, что шаги 1 и 2 выполняются легко, следующая наиболее вероятная проблема — РАЗМЕР ТРАНЗАКЦИИ. Если ваше обновление достигает 74 000 строк в одной инструкции, то SQL Server должен выделить ресурсы, чтобы иметь возможность откатить все 74 000 строк в случае прерывания. Обычно следует предполагать, что ресурсы (и время), необходимые для поддержания транзакции, увеличиваются экспоненциально по мере увеличения количества строк. Итак, создайте вручную еще один набор вставок, содержащий 50 000 строк. Вы должны обнаружить, что это занимает значительно больше времени. Дайте ему закончить. Прошло 10 минут, час? Если это занимает много времени, но завершается, у вас проблема с РАЗМЕРОМ ТРАНЗАКЦИИ, сервер с трудом отслеживает все, что требуется для отката вставки в случае сбоя.

ШАГ 4. Определите, работает ли вся ваша хранимая процедура в рамках одной подразумеваемой транзакции. Если это так, то дело обстоит еще хуже, потому что SQL Server отслеживает все, что требуется для отката как 74 000 обновлений, так и??? вставляет в одной транзакции. Смотрите эту страницу:

http://msdn.microsoft.com/en-us/library/ms687099 (v = против 85).aspx

ШАГ 5: Если у вас есть единственная неявная транзакция, вы можете либо. А) Отключите это, что может помочь некоторым, но не решит проблему полностью, или Б) разбейте sproc на два отдельных вызова, один для обновлений, другой для вставок, чтобы по крайней мере два выполнялись в отдельных транзакциях.

ШАГ 6: Рассмотрим «фрагментацию». Это метод, позволяющий избежать резкого увеличения транзакционных издержек. Рассматривая для начала только INSERT, вы помещаете insert в цикл, который запускается и фиксирует транзакцию внутри каждой итерации и завершается, когда затронутые строки равны нулю. ВСТАВКА изменена таким образом, что вы извлекаете только первые 1000 строк из источника и вставляете их (это число 1000 является произвольным, вы можете обнаружить, что 5000 обеспечивает лучшую производительность, вам нужно немного поэкспериментировать). Как только ВСТАВКА затрагивает нулевые строки, обрабатывать строки больше не нужно, и цикл завершается.

КРАТКОЕ РЕДАКТИРОВАНИЕ: Система «разбиения на фрагменты» работает, потому что полная пропускная способность для большого набора строк выглядит примерно как квадратичная. Если вы выполняете ВСТАВКУ, которая затрагивает огромное количество строк, общее время обработки всех строк увеличивается. Если, с другой стороны, вы разбиваете его и переходите по строкам, накладные расходы на открытие и фиксацию каждого оператора приводят к увеличению общего времени для всех строк. Где-то посередине, когда вы «выделили» по 1 тыс. строк на инструкцию, требования к транзакции минимальны, а накладные расходы на открытие и фиксацию инструкции незначительны, а общее время обработки всех строк минимально.

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

Необъяснимые тайм-ауты SQL Server и периодическая блокировка

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