SQL Server

Optimización del rendimiento de las consultas de SQL Server

Maciej Pilecki

 

Resumen:

  • Análisis de los planes de ejecución
  • Optimización de consultas
  • Identificación de consultas que optimizar

Al optimizar el servidor de la base de datos, necesita también mejorar el rendimiento de las consultas individuales. Esto es igual de importante, o incluso más, que optimizar otros aspectos de la instalación del servidor, aspectos que afectan al rendimiento, como las configuraciones de hardware y software.

Incluso si el servidor de la base de datos se ejecuta en el hardware más eficaz, su rendimiento puede verse afectado negativamente por un conjunto de consultas que se comporten incorrectamente. De hecho, incluso una sola consulta incorrecta, a veces denominada "consulta descontrolada", puede provocar serios problemas de rendimiento a la base de datos.

De manera opuesta, el rendimiento general de la base de datos puede mejorarse en gran medida al optimizar el conjunto de las consultas más costosas o que se ejecutan con mayor frecuencia. En este artículo, trataré algunas de las técnicas que puede emplear para identificar y optimizar las consultas más costosas y las de peor rendimiento del servidor.

Análisis de los planes de ejecución

Para optimizar una consulta individual, normalmente se empieza observando el plan de ejecución de esa consulta. El plan de ejecución describe la secuencia de operaciones, físicas y lógicas, que SQL ServerTM realizará con el objeto de cumplir la consulta y producir el conjunto de resultados deseado. El plan de ejecución lo genera un componente motor de la base de datos llamado Optimizador de consultas durante la fase de optimización del procesamiento de la consulta, esto tiene en cuenta muchos factores diferentes, como los predicados de búsqueda en la consulta, las tablas implicadas y sus condiciones de combinación, la lista de columnas devueltas y la presencia de índices útiles que pueden servir como rutas de acceso eficaces a los datos.

Para las consultas más complejas, el número de todas las permutaciones posibles puede ser muy grande, por lo que el optimizador de consultas no evalúa todas posibilidades, sino que en su lugar intenta encontrar un plan que sea "lo suficientemente bueno" para una consulta determinada. Esto es porque el encontrar un plan perfecto no siempre es posible; e incluso cuando es posible, el costo de la evaluación de todas las posibilidades para encontrar que el plan perfecto podría fácilmente tener más peso que cualquier ganancia de rendimiento. Desde el punto de vista de los administradores de bases de datos, es importante comprender el proceso y sus limitaciones.

Hay varias maneras de recuperar un plan de ejecución para una consulta:

  • Management Studio proporciona las características Mostrar el plan de ejecución real y Mostrar el plan de ejecución estimado, que presentan el plan de una manera gráfica. Estas características ofrecen la solución más conveniente para el examen directo y constituyen, sin duda, el enfoque usado con más frecuencia para mostrar y analizar los planes de ejecución. (En este artículo, usaré los planes gráficos generados de este modo para ilustrar mis ejemplos).
  • Varias opciones SET, como SHOWPLAN_XML y SHOWPLAN_ALL, devuelven el plan de ejecución en forma de documento XML que describe el plan mediante un esquema o como un conjunto de filas con descripciones textuales de cada una de las operaciones en el plan de ejecución.
  • Las clases de evento del Analizador de SQL Server, como Showplan XML, le permiten reunir los planes de ejecución de instrucciones recopiladas gracias al seguimiento.

Aunque una representación XML del plan de ejecución puede no ser el formato más sencillo para que las personas lo lean, esta opción le permite escribir los procedimientos y utilidades que pueden analizar sus planes de ejecución, buscar signos de problemas de rendimiento y planes poco óptimos. Una representación basada en XML también puede guardarse en un archivo con la extensión .sqlplan y abrirse, a continuación, en Management Studio para producir una representación gráfica. Estos archivos pueden guardarse también para un análisis posterior, eliminando, por supuesto, la necesidad de reproducir el plan de la ejecución cada vez que desee analizarlo. Esto es especialmente útil cuando desea comparar sus planes para ver cómo cambian con el tiempo.

Costo estimado de la ejecución

Lo primero que tiene que entender acerca de los planes de ejecución es cómo se generan. SQL Server usa un optimizador de consultas basado en el costo, es decir, intenta generar un plan de ejecución con el costo estimado más bajo. La estimación se basa en las estadísticas de distribución de datos que están disponibles para el optimizador cuando evalúa cada tabla implicada en la consulta. Si esas estadísticas se pierden o quedan obsoletas, el optimizador de consultas carecerá de la información esencial necesaria para el proceso de optimización de consultas y, por lo tanto, es probable que las estimaciones queden fuera de la marca. En dichos casos, el optimizador seleccionará un plan menos óptimo tanto sobrestimando como subestimando los costos de ejecución de los distintos planes.

Hay algunas suposiciones falsas muy comunes acerca del costo estimado de ejecución. En particular, con frecuencia las personas asumen que el costo estimado de ejecución es un buen indicador de cuánto tiempo tardará la consulta en ejecutarse y que esta estimación les permite distinguir los planes buenos de los que no lo son. Esto no es verdad. En primer lugar, existe la suficiente documentación sobre lo que son las unidades para expresar el costo estimado y si éstas tienen alguna relación directa con el tiempo de ejecución. En segundo lugar, puesto que se trata de una estimación y podría estar equivocada, en ocasiones los planes con costos estimados más altos pueden ser mucho más efectivos en términos de CPU, E/S y del tiempo de ejecución, a pesar de que sus estimaciones sean más altas. Este caso se suele dar con las consultas que implican variables de tabla, ya que para éstas no hay estadísticas disponibles, el optimizador de consultas siempre asume que una variable de tabla contiene sólo un fila, aunque contenga un número mucho mayor. Además, el optimizador de consultas elegirá un plan basado en una estimación inexacta. Por lo tanto, al comparar los planes de ejecución de las consultas, no debe depender únicamente del costo estimado de la consulta. En su lugar, incluya el resultado de las opciones STATISTICS I/O y STATISTICS TIME en el análisis, para entender cuál es realmente el costo de la ejecución en términos de E/S y tiempo de la CPU.

Merece también la pena mencionar aquí un tipo especial de plan de ejecución, llamado plan paralelo. Si ejecuta su consulta en un servidor con más de una CPU y su consulta puede optar por la paralelización, puede que se elija un plan paralelo. (Generalmente, el optimizador de consultas sólo considerará un plan paralelo para una consulta que tenga un costo que supere cierto umbral configurable). Debido a la sobrecarga generada al administrar múltiples subprocesos paralelos de ejecución (lo cual implica distribuir el trabajo a través de los subprocesos, llevar a cabo la sincronización y reunir los resultados), los planes paralelos son más costosos de ejecutar y eso se refleja en su costo estimado. Así que, ¿por qué se eligen frente a planes más baratos que no son paralelos? Gracias al uso de la eficacia de procesamiento de varias CPU, los planes paralelos tienden a generar los resultados más rápido que los planes estándar. Dependiendo de su situación específica, incluidas variables como los recursos disponibles y la carga simultánea de otras consultas, esta situación puede ser deseable para su instalación. Si éste fuera el caso, debe controlar cuál de sus consultas puede producir planes paralelos y cuántas CPU pueden usar cada uno. Esto lo puede hacer configurando el grado máximo de la opción de paralelismo en el nivel de servidor e invalidándolo en el nivel de consulta individual con OPTION (MAXDOP n) tal como considere necesario.

Análisis de un plan de ejecución

Ahora me centraré en una consulta sencilla, en su plan de ejecución y en algunos métodos para mejorar su rendimiento. Supongamos que ejecuto esta consulta mediante Management Studio con la opción Incluir plan de ejecución real activada en la base de datos de ejemplo de Adventure Works en SQL Server 2005:

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

Como resultado, veo el plan de ejecución descrito en la figura 1. Esta consulta sencilla calcula la cantidad total de pedidos realizados por cada cliente de Adventure Works. Al observar el plan de ejecución, puede consultar cómo el motor de la base de datos procesa la consulta y produce el resultado. Los planes de ejecución gráficos deben leerse de arriba a abajo y de derecha a izquierda. Cada icono representa una operación lógica y física realizada y las flechas muestran flujo de datos entre las operaciones. El grosor de las flechas representa el número de filas que se transmite entre las operaciones, cuanto más gruesa es la flecha, más filas habrá implicadas. Si coloca el puntero sobre uno de los iconos del operador, la información sobre herramientas en color amarillo (como la mostrada en la figura 2) mostrará detalles de esa operación en particular.

