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

Откуда: Саратов
Сообщений: 481
Есть таблица с прекалькулированными данными tbPrecalc.
Каждый час все данные из нее удаляются и перезаполняются внутри транзакции. Это занимает до 1.5 минут.
Есть хранимая процедура tbPrecalc_Get, которая по запросу пользователя получает из нее небольшую порцию данных,
самое долгое ее выполнение занимает не больше 0.8 секунды. Примерно такая

SELECT ...
FROM tbPrecalc ...
UNION ALL
SELECT ...
FROM tbPrecalc ...

-- Всего 3 UNION ALL


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

Есть 2 варианта решения проблемы:
1. Использовать уровень изоляции SNAPSHOT

2. Переписать процедуру прекалькуляции след. образом:
BEGIN TRAN

CREATE TABLE tbPrecalcTmp
-- заполнить  tbPrecalcTmp

DROP TABLE tbPrecalc

sp_rename 'tbPrecalcTmp', 'tbPrecalc'

COMMIT TRAN


Конечно, есть вероятность, что пользователь вызовет хранимку аккурат после удаления таблицы tbPrecalc.
На это бэк обещает сделать что-то вроде RETRY вызова tbPrecalc_Get.

Наверняка, кто-то уже решал подобные задачи. Какие подводные камни у второго подхода, которые мы сразу не увидели,
или он и правда очень хорош?
12 ноя 20, 12:51    [22230689]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
msLex
Member

Откуда:
Сообщений: 8455
Шамиль Фаридович,

Не нужно никаких rename. Есть switch.
12 ноя 20, 12:53    [22230692]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
msLex,
я правильно понимаю, что при использовании ALTER TABLE SWITCH
не потребуется даже никаких RETRY от бэка?
12 ноя 20, 13:54    [22230737]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
msLex
Member

Откуда:
Сообщений: 8455
Шамиль Фаридович
msLex,
я правильно понимаю, что при использовании ALTER TABLE SWITCH
не потребуется даже никаких RETRY от бэка?

Конечно, зависит от бека, но в сценарии

insert
begin tran
truncate 
switch
commit


Единственной долгой операций, которая может заблокировать бек, это truncate. И только в том случае, если кто-то будет блокировать ее выполнение, вычитывая данные из таблицы
12 ноя 20, 14:00    [22230747]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Шамиль Фаридович,

я если честно из первого поста нифига не понял:
а) каким способом удаляются данные из таблицы (delete/truncate/drop-create)?
б) как выглядит сама процедура и на что вы ее хотите переписать по Вашему второму варианту решения
12 ноя 20, 14:02    [22230750]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
felix_ff,
msLex уже во все разобрался и даже накидал шаблон процедуры прекалькуляции22230747,
за что ему огромное спасибо!

Сообщение было отредактировано: 12 ноя 20, 14:17
12 ноя 20, 14:20    [22230764]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
у вас какие-то интересные представления о снэпшоте.
если его в базе разрешить, это нагрузка не темпдб еще та,
даже если никто его и не использует.
RCSI грузит куда меньше, а вам хватит за глаза.
пока идет пересчет таблицы,
другая сп получит последние закоммиченные данные.
RCSI разруливает писателей с читателями, и это ваш случай
12 ноя 20, 14:23    [22230766]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2355
Yasha123,

RCSI обеспечивает не меньшую нагрузку на темпдб если есть хотя бы один жирный читатель.
причем сам этот читатель тупо ждет когда же его данные скопируются в темпдб.
13 ноя 20, 11:22    [22231302]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9489
ShIgor
сам этот читатель тупо ждет когда же его данные скопируются в темпдб.
А какие данные копируются? И как их количество зависит от "жирности" читателя?
13 ноя 20, 12:17    [22231343]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
...есть те, кто путает право/лево, а есть кто писателей с читателями.
ShIgor

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

товарищ, читатель ничего не пишет в темпдб, он оттуда читает
13 ноя 20, 12:36    [22231358]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
Имея на руках готовое решение, я долго не мог воспроизвести ошибку.
И с удивлением для себя обнаружил, что на дев-базе выставлено
READ_COMMITTED_SNAPSHOT = ON (RCSI)
Впрочем, именно на этой базе проблема и возникала, примерно раз в 1-2 дня.
На более высоких средах этот флаг выставлен в OFF, поэтому решение со SWITCH все равно будет востребовано.

