sql-server - permitir - sql read committed

¿Cuáles son las implicaciones de la configuración de ALLOW_SNAPSHOT_ISOLATION ON?

Question

Debería correr

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF

¿si el aislamiento de transacción de instantánea (TX) (iso) no se usa temporalmente?
En otras palabras,

• ¿Por qué debería estar habilitado, en primer lugar?
• ¿Por qué no está habilitado por defecto?

¿Cuál es el costo de tenerlo habilitado (pero temporalmente no se usa) en SQL Server?

--Actualizar:
la habilitación del nivel iso de TX de instantáneas en la base de datos no cambia READ COMMITTED tx iso para ser predeterminado.
Puedes comprobarlo ejecutando:

use someDbName;
--( 1 )
alter database someDbName set allow_snapshot_isolation ON;
dbcc useroptions;

la última fila muestra que el nivel tx iso de la sesión actual es (lectura confirmada).

Por lo tanto, habilitar el nivel de instantáneas tx iso sin cambiarlo no lo usa, etc. Para usarlo se debe emitir

--( 2 )
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Actualización2:
Repito los scripts de [1] pero con SNAPSHOT habilitado (pero no activado) pero sin habilitar READ_COMMITTED_SNAPSHOT

--with enabling allow_snapshot_isolation
alter database snapshottest set allow_snapshot_isolation ON

-- but without enabling read_committed_snapshot
--alter database snapshottest set read_committed_snapshot ON
-- OR with OFF
alter database snapshottest set read_committed_snapshot OFF 
go

No hay resultados / filas desde la ejecución

select * from sys.dm_tran_version_store

después de ejecutar INSERTAR, BORRAR o ACTUALIZAR

¿Me puede proporcionar secuencias de comandos que ilustren que el nivel SNAPSHOT tx iso habilitado en (1) pero que no esté activado en (2) produce versiones en tempdb y / o aumente el tamaño de los datos con 14 bytes por fila?
¿Realmente no entiendo cuál es el punto en el control de versiones si está habilitado por (1) pero no se usa (no se establece en (2))?

[1]
Administración de TempDB en SQL Server: Conceptos básicos de TempDB (Almacén de versiones: Ejemplo simple)
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example.aspx

Answer 1

De forma predeterminada, tiene el aislamiento de instantáneas desactivado. Si lo activa, SQL mantendrá instantáneas de datos para ejecutar transacciones. Ejemplo: en la conexión 1, está ejecutando gran selección. En la conexión 2, actualiza algunos de los registros que se devolverán con la primera selección.

En el aislamiento de instantáneas activado, SQL hará una copia temporal de los datos, afectada por la actualización, por lo que SELECT devolverá los datos originales.

Cualquier manipulación de datos adicional afectará el rendimiento. Es por eso que esta configuración está desactivada por defecto.

Answer 2

Tan pronto como se habilite la versión de la fila (también conocida como instantánea) en la base de datos, todas las escrituras deben ser versionadas. No importa en qué nivel de aislamiento se produjo la escritura, ya que los niveles de aislamiento siempre afectan solo a las lecturas. Tan pronto como se habilite la versión de la fila de la base de datos, cualquier inserción / actualización / eliminación:

• aumentar el tamaño de los datos con 14 bytes por fila
• posiblemente cree una imagen de los datos antes de la actualización en el almacén de versiones (tempdb)

De nuevo, es completamente irrelevante qué nivel de aislamiento se usa. Tenga en cuenta que el control de versiones de filas también ocurre si se cumple alguna de las siguientes condiciones:

• la mesa tiene un disparador
• MARS está habilitado en la conexión.
• La operación de índice en línea se está ejecutando en la mesa

Todo esto se explica en Uso de recursos de control de versiones de filas :

Cada fila de la base de datos puede usar hasta 14 bytes al final de la fila para obtener información sobre la versión de la fila. La información de la versión de la fila contiene el número de secuencia de la transacción que confirmó la versión y el puntero a la fila versionada. Estos 14 bytes se agregan la primera vez que se modifica la fila, o cuando se inserta una nueva fila , bajo cualquiera de estas condiciones:

• Las opciones READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION están activadas.
• La mesa tiene un disparador.
• Se están utilizando múltiples conjuntos de resultados activos (MARS).
• Las operaciones de creación de índices en línea se ejecutan actualmente en la tabla.

...

Las versiones de fila deben almacenarse durante el tiempo que una transacción activa necesite acceder a ella. ... si cumple alguna de las siguientes condiciones:

• Utiliza el aislamiento basado en la versión de fila.
• Utiliza desencadenantes, MARS u operaciones de creación de índices en línea.
• Genera versiones en fila.

Actualizar

: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