Mesurer avec précision les performances de la procédure stockée

Quel est le meilleur moyen de mesurer avec précision les performances (time d'exécution) d'une procédure stockée?

Je suis sur le sharepoint commencer une tentative d'optimization d'une procédure stockée monstre, et afin de déterminer correctement si mes réglages ont un effet, j'ai besoin de quelque chose pour comparer l'avant et l'après.

Mes idées jusqu'à présent:

Vous cherchez une heure d'exécution de la requête SQL Management Studio: Pas très précis, mais très pratique.
Ajouter des minuteurs dans la procédure stockée et imprimer le time écoulé: Ajouter du code de debugging comme ça pue.
À l'aide du SQL Server Profiler, en ajoutant des filters pour cibler uniquement ma procédure stockée. C'est ma meilleure option jusqu'à présent.

D'autres options?

Le profileur est la méthode la plus fiable. Vous pouvez également utiliser SET STATISTICS IO ON et SET STATISTICS TIME ON mais ceux-ci n'incluent pas l'impact total des fonctions UDF scalaires.

Vous pouvez également activer l'option "inclure les statistics client" dans SSMS pour get un aperçu des performances des 10 dernières exécutions.

Il y a beaucoup d'informations détaillées sur les performances dans DMV dm_exec_query_stats

 DECLARE @procname VARCHAR(255) SET @procname = 'your proc name' SELECT * FROM sys.dm_exec_query_stats WHERE st.objectid = OBJECT_ID(@procname)

Cela vous donnera des données de performance cumulées et des counts d'exécution par instruction caching.

Vous pouvez utiliser DBCC FREEPROCCACHE pour réinitialiser les counturs (ne l'exécutez pas dans un système de production, car tous les plans de requête mis en cache seront purgés).

Vous pouvez get les plans de requête pour chaque instruction en étendant cette requête:

 SELECT SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) [sub_statement] ,*, CONVERT(XML, tqp.query_plan) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset ) tqp WHERE st.objectid = OBJECT_ID(@procname) ORDER BY statement_start_offset, execution_count

Cela vous donnera des indications sur les parties du SP qui fonctionnent mal, et – si vous incluez les plans d'exécution – pourquoi.

Une amélioration possible de votre option timers / debug est de stocker les résultats dans une table. De cette façon, vous pouvez découper datatables de synchronisation résultantes avec des requêtes SQL plutôt que de simplement parsingr votre sortie de debugging.

Vous voulez vous assurer que vous effectuez des tests équitables, c'est-à-dire que vous comparez avec comme. Envisagez d'exécuter vos tests à l'aide d'un cache à froid afin de forcer l'exécution de votre procédure stockée à partir du sous-système IO chaque fois que vous effectuez vos tests.

Jetez un oeil aux procédures stockées système DBCC FREEPROCCACHE et DBCC FREESYSTEMCACHE