A partir de la version 2017 : Automatic tuning – SQL Server | Microsoft Learn

Surveillance et réglage des performances – SQL Server | Microsoft Learn

Script T-SQL pour :

Version et édition SQL Server

Paramètres critiques (MAXDOP, cost threshold)

Fichiers TempDB

Statut de Always On

Index manquants

Index inutilisés

Statistiques obsolètes

Fragmentation des index

Vérification des plans d’exécution longs (Query Store)

Instructions

1. Lance ce script sur une base utilisateur pour les parties dépendantes (USE tempdb, Query Store, etc.).
2. Assure-toi que Query Store est activé sur ta base (ALTER DATABASE <nom> SET QUERY_STORE = ON;).
3. Tu peux exporter les résultats dans Excel pour audit ou reporting.

script PowerShell qui interroge une base de données SQL Server pour :

1. Lister les 10 requêtes les plus consommatrices en :
   • CPU
   • I/O
   • Durée d’exécution
   • RAM (estimée via memory grants)
2. Fournir les plans d’exécution (sous forme de lien XML)
3. Identifier les index manquants
4. Détecter les deadlocks
5. Fournir les commandes SQL pour tuer les sessions bloquées
6. Générer un rapport HTML lisible dans un navigateur

Prérequis :

• Le module PowerShell SqlServer doit être installé (Install-Module -Name SqlServer).
• Exécuter le script avec des droits suffisants pour interroger les vues dynamiques de SQL Server.

Recommandations :

• Planifiez ce script dans le Planificateur de tâches Windows pour un reporting automatisé.
• Si vous utilisez SQL Authentication, ajoutez les paramètres -Username et -Password à Invoke-Sqlcmd

# Paramètres de connexion
$serverName = "NOM_SERVEUR\INSTANCE"
$databaseName = "NOM_BDD"
$outputHtml = "rapport_sqlserver.html"

# Chargement des outils SQL
Import-Module SqlServer -ErrorAction Stop

# Déclaration des requêtes
$queries = @{
    "Top 10 CPU" = @"
SELECT TOP 10
    qs.total_worker_time/1000 AS CPU_ms,
    qs.execution_count,
    qs.total_elapsed_time/1000 AS TotalDuration_ms,
    qs.total_logical_reads AS LogicalReads,
    qs.total_logical_writes AS LogicalWrites,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;
"@

    "Top 10 I/O" = @"
SELECT TOP 10
    qs.total_logical_reads + qs.total_logical_writes AS TotalIO,
    qs.total_logical_reads, qs.total_logical_writes,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY TotalIO DESC;
"@

    "Top 10 Duration" = @"
SELECT TOP 10
    qs.total_elapsed_time/1000 AS Duration_ms,
    qs.execution_count,
    qs.total_worker_time/1000 AS CPU_ms,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY Duration_ms DESC;
"@

    "Index Manquants" = @"
SELECT TOP 10
    migs.avg_total_user_cost * (migs.avg_user_impact/100.0) * (migs.user_seeks + migs.user_scans) AS ImprovementMeasure,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY ImprovementMeasure DESC;
"@

    "Deadlocks (via XE)" = @"
SELECT 
    XEvent.value('(event/data[@name=\"xml_report\"]/value)[1]', 'VARCHAR(MAX)') AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name=\"xml_deadlock_report\"]') AS XEventData(XEvent);
"@

    "Sessions à Killer" = @"
SELECT 
    blocking_session_id AS BlockingSessionID,
    session_id AS BlockedSessionID,
    wait_type,
    wait_time,
    wait_resource,
    text AS SQLText,
    'KILL ' + CAST(blocking_session_id AS VARCHAR) AS KillCommand
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id <> 0;
"@
}

# Création du fichier HTML
$html = @()
$html += "<html><head><title>Rapport SQL Server</title>"
$html += "<style>body{font-family:Segoe UI;} table{border-collapse:collapse;width:100%;} th,td{border:1px solid #ddd;padding:8px;} th{background-color:#f2f2f2;}</style>"
$html += "</head><body>"
$html += "<h1>Rapport d’Audit SQL Server - $serverName / $databaseName</h1>"

# Exécution des requêtes et génération du HTML
foreach ($section in $queries.Keys) {
    $html += "<h2>$section</h2>"
    try {
        $data = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $queries[$section] -ErrorAction Stop
        if ($data) {
            $html += "<table><tr>"
            foreach ($col in $data.Columns) {
                $html += "<th>$col</th>"
            }
            $html += "</tr>"
            foreach ($row in $data) {
                $html += "<tr>"
                foreach ($col in $data.Columns) {
                    $value = $row.$col
                    $value = $value -replace "<", "&lt;" -replace ">", "&gt;"  # Encodage HTML
                    $html += "<td>$value</td>"
                }
                $html += "</tr>"
            }
            $html += "</table>"
        } else {
            $html += "<p>Aucune donnée retournée.</p>"
        }
    } catch {
        $html += "<p><strong>Erreur dans la section '$section':</strong> $_</p>"
    }
}

$html += "</body></html>"

# Export HTML
$html | Out-File -FilePath $outputHtml -Encoding UTF8
Write-Host "Rapport généré : $outputHtml"