Figura 1 Plan de ejecución de ejemplo

Figura 1 Plan de ejecución de ejemplo(Hacer clic en la imagen para ampliarla)

Figura 2 Detalles sobre una operación

Figura 2 Detalles sobre una operación(Hacer clic en la imagen para ampliarla)

Observando cada uno de los operadores, puede analizar la secuencia de pasos realizados:

  1. El motor de la base de datos realiza una operación Clustered Index Scan en la tabla Sales.Customer y devuelve la columna CustomerID para todas las filas de esa tabla.
  2. A continuación, realiza un Index Scan (lo cual significa que realiza un examen de índice no agrupado) en uno de los índices de la tabla Sales.SalesOrderHeader. Éste es un índice de la columna CustomerID, pero también incluye implícitamente la columna SalesOrderID (la clave de clúster de la tabla). El examen devuelve los valores de ambas columnas.
  3. El resultado de ambos exámenes se une en la columna CustomerID mediante el operador físico Merge Join. (Esta es una de tres maneras físicas posibles de realizar una operación de unión lógica. Es rápido pero requiere la reorganización de ambas entradas en una columna unida. En este caso, ambas operaciones de examen ya han devuelto las filas reorganizadas en CustomerID, por lo que no hay necesidad de realizar la operación Ordenar adicional).
  4. A continuación, el motor de la base de datos realiza un examen del índice agrupado en la tabla Sales.SalesOrderDetail, recuperando los valores de cuatro columnas (SalesOrderID, OrderQty, UnitPrice y UnitPriceDiscount) de todas filas en esta tabla. (Se había estimado que esta operación devolvería 123.317 filas y esa fue la cifra realmente devuelta, tal como puede observar en las propiedades de número estimado de filas y número real de filas de la figura 2, por lo que la estimación fue bastante acertada).
  5. Las filas producidas por el examen de índice agrupado se transmiten al primer operador Compute Scalar de manera que el valor de la columna calculada LineTotal pueda calcularse para cada fila, según las columnas OrderQty, UnitPrice y UnitPriceDiscount implicadas en la fórmula.
  6. El segundo operador Compute Scalar aplica la función ISNULL al resultado del cálculo anterior, tal como lo requiera la fórmula de la columna calculada. Esto completa el cálculo de la columna LineTotal y lo devuelve, junto con la columna SalesOrderID, al operador siguiente.
  7. El resultado del operador Merge Join del paso 3 se une con el resultado del operador Compute Scalar del paso 6, mediante el operador físico Hash Match.
  8. A continuación, se aplica otro operador Hash Match para agrupar las filas devueltas de Merge Join por el valor de la columna CustomerID y el agregado calculado SUM de la columna LineTotal.
  9. El último nodo, SELECT, no es un operador físico ni lógico, sino más bien un marcador de posición que representa los resultados de consulta generales y el costo.

En mi equipo portátil, este plan de ejecución tuvo un costo estimado de 3,31365 (como se muestra en la figura 3). Cuando se ejecutó con STATISTICS I/O ON, la consulta notificó la realización de un total de 1.388 operaciones de lectura lógicas en las tres tablas implicadas. Los porcentajes que se muestran bajo cada uno de los operadores representan el costo de cada operador individual en relación al costo general estimado de todo el plan de ejecución. Si observa el plan de la figura 1, puede ver que la mayor parte del costo total de todo el plan de ejecución se asocia con los tres operadores siguientes: el Clustered Index Scan de la tabla Sales.SalesOrderDetail y los dos operadores Hash Match. Pero antes de seguir con la optimización de éstos, me gustaría señalar un cambio muy sencillo en mi consulta que me permitirá eliminar dos de los operadores por completo.

Figura 3 Costo total estimado de ejecución de la consulta

Figura 3 Costo total estimado de ejecución de la consulta

Puesto que la única columna que devuelvo de la tabla Sales.Customer es CustomerID y esta columna se incluye también como una clave externa en Sales.SalesOrderHeaderTable, puedo eliminar completamente la tabla Customer de la consulta sin cambiar el significado lógico o el resultado producido por nuestra consulta mediante este código:

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

Esto produce un plan de ejecución distinto, que se muestra en la figura 4.

