En estos casos, una imagen es mejor que 1000 palabras:

  • La línea azul representa el tamaño de cada lectura realizada; se mantiene constante sobre 500Kb, lo cual es bueno; en publicaciones anteriores podía ver la diferencia con un sistema fragmentado.
  • La línea roja representa el tamaño total de las lectura que se están realizado: entre 1.5GB/sec y 2GB/sec; este valor dependerá del número de bandejas (enclosures) que tenga y del número de discos al que se esté accediendo (cómo de distribuida esté la información). Este valor siempre estará por debajo de la pruebas sintéticas realizadas con SQLIO.

Cuando revise la fragmentación de sus objetos, intente que el sistema tenga un alto índice de páginas en cada fragmento: columna avg_fragment_size_in_pages de la DMV sys.dm_db_index_physical_stats; como ejemplo, le dejo esta consulta que puede servir para sus análisis:

select 
    t.name, si.name, dpages * 8. / (1024*1024) GB 
    , si.rows, v.avg_fragment_size_in_pages 
from sys.dm_db_index_physical_stats (DB_ID(),
null,null,null, 'sampled') v
join sys.sysindexes si
  on v.object_id = si.id
  and v.index_id = si.indid 
join sys.tables t
  on si.id = t.object_id 
order by 3 desc

 

En la configuración en la que estamos preparando pruebas de laboratorio, obtenemos los siguientes resultados:

Las tablas “importantes” – tablas de hechos – se encuentran con valores por encima de 1000; el valor de referencia suele ser por encima de 400.

Una vez hechas dichas validaciones, es cuando debe proceder a validar los valores de MCR; en dicho caso, debe elegir una consulta “tipo”, FTDW suele utilizar la siguiente como referencia:

SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM staging.lineitem
WHERE l_discount BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 
AND l_quantity < 25
OPTION (maxdop 12); -- 4, 8, 12, 24

y validar cual es el ratio de MB/sec que devuelve la máquina; para hacer ese cálculo, use la siguiente fórmula (que devolverá el ratio en MB/sec):

select 
-- ( PAGES * 8. / 1024 ) / (DURATION-MS / 1000 ) 
( 27665973 * 8. / 1024 ) / (821892 / 1000 )

En nuestro caso, hemos rellenado dos tablas (stagins y “real”) con los mismos datos, y ejecutando el siguiente código:

set statistics io on
set statistics time on
go
dbcc dropcleanbuffers
go

SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM staging.lineitem
WHERE l_discount BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 
AND l_quantity < 25
OPTION (maxdop 12); -- 4, 8, 12, 24
go

dbcc dropcleanbuffers
go

SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM dbo.lineitem
WHERE l_discount BETWEEN 0.04 - 0.01 AND 0.04 + 0.01 
AND l_quantity < 25
OPTION (maxdop 12); -- 4, 8, 12, 24
go

-- los siguientes valores se extraen de la pestaña de “mensajes”:
-- CPU Time, y Logical Reads
select 
-- ( PAGES * 8. / 1024 ) / (DURATION-MS / 1000 ) 
( 27665973 * 8. / 1024 ) / (821892 / 1000 ) 


select 
-- ( PAGES * 8. / 1024 ) / (DURATION-MS / 1000 ) 
( 27708513 * 8. / 1024 ) / (758696 / 1000 )

y hemos obtenido el siguiente resultado:

En la tabla “real” hemos obtenido en una tabla 263MB/sec y en otra 285 MB/sec con grado de paralelismo a 12 — la máquina tiene 24 cores.

Esta consulta la debe realizar con diferentes grados de paralelismo para identificar la mejor relación “duración consulta” vs. “paralelización de consulta”. Normalmente los mejores valores de MB/sec se obtienen con MAXDOP = 1, pero considere que no tiene sentido tener una máquina de 24 cores, con el grado de paralelismo a 1 🙂

Generalmente, la arquitectura de referencia de FTDW (Fast Track 3.0 Reference Guide http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/Fast_Track_Configuration_Guide.docx), suele indicar que deben obtenerse valores por encima de 200MB/sec; esos valores variarán mucho en función:

  • del tipo de consulta que procese: lo es lo mismo un group by sencillo que un group by que cruza 12 tablas.
  • de la capacidad de procesamiento de la máquina: no es lo mismo CPUs a 3GHz, que a 1.6GHz.
  • de la fragmentación de los datos: el proceso de carga de datos en el DW es clave en este aspecto.

Además, note que en nuestra consulta hemos calculado el BCR (valor posterior – lo que sería el consumo real de un coche, siguiendo el simil de FTDW), que generalmente no debe ser inferior al 80% del MCR. Con esas tablas no hemos podido calcular el MCR porque la tabla es tan grande que no entra toda en memoria (no es posible cargar de un “tirón” 210GB cuando dispones de 98 GB de RAM). Recuerde que MCR mide capacidad del sistema sin considerar el acceso a disco, por lo que en una primera consulta se cargan los datos en memoria y luego se ejecuta el agregado “patrón”.

Ya para finalizar, la tabla sobre la que se han hecho las consultas tiene 2.664.000.000 de filas, y las 2 consultas de agregado realizadas han tardado 5 minutos; es decir, SQL Server ha leido más de 5.000.000.000 de filas en algo más de 5 minutos. Un buen número verdad? Smile

PD. Si está interesado en el script de base de datos, hemos utilizado el estandard de TPCH (http://www.tpc.org/tpch/default.asp); próximamente publicaremos datos adicionales que les puedan resultar interesantes (sugerencias a erincon@solidq.com).

Tamaño de base de datos: casi 700GB; tenemos para 9TB, pero no hemos podido rellenar más de momento.

Filas por tabla; la importante, tabla de hechos – lineItem 2.600 millones de filas: