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 (6)
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:
¿Hay una fuente definitiva para explicar lo que significa esta medida?
¿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 :)
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.
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
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 ...
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.
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
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.