Capítulo 6 – lección 1: Técnicas para mejorar el rendimiento de consultas

Después de diseñar la base de datos y la aplicación, normalmente se desea asegurarse de que puede ofrecer el rendimiento que los usuarios de aplicaciones exigen. Ahora estudiaremos como encontrar problemas de rendimiento en su base de datos y la forma de resolver los problemas que descubramos. Microsoft SQL Server 2008 es un motor de base de datos extremadamente capaz y eficiente, pero requiere que usted haga algunos ajustes de rendimiento si tiene una gran base de datos, consultas complejas, un gran número de consultas atendidas, o una combinación de estos factores.

Optimización de Consultas y evaluación de rendimiento:
Uno de los aspectos más importantes de las consultas de ajuste es la medición del desempeño. Al medir el rendimiento, lo que necesita saber lo que realmente miden, es decir, qué métricas utilizan. En SQL Server, hay tres indicadores principales a considerar: costo de procesamiento de la consulta, lecturas de páginas, y tiempo de ejecución de la consulta.

Costo de consultas
El costo de la consulta es por lo general (pero no siempre, como pronto veremos) una buena métrica a utilizar al comparar el rendimiento de consulta. Se trata de un indicador interno que se utiliza en SQL Server que tiene en cuenta tanto la CPU y entrada/salida (I/O) de los recursos utilizados por la consulta. Cuanto menor sea el costo de la consulta, en teoría, mejor será el rendimiento de las consultas. El costo de la consulta no se ve afectado por cuestiones tales como conflictos de recursos o de espera para los bloqueos. El costo de la consulta suele ser una buena medición del desempeño, pero cuando algunos elementos utilizados en una consulta, como las funciones escalares definidas por el usuario (UDF) y Common Language Runtime (CLR), muestran un menor costo de consulta, porque el costo de estos elementos no se calcula, lo que no nos da una evaluación realmente exacta.
Es por eso que se llama costo estimado de la consulta.

Lecturas de páginas
Lecturas de página representa la cantidad de datos de 8 kilobytes (KB) de datos que accede por al motor de almacenamiento de SQL Server durante la ejecución de una consulta. Puede recuperar esta medida mediante la ejecución de SET STATISTICS IO ON. Esto hace que cada ejecución de la consulta a la salida algo similar a lo siguiente en la ficha Mensajes de la ventana de consulta:ç

Table 'Customer'. Scan count 2, logical reads 136, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 121, logical reads 822, physical reads 5,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

El total de páginas leídas de este resultado es de 136 + 822, que es la suma de los valores de la etiqueta "logical reads." logical reads es un número de páginas leídas de la memoria. La etiqueta logical reads representa el número total de páginas de lectura de datos de los índices en la tabla SalesOrderHeader.
Los demás elementos le dirán cuántas de las lecturas lógicas fueron leídos desde el disco duro (físico y lectura anticipada), el número de veces a través de un índice o pila que se tardó en responder a la consulta (scan count), y el número de de la página de lecturas utilizadas para recuperar objetos grandes (LOB). Los datos LOB se almacenan fuera de la fila de los tipos correspondientes adatos varchar(max), nvarchar(max), varbinary(max), text, ntext, image, y XML. Las lecturas de página indicadas no tiene en cuenta la cantidad de recursos de la CPU utilizada al ejecutarla consulta. Esta es la razón por la que las lecturas de páginas normalmente no son adecuados como una medida de desempeño como el costo de la consulta. Esta medida también tiene el mismo problema con las funciones UDF escalares y las rutinas CLR como el costo de la consulta, que es la lecturas de páginas causada por estas rutinas las cuales no se incluyen en la salida de STATISTICS IO.

Tiempo de Ejecución de las consulta
El tiempo de ejecución de la consulta es la métrica más inestables. Se ve afectada por los bloqueos (locks), así como tambien por los conflictos de recursos en el servidor. Dicho esto, es especialmente importante incluir siempre el tiempo de ejecución de la consulta en métricas de las comparaciones de rendimiento, ya que le puede ayudar a detectar problemas “omitidos” por las demás métricas de rendimiento (costo de procesamiento de la consulta y lecturas de páginas). Si se ejecuta SET STATISTICS TIME ON, SQL Server devuelve el tiempo de ejecución en milisegundos para cada ejecución de la consulta.

Examinando la orden ejecución de consultas teóricas
Es de vital importancia tener un conocimiento básico de la orden de ejecución teórica de una sentencia SELECT cuando se trabaja con optimización de consultas.Esto ayuda a entender lo que SQL Server en realidad tiene que hacer para producir los resultados de la consulta. Las orden de ejecución teórica que se conoce como "teóricas" ​​porque el optimizador puede cambiar el orden de ejecución real para optimizar el rendimiento.Una orden de ejecución alternativa se utiliza sólo si el resultado de la consulta sería igual de la orden de ejecución teórica a utilizar.
Las ordenes de ejecución deben ser divididas en dos ramas, ya que es diferente si la cláusula UNION está incluido en la consulta o no. Una versión simplificada de la primera rama, sin unión, se describe en la siguiente, que muestra el orden en que las cláusulas se procesan.

Orden de Ejecución teórica-Excluyendo la cláusula UNION


OrdenClausulaResultado
1FROM, JOIN, APPLY, y ONEl join es ejecutado y el filtro de la primera consulta (la cláusula ON) se aplica.
2WHEREEl segundo filtro de la consulta es aplicado
3GROUP BY y funciones de agregado (como SUM, AVG, etc) que se incluyen en la consultaLos cálculos de la agrupación y agregación se realizan.
4HAVINGEl tercer Filtro de consulta (filtrado de los resultados de funciones agregadas) se aplica.
5SELECTLas columnas que deben ser devueltos por la consulta son seleccionados.
6ORDER BYLos resultados se ordenan.
7TOPEl cuarto (y último) filtro de consulta se aplica, Esto ocasiona que la consulta solo devuelva sólo las filas de X primero de los resultados hasta ahora.
8FOR XMLEl resultado tabular devuelto por la sentencia SELECT se convierte en Extensible Markup Language (XML).

Las consultas que utilizan la cláusula UNION utilizar el orden de ejecución teórica que se muestra en la siguiente tabla


Orden
ClausulaResultado
1FROM, JOIN, APPLY, y ONEl join es ejecutado y el filtro de la primera consulta (la cláusula ON) se aplica.
2WHEREEl segundo filtro de la consulta es aplicado
3GROUP BY y funciones de agregado (como SUM, AVG, etc) que se incluyen en la consultaLos cálculos de la agrupación y agregación se realizan.
4HAVINGEl tercer Filtro de consulta (filtrado de los resultados de funciones agregadas) se aplica.
5TOPEl cuarto (y último) filtro de consulta se aplica, esto hace que la consulta devuelva sólo las filas de X primero de los resultados hasta ahora. (Tenga en cuenta que en este caso, la cláusula TOP se ejecuta antes de la cláusula ORDER BY).
6UNION  y SELECTLos resultados de cada instrucción SELECT incluidas en la consulta se concatenan, las columnas que deben ser devueltos por la consulta son seleccionados.
7ORDER BYLos resultados se ordenan.
8FOR XMLEl resultado tabular devuelto por el SELECT con UNION se convierte en XML

La causa de la diferencia en el orden de ejecución es la introducción de la cláusula TOP (en SQL Server 7.0), que no es parte del estándar ANSI / ISO SQL. El comportamiento compatible con el estándar de la cláusula UNION permite que sólo una cláusula ORDER BY, deba ser colocado en la última instrucción SELECT de la consulta y ordenar los resultados de la consulta completa. Esto significa que la cláusula TOP puede estar en cualquier lugar, pero la declaración final SELECT de una consulta UNION devuelve los elementos de X antes de que sean ordenados. Esto puede sonar como una cuestión menor, pero es importante tener en cuenta. Por ejemplo, comparar el resultado de dos consultas, las cuales están pensadas para devolver los dos productos más caros de color rojo y de color negro. La primera consulta, se muestra aquí, produce un resultado incorrecto:
USE AdventureWorks;
SELECT TOP(2) ProductID, Name, Color, ListPrice
FROM Production.Product
WHERE Color = 'Black'
UNION
SELECT TOP(2) ProductID, Name, Color, ListPrice
FROM Production.Product
WHERE Color = 'Red'
ORDER BY ListPrice DESC;
 
Aquí está el resultado (incorrecto) de esta primera consulta:
image
La segunda consulta, se muestra aquí, genera el resultado deseado:
USE AdventureWorks;
WITH a AS (
  SELECT TOP(2) ProductID, Name, Color, ListPrice
  FROM Production.Product
  WHERE Color = 'Black'
  ORDER BY ListPrice DESC
), b AS (
  SELECT TOP(2) ProductID, Name, Color, ListPrice
  FROM Production.Product
  WHERE Color = 'Red'
  ORDER BY ListPrice DESC
)
  SELECT * FROM a
  UNION ALL
  SELECT * FROM b;
image

Como puede ver, la primera consulta no devuelve los valores correctos, porque la cláusula ORDER BY se ejecuta después de la cláusula TOP.

Optimización del Rendimiento de consultas

Hay varias maneras para optimizar las consultas. Optimización consiste en tareas como la reescritura de la consulta, la normalización de las tablas, agregar índices, la eliminación de los índices, o una combinación de estas tareas.

El plan de ejecución gráfico

El plan de ejecución gráfico es una herramienta útil para optimizar las consultas. Algunos de los elementos que debe buscar en el plan de ejecución se muestran en la siguiente Tabla:

Imagen representativaPosible Implicación
flechas gruesas:
image

Una flecha gruesa representa un gran número de filas de pasar de una operación en el plan de ejecución a otro. Cuanto mayor sea el número de filas transferidos de un operación a otra, más gruesa es la flecha.
operaciones de hash:
image
Si una operación de hash se utiliza para manejar cláusulas como GROUP BY y JOIN, a menudo significa que no existe un índice adecuado para optimizar la consulta.
Ordenamientos:
image
Una ordenamiento no es necesariamente malo, pero si se trata de un alto porcentaje del costo de la consulta, usted debe considerar si se puede construir un índice para eliminar la necesidad de que la operación de ordenación.
Planes LargosEl plan con menor número de operaciones suele ser el mejor plan optimizado.
Recorridos de índice agrupados o tablas
image
Un recorrido de índice agrupado y un recorrido de una tabla indican que no hay un índice adecuado que se pueden utilizar para optimizar la consulta.


Usando argumentos de búsqueda

Un argumento de búsqueda (SARG) es una expresión de filtro que se utiliza para limitar el número de filas devueltas por una consulta y que se puede utilizar un índice de operación de búsqueda que mejora sustancialmente el rendimiento de la consulta. Por lo general, una expresión de filtro no es un SARG si la columna de la tabla se utiliza en una expresión (por ejemplo, LEFT(Name, 1) = 'A'). Si el filtro no es un SARG y no existen otros argumentos SARG en la consulta, esto se traduce en un recorrido de índice o tabla, que recorre todo el índice o la tabla. En lugar de un recorrido, una operación de búsqueda es realizada.

Una búsqueda implica el uso de árboles balanceados (o equilibrados) de índices para encontrar los valores que la consulta busca. El uso de un árbol de índices equilibrado reduce considerablemente el trabajo que SQL Server necesita para llevar a cabo para buscar una fila. La diferencia entre una búsqueda y exploración puede ser un orden de magnitud. Por ejemplo, en la siguiente consulta, SQL Server examina el índice OrderDateIndex (en lugar de buscar a través de él). El plan de ejecución para la siguiente consulta,que puede ver pulsando Ctrl + M (o Ctrl + L)  en SQL Server Management Studio (SSMS), y se muestra en la siguiente figura:

USE AdventureWorks;
CREATE NONCLUSTERED INDEX OrderDateIndex
ON Sales.SalesOrderHeader (OrderDate);
SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2004;

image

Si la consulta se vuelve a escribir en lugar de modo que la columna OrderDate no se utiliza en la expresión, una operación de búsqueda en el índice se puede utilizar en lugar de un recorrido o escaneo. El plan de ejecución para la consulta siguiente se muestra a continuación:

SELECT COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20040101' AND OrderDate < '20050101';

image

Tenga en cuenta que el uso del operador COLLATE en una expresión de filtro también invalida el uso de un índice en esa columna.

Usando cláusulas Join

Para optimizar las consultas, una de las estrategias básicas es reducir al mínimo el número de join las cláusulas usadas. Otra consideración es que los outer joins incurren en más costos que inner joins por el trabajo extra necesario para encontrarlas filas no coincidentes. Si sólo se utilizan inner joins en una consulta, el comportamiento de las cláusulas ON y WHERE es el mismo, no importa si usted pone una expresión en la cláusula ON o WHERE. Compare las dos consultas siguientes, con las diferencias en negrita, que devuelven los mismos resultados y utilizan planes de ejecución idéntico:

-- Query 1

SELECT p.ProductID, p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.ProductID = p.ProductID
WHERE p.Color = 'Black';
image

-- Query 2

SELECT p.ProductID, p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.ProductID = p.ProductID
AND p.Color = 'Black';
image

Si estas consultas había sido escrito con un outer join, no sería sintácticamente igual y podría tener resultados muy diferentes.

Subconsultas sin correlación con la consulta externa

Como se ha explicado antes una subconsulta correlacionada se ejecuta sólo una vez por ejecución de la consulta y devuelve un único valor. Estas consultas suelen soportar muy poca sobrecarga. Tenga en cuenta que este tipo de subconsulta no puede tener ninguna referencia (correlación) a la consulta externa. El ejemplo siguiente utiliza una subconsulta para devolver todos los productos que son más baratos que el precio del producto promedio. La subconsulta para calcular el precio promedio del producto se ejecuta en primer lugar (solo una vez), y el valor devuelto por la subconsulta se utiliza como parámetro en la consulta externa:

SELECT p.ProductID ,p.Name ,p.ListPrice
FROM Production.Product AS p
WHERE p.ListPrice > (
        SELECT AVG(p2.ListPrice)
        FROM Production.Product AS p2
        );

image

Veamos el resultado de lo que seria la subconsulta si la ejecutaramos aparte:

image

Las subconsultas correlacionadas

Subconsultas correlacionadas hacen referencia a la consulta externa. Por lo general, esta referencia se utiliza para filtrar la subconsulta correlacionada. Una subconsulta correlacionada es generalmente igual en rendimiento en comparación con el uso de un JOIN cuando se utiliza en combinación con el operador EXISTS para filtrar la consulta externa. La consulta de ejemplo siguiente utiliza el operador EXISTS para devolver sólo los productos que se han vendido:

SELECT p.ProductID, p.Name
FROM Production.Product AS p
WHERE EXISTS (
        SELECT * FROM Sales.SalesOrderDetail AS sod
        WHERE sod.ProductID = p.ProductID
        );

Aunque este tipo de subconsulta correlacionada es normalmente una buena implementación, el uso de subconsultas correlacionadas en la cláusula SELECT a menudo tiene un efecto negativo en el rendimiento en comparación con el JOIN. Por supuesto, esto depende del número de filas devueltas por la consulta externa. Si un gran número de filas son devueltos, cada consulta en la cláusula SELECT se ejecuta para cada fila, lo que significa que un gran número de ejecuciones de la consulta. La siguiente consulta devuelve 6224 filas e incluye dos subconsultas correlativas. Cada una de estas consultas se ejecuta una vez en cada fila, lo que resulta en un total de 12.448 ejecuciones subconsulta:

SELECT soh.SalesOrderID ,soh.OrderDate
,( SELECT TOP(1)
        sod1.UnitPrice
        FROM Sales.SalesOrderDetail AS sod1
        WHERE sod1.SalesOrderID = soh.SalesOrderID
        ORDER BY sod1.OrderQty DESC
) AS UnitPrice
,( SELECT TOP(1)
        sod2.OrderQty
        FROM Sales.SalesOrderDetail AS sod2
        WHERE sod2.SalesOrderID = soh.SalesOrderID
        ORDER BY sod2.OrderQty DESC
) AS OrderQty
FROM Sales.SalesOrderHeader AS soh
WHERE soh.TerritoryID = 4;

También hay un error potencial en esta consulta. Debido a que cada subconsulta se ejecuta por separado, podrían terminar con distintos índices. Esto significa que estas consultas no puede devolver los valores de la misma fila (que son probablemente pretende) si el mismo valor para OrderQty existe para SalesOrderDetail en cualquier SalesOrder.

