Audit de santé SQL Server : requêtes T-SQL et scripts PowerShell

Un audit de santé commence par le contexte

Une instance peut être disponible tout en exposant un risque de restauration, une saturation progressive ou une dépendance inconnue. Documentez propriétaire, application, criticité, RPO/RTO, heures de pointe, fenêtres, changements récents et symptômes.

Les exemples sont en lecture seule, sauf DBCC CHECKDB qui effectue un travail intensif sans modifier les données en l’absence d’option REPAIR. Adaptez-les à la version, l’édition et l’hébergement.

1. Inventorier l’instance et les bases

Recueillez build exacte, édition, disponibilité Always On, dernier démarrage, état, recovery model, compatibilité et PAGE_VERIFY. Comparez la build au catalogue Microsoft; une version majeure seule ne suffit pas.

Plusieurs DMV repartent au redémarrage. AUTO_CLOSE ou AUTO_SHRINK sur une production exigent une justification, mais aucun écart ne devrait être corrigé automatiquement sans contexte.

SELECT @@SERVERNAME AS instance_name,
 SERVERPROPERTY('ProductVersion') AS product_version,
 SERVERPROPERTY('ProductLevel') AS product_level,
 SERVERPROPERTY('Edition') AS edition,
 SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled,
 sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT name,state_desc,recovery_model_desc,compatibility_level,
 page_verify_option_desc,is_auto_close_on,is_auto_shrink_on
FROM sys.databases ORDER BY name;

2. Prouver que les sauvegardes se restaurent

L’historique msdb prouve qu’une sauvegarde a été enregistrée, pas qu’elle est exploitable ou conforme au RPO. Vérifiez fichiers, chiffrement et clés, copie protégée, CHECKSUM lorsque pertinent, puis restaurez dans un environnement isolé.

RESTORE VERIFYONLY ne remplace pas une restauration. Le test utile ouvre la base, exécute les contrôles d’intégrité et valide une transaction métier.

