Dans le cadre de l'optimisation des performances sur SQL Server, plusieurs requêtes utiles permettent de diagnostiquer et d'améliorer les opérations de la base de données. Ces scripts aident à identifier les problèmes courants comme les sessions actives, les bolcages, les index manquants ou fragmentés, ainsi que les requêtes coûteuses.

Nombre de sessions actives par utilisateur et application :

SELECT 
    nom_utilisateur AS "Nom d'utilisateur",
    nom_application AS "Application",
    COUNT(identifiant_session) AS "Nombre de sessions"
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY nom_utilisateur, nom_application
ORDER BY COUNT(identifiant_session) DESC;

Nombre de connexions par base de données :

SELECT 
    DB_NAME(identifiant_base) AS "Nom de la base",
    COUNT(identifiant_base) AS "Nombre de connexions",
    nom_connexion AS "Identifiant de connexion"
FROM sys.sysprocesses
WHERE identifiant_base > 0
GROUP BY identifiant_base, nom_connexion;

Identifier les sessions bloquées et leurs détails :

SELECT
    session_id AS "ID de session",
    etat AS "Statut",
    nom_connexion AS "Utilisateur",
    machine_hote AS "Hôte",
    session_bloquante_id AS "Bloqué par",
    DB_NAME(base_id) AS "Base de données",
    type_commande AS "Commande",
    texte_sql AS "Requête SQL",
    texte_bloquant AS "Requête bloquante",
    nom_objet AS "Objet",
    temps_ecoule_ms AS "Temps écoulé (ms)",
    temps_cpu AS "Temps CPU (ms)",
    lectures_io AS "Lectures IO",
    ecritures_io AS "Écritures IO",
    type_attente AS "Type d'attente",
    heure_debut AS "Heure de début",
    protocole AS "Protocole",
    ecritures_connexion AS "Écritures connexion",
    lectures_connexion AS "Lectures connexion",
    adresse_client AS "Adresse client",
    authentification AS "Authentification"
FROM sys.dm_exec_requests requete
OUTER APPLY sys.dm_exec_sql_text(requete.sql_handle) texte
LEFT JOIN sys.dm_exec_sessions session ON session.session_id = requete.session_id
LEFT JOIN sys.dm_exec_connections connexion ON connexion.session_id = session.session_id
LEFT JOIN sys.dm_exec_requests requete_bloquante ON requete.session_id = requete_bloquante.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(requete_bloquante.sql_handle) texte_bloquant
WHERE requete.session_id > 50
ORDER BY requete.blocking_session_id DESC, requete.session_id;

Détecter les index manquants susceptibles d'améliorer les performanecs :

SELECT 
    CONVERT(DECIMAL(18, 2), recherches_utilisateur * cout_moyen * (impact_moyen * 0.01)) AS "Avantage index",
    stats.derniere_recherche_utilisateur,
    details.requete AS "Base.Schema.Table",
    details.colonnes_egalite,
    details.colonnes_inegalite,
    details.colonnes_incluses,
    stats.compilations_uniques,
    stats.recherches_utilisateur,
    stats.cout_moyen_utilisateur,
    stats.impact_moyen_utilisateur
FROM sys.dm_db_missing_index_group_stats stats WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups groupes WITH (NOLOCK) ON stats.group_handle = groupes.index_group_handle
INNER JOIN sys.dm_db_missing_index_details details WITH (NOLOCK) ON groupes.index_handle = details.index_handle
ORDER BY "Avantage index" DESC;

Vérifier la date de dernière mise à jour des statistiques d'index :

SELECT 
    SCHEMA_NAME(objet.schema_id) + '.' + objet.nom AS "Nom de l'objet",
    objet.type_description AS "Type d'objet",
    index_info.nom AS "Nom de l'index",
    STATS_DATE(index_info.object_id, index_info.index_id) AS "Date des statistiques",
    stats.auto_created AS "Créé automatiquement",
    stats.no_recompute AS "Pas de recalcul",
    stats.user_created AS "Créé par l'utilisateur",
    partitions.lignes_nombre AS "Nombre de lignes",
    partitions.pages_utilisees AS "Pages utilisées"
FROM sys.objects objet WITH (NOLOCK)
INNER JOIN sys.indexes index_info WITH (NOLOCK) ON objet.object_id = index_info.object_id
INNER JOIN sys.stats stats WITH (NOLOCK) ON index_info.object_id = stats.object_id AND index_info.index_id = stats.stats_id
INNER JOIN sys.dm_db_partition_stats partitions WITH (NOLOCK) ON objet.object_id = partitions.object_id AND index_info.index_id = partitions.index_id
WHERE objet.type IN ('U', 'V')
    AND partitions.lignes_nombre > 0
ORDER BY STATS_DATE(index_info.object_id, index_info.index_id) DESC;

Évaluer la fragmentation des index :

SELECT 
    DB_NAME(stats.base_id) AS "Nom de la base",
    OBJECT_NAME(stats.objet_id) AS "Nom de l'objet",
    index_info.nom AS "Nom de l'index",
    stats.index_id,
    stats.type_index_description AS "Type d'index",
    stats.pourcentage_fragmentation_moyen AS "Fragmentation moyenne (%)",
    stats.fragment_nombre AS "Nombre de fragments",
    stats.page_nombre AS "Nombre de pages",
    index_info.taux_remplissage AS "Taux de remplissage",
    index_info.a_filtre AS "A filtre",
    index_info.definition_filtre AS "Définition du filtre"
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') stats
INNER JOIN sys.indexes index_info WITH (NOLOCK) ON stats.objet_id = index_info.object_id AND stats.index_id = index_info.index_id
WHERE stats.base_id = DB_ID()
    AND stats.page_nombre > 2500
ORDER BY stats.pourcentage_fragmentation_moyen DESC;

Identifier les requêtes SQL les plus coûteuses en termes de performance :

SELECT TOP 10 
    texte_sql AS "Requête SQL",
    derniere_execution AS "Dernière exécution",
    (lectures_logiques + lectures_physiques + ecritures_logiques) / nombre_executions AS "IO moyen",
    (temps_cpu / nombre_executions) / 1000000.0 AS "Temps CPU moyen (sec)",
    (temps_ecoule / nombre_executions) / 1000000.0 AS "Temps écoulé moyen (sec)",
    nombre_executions AS "Nombre d'exécutions",
    plan_requete AS "Plan de requête"
FROM sys.dm_exec_query_stats statistiques
CROSS APPLY sys.dm_exec_sql_text(statistiques.plan_handle) texte
CROSS APPLY sys.dm_exec_query_plan(statistiques.plan_handle) plan
ORDER BY temps_ecoule / nombre_executions DESC;