Каковы последствия SET-ting ALLOW_SNAPSHOT_ISOLATION ON?

Ответ 1

Как только управление версиями строк (aka. snapshot) включено в базу данных, все записи должны быть версированы. Неважно, на каком уровне изоляции произошла запись, поскольку уровни изоляции всегда влияют только на чтение. Как только управление версией строки базы данных будет включено, любая вставка/обновление/удаление будет:

• увеличить размер данных с 14 байтами в строке
• возможно создать образ данных перед обновлением в хранилище версий (tempdb)

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

• таблица имеет триггер
• MARS включен в соединении
• Операция онлайн-индекса выполняется в таблице

Все это объясняется в Row Versioning Resource Usage:

Каждая строка базы данных может использовать до 14 байт в конце строки для строки информация о версии. Ряд информация о версии содержит номер последовательности транзакций транзакция, совершившая версию и указатель на версию строки. Эти 14 байтов добавляются первые время, в которое изменяется строка, или когда новая строка вставлена ​​в любойиз этих условий:

• Параметры READ_COMMITTED_SNAPSHOT или ALLOW_SNAPSHOT_ISOLATION: ON.
• В таблице есть триггер.
• Используются несколько активных наборов результатов (MARS).
• Операции онлайн-сборки индексов в настоящее время выполняются в таблице.

...

Версии строк должны храниться как поскольку активная транзакция должна доступ к нему.... если он встречает любойследующие условия:

• Он использует изоляцию на основе версии на основе версий.
• Он использует триггеры, MARS или операции создания индексов в Интернете.
• Он генерирует версии строк.

Обновление

:setvar dbname testsnapshot

use master;

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

use [$(dbname)];
go


-- create a table before row versioning is enabled
--
create table t1 (i int not null);
go
insert into t1(i) values (1);
go

-- this check will show that the records do not contain a version number
--
select avg_record_size_in_bytes 
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 11 (lacks version info that is at least 14 bytes)


-- enable row versioning and and create an identical table
--
alter database [$(dbname)] set allow_snapshot_isolation on;
go

create table t2 (i int not null);
go

set transaction isolation level read committed;
go

insert into t2(i) values (1);
go

-- This check shows that the rows in t2 have version number
--
select avg_record_size_in_bytes
     from sys.dm_db_index_physical_stats (db_id(), object_id('t2'), NULL, NULL, 'DETAILED')
-- record size: 25 (11+14)

-- this update will show that the version store has records
-- even though the isolation level is read commited
--
begin transaction;
update t1 
    set i += 1; 
select * from sys.dm_tran_version_store;
commit;
go

-- And if we check again the row size of t1, its rows now have a version number
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 25

Ответ 2

По умолчанию у вас отключена блокировка моментального снимка, если вы включите ее, SQL будет поддерживать моментальные снимки данных для запуска транзакций. Пример: при подключении 1 вы выполняете большой выбор. При подключении 2 вы обновите некоторые записи, которые будут возвращены при первом выборе.

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

Любые дополнительные манипуляции с данными повлияют на производительность. Вот почему этот параметр отключен по умолчанию.