Memoria usada por bases de datos (Database Snapshot)

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.

 

 

 

Documentar nuestros cubos SSAS (SQL Server Analysis Services)

Documentar nuestros cubos SSAS (SQL Server Analysis Services)

Hoy he tenido que analizar las medidas calculadas de un cubo en el que tenía más de 200 cálculos distintos y he pensado en lo bien que me vendría tener una documentación con el listado medidas calculadas del cubo. Entonces me he acordado que hace unos cuantos días utilice un listado de las medidas de un cubo para hacer un informe, y para ello utilizaba una DMV (Dynamic Management Views) que me permitía ver este listado.

(más…)

Si no necesitas el índice, ¿por qué no lo borras? (I)

Si no necesitas el índice, ¿por qué no lo borras? (I)

Con cierta frecuencia surgen preguntas sobre cuando es el momento ideal de la creación de índices; para empezar, sabes que un índice se crea para usarse, es decir, para fomentar la resolución eficiente de consultas: algunos ejemplos, pueden ser operaciones de agregados, consultas con predicados selectivos, o ayudar a la resolución de cruces (JOIN) entre tablas. No hay ningún problema con todo esto, está asumido, pero la pregunta quizás es: (más…)

Manten tus índices de forma eficiente

Desde SQL Server 2005 la sentencia DBCC SHOWCONTIG ha sido reemplazada mediante la función de administración del sistema sys.dm_db_index_physical_stats(…) mediante la cual podemos realizar consultas dinámicas sobre el estado de nuestros índices y montones.Al margen de que en este artículo no voy a evaluar el uso de DBCC SHOWCONTIG, debes saber que dicha cláusula marcada como DEPRECIADA desde SQL Server 2005 no contempla ya algunas de las características de SQL 2005 o 2008 (datos espaciales, nvarchar(max), varchar(max),…particiones,…) por lo que deberías de dejar de consultarla en pro de la DMF sys.dm_db-index-physical_stats(…)

(más…)

DMVS para Analysis Services 2008

He estado buscando información sobre las DMVS para SSAS 2008 y me he encontrado con que no hay mucha disponible ni siquiera en los books on-line. Incluso en los books on-line están todavía en formato beta. Además no hay que buscar por DMV sino por Schema Rowset.
(más…)

Acabando con los mitos: Memoria usada por bases de datos

Hola,Al hilo del post anterior sobre las DMVs de memoria, me gustaría aclarar el siguiente mito:

“Lecturas a una base de datos (BD1), desde distintas bases de datos (BD2, BD3), implican que en la caché de datos existan varias copias de la información leída de BD1.”. La afirmación es errónea, y es lo que quiero demostrar en este post J

(más…)