Dans un environnement de production SQL Server, il n’est pas toujours possible d’optimiser toutes les requêtes avant leur exécution. Des facteurs tels que la taille des données, les différences matérielles et les variations de schéma peuvent affecter les performances des requêtes. Cet article de blog discutera d’une méthode de collecte et de stockage automatique des requêtes SQL sous-performantes pour une analyse ultérieure.

Solution

Avec l’introduction des vues et des fonctions de gestion dynamique dans SQL Server 2005, la collecte d’informations sur les performances des requêtes SQL est devenue une tâche simple. Les vues et fonctions suivantes fournissent toutes les informations nécessaires pour déterminer les performances des requêtes SQL dans le cache :

• sys.dm_exec_query_stats
• sys.dm_exec_sql_text(sql_handle)
• sys.dm_exec_query_plan(plan_handle)

En utilisant ces vues et fonctions, nous pouvons créer une requête qui récupère toutes les requêtes SQL actuellement présentes dans le cache. En plus du texte de la requête et du plan d’exécution, nous pouvons également extraire des statistiques importantes sur les performances de la requête et l’utilisation des ressources. Voici un exemple de requête :

SELECT TOP 20
    GETDATE() AS "Date de collecte",
    qs.execution_count AS "Nombre d'exécutions",
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                       ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
             ) AS "Texte de la requête", 
     DB_NAME(qt.dbid) AS "Nom de la base de données",
     qs.total_worker_time AS "Temps total du processeur",
     qs.total_worker_time/qs.execution_count AS "Temps moyen du processeur (ms)",     
     qs.total_physical_reads AS "Lectures physiques totales",
     qs.total_physical_reads/qs.execution_count AS "Lectures physiques moyennes",
     qs.total_logical_reads AS "Lectures logiques totales",
     qs.total_logical_reads/qs.execution_count AS "Lectures logiques moyennes",
     qs.total_logical_writes AS "Écritures logiques totales",
     qs.total_logical_writes/qs.execution_count AS "Écritures logiques moyennes",
     qs.total_elapsed_time AS "Durée totale",
     qs.total_elapsed_time/qs.execution_count AS "Durée moyenne (ms)",
     qp.query_plan AS "Plan"
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE 
     qs.execution_count > 50 OR
     qs.total_worker_time/qs.execution_count > 100 OR
     qs.total_physical_reads/qs.execution_count > 1000 OR
     qs.total_logical_reads/qs.execution_count > 1000 OR
     qs.total_logical_writes/qs.execution_count > 1000 OR
     qs.total_elapsed_time/qs.execution_count > 1000
ORDER BY 
     qs.execution_count DESC,
     qs.total_elapsed_time/qs.execution_count DESC,
     qs.total_worker_time/qs.execution_count DESC,
     qs.total_physical_reads/qs.execution_count DESC,
     qs.total_logical_reads/qs.execution_count DESC,
     qs.total_logical_writes/qs.execution_count DESC

Cette requête peut être facilement modifiée pour capturer des requêtes spécifiques en fonction du problème que vous essayez de résoudre. Par exemple, si vous rencontrez une utilisation élevée du processeur sur votre instance SQL, vous pouvez modifier la clause WHERE pour capturer uniquement les requêtes SQL avec un temps d’exécution élevé. De même, si vous rencontrez des problèmes d’E/S, vous pouvez capturer les requêtes SQL avec des lectures ou des écritures élevées.

Maintenant que nous avons une requête pour capturer les informations souhaitées, nous avons besoin d’un endroit pour stocker les données. La définition de table suivante peut être utilisée pour stocker le résultat de la requête ci-dessus :

CREATE TABLE [DBA].[dbo].[My_Poor_Query_Cache] (
 [Date de collecte] [datetime] NOT NULL,
 [Nombre d'exécutions] [bigint] NULL,
 [Texte de la requête] [nvarchar](max) NULL,
 [Nom de la base de données] [sysname] NULL,
 [Temps total du processeur] [bigint],
 [Temps moyen du processeur (ms)] [bigint] NULL,
 [Lectures physiques totales] [bigint] NULL,
 [Lectures physiques moyennes] [bigint] NULL,
 [Lectures logiques totales] [bigint] NULL,
 [Lectures logiques moyennes] [bigint] NULL,
 [Écritures logiques totales] [bigint] NULL,
 [Écritures logiques moyennes] [bigint] NULL,
 [Durée totale] [bigint] NULL,
 [Durée moyenne (ms)] [bigint] NULL,
 [Plan] [xml] NULL
) ON [PRIMARY]
GO

En ajoutant la ligne “INSERT INTO [DBA].[dbo].[My_Poor_Query_Cache]” avant la requête, le résultat sera automatiquement inséré dans la table.

Enfin, nous pouvons utiliser l’Agent SQL Server pour planifier l’exécution de la requête à intervalles réguliers. La fréquence d’exécution de la requête dépend de l’application spécifique et de l’environnement. Si les requêtes ont tendance à rester longtemps dans le cache SQL, la requête peut être exécutée moins fréquemment. Cependant, si les requêtes sont fréquemment évacuées du cache, il est recommandé d’exécuter la requête plus souvent pour éviter de manquer des requêtes SQL sous-performantes.

En interrogeant les données stockées dans la table, vous pouvez commencer à ajuster et à optimiser les requêtes SQL sous-performantes identifiées lorsque vous avez du temps libre.

C’est tout ! Avec cette méthode, vous pouvez collecter et analyser automatiquement les requêtes SQL sous-performantes dans SQL Server, ce qui vous permet d’optimiser et d’améliorer les performances globales de votre base de données.