Cette requête multibase recherche les procédures et les requêtes dépassant un certain seuil (plus de 500 ms pour les procédures et plus de 250 pour les requêtes)
SELECT 'PROCEDURE' AS SQL_TYPE, DB_NAME(database_id) AS base, max_worker_time,
total_worker_time / CAST(execution_count AS FLOAT) AS AVG_TIME,
COALESCE('mephisto..' + om.name,
'galeo..' + og.name,
'gescom..' + oc.name,
'prospects..' + op.name,
'aveilla..' + oa.name,
'master..' + osm.name,
'msdb..' + oss.name,
'distribution..' + osd.name,
'tempdb..' + ost.name) AS CODE,
'500000' AS DURE_PLUS_DE_MICROSECONDES
FROM sys.dm_exec_procedure_stats AS ps
LEFT OUTER JOIN Base1.sys.objects AS om
ON ps.object_id = om.object_id AND ps.database_id = DB_ID('Base1')
LEFT OUTER JOIN Base2.sys.objects AS og
ON ps.object_id = og.object_id AND ps.database_id = DB_ID('Base2')
LEFT OUTER JOIN Base3.sys.objects AS oc
ON ps.object_id = oc.object_id AND ps.database_id = DB_ID('Base3')
--> remplacer Base1, base2, Base3... par le noms de toutes vos base de production....
WHERE total_worker_time / CAST(execution_count AS FLOAT) > 500000
UNION ALL
SELECT 'REQUETE' AS SQL_TYPE, NULL AS base, max_worker_time,
total_worker_time / CAST(execution_count AS FLOAT) AS AVG_TIME,
text,
'250000' AS DURE_PLUS_DE_MICROSECONDES
FROM sys.dm_exec_query_stats AS ps
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE total_worker_time / CAST(execution_count AS FLOAT) > 250000
ORDER BY AVG_TIME DESC
Partant de cette requête on peut trouver par exemple les procédures et les requêtes les plus erratiques , c’est à dire celle dont l’écart entre durée min et max est d’au moins 10 fois :
-- récupère les procédures, requêtes et trigers dont l'écart entre durée min et max est d'au moins 10 fois
WITH T AS
(
SELECT 'PROCEDURE' AS SQL_TYPE,
COALESCE(DB_NAME(database_id), 'MSSQLsystemResource') AS BASE, database_id,
min_worker_time, max_worker_time,
total_worker_time / CAST(execution_count AS FLOAT) AS AVG_TIME,
COALESCE('Base1..' + b1.name,
'Base2..' + b2.name,
'Base3..' + b3.name,
...
'master..' + osm.name,
'msdb..' + oss.name,
'tempdb..' + ost.name,
'resource..' + osr.name) AS CODE, ps.object_id ,
max_worker_time - min_worker_time AS ECART_MUS,
max_worker_time / CAST(min_worker_time AS DECIMAL(16,2)) AS ECART_FOIS
FROM sys.dm_exec_procedure_stats AS ps
LEFT OUTER JOIN Base1.sys.objects AS b1
ON ps.object_id = b1.object_id AND ps.database_id = DB_ID('Base1')
LEFT OUTER JOIN Base2.sys.objects AS b2
ON ps.object_id = b2.object_id AND ps.database_id = DB_ID('Base2')
LEFT OUTER JOIN Base3.sys.objects AS b3
ON ps.object_id = b3.object_id AND ps.database_id = DB_ID('Base3')
...
LEFT OUTER JOIN master.sys.objects AS osm
ON ps.object_id = osm.object_id AND ps.database_id = DB_ID('master')
LEFT OUTER JOIN msdb.sys.objects AS oss
ON ps.object_id = oss.object_id AND ps.database_id = DB_ID('msdb')
LEFT OUTER JOIN tempdb.sys.objects AS ost
ON ps.object_id = ost.object_id AND ps.database_id = DB_ID('tempdb')
LEFT OUTER JOIN master.sys.objects AS osr
ON ps.object_id = osr.object_id AND ps.database_id = 32767
WHERE max_worker_time - min_worker_time >= 10 * total_worker_time / CAST(execution_count AS FLOAT)
UNION ALL
SELECT 'REQUETE' AS SQL_TYPE, NULL AS BASE, NULL, min_worker_time, max_worker_time,
total_worker_time / CAST(execution_count AS FLOAT) AS AVG_TIME,
text, NULL ,
max_worker_time - min_worker_time AS ECART_MUS,
max_worker_time / CAST(min_worker_time AS DECIMAL(16,2)) AS ECART_FOIS
FROM sys.dm_exec_query_stats AS ps
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE max_worker_time - min_worker_time >= 10 * total_worker_time / CAST(execution_count AS FLOAT)
UNION ALL
SELECT 'DÉCLENCHEUR' AS SQL_TYPE, COALESCE(DB_NAME(database_id), 'MSSQLsystemResource') AS BASE, database_id,
min_worker_time, max_worker_time,
total_worker_time / CAST(execution_count AS FLOAT) AS AVG_TIME,
COALESCE('Base1..' + b1.name,
'Base2..' + b2.name,
'Base3..' + b3.name,
...
'master..' + osm.name,
'msdb..' + oss.name,
'tempdb..' + ost.name,
'resource..' + osr.name) AS CODE, ts.object_id ,
max_worker_time - min_worker_time AS ECART_MUS,
max_worker_time / CAST(min_worker_time AS DECIMAL(16,2)) AS ECART_FOIS
FROM sys.dm_exec_trigger_stats as ts
LEFT OUTER JOIN Base1.sys.objects AS b1
ON ts.object_id = b1.object_id AND ts.database_id = DB_ID('Base1')
LEFT OUTER JOIN Base2.sys.objects AS b2
ON ts.object_id = b2.object_id AND ts.database_id = DB_ID('Base2')
LEFT OUTER JOIN Base3.sys.objects AS b3
ON ts.object_id = b3.object_id AND ts.database_id = DB_ID('Base3')
...
LEFT OUTER JOIN master.sys.objects AS osm
ON ps.object_id = osm.object_id AND ps.database_id = DB_ID('master')
LEFT OUTER JOIN msdb.sys.objects AS oss
ON ts.object_id = oss.object_id AND ts.database_id = DB_ID('msdb')
LEFT OUTER JOIN distribution.sys.objects AS osd
ON ts.object_id = ost.object_id AND ts.database_id = DB_ID('tempdb')
LEFT OUTER JOIN master.sys.objects AS osr
ON ts.object_id = osr.object_id AND ts.database_id = 32767
WHERE max_worker_time - min_worker_time >= 10 * total_worker_time / CAST(execution_count AS FLOAT)
)
SELECT SQL_TYPE, BASE, CAST(min_worker_time / 1000.0 AS DECIMAL(16,3)) AS TEMPS_MIN_MS,
CAST(max_worker_time / 1000.0 AS DECIMAL(16,3)) AS TEMPS_MAX_MS,
CAST(ECART_MUS / 1000.0 AS DECIMAL(16,3)) AS ECART_MS,
CAST(ECART_FOIS AS DECIMAL(16,0)) AS ECART_FOIS, CODE
FROM T
ORDER BY ECART_FOIS DESC
Dans ces deux requêtes, veuillez remplacer Base1, base2, base3… par le noms de vos bases de production.
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