Но мне непонятно как возникает блокировка читателя писателем при RCSI. Уточню вводные:
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) 
	Jul 12 2019 17:43:08 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

Хранимка по прекалькуляции данных:
ALTER PROCEDURE  DataPrecalculate
(    
    @ids PkList readonly   -- (list of int)
) 
AS
BEGIN
...
BEGIN TRAN
declare @entityList PkList  (

        if exists(select 1 from @ids)
        begin
            insert into @entityList(Id)
            select d.Id
            from tbData d
                inner join @ids i on d.Id = i.Id
        end
        else begin
            insert into @entityList(Id)
            select Id
            from tbData d
        end
     
    delete r
        from tbPrecalc r
        inner join @entityList c on r.EntityId = c.Id

insert into tbPrecalc
... from tbData ...

insert into tbPrecalc
...

;with ...
insert into tbPrecalc

COMMIT TRAN
...
END


Она вызывается раз в час с пустым @ids + автоматически при изменении сущностей в tbData.

Хранимка по получению прекалькулированных данных:
CREATE PROCEDURE tbPrecalc_Get
(
    @entitytId int,
    ...
) AS
BEGIN
    ;with x as ...,
     xx as
    (
        select ...
        from tbPrecalc ...

        union all
        select ...
        from tbPrecalc ...&#8203;

        union all
        select ...
        from tbPrecalc ...

        union all
        select ...
        from tbPrecalc ...
    )
    insert into #result ...
 
END

Таймаут на нее установлен в 30 секунд.

А вот так она падает:
1) 15:00:57.171 Execution Timeout Expired
2) 16:00:39.883
16:00:57.903 (то есть видимо во время выполнение полной прекалькуляции, запущенной в 16:00:00, хранимка получения данных упала дважды).

Из лога видно, что на момент запуска tbPrecalc_Get полная прекалькуляция уже шла (запускается в начале каждого часа и выпоняется до 1.5 мнут)

Во время выполнение полной прекалькуляции теоритически могла быть запущена частичная + упавшая в итоге tbPrecalc_Get.

1. Помогите пожалуйста разобраться, почему писатель лочил читатетля, если для базы установлен RCSI ?
2. Ткните плиз, где найти разницу между RCSI и ALLOW_SNAPSHOT_ISOLATION, правильно ли я вообще понимаю, что
ALLOW_SNAPSHOT_ISOLATION = ON когда для базы установлен уровень изоляции SNAPSHOT ?
16 ноя 20, 15:29    [22233021]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
во-первых, если возникает таймаут на базе с выставленным RCSI,
то это не читатель ждет писателя, а что угодно кроме этого.
так что снимайте слепок ожиданий (sys.dm_os_waiting_tasks wt sys.dm_exec_sessions)
и выясняйте, чего же ждет падающая сессия.

а теперь вернемся к RCSI vs ALLOW_SNAPSHOT_ISOLATION.
одно к другому никак не относится, но некоторые, выставив ALLOW_SNAPSHOT_ISOLATION,
считают, что у них RCSI.
поэтому давайте для очистки совести выясним, как вы установили, что
что "на дев-базе выставлено READ_COMMITTED_SNAPSHOT = ON (RCSI)"
----

"На более высоких средах этот флаг выставлен в OFF"
это вообще забавно: у вас девелоперы умудряются нагружать сервер больше, чем то, что на проде крутится?

"правильно ли я вообще понимаю, что
ALLOW_SNAPSHOT_ISOLATION = ON когда для базы установлен уровень изоляции SNAPSHOT"

неправильно.
"на базе" такой уровень не выставляют.
но зато там, где это выставлено, позволено иметь транзакции c уровнем изоляции SNAPSHOT.
даже само название опции говорит о том, что такой уровень лишь "позволен".
вот пока его транзакция в явном виде не выставит, не будет нигде, кроме этой транзакции, такого уровня изоляции
16 ноя 20, 15:54    [22233041]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
Yasha123

поэтому давайте для очистки совести выясним, как вы установили, что
что "на дев-базе выставлено READ_COMMITTED_SNAPSHOT = ON (RCSI)"

select name, is_read_committed_snapshot_on from sys.databases where name = 'DEV'

Yasha123,
спасибо за пояснение про ALLOW_SNAPSHOT_ISOLATION.