Hay varias maneras de volver a escribir esta consulta, la más común en SQL Server 2008 seria, probablemente, utilizar la nueva cláusula APPLY. Si la subconsulta se utiliza en el FROM, JOIN, o en cláusulas APPLY , también podría ser denominado como una tabla derivada, como se explica en los capítulos anteriores. La cláusula APPLY básicamente da la oportunidad de combinar dos subconsultas en una sola, reduciendo el número de las ejecuciones de las subconsulta a la mitad. Para obtener que la nueva consulta devuelva los mismos resultados que la consulta anterior, debe utilizar un OUTER APPLY. (Una OUTER APPLY funciona de manera similar que una combinación outer join , y su contraparte, la cláusula CROSS APPLY, se comporta como una inner join.) Esto funciona en este ejemplo porque, en la consulta anterior, la consulta externa devuelve una fila, incluso si las subconsultas no devuelven ningún valor. La nueva consulta se podría escribir de la siguiente manera:

SELECT soh.SalesOrderID ,soh.OrderDate ,a.*
FROM Sales.SalesOrderHeader AS soh
OUTER APPLY (
            SELECT TOP(1)
            sod.UnitPrice
            ,sod.OrderQty
            FROM Sales.SalesOrderDetail AS sod
            WHERE sod.SalesOrderID = soh.SalesOrderID
            ORDER BY sod.OrderQty DESC
) AS a
WHERE soh.TerritoryID = 4;

Esta consulta tiene un costo de aproximadamente 76 mientras que el costo de la primera consulta fue el doble, alrededor de 151.

Otra solución a este tipo de problemas es hacer uso de la función ROW_NUMBER en lugar de una subconsulta correlacionada. Mediante el uso de la función ROW_NUMBER, se puede encontrar el número específico de filas que necesita mediante el filtrado en el número de fila en lugar de utilizar la cláusula TOP. Para ser capaz de filtrar el resultado de la función ROW_NUMBER, la consulta tiene que ser colocado dentro de una tabla derivada o una expresión de tabla común (CTE). Cuanto mayor sea el conjunto de resultados, este enfoque es mejor en comparación con el las consultas anteriores. El costo de la consulta siguiente se merma de 76 a alrededor de 3,6, una enorme reducción:

WITH a AS (
        SELECT
        soh.SalesOrderID
        ,soh.OrderDate
        ,sod.UnitPrice
        ,sod.OrderQty
        ,ROW_NUMBER() OVER (
        PARTITION BY soh.SalesOrderID
        ORDER BY sod.OrderQty DESC
        ) AS RowNo
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
        WHERE soh.TerritoryID = 4
)
SELECT
        a.SalesOrderID
        ,a.OrderDate
        ,a.UnitPrice
        ,a.OrderQty
        FROM a
        WHERE a.RowNo = 1;

Las UDF escalares

Un UDF escalar es una función que devuelve un único valor (no es un conjunto de resultados). Este tipo de función se utiliza con frecuencia en las consultas y puede degradar significativamente el rendimiento. La razón de esto es que estas funciones no se expanden y optimizan en el plan de consulta principal por el optimizador, más bien, se acaba de llamar desde el plan de ejecución sin ningún tipo de optimización basada en el contexto en el que se inserta en el plan. Esto también significa que el costo de lo que se hace dentro de la función no está incluido en las estimaciones de costos en el plan de ejecución gráfico para la consulta.
Este mismo problema se produce por la salida de la declaración SET STATISTICS IO ON, que no contiene referencias a lo que se hace dentro de la UDF. A modo de ejemplo, en esta sección se compara el rendimiento de una consulta usando una UDF y que de otra consulta utilizando una subconsulta correlacionada. La UDF y la sentencia realizar idénticas declaraciones SELECT. Debido a que el costo de la UDF no se refleja en el costo de la consulta métricas, el rendimiento más adecuado métricas de estas solicitudes es el tiempo de ejecución, que se devuelve usando la cláusula SET STATISTICS TIME ON.
Creamos primero al UDF:

