Lo que Database Snapshot aporta es una vista de la base de datos en un instante determinado (cuando se creó). Lo que hace por debajo SQL Server, es que cuando el usuario necesita información de la BD DBS:
- Si los datos NO se han modificado desde el momento de la creación de la DBS, leerá la información de la BD original.
- Si los datos SI se han modificado desde el momento de la creación de la DBS, leerá la información de la DBS combinándola con la información de la BD original: por ejemplo, una tabla que en el momento de la creación del snapshot tenía 5 filas, y en el momento actual tiene 3, para consultar el estado de la tabla en el estado original leerá 3 filas de la BD original + 2 filas de la BDS.
Veamos cómo funciona internamente más allá de la explicación conceptual; Primero crearemos una tabla grande en la base de datos Adventureworks con el siguiente script (tabla con 1 millón de filas):
use Adventureworks
go
if exists (select * from sys.tables where name = ‘tabla_1Millon_filas’)
drop table tabla_1Millon_filas
go
create table tabla_1Millon_filas (
id1 bigint identity primary key
, relleno char(50) default ‘a’
, v char(1) null)
go
— insertar 1.000 x 1.000 = 1.000.000 filas
declare @i int
set @i=1
while @i<=1000
begin
insert tabla_1Millon_filas (v)
select top (1000) null
from Sales.SalesOrderHeader
set @i=@i+1
end
select count(*) [cantidad] from dbo.tabla_1Millon_filas
A continuación crearemos el snapshot; para ello ejecutaremos la siguiente instrucción:
if exists (select name from sys.databases
where name = N‘DBS_Adventureworks’)
drop database DBS_Adventureworks
go
— create the snapshot database
create database DBS_Adventureworks on (
name = Adventureworks_Data,
filename = ‘c:program filesmicrosoft sql servermssql.1mssqldatadbs_Adventureworks.ss’ )
as snapshot of Adventureworks;
A continuación, borraremos de la base de datos original (Adventureworks), las 10.000 primeras filas:
— borrado de 10.000 filas
use Adventureworks
go
delete from dbo.tabla_1Millon_filas
where id1 <= 10000
Y ahora ya estamos preparados para ver el comportamiento de las lecturas. Usaremos la siguiente consulta que devuelve el número de páginas en memoria para cada base de datos – con la que ya estás familiarizado:
SELECT
count(*) AS cached_pages_count
, count(*) * 8. / 1024 AS MB
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
WHERE db_name(database_id) IN
(‘DBS_Adventureworks’, ‘Adventureworks’)
GROUP BY db_name(database_id) ,database_id
Primero limpiaremos el cache de datos para tener unos resultados exactos (por favor, no ejecutar esto en producciónJ):
— limpieza de caché
use master;
dbcc dropcleanbuffers;
dbcc freeproccache;
Habilitaremos STATISTICS IO que también nos muestra información válida en cuanto a las lecturas físicas y lógicas realizadas:
set statistics io on
Y a continuación, contaremos las filas que tiene la tabla de 1 millón de filas en cada base de datos con la siguiente consulta:
use DBS_Adventureworks
go
print ‘Snapshot…’
select count(*) [cantidad_en_snapshot]
from dbo.tabla_1Millon_filas
go
SELECT
count(*) AS cached_pages_count
, count(*) * 8. / 1024 AS MB
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
WHERE db_name(database_id) IN
(‘DBS_Adventureworks’, ‘Adventureworks’)
GROUP BY db_name(database_id) ,database_id
go
use Adventureworks
go
print ‘Source bd…’
select count(*) [cantidad_en_bd_original]
from dbo.tabla_1Millon_filas
go
SELECT
count(*) AS cached_pages_count
, count(*) * 8. / 1024 AS MB
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
WHERE db_name(database_id) IN
(‘DBS_Adventureworks’, ‘Adventureworks’)
GROUP BY db_name(database_id) ,database_id
go
Y obtenemos los siguientes resultados:
cached_pages_count MB Database_name
—————————————————–
114 0.890625 AdventureWorks
Snapshot…
cantidad_en_snapshot
——————–
1000000
cached_pages_count MB Database_name
—————————————————–
10261 80.164062 DBS_Adventureworks
114 0.890625 AdventureWorks
Source bd…
cantidad_en_bd_original
———————–
990000
cached_pages_count MB Database_name
—————————————————–
10261 80.164062 DBS_Adventureworks
10324 80.656250 AdventureWorks
Que nos deja un poco sorprendidos: fíjate que de la BDS, tenemos en memoria más de 10.000 páginas, al igual que las 10.000 de la BD original. Que nos viene a tirar por tierra otro mito que existe en la comunidad, obteniendo la siguiente conclusión:
Cuando la BDS necesita leer páginas de la BD original, las páginas las sube a su caché de datos (el de la BD), independientemente de que las tenga o no “cargadas” la BD Original.
Unos cuantos datos más: si te fijas en el resultado de Statistics IO, el resultado de las physical reads nos deja un poco traspuestos J:
Snapshot…
cantidad_en_snapshot
——————–
1000000
Table ‘tabla_1Millon_filas’. Scan count 1, logical reads 10253, physical reads 0, read-ahead reads 10253, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Source bd…
cantidad_en_bd_original
———————–
990000
Table ‘tabla_1Millon_filas’. Scan count 1, logical reads 10153, physical reads 1, read-ahead reads 10149, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
¿Tiene algún sentido que en una BD con la caché limpia (hemos ejecutado cleanbuffers), se lea el contenido de una tabla de usuario sin realizar lecturas físicas? Y además, ¿existe la posibilidad de realizar 0 lecturas físicas, y de esas lecturas físicas se lean por adelantado (read-ahead) más de 10.000 páginas? No, ¿verdad? pues STATISTICS IO nos ha engañado J
Para dejar la base de datos en el estado original utilizaremos el comando RESTORE:
use master;
restore database Adventureworks
from database_snapshot = ‘DBS_Adventureworks’;
Conclusiones:
Aunque conceptualmente, DBS combina los datos que mantiene la BD Original + los cambios sucedidos desde la creación de la DBS, cada base de datos es independiente, y los datos que necesita leer, residirán en su propia área de memoria:
- La BD Original en el momento de creación del Snapshot tenía 100 páginas.
- Con el paso del tiempo de la original se borraron 30 páginas, por lo que en la Snapshot se registraron las 30 páginas “cambiadas”.
Al realizar lecturas de páginas sucederá lo siguiente:
- Lecturas en la BDS de la tabla original devolverá 100 páginas, y en su área de memoria contendrá las 100 páginas.
- Lecturas en la BD Original leerán las 70 páginas, y en su área de memoria contendrá las 70 páginas.