Поправлю свой второй вопрос:
Где найти разницу между RCSI и уровнем изоляции SNAPSHOT (тем, что выставляется командой
SET TRANSACTION ISOLATION LEVEL SNAPSHOT),
или разница всего лишь в том, что RCSI устанавливается на базу в целом, а вторая команда для каждой транзакции по отдельности?

Yasha123

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

А чего же еще ждет читатель, если судя по логам ошибка возникает только тогда, когда параллельно запущена полная прекалькуляция?
В указанные вами представления конечно загляну.
16 ноя 20, 18:11    [22233195]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
Шамиль Фаридович

Где найти разницу между RCSI и уровнем изоляции SNAPSHOT (тем, что выставляется командой
SET TRANSACTION ISOLATION LEVEL SNAPSHOT),
или разница всего лишь в том, что RCSI устанавливается на базу в целом, а вторая команда для каждой транзакции по отдельности?

не-не, разница в том, что при RCSI уровень изоляции транзакций,
если его явно не менять, так и остается Read Committed,
но реализуется он уже не пессимистически(блокировками чтения),
а оптимистически (вместо того, чтобы ждать получения S, сессии выдают последние закоммиченные данные).

когда вы выставляете ALLOW_SNAPSHOT_ISOLATION,
то версии строк в темпдб начинают валиться,
чтобы обеспечить их желающим заюзать уровень изоляции SNAPSHOT в их транзакции,
но при этом Read Committed остается пессимистическим.
т.е. вы ровным счетом ничего не меняете, пока не поменяете свой код,
вписав в него явно SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

вот тут вроде хорошо написано и со ссылками, где посмотреть еще:
ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT
16 ноя 20, 18:24    [22233205]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1590
Шамиль Фаридович,

ну вот неплохое объяснение разницы
https://gavindraper.com/2018/05/08/SQL-Server-Snapshot-Vs-Read-Committed-Snapshot/
16 ноя 20, 18:26    [22233207]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
Шамиль Фаридович

А чего же еще ждет читатель, если судя по логам ошибка возникает только тогда, когда параллельно запущена полная прекалькуляция?
В указанные вами представления конечно загляну.

ну проще всего это выяснить, сняв в тот момент ожидания.
ну а так, кто ж знает, как написана ваша "перекалькуляция".
может, там так:
truncate table...
alter table ... drop constraint PK_...
insert into...
alter table ... add constraint PK_...

ну и последний add constraint (create index) лочит всю таблицу со Sch-M,
и тогда читатель хоть и не хочет S,
но не получает даже Sch-S, который нужен даже при RCSI.
16 ноя 20, 18:30    [22233213]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1929
если текст "перекалькуляции" именно тот, что приведен,
то непонятно, что там можно делать полторы минуты.
табличная переменная подразумевает всего несколько строк, вы туда миллион строк не пихаете часом?

и что есть "полная" перекалькуляция?
уж не передаете ли вы в @ids PkList readonly полный список всех ид из таблицы?
в вашей сп табличные переменные это зло.
сделайте первым шагом
 select id into #ids from @ids

и кластерный навесьте на темповую #ids, поди даже ПК можно, судя по смыслу.

и теперь уже манипулируйте темповой таблицей, а не переменной.
16 ноя 20, 18:45    [22233226]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
Yasha123, felix_ff
Спасибо за ссылки!
Я бы для себя отметил 2 принципиальных различия
1. SNAPSHOT изоляция при первом же обращении транзакции к данным создает снимок всех используемых в транзакции данных и до конца транзакции работает только с ним. Вообще единственное, что препятствовало созданию такого снимка, было WAITFOR DELAY.
RCSI же просто берет ту версию данных конкретной таблицы, что существовала до начала запуска параллельно меняющих эти данные транзакции, и если эта параллельная транзакция завершится до окончания первой, и в первой потом будет повторное обращение к тем же строкам той же таблицы, что поменяла вторая транзакция, то первая транзакция увидит уже обновленные данные (NONREPEATABLE READ)

2. При SNAPSHOT изоляции, если 2 транзакции меняют одни и те же данные, то
а) вторая транзакция будет ждать, пока первая не снимет блокировку
б) выполнится только та транзакция, которая первой доберется до коммита. Вторая откатится.
RCSI:
а) аналогично
б) последовательно применятся изменения обеих транзакций.
22 ноя 20, 21:35    [22236752]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
Сабж решен методом со
msLex
SWITCH

Теперь по любопытству:)
Yasha123
если текст "перекалькуляции" именно тот, что приведен