Figura 4 Plan de ejecución después de eliminar la tabla Customer de la consulta

Figura 4 Plan de ejecución después de eliminar la tabla Customer de la consulta(Hacer clic en la imagen para ampliarla)

Se eliminaron por completo dos operaciones, Clustered Index Scan en la tabla Customer y Merge Join entre Customer y SalesOrderHeader, y la unión Hash Match se sustituyó por Merge Join, que es más eficaz. Sin embargo, para usar Merge Join entre las tablas SalesOrderHeader y SalesOrderDetail, se tuvieron que devolver las filas de ambas tablas clasificadas por la columna de unión SalesOrderID. Para conseguirlo, el optimizador de consultas decidió realizar un Clustered Index Scan en la tabla SalesOrderHeader, en lugar de usar un Non-Clustered Index Scan, que hubiera sido más barato en términos de la E/S implicada. Se trata de un buen ejemplo sobre cómo trabaja en la práctica el optimizador, puesto que los ahorros en el costo por cambiar la manera física de llevar a cabo la operación de unión fueron mayores que los costos de E/S adicionales generados por el Clustered Index Scan, el optimizador de consultas eligió la combinación resultante de operadores porque ofreció el costo total estimado de ejecución más reducido posible. En mi equipo portátil, aunque el número de lecturas lógicas subió (a 1.941), el tiempo de CPU consumido fue realmente más pequeño y el costo de ejecución estimado de esta consulta se redujo alrededor de un 13 por ciento (2,89548).

Digamos que deseo mejorar aún más el rendimiento de esta consulta. Ahora observo el Clustered Index Scan de la tabla SalesOrderHeader, que se ha convertido en el operador más caro de este plan de ejecución. Puesto que sólo necesito dos columnas de esta tabla para llevar a cabo la consulta, puedo crear un índice no agrupado que contenga sólo esas dos columnas, reemplazando el examen de toda la tabla por uno del índice no agrupado, mucho más pequeño. La definición del índice puede que tenga este aspecto:

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

Tenga en cuenta que el índice que he creado incluye una columna calculada. Esto no es siempre posible, dependiendo de la definición de la columna calculada.

Después que crear este índice y ejecutar la misma consulta, obtengo el nuevo plan de ejecución mostrado en la figura 5.

Figura 5 Plan de ejecución optimizado

Figura 5 Plan de ejecución optimizado(Hacer clic en la imagen para ampliarla)

El Clustered Index Scan de la tabla SalesOrderDetail se ha reemplazado por un Non-Clustered Index Scan con un costo de E/S mucho más reducido. También he eliminado uno de los operadores Compute Scalar, puesto que mi índice incluye un valor ya calculado de la columna LineTotal. El costo estimado del plan de ejecución es ahora 2,28112 y la consulta realiza 1.125 lecturas lógicas cuando se ejecuta.

El índice de cobertura

Ejercicio de consulta de pedido del cliente

P Aquí tiene un ejercicio de consulta de pedido del cliente: trate de averiguar la definición del índice, qué columnas debe contener para convertirse en un índice de cobertura para esta consulta y si el orden de las columnas en la definición del índice provocaría alguna diferencia en el rendimiento.

R Le reté a averiguar cuál sería el índice de cobertura más óptimo de la tabla Sales.SalesOrderHeader para la consulta de ejemplo en mi artículo. Para conseguirlo, lo primero que debe tener en cuenta es que la consulta usa sólo dos columnas de la tabla: CustomerID y SalesOrderID. Si ha leído mi artículo detenidamente, habrá advertido que en el caso de la tabla SalesOrderHeader, ya hay un índice existente que cubre esta consulta, se trata del índice en CustomerID y contiene implícitamente la columna SalesOrderID, que es la clave de clúster de la tabla.

Por supuesto, expliqué también por qué el optimizador de consultas decidió no usar este índice. Sí, podría forzar al optimizador de consultas a usar este índice, pero la solución sería menos eficiente que el plan existente que usa los operadores Clustered Index Scan y Merge Join. Esto es porque estaría forzando al optimizador de consultas a elegir entre realizar una operación Sort adicional para poder seguir usando Merge Join o retroceder y usar un Hash Join menos efectivo. Ambas opciones tienen un costo estimado de ejecución más alto que el plan existente (la versión con la operación Sort funcionaría bastante mal), así que el optimizador de consultas no las usará a menos que se le obligue. Así pues, en esta situación, el único índice que funcionaría mejor que el Clustered Index Scan sería un Non-Clustered Index Scan en SalesOrderID, CustomerID. Pero es importante tener en cuenta que las columnas deben estar en ese orden exacto:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

Si crea este índice, el plan de ejecución contendrá el Index Scan y no el operador Clustered Index Scan. Esto constituye una diferencia considerable. En este caso, el índice no agrupado que contiene sólo dos columnas es mucho más pequeño que toda la tabla en forma de índice agrupado. Por lo tanto, requerirá menos E/S para leer los datos necesarios.

Este ejemplo muestra también como el orden de las columnas del índice puede tener una repercusión considerable en la utilidad para el optimizador de consultas. Asegúrese de tener esto en cuenta cuando diseñe los índices de varias columnas.

El índice que he creado en SalesOrderDetail es un ejemplo del llamado "índice de cobertura". Se trata de un índice no agrupado que contiene todas las columnas necesarias para realizar la consulta, eliminando la necesidad de examinar toda la tabla mediante los operadores Table Scan o Clustered Index Scan. El índice es, en esencia, una copia más pequeña de la tabla, que contiene un subconjunto de columnas de la tabla. Sólo esas columnas necesarias para contestar la consulta (o consultas) se incluyen en el índice, en otras palabras, el índice contiene sólo lo que necesita para "cubrir" la consulta.

La creación de índices de cobertura para las consultas más frecuentemente ejecutadas es una de las técnicas más fáciles y comunes que se usa en la optimización de consultas. Funciona especialmente bien en situaciones en las que la tabla contiene un número de columnas pero sólo algunas de ellas son mencionadas con frecuencia por las consultas. Al crear uno o más índices de cobertura, puede mejorar mucho el rendimiento de las consultas afectadas, puesto que éstas obtendrá acceso a una cantidad de datos mucho más pequeña y, por lo tanto, proporcionarán menos E/S. Existe, sin embargo, un costo oculto por mantener los índices adicionales durante las operaciones de modificación de datos (INSERT, UPDATE y DELETE). Dependiendo de su entorno y de la relación entre las consultas SELECT y las modificaciones de datos, debería juzgar detenidamente si los costos adicionales de mantenimiento del índice están justificados por las mejoras de rendimiento de consultas.

No tenga miedo de crear índices de varias columnas en vez de índices de una sola columna. Éstos tienden a ser mucho más útiles que los índices de una sola columna y es más probable que el optimizador de consultas los use para cubrir la consulta. La mayoría de los índices de cobertura son índices de varias columnas.

En el caso de mi consulta de ejemplo, hay todavía posibilidad de incorporar alguna mejora y esta consulta podría optimizarse aún más colocando un índice de cobertura en la tabla SalesOrderHeader. Esto elimina el Clustered Index Scan en favor de un Non-Clustered Index Scan. Esto se lo dejo para que lo haga como ejercicio. Trate de resolver la definición del índice, qué columnas debe contener para convertirse en un índice de cobertura para esta consulta y si el orden de las columnas en la definición del índice provocaría alguna diferencia en el rendimiento. Consulte la barra lateral del "Ejercicio de consulta de pedido del cliente" si desea ver la solución.

Vistas indizadas

Si el rendimiento de mi consulta de ejemplo es muy importante, puedo ir un paso más adelante y crear una vista indizada que almacene físicamente los resultados materializados de la consulta. Hay ciertos requisitos previos y limitaciones de vistas indizadas, pero si se puede usar uno, puede mejorar el rendimiento drásticamente. Tenga presente que las vistas indizadas conllevan un costo más alto de mantenimiento que los índices estándar. Como resultado, debe tener cuidado cuando las use. En este caso, la definición del índice tiene este aspecto:

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID 

Tenga en cuenta la opción WITH SCHEMABINDING, que es un requisito previo para crear un índice en esa vista y la función COUNT_BIG(*), que es necesaria si nuestra definición del índice contiene una función agregada (en este ejemplo, SUM). Después de crear esta vista, puedo crear un índice en ella, como éste:

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID 
ON vTotalCustomerOrders(CustomerID)

