sql - optimizer - Medición del rendimiento de la consulta: "Costo de consulta del plan de ejecución" frente a "Tiempo ocupado"

sql server analyze query

Question

Estoy tratando de determinar el rendimiento relativo de dos consultas diferentes y tengo dos formas de medir esto disponible para mí:
1. Ejecute ambos y espere cada consulta
2. Ejecute ambos y obtenga "Costo de consulta" del plan de ejecución real

Aquí está el código que ejecuto para sincronizar las consultas ...

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME SET @start = getDate()
EXEC test_1a
SELECT getDate() - @start AS Execution_Time
GO

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME SET @start = getDate()
EXEC test_1b
SELECT getDate() - @start AS Execution_Time
GO

Lo que obtengo es lo siguiente:

Stored_Proc     Execution_Time     Query Cost (Relative To Batch)

test_1a         1.673 seconds      17%
test_1b         1.033 seconds      83%

Los resultados del tiempo de ejecución contradicen directamente los resultados del Costo de consulta, pero tengo dificultades para determinar qué significa realmente "Costo de consulta". Mi mejor suposición es que es un agregado de Reads / Writes / CPU_Time / etc, así que supongo que tengo un par de preguntas:

1. ¿Hay una fuente definitiva para explicar lo que significa esta medida?

2. ¿Qué otras métricas de "Rendimiento de consulta" usan las personas y cuáles son sus méritos relativos?

Puede ser importante tener en cuenta que se trata de un servidor SQL de tamaño mediano, que ejecuta MS SQL Server 2005 en MS Server 2003 Enterprise Edition con múltiples procesadores y más de 100 usuarios simultáneos.

EDITAR:

Después de un poco de molestia me las arreglé para obtener el acceso de Profiler en ese servidor SQL, y puedo dar información adicional (que admite el costo de consulta relacionado con los recursos del sistema, no el tiempo de ejecución en sí ...)

Stored_Proc    CPU      Reads    Writes   Duration   

test_1a        1313     3975     93       1386
test_1b        2297     49839    93       1207

Impresionante que llevar más CPU con MUCHAS más lecturas lleva menos tiempo :)

Answer 1

Los resultados del tiempo de ejecución contradicen directamente los resultados del Costo de consulta, pero tengo dificultades para determinar qué significa realmente "Costo de consulta".

Query cost es lo que el optimizador piensa de cuánto tiempo llevará su consulta (en relación con el tiempo total del lote).

El optimizador intenta elegir el plan de consulta óptimo mirando su consulta y estadísticas de sus datos, probando varios planes de ejecución y seleccionando el menos costoso de ellos.

Here puede leer con más detalle cómo trata de hacer esto.

Como puede ver, esto puede diferir significativamente de lo que realmente obtiene.

La única métrica de rendimiento de consulta real es, por supuesto, cuánto tiempo realmente toma la consulta.

Answer 2

Entiendo que es una vieja pregunta; sin embargo, me gustaría agregar un ejemplo donde el costo sea el mismo, pero una consulta es mejor que la otra.

Como observó en la pregunta, el% mostrado en el plan de ejecución no es el único criterio para determinar la mejor consulta. En el siguiente ejemplo, tengo dos consultas haciendo la misma tarea. El plan de ejecución muestra que ambos son igualmente buenos (50% cada uno). Ahora ejecuté las consultas con SET STATISTICS IO ON que muestra claras diferencias.

En el siguiente ejemplo, la consulta 1 usa seek mientras que Query 2 usa scan en la tabla LWManifestOrderLineItems. Sin embargo, cuando comprobamos el tiempo de ejecución, encontramos que Query 2 funciona mejor.

Lea también ¿ Cuándo es una búsqueda no una búsqueda? por Paul White

CONSULTA

---Preparation---------------
-----------------------------
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS IO ON  --IO
SET STATISTICS TIME ON

--------Queries---------------
------------------------------

SELECT LW.Manifest,LW.OrderID,COUNT(DISTINCT LineItemID)
FROM LWManifestOrderLineItems LW
INNER JOIN ManifestContainers MC
    ON MC.Manifest = LW.Manifest
GROUP BY LW.Manifest,LW.OrderID
ORDER BY COUNT(DISTINCT LineItemID) DESC  

SELECT LW.Manifest,LW.OrderID,COUNT( LineItemID) LineCount
FROM LWManifestOrderLineItems LW
WHERE LW.Manifest IN (SELECT Manifest FROM ManifestContainers)
GROUP BY LW.Manifest,LW.OrderID
ORDER BY COUNT( LineItemID) DESC  

Estadísticas IO

Plan de ejecución

Answer 3

La traza del perfilador lo pone en perspectiva.

• Consulta A: 1.3 segundos de CPU, 1.4 segundos de duración
• Consulta B: 2.3 segundos CPU, 1.2 segundos de duración

La consulta B usa paralelismo: duración de CPU> p. Ej., La consulta utiliza 2 CPU, promedia 1.15 segundos cada una

La consulta A probablemente no sea: CPU <duración

Esto explica el costo relativo al lote: el 17% del plan de consulta no paralelo más simple.

El optimizador calcula que la consulta B es más costosa y se beneficiará del paralelismo, aunque requiere un esfuerzo adicional para hacerlo.

Sin embargo, recuerde que la consulta B utiliza el 100% de 2 CPUS (por lo que 50% para 4 CPU) durante un segundo más o menos. Query A usa el 100% de una sola CPU durante 1,5 segundos.

El pico para la consulta A es menor, a expensas de una mayor duración. Con un usuario, ¿a quién le importa? Con 100, tal vez haga la diferencia ...

Answer 4

Use SET STATISTICS TIME ON

encima de tu consulta.

Debajo de la pestaña de resultados cercanos puede ver una pestaña de mensaje. Ahí puedes ver el tiempo.

Answer 5

Tiempo de ejecución de consultas:

DECLARE @EndTime datetime
DECLARE @StartTime datetime 
SELECT @StartTime=GETDATE() 


` -- Write Your Query`

SELECT @EndTime=GETDATE()
--This will return execution time of your query
SELECT DATEDIFF(MILLISECOND,@StartTime,@EndTime) AS [Duration in millisecs] 

Query Out Put será como:

Para optimizar el costo de la consulta:

Haga clic en su SQL Management Studio

Ejecute su consulta y haga clic en el plan de ejecución junto a la pestaña Mensajes del resultado de la consulta. verás como

Answer 6

SET STATISTICS TIME ON

SELECT * 

FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;

SET STATISTICS TIME OFF;

Y ver la pestaña del mensaje se verá así:

SQL Server Execution Times:

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

(778 row(s) affected)

SQL Server parse and compile time: 

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