Прекалькуляции. Код обеих процедур именно такой, как выше, с выделением принципиальных моментов и сохранением недостатков, чтобы был шанс понять, почему на выполнение простой хранимки tbPrecalc_Get, получающей данные из таблицы tbPrecalc по индексированному полю EntitytId, вдруг начинает тратится 30+ секунд вместо 1, если параллельно запущена полная прекалькуляция и уровень изоляции RCSI.
Yasha123
и что есть "полная" перекалькуляция?
уж не передаете ли вы в @ids PkList readonly полный список всех ид из таблицы?

Шамиль Фаридович
Она вызывается раз в час с пустым @ids

Если вы посмотрите внимательнее код DataPrecalculate, то увидите, что при этом в @entityList запихивается вся таблица tbData(~3000 строк)
Yasha123
то непонятно, что там можно делать полторы минуты.

Там довольно сложная математика + на
delete r
        from tbPrecalc r
        inner join @entityList c on r.EntityId = c.Id

Уходило 30+ секунд (там 1.2М строк)

Вернемся к tbPrecalc_Get, чуть уточню содержимое 4х запросов, что соединены union all внутри CTE xx:
select ...
from tbPrecalc p ...
where p.EntityId = @entityId
            and (p.ClientId in (select Id from #clients_tmp) or p.ClientId is null)
union all ...

В состоянии покоя SQL-сервер строил для всех этих 4 запросов прекрасные планы (меня вообще не перестают восхищать разработчики оптимизатора запросов MS SQL), а именно
INDEX SEEK по IX_tbPrecalc_EntityId + NESTED LOOPS по объединению
CONSTANT SCAN(для ClientId is null) +
INDEX SEEK по ix_tmp_clients_Id
И выполнялась хранимка tbPrecalc_Get за < 1сек.

Но вот если параллельно шла полная прекалькуляция, то сервер иногда использовал полное сканирование таблицы tbPrecalc (1.2M) во всех 4 запросах. Вот это иногда и мешало воспроизвести проблему.
В одном из планов, который кстати был не так ужасен, как предыдущий, я видел явное предупреждение, что не хватает статистики по некоторым столбцам.

Так что же, в режиме RCSI параллельно идущая большая транзакция может взять и обнулить статистику?
22 ноя 20, 22:07    [22236769]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
fkthat
Member

Откуда:
Сообщений: 3840
Можно просто не удалять старые данные. Только добавлять новые. Просто каждую порцию добавляемых данных метить каким-то sequence_id.
22 ноя 20, 22:35    [22236793]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

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

А решение, которое я собираюсь развернуть, такое:
Уровень изоляции READ COMMITTED

Для операций со SWITCH я дополнительно создал tbPrecalc_temp - пустую копию таблицы tbPrecalc

ALTER PROCEDURE  DataPrecalculateAll 
AS
BEGIN
BEGIN TRY

-- реализация синглтона с помощью sys.sp_getapplock

    drop table if exists tbPrecalc_EmptyPhantom
    create table tbPrecalc_EmptyPhantom

    ALTER INDEX ALL ON tbPrecalc_temp DISABLE
    truncate table tbPrecalc_temp  -- она и так должна быть пустой, но на всякий случай

-- заполнение tbPrecalc_temp
-- включение всех индексов на tbPrecalc_temp

BEGIN TRAN
    alter table tbPrecalc switch to tbPrecalc_EmptyPhantom;
    alter table tbPrecalc_temp switch to tbPrecalc;
COMMIT TRAN
   drop table if exists tbPrecalc_EmptyPhantom
END TRY

END
23 ноя 20, 00:16    [22236832]     Ответить | Цитировать Сообщить модератору
 Re: SNAPSHOT или трюк с пересозданием таблицы  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 481
Yasha123,
Yasha123

когда вы выставляете ALLOW_SNAPSHOT_ISOLATION,
то версии строк в темпдб начинают валиться,
чтобы обеспечить их желающим заюзать уровень изоляции SNAPSHOT в их транзакции

А разве тоже самое не происходит при RCSI - запись в TempDB версий всех обновляемых транзакциями строк?
Yasha123

RCSI грузит куда меньше

Почему?
Кстати, а сколько хранятся версии строк в TempDB - до тех пор, пока не выполнятся использующие их транзакции?
23 ноя 20, 14:22    [22237204]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить