Utilizando índices y hints para mejorar la concurrencia (y gestión de bloqueos) en soluciones de SQL Server

Utilizando índices y hints para mejorar la concurrencia (y gestión de bloqueos) en soluciones de SQL Server

Recientemente hemos tratado un incidente con un cliente que tenía problemas de bloqueos en SQL Server. Me gustaría explicar el caso de este cliente — simplificando el problema, y aprovechar la oportunidad que los seguidores de nuestros blogs, entendáis algunos aspectos importantes de SQL Server, a saber, gestión de bloqueos, planes de ejecución, e indexación. (más…)

Las lecturas no confirmadas te pueden hacer leer muchas filas de más y/o de menos

Las lecturas no confirmadas te pueden hacer leer muchas filas de más y/o de menos

Cuando se hacen lectura sucias (tanto eligiendo el nivel de aislamiento READ UNCOMMITTED, o utilizando el hint NOLOCK), estás leyendo datos que pueden ser inconsistentes desde el punto de vista transaccional; en este post vamos a reproducir un caso extremo para que te convenzas de los “desbarajustes” que se pueden estar haciendo.

 

Qué son las lecturas sucias

Partiendo de una tabla de pedidos, con las siguientes filas:

IDPedidoCantidad
110
215

y dándose las siguientes operaciones ordenadas en el tiempo:

CuandoUsuarioInstrucciónResultado
T+1Usuario 1BEGIN TRAN
T+2Usuario 1DELETE PEDIDOS where IdPedido = 21 fila afectada
T+3Usuario 2SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT SUM(Cantidad) c
FROM PEDIDOS (NOLOCK)
10 Unidades
T+4Usuario 3SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT SUM (Cantidad) c FROM PEDIDOS
—  sin resultado
T+5Usuario 1ROLLBACK TRAN;
T+6Usuario 2SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT SUM(Cantidad) c
FROM PEDIDOS (NOLOCK)
25 Unidades
T+7Usuario 3SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT SUM (Cantidad) c FROM PEDIDOS
25 Unidades

Puede suceder que el Usuario 2 lea datos que realmente no están confirmados.

Sin embargo, el Usuario 3, mientras la transacción no esté confirmada, no podrá leer el dato, y cuando la transacción esté confirmada, podrá leer el dato real.

Para aliviar la situación de que el Usuario 3 está bloqueado en SQL Server 2005 aparece el concepto de lectura confirmada de instantánea (READ COMMITTED SNAPSHOT) que se menciona al final de este post.

Preparando una demo “impactante”

Dada una tabla con XX filas, vamos a definir dos conexiones haciendo las siguientes operaciones:

  • una conexión contará el número de filas de la tabla
  • otra conexión actualizará la tabla entera

Para ello tenemos la siguiente estructura de tabla:

--
-- bloqueos (lectura sucia)
--
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- https://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com
--
use Adventureworks
go

if exists (select * from sys.tables where name='bloqueos')
    drop table bloqueos;
go

create table bloqueos (
    id  int not null identity,
    relleno char(8000),
    mi_guid uniqueidentifier default(newid())
)
go

create unique clustered index ci_bloqueos_malo 
on bloqueos (mi_guid);
create unique nonclustered index nci_bloqueos_id
on bloqueos (id);

 

 

En la que insertaremos 10.000 filas – comprobamos espacio usado y número de página asignadas:

insert bloqueos (relleno)
    select 'a' from master.dbo.fn_nums (10000)
go
 
select si.name, page_count, record_count
from sys.dm_db_index_physical_stats (
    db_id()
    , OBJECT_ID ('bloqueos'), null, null, 'detailed') v
    join sys.indexes si
    on v.index_id = si.index_id 
    and v.object_id = si.object_id 
where index_level = 0
go

sp_spaceused bloqueos

 

Donde obtenemos el siguiente resultado:

Untitled_1_17E87756

 