Cuando creo este índice, el resultado de la consulta incluida en la definición de la vista se materializa y se almacena físicamente en disco en el índice. Tenga en cuenta que todas las operaciones de modificación de datos en las tablas base a continuación actualizan automáticamente los valores de la vista según su definición.

Si ahora vuelvo a ejecutar la consulta, lo que suceda dependerá de la versión de SQL Server en la que se ejecute. En las versiones Enterprise o Developer, el optimizador de consultas asociará automáticamente esta consulta con la definición de la vista indizada y usará la vista indizada en vez de consultar las tablas base implicadas. La figura 6 muestra el plan de ejecución producido en este caso. Consiste en una sola operación, un Clustered Index Scan del índice que creé en la vista. El costo estimado de ejecución es sólo 0,09023 y sólo realiza 92 lecturas lógicas.

Figura 6 Plan de ejecución cuando se usa una vista indizada

Figura 6 Plan de ejecución cuando se usa una vista indizada(Hacer clic en la imagen para ampliarla)

También puede crear y usar esta vista indizada en otras ediciones de SQL Server, pero para lograr el mismo efecto tiene que cambiar la consulta para hacer referencia a la vista directamente mediante la sugerencia NOEXPAND, así:

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

Cuando puede observar, las vistas indizadas pueden ser una característica muy eficaz si se usan adecuadamente. Son las más útiles para optimizar las consultas que realizan agregaciones sobre grandes cantidades de datos. Si se usa en Enterprise Edition, pueden beneficiar a muchas consultas sin la necesidad de realizar cambios en el código.

Identificación de las consultas a optimizar

¿Cómo identifico las consultas que deben optimizarse? Yo busco las consultas que se ejecutan con frecuencia, es posible que individualmente no tengan un costo muy alto de ejecución, pero el costo agregado de ejecución puede ser mucho más alto que el de una consulta más grande que se ejecuta raramente. No estoy diciendo que no deba optimizar las grandes, simplemente creo que debe concentrarse en primer lugar en las consultas que se ejecutan con más frecuencia. Así que, ¿cómo las identifica?

Desgraciadamente, el método más seguro es algo complicado e implica realizar un seguimiento de todas las consultas ejecutadas en su servidor y, a continuación, agruparlas por sus firmas. (Es decir, texto de consulta con los valores de parámetros reales reemplazados por marcadores con el objeto de identificar el mismo tipo de consulta, incluso si se ejecutó con valores de parámetro diferentes). Se trata de un proceso complicado, puesto que las firmas de consulta son difíciles de generar. Itzik Ben-Gan describe una solución que usa funciones definidas por el usuario de CLR y expresiones regulares en su libro Inside Microsoft SQL Server 2005: T-SQL Querying.

Existe otro método mucho más sencillo, pero de alguna manera menos confiable. Puede confiar en las estadísticas que se mantienen para todas consultas en la memoria caché del plan de ejecución y consultarlas mediante vistas dinámicas de administración. La figura 7 contiene una consulta de ejemplo que le muestra tanto el texto como el plan de ejecución de las 20 consultas en la memoria caché con el número más alto acumulado de lecturas lógicas. Esta consulta resulta muy útil para identificar rápidamente las consultas que generan el número más alto de lecturas lógicas, pero tiene sus limitaciones. Es decir, sólo mostrará las consultas que tienen sus planes en la memoria caché en el momento en el que ejecuta la consulta. Si algo no se copia en la memoria caché, se perderá.

Figure 7 Identificación de las 20 consultas más costosas en términos de E/S de lectura

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Una vez que haya identificado las de más escaso rendimiento, observe sus planes de consulta y busque maneras de mejorar su rendimiento usando algunas de las técnicas de indización que he descrito en este artículo. Si el resultado es satisfactorio, será tiempo bien invertido.

¡Feliz optimización!

Maciej Pilecki es Consejero asociado en Solid Quality Mentors, una organización global especializada en aprendizaje, apoyo y consultoría. Cuenta también con la certificación Microsoft Certified Trainer (MCT) y el programa Most Valuable Professional (MVP) de SQL Server, con frecuencia imparte cursos y conferencias sobre numerosos aspectos de SQL Server y el desarrollo de aplicaciones.

© 2008 Microsoft Corporation and CMP Media, LLC. Reservados todos los derechos; queda prohibida la reproducción parcial o total sin previa autorización.