SQL Server lee lógico - ¿Qué es lo que realmente nos dicen?

Seguimiento de SQL Server, los administradores de bases herramienta más de uso común para evaluar el rendimiento de las consultas, ofrece la 'lógica dice' contador en el que los DBA muchos confían para evaluar una consulta de rendimiento I / O. En este artículo vamos a examinar verdadero significado de este contador, con ejemplos que demuestran que a veces puede ser muy engañosa ...
Estoy seguro de que tiene todas las trazas de SQL utiliza para evaluar el rendimiento de las consultas y los lotes. Las columnas de datos más utilizados para este propósito son Duración , CPU , escribe y lee . Estos son, en realidad el verdadero rendimiento de las métricas sólo está disponible para un evento de seguimiento de SQL Server. Un error común que he encontrado en numerosas conversaciones con administradores de bases es que "la reducción del número de lecturas realiza una consulta es un aspecto importante para mejorar su rendimiento" . Aunque esto puede llegar a ser cierto en muchos casos, en este artículo quiero llamar su atención sobre el hecho de que "no es necesariamente así"? A veces ocurre lo contrario.
En primer lugar, tenemos que entender lo que es una lectura que realmente es. He aquí una cita de un documento técnico de Microsoft acerca de E / S, que defina claramente lógico y físico dice: "La E / S de una instancia de SQL Server se divide en lógica y física de I / O. Una lectura lógica se produce cada vez que las solicitudes de motor de base de una página de la caché del búfer. Si la página no se encuentra actualmente en la caché del búfer, una lectura física se realiza entonces a leer la página en la caché del búfer. Si la página se encuentra actualmente en la caché, no se lee física se genera, la caché del búfer simplemente usa la página ya en la memoria ".
Es importante recordar que el seguimiento de SQL lee son lógicas lee y lee, no física. Supongo que esto tiene que ver con el hecho de que lecturas físicas son comunes a todos en la actualidad (y poco) los lotes que se ejecuta en el servidor y por lo tanto no son realmente asignado a un caso específico, a pesar de que siempre son desencadenados por un evento específico. Según el contenido actual de la caché, la misma consulta o lote puede o no puede desencadenar una lectura física. Lecturas físicas son la verdadera causa del dolor en cuanto a rendimiento se refiere, porque los subsistemas de almacenamiento, por desgracia (todavía) por sí lento, a diferencia de lecturas lógicas que se producen en los módulos de memoria súper rápida moderna.

* Física lee métricas están disponibles al utilizar ESTADÍSTICAS E / S y de SQL Server DMV.

Creo que la mejor manera de hacer que mi punto es el uso de un ejemplo. Para esta demostración he utilizado SQL Server 2005 Service Pack 3 y el " AdventureWorks "base de datos de la muestra.

* Como no podemos saber de antemano lo que el contenido de la caché será en cualquier punto dado en el tiempo, el código de ejemplo, he explícitamente vacía la memoria caché para simular el peor de los casos de una caché de vacío.

Tomemos el siguiente ejemplo de consulta y asumir que me dieron la tarea de optimizar su rendimiento:

SELECCIONAR      C . CustomerID , SOH . SalesOrderID , SOH . OrderDate

DE        Ventas . cliente C

            INTERIOR SE UNEN

            Ventas . SOH SalesOrderHeader

            EN SOH . CustomerID = C . CustomerID

DONDE       C . TerritoryID = 1 Y C . CustomerType = N '
Las métricas para esta consulta grabé en mi PC a través de perfiles son los siguientes:

Nota: Las cifras a continuación indican los promedios de varias ejecuciones, con un caché de frío.

CPU: ~ 40, dice: ~ 840 ~ Duración 300 ms.
Los "índices que faltan" opción en el estudio de la gestión me sugirió que añadir el siguiente índice:

/ *

Falta Índice de Datos de logical_reads.sql - AMI-PC.AdventureWorks (DBSOPHIC \ Ami (52))