Nota como hemos “conseguido” que el índice clustered tenga sólo una fila por página (fíjate que en la creación de la tabla dijimos que la columna relleno ocupaba 8000 bytes). Además, fíjate que el índice nonclustered es muy “ligero” y apenas ocupa 50 páginas (la clave es integer  — 4 bytes por referencia).

Seguramente te has dado cuenta que hemos cometido una irresponsabilidad al definir como clustered una columna newid(); lo hemos hecho así para provocar que sentencias TSQL sucesivas que veréis después, fuercen a que la fila tratada sea muy volátil en cuanto a su ubicación lógica en la tabla.

Además, vamos a crear una tabla de auditoria, donde vamos a registrar las operaciones que se van a ir haciendo:

-- auditoria
if exists (select * from sys.tables where name='auditoria')
    drop table auditoria;
go

create table auditoria (
    id  int not null identity primary key,
    quien varchar(100),
    operacion varchar(100),
    cuando datetime default (getdate()),
    filas int
)

 

Provocando el escenario deseado para la demo

vamos a tener dos usuarios realizando las siguientes operaciones:

  • Usuario 1: actualización completa de la tabla renovando el valor de la columna GUID.
  • la función NEWID() genera valores muy “dispersos”, lo que provocará que las filas se muevan con un ratio muy alto de unas página a otras
  • Usuario 2: bucle iterativo contando cuantas filas hay en la tabla en modo lectura sucia
  • forzaremos que se use el índice clustered (donde están los datos) para mostrar el efecto deseado

Para ello, necesitas dos conexiones de SSMS; en las demostraciones de bloqueos suele ser útil ver las dos conexiones en vertical (menú Window, New Vertical Tab Group).

 

En una de las conexiones tendrás el siguiente código (la que va a hace los SELECT):

-- Conexión 1: actualizaciones
--
insert auditoria 
(quien, operacion, filas)
select 'Conexion1', 'INICIO UPDATE', NULL
go

update bloqueos
set mi_guid = newid()
go

insert auditoria 
(quien, operacion, filas)
select 'Conexion1', 'FIN UPDATE', NULL

 

 

En la segunda conexión, tendrás el siguiente código (el del UPDATE):

-- Conexión 2: contar filas -- por CI
--
truncate table auditoria
go

while 1 = 1
begin
    insert auditoria 
        (quien, operacion, filas)
    select 
        'Conexion2', 'SELECT (NOLOCK)', count(*) 
    from bloqueos with (nolock, index=ci_bloqueos_malo)
    waitfor delay '00:00:00.5'
end

 

A continuación pones en marcha la consulta que lee (bucle iterativo), y después ejecutas la primera consulta (la del UPDATE). Cuando finalice la consulta del UPDATE, paras (le das a STOP) a la consulta de la SELECT.

 

Ahora ejecuta la siguiente consulta, y verás la sorpresa:

select *
from auditoria
Untitled2_1_17E87756

 

Las líneas de color Rojo, marcan la “zona” de tiempo en la que se está ejecutando el UPDATE.

Fíjate que existen una cuantas filas insertadas, en las que el número de filas contadas ha sido distinto de 10.000 que es el número de filas que tiene la tabla. ¿cual es la causa de esto?

Mientras la consulta SELECT se estaba procesando, SQL Server estaba leyendo filas de más o de menos; ¿cómo ha sucedido esto? mejor un dibujo 🙂

Untitled4_1_17E87756
  • En color Rojo, se representa en Index Scan, que lee todas las página para contar cuantas fijas hay
  • En color Azul, se representa la actualización de una fija, que la mueve de una página a otra
  • Cuando se estaba leyendo a la altura del asterisco (*), se movió la fija a la ubicación donde finaliza la flecha azul
  • lo cual implica que cuando el Index Scan pasó por donde se movió la fila ya leída, lo leyera otra vez

 

