Calcular el MCR (Maximum Consumption Rate)

Planificar la infraestructura de hardware para una solución OLAP o OLTP basada en Microsoft SQL Server requiere comprender cómo funcionan los distintos componentes de SQL Server y cómo sus cargas de trabajo típicas utilizan recursos de hardware.

La arquitectura del sistema de núcleo equilibrado se basa en el hecho de que la mayoría de las cargas de trabajo OLAP o OLTP necesitan transferir cantidades pequeñas a grandes cantidades de datos (normalmente accedidas por operaciones de lectura secuencial o aleatoria) a través de múltiples componentes del sistema, desde donde se almacenan los datos hasta el solicitante Aplicaciones. Cada componente a través del cual se transfieren los datos es un cuello de botella potencial que limitará el rendimiento general del sistema. Los datos sólo pueden fluir a la aplicación solicitante a la velocidad del componente más lento. Todos los componentes que pueden operar a una velocidad mayor son subutilizados, lo que desestabiliza el sistema y puede representar un coste desperdiciado significativo.

Maximum Consumption Rate (MCR)

La mayoría de los administradores de bases de datos quieren comenzar a estimar los requisitos de hardware para una base de datos determinando el volumen de datos que se van a almacenar y eligiendo la solución de almacenamiento más grande y más rápida posible. El enfoque de núcleo equilibrado comienza con el rendimiento del núcleo de la CPU, y luego construye un sistema equilibrado que se basa en esa métrica. Es importante darse cuenta de que la tasa máxima de consumo (MCR) es puramente una medida del rendimiento de datos de SQL Server para un único núcleo y no incluye operaciones de lectura de disco ni E / S de red. El objetivo es determinar el MCR del núcleo y utilizarlo para calcular el número de núcleos que se requieren para soportar la tasa de rendimiento de datos de destino. A continuación, puede diseñar el subsistema de almacenamiento y red con un conjunto equilibrado de componentes que alimentarán los datos a los núcleos de la CPU a una velocidad suficiente para mantener el MCR.

En resumen:

• MCR no es definitivo de los resultados reales para una carga de trabajo del cliente.
• MCR proporciona una línea de base de velocidad de procesamiento de datos máxima para SQL Server.
• MCR es específico para una CPU y un servidor. En general, las tasas para una CPU determinada no varían mucho según la arquitectura del servidor y la placa base, pero la MCR final debe determinarse mediante pruebas reales.

MCR es específico para una combinación de una CPU y una placa base, y SQL Server. No es una medida de velocidad de procesamiento pura o una indicación del rendimiento que puede esperar para todas las consultas de solución. En su lugar, MCR es una medida de referencia específica del sistema de rendimiento máximo por núcleo para cargas de trabajo de consulta OLAP o OLTP. El cálculo de MCR requiere la ejecución de una consulta que puede satisfacerse desde la memoria caché mientras limita la ejecución a un solo núcleo y revisa las estadísticas de ejecución para determinar el número de megabytes de datos procesados ​​por segundo.

Esta métrica mide la máxima velocidad de procesamiento de datos de SQL Server para una consulta estándar y un conjunto de datos para una combinación de servidor y CPU específica. Esto se proporciona como una tasa por núcleo, y se mide como una exploración basada en la consulta de la memoria caché. MCR es el punto de partida inicial para el diseño del sistema. Representa un ancho de banda de E / S máximo estimado para el servidor, la CPU y la carga de trabajo. MCR es útil como una guía de diseño inicial porque requiere sólo un almacenamiento local mínimo y un esquema de base de datos para estimar el rendimiento potencial de una CPU determinada. Es importante reforzar que el MCR se utiliza como punto de partida para el diseño del sistema - no es una medida del rendimiento del sistema.

Calcular el MCR

Una tasa de consumo de CPU de línea de base para la aplicación de SQL Server se establece mediante la ejecución de una consulta SQL estándar. Esta consulta está diseñada para ser una representación relativamente simple de una consulta típica para el tipo de carga de trabajo (en este caso OLTP) y se ejecuta desde caché de búfer. El valor resultante es específico de la CPU y del servidor en el que se ejecuta la consulta.