El procesador de consultas estima que la aplicación del siguiente índice podría mejorar el costo de la consulta por el 13,1751%.

* /

/ *

USO [AdventureWorks]

GO

CREAR índice no agrupado [<Nombre de la falta Index, sysname,>]

ON [ventas]. [Cliente] ([TerritoryID], [CustomerType])

INCLUYE ([IdCliente])

GO

* /
Al ser un obediente DBA, inmediatamente obligado, creado este índice y, a continuación ejecuta la misma consulta de nuevo. Esta vez, tengo las métricas de rendimiento siguientes perfiles:
CPU: ~ 20, dice: ~ 4000, Duración ~ 200ms.
¿Qué pasó aquí? Se añade este índice mejorar el rendimiento de la consulta o se empeoran? Parece que tengo algo de información contradictoria aquí. La CPU y la duración parecen haber mejorado significativamente, pero la lógica dice se han incrementado en casi un factor de 5!
Para entender lo que está pasando detrás del escenario, debemos consultar el plan de ejecución.
Este es el plan de ejecución original de la consulta antes de que el índice sugerido fue creado:

Y éste es el plan que el optimizador eligió después de que el índice sugerido fue creado:

En el plan original, el optimizador decidieron sumarse a las tablas mediante un hash coinciden con operador físico. En un partido de hash, cada valor de customerID de cada una de las tablas combinadas se debe acceder una sola vez. Los cubos clave hash se construyeron en el CustomerID valores de los clientes de la tabla y luego probaron mediante el escaneo de la SalesOrderHeader mesa. Cada página necesita para ser leído sólo una vez y todos los valores recuperados. Puede comprobar esto examinando el número de páginas utilizadas por ambas tablas - el cliente de mesa es de ~ 110 páginas de gran tamaño y la SalesOrderHeader mesa es de ~ 700 páginas. Esto explica el ~ 800 dice que vio en la traza. También es importante recordar que la tabla hash se probó 31465 veces - una vez por cada tecla del SalesOrderHeader mesa. Estas puntas de prueba, que no consumen recursos, no constituyen una lectura lógica y no están disponibles como contadores independientes en el seguimiento de SQL y en STATISTICS IO.
Después se creó el índice, el optimizador había muchas más opciones para trabajar. El índice en el cliente de mesa permite buscar filtrado eficiente tanto en el TerritoryID y CustomerType predicados, lo que resulta en menos de un 100 filas que satisfacen los filtros. El optimizador decidió (correctamente) que la realización de un operador de bucles anidados, la recuperación de los punteros a todas las filas correspondientes de los clientes de mesa, y luego de realizar una búsqueda de claves para recuperar el OrderDate y IdPedido columnas de la lista de selección sería más eficiente. Ahora, debido a que cada fila tenía que ser «índice buscado 'por separado, las mismas páginas necesarias para tener acceso en varias veces la memoria, cada uno cuenta como una lectura lógica. Como resultado, el número total de lecturas lógicas aumentado significativamente.
 En general, el rendimiento de las consultas de manera significativa mejora en varios aspectos:
·          Sólo el subconjunto correcto de las filas de la tabla de clientes se ha recuperado, lo que podría reducir la E / S física y la contención de bloqueos

·          Las funciones hash intensivo de la CPU se eliminaron

·          El consumo de memoria para ambos y hash cubos de datos se redujo

Este ejemplo demuestra claramente que la hora de evaluar el rendimiento de una consulta, siempre se debe tener en cuenta todos los aspectos de su ejecución y recuerda que la lógica dice métricas puede ser muy engañoso en algunos casos. Vaciar la caché de búfer y utilización de estadísticas de E / S en cambio, le proporcionará la física dice contador que, en muchos casos, más acordes con la E / S demandas de la consulta analizada.

Una reflexión final: Aunque no hay recomendaciones índice más fueron sugeridas por el optimizador de consultas, se puede pensar en un índice adicional que podría mejorar el rendimiento de la consulta aún más?