CREATE FUNCTION dbo.fnGetCustomerAccountNumber(@CustomerID INT)
RETURNS VARCHAR(10)
        AS
        BEGIN
        RETURN ISNULL(
        (
            SELECT
            AccountNumber
            FROM Sales.Customer
            WHERE CustomerID = @CustomerID
        ), 'NOT FOUND');
        END
GO

y despues ejecutamos la consulta:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT soh.SalesOrderID ,soh.OrderDate
,dbo.fnGetCustomerAccountNumber(soh.CustomerID)
FROM Sales.SalesOrderHeader AS soh;

image

El costo de esta consulta es de 0,56 y el número de lecturas de páginas es 706 (ninguna métrica es exacta y por lo tanto no puede ser utilizado para medir el rendimiento), mientras que el tiempo de ejecución en el equipo de prueba utilizados en este ejemplo es de 25 segundos. Examinar el plan de ejecución gráfico y nota que no contiene ninguna referencia a la tabla Sales.Customer.

La siguiente es una consulta de ejemplo que utiliza una subconsulta correlacionada en lugar de la UDF. El plan de la consulta de ejecución se muestra en la figura siguiente

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT soh.SalesOrderID ,soh.OrderDate
,ISNULL(
       (SELECT
        AccountNumber
        FROM Sales.Customer
        WHERE CustomerID = soh.CustomerID
        )
        , 'NOT FOUND')
FROM Sales.SalesOrderHeader AS soh;

image

El costo de la consulta sin la UDF se eleva a 1,05 y el número de página se lee que 742 (ambos de los cuales son valores exactos). Al mismo tiempo, el tiempo de ejecución se reduce a alrededor de 1 segundo. Como puede ver, la primera consulta con el UDF es de aproximadamente 25 veces más lenta que la consulta este último debido al uso de UDF.

¿Por qué el con valores de tabla en línea UDF es mucho más rápido?
La utilización de valores de tabla UDF o vistas no incurre en la pena de rendimiento que el uso de las UDF escalares ya los valores de tabla UDF y los vistas se han optimizado (ampliada) en el plan de consulta.

Valores de tabla UDF


Hay tres tipos diferentes de con valores de tabla UDF. Dos de ellos se pueden desarrollar en T-SQL y el otro en un lenguaje CLR, como C # o Microsoft Visual Basic. NET:
  • T-SQL en línea con valores de tabla UDF
  • T-SQL varias instrucciones con valores de tabla UDF
  • CLR con valores de tabla UDF

Estos diferentes tipos de funciones se comportan de manera diferente. Una funcion T-SQL con valores de tabla UDF en realidad es sólo una vista que puede aceptar parámetros. Se ha optimizado de la misma manera como sería una vista o cualquier otra instrucción SELECT . Una ventaja de usar funciones con valores de tabla UDF en lugar de vista es que se puede exigir al usuario que utiliza la función y suministrar los parámetros. De esta manera usted puede asegurarse de que un filtro siempre se utiliza para la consulta dentro de la función (en base a los parámetros establecidos).
T-SQL con valores de tabla de múltiples instrucciones UDF, por otro lado, se puede considerar al trabajo como un procedimiento almacenado que rellena una tabla temporal que puede ser utilizado por una consulta externa.
Si se incluye una tabla con varias UDF valorados en una consulta (por ejemplo, en un join), la función tiene que ser ejecutada por completo (es decir, que debe terminar la ejecución) antes de la consulta puede utilizar sus resultados. Esto significa que si una instrucción múltiple con valores de tabla UDF tiene que devolver 1.000.000 de filas, la función debe procesar todas las filas antes la consulta puede utilizar los resultados de la función.
El tercer tipo, CLR con valores de tabla UDF, flujo de sus resultados. Esto significa que mientras que la CLR UDF con valores de tabla se está ejecutando, sus resultados estén disponibles a la consulta llamada.
Esta diferencia puede mejorar el rendimiento porque la consulta externa no tiene que esperar a que todo el resultado de la función esté disponible antes de que pueda comenzar a procesar las filas devueltas. Un UDF CLR con valores de tabla se compone de dos métodos de CLR: un método que se encarga de la ejecución general de la función y un método que se llama para cada fila que se devuelve por la función. El método que se ejecuta para cada fila devuelta por la función no se ejecuta hasta que el método que se encarga de la ejecución de la función comienza a ejecutar los comandos de maximizar su retorno.
Esto es importante de recordar, porque cualquier proceso antes de la salida de los comandos de retorno de rendimiento tiene que estar terminado antes de que las filas se devuelven desde la función. UDF CLR con valores de tabla son generalmente útiles para la consulta de otros objetos que las tablas, como las cadenas (mediante el uso de expresiones regulares) o el sistema de archivos. Tenga en cuenta que el procesamiento realizado por una función CLR no es exactamente incluido en el costo de la consulta o la página de leer parámetros de una consulta.