Notas:

1.    La consulta debe mantener un tiempo mínimo de ejecución de un segundo.

2.    Asegúrese de que la configuración del Administrador de recursos tenga valores predeterminados.

3.    Asegúrese de que la consulta se está ejecutando desde la caché del búfer.

4.    Establezca STATISTICS IO y STATISTICS TIME en ON para obtener resultados.

5.    Ejecute la consulta varias veces, en MAXDOP = # núcleo por procesador Socket.
(Por ejemplo: 4). 

La siguiente consulta se utiliza para calcular MCR: -

SET STATISTICS IO ON;SET STATISTICS TIME ON;SELECT pivot.[SalesPersonID], pivot.[FullName], pivot.[JobTitle], pivot.[SalesTerritory], pivot.[2011], pivot.[2012], pivot.[2013], pivot.[2014] FROM (SELECT  soh.[SalesPersonID], p.[FirstName] + ' ' + COALESCE (p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName], e.[JobTitle], st.[Name] AS [SalesTerritory], soh.[SubTotal], YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] FROM [SalesPerson] sp INNER JOIN [SalesOrderHeader] soh ON sp.[BusinessEntityID] = soh.[SalesPersonID] INNER JOIN  [SalesTerritory] st ON sp.[TerritoryID] = st.[TerritoryID] INNER JOIN [Employee] e ON soh.[SalesPersonID] = e.[BusinessEntityID] INNER JOIN [Person] p ON p.[BusinessEntityID] = sp.[BusinessEntityID]) AS soh PIVOT (SUM([SubTotal]) FOR [FiscalYear] IN ([2011], [2012], [2013],[2014])) AS pivot OPTION (MaxDop 4)
GO 5

A continuación se muestra el resultado de las estadísticas de E / S y tiempo de la consulta anterior.

Beginning execution loop

SQL Server parse and compile time:

   CPU time = 312 ms, elapsed time = 313 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