Como ejercicio, te propongo que simules el proceso reemplazando el código de Conexión 2 por el siguiente:

-- Conexión 3: contar filas por NCI
--
while 1 = 1
begin
    insert auditoria 
        (quien, operacion, filas)
    select 
        'Conexion3', 'SELECT (NOLOCK) -- NCI', count(*) 
    from bloqueos with (nolock)
    waitfor delay '00:00:00.5'
end

 

y verás como siempre se leerán el mismo número de filas; ¿por qué razón? porque el UPDATE de la columna mi_guid, no proboca cambio de ubicación de las filas en el índice nonclustered (columna id)

 

Conclusión

  • Este efecto “no deseado”, no sucederá si se usas un índice que no sufre movimientos de páginas (como el NCI de Conexión 3).
  • Tampoco sucederá si no utilizas el hint NOLOCK y trabajas en modo de aislamiento LECTURA CONFIRMADA.
  • SI sucederá si tienes configurada la base de datos en modo READ_COMMITTED_SNAPSHOT y realizas lecturas SUCIAS (NOLOCK).
  • Tampoco sucederá si tienes configurada la base de datos en modo READ_COMMITTED_SNAPSHOT y trabajas en modo de aislamiento LECTURA CONFIRMADA.

(más…)

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.

 

 

 

Efectos de DBCC FREEPROCCACHE, y DBCC DROPCLEANBUFFERS en SQL Server

Cuando te encuentras optimizando tus sistemas de bases de datos, el escenario que se suele intentar optimizar es como si el servidor estuviera “limpio”. Es decir, como si no tuviera en memoria ninguna de las filas que necesita para procesar la consulta. Por otra parte, te sueles encargar de limpiar los buffers de SQL Server para que el procesamiento de la consulta/procedimiento almacenado tenga que asumir el coste de compilar y general el plan de ejecución adecuado. Al final obtendremos valores como duración total del proceso (consulta/procedimiento almacenado), y CPU utilizada (incluida la utilizada para compilar el plan de ejecución). (más…)

Creación de trazas de SQL Profiler desde Transact-SQL (TSQL) para SQL Server 2000 y SQL Server 2005 de una forma sencilla

En la mayoría de los proyectos de Mentoring en los que trabajamos en SolidQ necesitamos crear trazas de SQL Profiler para conocer el tipo de consultas que llegan al servidor de bases de datos; casi siempre, los servidores de bases de datos, están muy ocupados con su trabajo, es decir, servir datos a las aplicaciones cliente que lo solicitan, y nuestras trazas de auditoría deben ser lo menos agresivas posible. (más…)
Problema con Expresión CASE en Predicados WHERE: Análisis de un Plan de Ejecución raro y la importancia de conocer el motor de SQL Server

Problema con Expresión CASE en Predicados WHERE: Análisis de un Plan de Ejecución raro y la importancia de conocer el motor de SQL Server

Cuando damos clases de SQL Server un aspecto al que los clientes – y a veces alumnos – quitan importancia a la parte de interioridades del motor de SQL Server. En mi puesto, en muchas ocasiones debo convencer a decisores sobre la formación a impartir a sus empleados; con mucha frecuencia tratan de evitar aspectos que se consideran internos del producto y que pueden verse como “de junta de trócola” y “para frikis”. En SolidQ siempre queremos que se imparta ese contenido, pero hoy día donde el argumento de más ponderación es el coste del servicio, muchos clientes intentan evitar contenidos formativos a los que no se les vea “retorno inmediato”. No quiero hacer de esta publicación una crítica o análisis de la formación especializada en España, pero debo indicar que cuando estaba pensando en la motivación de este post, la asociación entre interioridades de SQL Server, conocimiento que “quizás te sirva a medio plazo” y la educación especializada me vino directamente a la cabeza. También es posible que sea un problema mío de no saber convencer al cliente de la importancia del contenido presupuestado(más…)