SELECT d.name,
 MAX(CASE WHEN b.type='D' THEN b.backup_finish_date END) AS last_full,
 MAX(CASE WHEN b.type='I' THEN b.backup_finish_date END) AS last_diff,
 MAX(CASE WHEN b.type='L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name=d.name
WHERE d.database_id>4 GROUP BY d.name ORDER BY d.name;

• Microsoft — Sauvegarde et restauration SQL Server ↗

3. Planifier DBCC CHECKDB

CHECKDB peut consommer CPU, mémoire, E/S, tempdb et espace pour son snapshot interne. Commencez par ESTIMATEONLY. PHYSICAL_ONLY peut être exécuté plus souvent sur les grandes bases, mais ne remplace pas un contrôle complet périodique.

Exécuter le contrôle complet sur une restauration récente réduit l’impact sur la production. REPAIR_ALLOW_DATA_LOSS reste un dernier recours; la restauration d’une sauvegarde saine est l’approche recommandée.

DBCC CHECKDB (N'MaBase') WITH ESTIMATEONLY;
DBCC CHECKDB (N'MaBase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB (N'MaBase') WITH PHYSICAL_ONLY, NO_INFOMSGS;

• Microsoft — DBCC CHECKDB ↗

4. Jobs, journaux, fichiers et croissance

Repérez jobs désactivés, échecs et durées anormales. Consultez SQL Server Error Log et Windows Event Log pour erreurs 823/824/825, corruption, dumps, échecs de connexion et bascules.

Mesurez taille, emplacement et croissance. Une croissance fixe doit être dimensionnée selon la charge. Interprétez le journal avec le recovery model, les sauvegardes de log, transactions longues, réplication et Always On. Évitez SHRINK comme entretien récurrent.

SELECT j.name,j.enabled,
 msdb.dbo.agent_datetime(h.run_date,h.run_time) AS last_run,
 h.run_status,h.message
FROM msdb.dbo.sysjobs j
OUTER APPLY (SELECT TOP(1) run_date,run_time,run_status,message
 FROM msdb.dbo.sysjobhistory WHERE job_id=j.job_id AND step_id=0
 ORDER BY instance_id DESC) h
ORDER BY h.run_status,j.name;

SELECT DB_NAME(mf.database_id) AS database_name,mf.type_desc,
 mf.physical_name,mf.size/128.0 AS size_mb,
 CASE WHEN mf.is_percent_growth=1 THEN CONCAT(mf.growth,'%')
 ELSE CONCAT(mf.growth/128.0,' MB') END AS growth
FROM sys.master_files mf ORDER BY database_name,mf.type_desc;

SELECT DB_NAME(database_id) AS database_name,
 total_log_size_in_bytes/1048576.0 AS log_size_mb,
 used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

5. tempdb, mémoire, CPU et E/S

Vérifiez taille et croissance homogènes de tempdb, espace disponible et contention. Corrélez mémoire et CPU à la charge plutôt que d’appliquer un seuil universel.

Les latences de sys.dm_io_virtual_file_stats sont des moyennes cumulatives depuis le démarrage ou la remise à zéro. Comparez données, journal et périodes semblables; une moyenne peut masquer des pointes.

SELECT DB_NAME(v.database_id) AS database_name,m.type_desc,m.physical_name,
 CASE WHEN v.num_of_reads=0 THEN NULL ELSE 1.0*v.io_stall_read_ms/v.num_of_reads END AS avg_read_ms,
 CASE WHEN v.num_of_writes=0 THEN NULL ELSE 1.0*v.io_stall_write_ms/v.num_of_writes END AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) v
JOIN sys.master_files m ON m.database_id=v.database_id AND m.file_id=v.file_id
ORDER BY avg_read_ms DESC;

• Microsoft — sys.dm_io_virtual_file_stats ↗

6. Attentes, blocages et deadlocks

Les wait stats indiquent ce que les tâches ont attendu, pas une correction automatique. Capturez un delta sur une période représentative. N’effacez pas les statistiques sans accord, car cela retire le contexte aux autres intervenants.

Le blocage court est normal. Pour les incidents intermittents, utilisez Extended Events system_health et analysez le graphe de deadlock. NOLOCK n’est pas un remède universel : il peut rendre des résultats incohérents.

SELECT TOP(15) wait_type,waiting_tasks_count,wait_time_ms,signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('SLEEP_TASK','SLEEP_SYSTEMTASK','LAZYWRITER_SLEEP')
ORDER BY wait_time_ms DESC;

SELECT r.session_id,r.blocking_session_id,r.status,r.wait_type,r.wait_time,
 DB_NAME(r.database_id) AS database_name,t.text AS batch_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id<>@@SPID AND (r.blocking_session_id<>0 OR r.wait_type IS NOT NULL)
ORDER BY r.blocking_session_id DESC,r.session_id;

• Microsoft — sys.dm_os_wait_stats ↗

7. Requêtes coûteuses et Query Store

Query Store conserve requêtes, plans et statistiques par intervalle. Vérifiez état, taille et rétention. Classez par CPU, durée, lectures et variabilité, puis tenez compte de la fréquence et de la valeur métier.

Une requête coûteuse unique peut être moins prioritaire qu’une petite requête appelée des millions de fois. Ne forcez pas un plan sans surveillance ni procédure de retrait.

SELECT TOP(20) qt.query_sql_text,
 SUM(rs.count_executions) AS executions,
 SUM(rs.avg_cpu_time*rs.count_executions)/NULLIF(SUM(rs.count_executions),0) AS avg_cpu_us
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id=qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id=q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id=p.plan_id
JOIN sys.query_store_runtime_stats_interval i ON i.runtime_stats_interval_id=rs.runtime_stats_interval_id
WHERE i.start_time>=DATEADD(day,-7,SYSUTCDATETIME())
GROUP BY qt.query_sql_text ORDER BY avg_cpu_us DESC;

• Microsoft — Query Store ↗

8. Index et statistiques avec discernement

Les suggestions d’index manquants sont cumulatives et ignorent certains coûts d’écriture. Regroupez-les, vérifiez les index existants et mesurez le gain.

La fragmentation seule ne justifie pas un rebuild général. Considérez taille, stockage, charge et fenêtre. Corrigez les statistiques de façon ciblée et conservez plans, durée, CPU et lectures avant/après.

• Pas de seuil universel appliqué à toutes les bases.
• Prévoir espace de journal et impact sur réplication ou AG.
• Éviter les corrections automatiques non validées.

9. Configuration, sécurité et permissions

Inventoriez max server memory, MAXDOP, cost threshold, comptes privilégiés, propriétaires, authentification, chiffrement et exposition réseau. Ces valeurs dépendent du matériel et de la charge.

SQL Server 2022 exige VIEW SERVER PERFORMANCE STATE pour plusieurs DMV qui demandaient auparavant VIEW SERVER STATE. Utilisez un compte de collecte dédié et les permissions minimales; jamais de secret SQL en clair dans un script.

SELECT name,value_in_use FROM sys.configurations
WHERE name IN ('backup checksum default','cost threshold for parallelism',
'max degree of parallelism','max server memory (MB)','optimize for ad hoc workloads');

SELECT name,type_desc,is_disabled,create_date,modify_date
FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE '##%'
ORDER BY type_desc,name;

10. Toujours vérifier Always On lorsque présent

Examinez rôle, connexion, synchronisation, files d’envoi et de rétablissement, quorum, listeners, sauvegardes préférées et tests de bascule. HEALTHY ne prouve pas que l’application se reconnecte.

Cette requête est un aperçu local. Complétez avec états par base et journaux de cluster. Ne déclenchez pas de bascule sans changement approuvé.

SELECT ag.name AS ag_name,ar.replica_server_name,rs.role_desc,
 rs.connected_state_desc,rs.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id=ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states rs ON rs.replica_id=ar.replica_id
ORDER BY ag.name,ar.replica_server_name;

11. Automatiser une collecte légère avec PowerShell

Le module SqlServer permet une collecte multi-instance avec authentification Windows, gMSA ou jeton selon la plateforme. L’exemple gère délais et erreurs sans demander de secret; adaptez Encrypt à vos certificats.

Planifiez sous une identité à privilèges minimaux, protégez les sorties et fixez une rétention. Gardez CHECKDB, restaurations et analyses coûteuses dans des tâches séparées.

# instances.txt: one instance per line
Import-Module SqlServer
$Out=Join-Path $PSScriptRoot ('SQLHealth-'+(Get-Date -Format yyyyMMdd-HHmmss))
New-Item -ItemType Directory -Path $Out -ErrorAction Stop | Out-Null
$Query="SELECT @@SERVERNAME InstanceName, CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)) ProductVersion, CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) Edition, (SELECT COUNT(*) FROM sys.databases WHERE state_desc<>'ONLINE') DatabasesNotOnline;"
$Results=foreach($Instance in Get-Content "$PSScriptRoot\instances.txt"){
 try { Invoke-Sqlcmd -ServerInstance $Instance -Database master -Query $Query -ConnectionTimeout 15 -QueryTimeout 60 -Encrypt Optional -AbortOnError -ErrorAction Stop | Select-Object *,@{n='CollectedAt';e={Get-Date}},@{n='Status';e={'OK'}} }
 catch { [pscustomobject]@{InstanceName=$Instance;CollectedAt=Get-Date;Status='ERROR';Error=$_.Exception.Message} }
}
$Results|Export-Csv (Join-Path $Out 'instances.csv') -NoTypeInformation -Encoding UTF8
$Results|ConvertTo-Html -Title 'SQL Server health collection'|Set-Content (Join-Path $Out 'report.html') -Encoding UTF8

• Microsoft — Invoke-Sqlcmd ↗

Prioriser le rapport et le plan d’action

Chaque constat doit contenir preuve, impact, confiance, propriétaire, échéance, test et retour arrière. La checklist couvre inventaire, restauration, CHECKDB, jobs, journaux, capacité, tempdb, E/S, attentes, blocages, Query Store, sécurité et haute disponibilité.

Ces requêtes sont une base de collecte, pas un audit complet automatisé. L’interprétation de la charge et de l’application demeure la partie la plus importante.

• Service — Infrastructure et modernisation →
• Guide — Sauvegardes Veeam immuables →