(14 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 43769, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 SQL Server Execution Times:

   CPU time = 2328 ms,  elapsed time = 2347 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

(14 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 43769, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 SQL Server Execution Times:

   CPU time = 2203 ms,  elapsed time = 2212 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

(14 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 43769, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 2235 ms,  elapsed time = 2304 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

(14 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 43769, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 SQL Server Execution Times:

   CPU time = 2234 ms,  elapsed time = 2271 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

 

(14 row(s) affected)

Table 'Employee'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesTerritory'. Scan count 0, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'SalesOrderHeader'. Scan count 1, logical reads 43769, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 SQL Server Execution Times:

   CPU time = 2266 ms,  elapsed time = 2293 ms.

Batch execution completed 5 times.

Permite calcular MCR mediante la siguiente fórmula:

(Promedio de lecturas lógicas / Promedio de tiempo de CPU) (Sec)) * 8/1024

(28 + 28 + 34 + 51 + 0 + 0 + 43769) * 5) / 5) = 43910  Promedio de Lecturas  Lógicas

(2328 + 2203 + 2235 + 2234 + 2266) / 5 = 2253.2 Tiempo Promedio de la CPU (milisegundos)

(43910 / 2,253) * 8/1024 = 152,26 MB / S por núcleo

Enfatice que MCR es específico al sistema en el cual fue calculado. Sin embargo, las arquitecturas similares de procesadores y placas base probablemente exhibirán MCRs comparables. Si los estudiantes tienen la intención de usar sistemas de Fast Track Data Warehouse de socios de hardware, el MCR para estos sistemas se publica.

Determinación de los requisitos de los núcleos del procesador

Después de encontrar el valor de MCR para cada núcleo de CPU, puede comenzar a estimar el número de núcleos necesarios para soportar cargas de trabajo de consulta. Recuerde que MCR indica la cantidad de datos que puede procesar un núcleo de procesador en un segundo.

Para determinar el número de núcleos de procesador necesarios, es necesario conocer los siguientes factores: -

•       La cantidad de datos es devuelta por la carga de trabajo promedio de la consulta.

•       Número de usuarios simultáneos.

•       Tiempo de respuesta de destino para la carga de trabajo de la consulta.

Debe aplicarse la siguiente fórmula: -

((Tamaño Promedio de resultados de la consulta (MB) / MCR) * Usuarios concurrentes) / Target Time (Sec)

El MCR del procesador que vamos a utilizar es 152.26 MB / s, y el resultado medio esperado de la consulta es 343 MB. Debido a la característica OLTP, el número de usuarios simultáneos es de 200 usuarios y nuestro tiempo objetivo es de 5 segundos. El siguiente cálculo determina el número de núcleos de procesador que se requieren para dicho entorno.

((343 / 152,26) * 200) / 5 = 90 núcleos del procesador

Arquitectura de la CPU no incluye exactamente 90 núcleos, por lo que redondear hasta un requisito de 96.

Recuerde que necesita equilibrar el número de CPUs para que coincida con el número de matrices de almacenamiento que se utilizarán, lo que a su vez puede depender del volumen de datos que su almacén de datos debe soportar.

Determinación de los requisitos de memoria (RAM)

Cálculo de la cantidad de RAM que se requiere es difícil porque muchas cargas de trabajo pueden utilizar la memoria para aumentar el rendimiento general. En general, debe considerar una cifra mínima para un OLTP pequeño o mediano OLAP o sistema de ser de 4 GB por núcleo, o de 64 a 128 GB por socket de la CPU. Si tiene la intención de utilizar índices columnstore o soportar modelos de datos tabulares en el servidor de almacén de datos, debe favorecer el extremo superior de estas estimaciones. Otra forma de estimar los requisitos de memoria es considerar que, en una carga de trabajo promedio de almacén de datos, los usuarios necesitan regularmente acceder a aproximadamente el 20 por ciento de los datos almacenados.

Determinación de los requisitos de almacenamiento de la base de datos

Antes de poder determinar completamente los requisitos de CPU, memoria y hardware de almacenamiento, debe evaluar el volumen de datos que debe soportar el sistema. La mayoría de las bases de datos consisten principalmente en datos. Determinar el volumen de datos que la base de datos debe almacenar es el factor más importante en la evaluación de los requisitos generales de almacenamiento.

Para comenzar a estimar los volúmenes de datos, determine el número de filas que se cargarán inicialmente en la base de datos y multiplíquela por el tamaño promedio de una fila. Si no conoce el tamaño promedio de la fila en esta etapa, utilice una estimación conservadora tal como 100 bytes por fila. Por ejemplo, una base de datos que contendrá 200.000.000 filas, cada 100 bytes de longitud, tendrá un volumen de datos de hechos inicial de aproximadamente 20 GB.

Después de estimar los datos iniciales, añada aproximadamente 30 a 40 por ciento para permitir los índices. Por lo tanto, para continuar el ejemplo con 20 GB de datos, se sumarían aproximadamente 8 GB (40 por ciento de 20 GB), dando un volumen de datos inicial de aproximadamente 28 GB.

Para asegurarse de que su solución de almacenamiento soportará la base de datos en el futuro (digamos, en tres años a partir de ahora), debe tener en cuenta los datos incrementales anticipados que se cargarán. Por ejemplo, supongamos que los datos de hecho en nuestra base de datos representan los artículos individuales que se han ordenado en las transacciones de ventas y la empresa suele vender 5.000.000 de artículos al mes, puede esperar cargar 5.000.000 de filas (cada una con 100 bytes de datos) o aproximadamente 500 MB cada mes. Eso equivale a una tasa de crecimiento de datos de 6 GB por año, por lo que en tres años, la base de datos de ejemplo necesitaría soportar los 28 GB de datos iniciales más otros 18 GB (6 GB al año multiplicados por tres años), dando un total de 46 GB.

Puede planear para comprimir los datos en su base de datos. Normalmente, SQL Server proporciona un factor de compresión de aproximadamente 3: 1, por lo que los 46 GB de datos deben comprimir a aproximadamente 15,5 GB en disco

Además de la base de datos, debe incluir otros datos en su estimación de almacenamiento. Se requiere almacenamiento adicional para:

•       Bases de datos de configuración: si las bases de datos que son utilizadas por otros servicios de BI, incluidas las bases de datos de SSIS Catalog y Reporting Services, se deben instalar en el servidor de bases de datos, debe incluirlas en su estimación de almacenamiento. Además, la instancia de SQL Server incluye bases de datos del sistema, aunque en la práctica, éstas suelen almacenarse por separado de los archivos de datos de la base de datos.

•       Archivos de registro de transacciones: Cada base de datos requiere un registro de transacciones. Normalmente, las bases de datos se configuran para utilizar el modelo de recuperación simple (para Data Warehouse) y el modelo de recuperación completa (para OLTP).

•       TempDB: muchas consultas de base de datos requieren espacio de almacenamiento temporal. Generalmente se recomienda localizar tempdb en una columna de almacenamiento adecuada y asignar un tamaño inicial adecuado para evitar que el sistema lo haga crecer automáticamente según sea necesario. •

•       Tablas de espera: si los datos se almacenan en una base de datos de almacenamiento temporal dedicada, en tablas dentro de la propia base de datos o en una combinación de ambos, debe asignar espacio suficiente para permitir la estadificación de datos durante los procesos de ETL.

•       Copias de seguridad: si tiene la intención de realizar una copia de seguridad de la base de datos y otras bases de datos en disco, debe asegurarse de que el diseño de almacenamiento proporcione espacio para los archivos de copia de seguridad. •

•       Modelos de Analysis Services: si tiene la intención de alojar modelos de datos de Analysis Services multidimensionales en el servidor de almacén de datos, debe asignar suficiente espacio en disco para ellos.

Consideraciones sobre el sistema de almacenamiento

La solución de hardware de almacenamiento óptima para un almacén de datos depende de varios factores, incluido el volumen de datos y el sistema MCR que debe soportar el rendimiento de datos del sistema de almacenamiento. Al planear una solución de almacenamiento, considere las siguientes pautas:

•       Tamaño del disco: Utilice más, discos más pequeños en lugar de menos, discos más grandes. Aunque es posible crear una base de datos que almacena todos sus datos en un único disco duro grande, normalmente es posible lograr un mejor equilibrio de rendimiento (y, por lo tanto, rendimiento general del sistema) distribuyendo los datos a través de múltiples discos pequeños. Esto permite realizar varias lecturas de disco en paralelo y reduce los tiempos de espera para las operaciones de E / S.

•       Velocidad del disco: utilice los discos más rápidos que pueda permitirse. Las tecnologías de disco han avanzado dramáticamente en los últimos años, con la velocidad de los discos mecánicos aumentando y la llegada de discos de estado sólido sin partes móviles. Sin embargo, un disco más rápido significa un mayor rendimiento al leer datos. Los discos de estado sólido suelen ser más caros que los discos mecánicos, pero si el rendimiento del disco es crítico, puede decidir que el costo adicional vale la pena pagar. La falta de partes móviles hace que sean particularmente eficaces para el acceso aleatorio de datos de E / S, que es típico de las consultas contra la base de datos.

•       RAID: Utilice RAID 10 o RAID mínimo 5. RAID 10 (en el que los datos están reflejados y rayados) proporciona el mejor equilibrio entre el rendimiento de lectura y la protección frente a fallos de disco, lo que suele ser la primera opción para las bases de datos. Sin embargo, el requisito de un conjunto completo de discos redundantes por arreglo puede hacer que esta opción sea costosa. Como alternativa, puede utilizar RAID 5, que proporciona rayas para un alto rendimiento de lectura y redundancia de datos basada en paridad para proteger contra fallas de disco.

Fuente: 20767- Course Implementación de SQL Data Warehouse