Cursores
En general, debería evitar el uso de cursores debido a su efecto negativo en el rendimiento. Ellos tienen un efecto en parte debido a que cada ejecución de una sentencia FETCH en un bucle de cursor es similar en el costo de rendimiento al ejecutar una instrucción SELECT que devuelve una fila. Otro problema es que un lenguaje de manipulación de datos (DML) se optimiza como una sola unidad, mientras que un bucle de cursor no puede ser optimizado de la misma manera (si acaso). En su lugar, cada elemento en el circuito se ha optimizado y ejecutado por separado para cada iteración del bucle.
Usted debe tratar de reescribir la lógica del cursor en una o más declaraciones basadas en conjunto (SELECT, INSERT, UPDATE, DELETE o MERGE). Si no puede convertir la lógica del cursor a una declaración considere la aplicación de la lógica mediante un procedimiento CLR almacenado o una UDF con valores de tabla en su lugar (dependiendo de la funcionalidad que necesita).

Cómo darse cuenta qué consultas optimizar
Como usted probablemente ha descubierto, puede hacer mucho para mejorar el rendimiento de las consultas. El siguiente problema es, obviamente, encontrar que las consultas para optimizar. Para hacerlo con eficacia, debe utilizar SQL Server, que se utiliza para detectar los eventos que ocurren en una instancia de SQL Server.
Hay varios eventos que puede escuchar, pero para un ajuste, normalmente se desea usar el SQL: BatchCompleted y RPC: completado eventos. Además de decidir qué eventos para escuchar, también es necesario especificar las columnas que obtener cuando los eventos se producen.
Las columnas que son típicamente útil para determinar si un evento contiene algo que necesita ajustarse son las siguientes:

  • Duration: Devuelve el número de milisegundos (o microsegundos al escribir el eventos en un archivo o una tabla en lugar de utilizar la utilidad gráfica para ver los eventos)
  • Reads: Devuelve el número total de 8 kilobytes (KB) de páginas vistas durante la ejecución
  • Writes: Devuelve el número total de páginas de 8 KB por escrito durante la ejecución
  • CPU: Devuelve el tiempo total de CPU utilizado durante la ejecución

Un valor más alto de lo deseado por cualquiera de estas columnas debe llevar a mirar en el rendimiento de la consulta.
Debido a que SQL Server suele dar como resultado una gran cantidad de filas cuando se utilizan contra un servidor de producción, se puede considerar permitir que SQL Server grabar el resultado un archivo o una tabla. Tenga en cuenta que también puede iniciar el seguimiento en el servidor sin necesidad de utilizar el Analizador de SQL Server de la interfaz gráfica.
SQL Server puede ser utilizado para crear la secuencia de comandos necesarios para iniciar el seguimiento en el servidor. Para ello, basta con iniciar el rastreo de SQL Server, y luego, File menu, select Export, Script Trace Definition, For SQL Server 2005–2008. El seguimiento del servidor sólo admite seguimiento en un archivo. Si se traza en un archivo, puede que la consulta de SQL Server pasando el nombre del archivo de traza como un parámetro a la funcion fn_trace_gettable con valores de tabla UDF.

Resumen de la lección

  • La comprensión de cómo las consultas son lógicamente construido es importante saber que devuelve correctamente el resultado deseado.
  • La comprensión de cómo las consultas son, lógicamente, construida ayuda a entender lo construcciones físicas (como índices) ayudan a la consulta se ejecutan más rápidamente.
  • Asegúrese de que entiende sus indicadores al medir el rendimiento.

No hay comentarios:

Publicar un comentario