Mesure de la performance des requêtes : "Coût du plan d'exécution de la requête" par rapport au "temps pris".

Question

J'essaie de déterminer les performances relatives de deux requêtes différentes et je dispose de deux méthodes de mesure :
1. Exécutez les deux et chronométrez chaque requête
2. Exécutez les deux et obtenez le "coût de la requête" à partir du plan d'exécution réel.

Voici le code que j'exécute pour chronométrer les requêtes...

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

Ce que j'obtiens est le suivant :

Stored_Proc     Execution_Time     Query Cost (Relative To Batch)

test_1a         1.673 seconds      17%
test_1b         1.033 seconds      83%

Les résultats du temps d'exécution contredisent directement les résultats du coût de la requête, mais j'ai du mal à déterminer ce que signifie réellement "coût de la requête". Je pense que c'est un agrégat de Reads/Writes/CPU_Time/etc, donc j'ai quelques questions :

1. Existe-t-il une source définitive pour expliquer ce que signifie cette mesure ?

2. Quelles autres mesures de "performance des requêtes" les gens utilisent-ils, et quels sont leurs mérites relatifs ?

Il est important de noter qu'il s'agit d'un serveur SQL de taille moyenne, exécutant MS SQL Server 2005 sur MS Server 2003 Enterprise Edition avec plusieurs processeurs et plus de 100 utilisateurs simultanés.

EDITAR:

Après quelques soucis, j'ai réussi à obtenir un accès à Profiler sur ce serveur SQL, et je peux donner des informations supplémentaires (ce qui confirme que le coût de la requête est lié aux ressources du système, et non au temps d'exécution lui-même...).

Stored_Proc    CPU      Reads    Writes   Duration   

test_1a        1313     3975     93       1386
test_1b        2297     49839    93       1207

Il est impressionnant de constater que prendre plus de CPU avec BEAUCOUP plus de lectures prend moins de temps :)

Answer 1

La trace du profileur met tout cela en perspective.

• Requête A : 1,3 sec CPU, 1,4 sec durée
• Requête B : 2,3 sec CPU, 1,2 sec durée

La requête B utilise le parallélisme : CPU > durée Par exemple, la requête utilise 2 CPU, en moyenne 1,15 secondes chacun.

La requête A ne l'est probablement pas : CPU < durée

Cela explique le coût par rapport au lot : 17% du coût du plan de requête plus simple et non parallèle.

L'optimiseur constate que la requête B est plus coûteuse et qu'elle bénéficiera du parallélisme, même si cela demande un effort supplémentaire.

Rappelez-vous cependant que la requête B utilise 100% de 2 CPUS (donc 50% pour 4 CPU) pendant une seconde environ. La requête A utilise 100 % d'un seul CPU pendant 1,5 seconde.

Le pic de la requête A est plus faible, au prix d'une durée plus longue. Avec un seul utilisateur, qui s'en soucie ? Avec 100, cela fait peut-être une différence...

Answer 2

SET STATISTICS TIME ON

SELECT * 

FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;

SET STATISTICS TIME OFF;

Et dans l'onglet message, cela ressemblera à ceci :

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.

Answer 3

Les résultats du temps d'exécution contredisent directement les résultats du coût de la requête, mais j'ai du mal à déterminer ce que signifie réellement "coût de la requête".

Query cost est ce que l'optimiseur pense du temps que prendra votre requête (par rapport au temps total du lot).

L'optimiseur tente de choisir le plan de requête optimal en examinant votre requête et les statistiques de vos données, en essayant plusieurs plans d'exécution et en sélectionnant le moins coûteux d'entre eux.

Aquí vous pouvez lire plus en détail comment il essaie de le faire.

Comme vous pouvez le constater, cela peut être très différent de ce que vous obtenez réellement.

La seule véritable mesure de la performance d'une requête est, bien entendu, le temps que prend réellement la requête.

Answer 4

Utilice SET STATISTICS TIME ON

au-dessus de votre requête.

Sous l'onglet des résultats proches, vous pouvez voir un onglet de message. Vous pouvez y voir l'heure.

Answer 5

Temps d'exécution des requêtes :

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] 

Le résultat de la requête sera le suivant :

Optimiser le coût des requêtes :

Cliquez sur votre SQL Management Studio

Exécutez votre requête et cliquez sur Plan d'exécution à côté de l'onglet Messages du résultat de votre requête. Vous verrez ceci