sql-server-tuner

name	sql-server-tuner
description	Optimisation et administration SQL Server. Se déclenche avec "SQL Server", "SSMS", "execution plan", "index SQL Server", "Always On", "tempdb. Also triggers on "SQL Server performance", "T-SQL tuning", "execution plan analysis".

SQL Server Tuner

Workflow en étapes

1. Baseline — Collecter les informations système

Avant tout diagnostic, fixer le contexte :

-- Version, édition, mémoire configurée
SELECT @@VERSION;
SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'cost threshold for parallelism',
               'max degree of parallelism', 'optimize for ad hoc workloads');

-- Taille et état de chaque base
SELECT name, state_desc, log_reuse_wait_desc,
       CAST(size * 8.0 / 1024 AS INT) AS size_MB
FROM sys.databases ORDER BY size DESC;

2. Identifier les goulets — Wait Stats

Point d'entrée systématique : les wait stats expliquent 80 % des lenteurs.

-- Top 10 waits (hors idle)
SELECT TOP 10 wait_type,
       waiting_tasks_count,
       CAST(wait_time_ms / 1000.0 AS DECIMAL(10,2)) AS wait_sec,
       CAST(signal_wait_time_ms * 100.0 / wait_time_ms AS DECIMAL(5,2)) AS pct_signal
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_EVENTHANDLER','WAITFOR',
    'SLEEP_DBSTARTUP','DISPATCHER_QUEUE_SEMAPHORE','XE_TIMER_EVENT',
    'SQLTRACE_BUFFER_FLUSH','CLR_QUANTUM_TASK','HADR_WORK_QUEUE',
    'LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','SNI_HTTP_ACCEPT',
    'ONDEMAND_TASK_MANAGER','SERVER_IDLE_CHECK','SLEEP_MASTERDBREADY',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT','ASYNC_NETWORK_IO',
    'SLEEP_TEMPDBSTARTUP','DBMIRROR_EVENTS_QUEUE')
ORDER BY wait_time_ms DESC;

Critères de décision rapide :

Wait type	Cause probable	Action
PAGEIOLATCH_SH/EX	I/O disque, manque de mémoire	Augmenter max server memory, vérifier PLE
LCK_M_*	Contention de verrous	Analyser requêtes bloquantes, envisager RCSI
CXPACKET / CXCONSUMER	Parallelisme excessif	Ajuster MAXDOP, cost threshold
RESOURCE_SEMAPHORE	Pression mémoire sur grants	Requêtes avec sort/hash coûteux, revoir index
WRITELOG	I/O log lent	Déplacer LDF sur SSD dédié

3. Identifier les requêtes coûteuses

-- Top requêtes par CPU total (Query Store, SQL 2016+)
SELECT TOP 20
    qsq.query_id,
    SUBSTRING(qsqt.query_sql_text, 1, 200) AS sql_text,
    SUM(qsrs.count_executions) AS executions,
    CAST(SUM(qsrs.avg_cpu_time * qsrs.count_executions) / 1e6 AS DECIMAL(10,2)) AS total_cpu_sec,
    CAST(AVG(qsrs.avg_duration) / 1e6 AS DECIMAL(10,4)) AS avg_dur_sec
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
GROUP BY qsq.query_id, qsqt.query_sql_text
ORDER BY total_cpu_sec DESC;

-- Alternative : DMV en temps réel (pas de persistence)
SELECT TOP 20
    total_worker_time / execution_count AS avg_cpu_us,
    execution_count,
    total_elapsed_time / execution_count  avg_dur_us,
    (st.text, (qs.statement_start_offset),
        (( statement_end_offset    DATALENGTH(st.text)
           qs.statement_end_offset   qs.statement_start_offset)))  stmt
 sys.dm_exec_query_stats qs
 APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  avg_cpu_us ;

4. Optimiser les index

Processus décisionnel :

1. Vérifier les index manquants signalés :

SELECT TOP 20
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS score,
    mid.statement AS table_name,
    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 migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY score DESC;

2. Repérer les index inutilisés (candidats à la suppression) :

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
       i.type_desc, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
    AND ius.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
  AND i.type_desc <> 'HEAP'
  AND (ius.user_seeks IS NULL OR ius.user_seeks + ius.user_scans + ius.user_lookups = 0)
ORDER BY ISNULL(ius.user_updates, 0) DESC;

3. Critère création index : score > 100 000 ET ratio seeks/scans > 10. Préférer un index couvrant (INCLUDE) plutôt que plusieurs index partiels.

4. Columnstore : envisager un index columnstore non-clustered sur les tables > 10 M lignes accédées en analytique.

5. Tuner les requêtes

Pièges fréquents à éliminer en priorité :

• Implicit conversion : WHERE VarcharCol = 123 → force un scan. Typer correctement.
• Functions on indexed columns : WHERE YEAR(DateCol) = 2025 → non SARGable. Réécrire en range.
• SELECT * : ramène des colonnes inutiles, bloque les index couvrants.
• N+1 loops : remplacer par un JOIN ou une table-valued parameter.

-- Réécriture SARGable
-- Avant (non-SARGable)
WHERE CONVERT(DATE, CreatedAt) = '2025-01-01'
-- Après
WHERE CreatedAt >= '2025-01-01' AND CreatedAt < '2025-01-02'

6. Configurer tempdb

-- Nombre de fichiers recommandé : MIN(nb_CPU_logiques, 8), taille pré-allouée identique
-- SQL Server 2019+ : activer optimisation metadata en mémoire
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- Vérifier la fragmentation tempdb
SELECT file_id, name, physical_name, size * 8 / 1024 AS size_MB
FROM tempdb.sys.database_files;

Règle : tous les fichiers de données tempdb doivent avoir la même taille initiale et le même autogrowth pour éviter la contention sur l'allocation.

7. RCSI / Isolation levels

Activer RCSI sur les bases OLTP pour réduire la contention lecture/écriture sans changer le code applicatif :

-- Vérifier l'état
SELECT name, is_read_committed_snapshot_on FROM sys.databases;
-- Activer (nécessite accès exclusif momentané)
ALTER DATABASE [MaBase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 5 SECONDS;

8. Maintenance planifiée

-- Rebuild si fragmentation > 30%, reorganize entre 10% et 30%
SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000
ORDER BY avg_fragmentation_in_percent DESC;

Fréquences recommandées :

Opération	Fréquence	Remarque
Rebuild index	Hebdo (nuit)	Online si édition Enterprise
Update statistics	Après rebuild + quotidien si changement > 20 %	WITH FULLSCAN sur tables clés
DBCC CHECKDB	Hebdo off-peak	Jamais skipper ; alerter sur erreur
Backup full	Quotidien	Tester la restauration

---

Garde-fous et anti-patterns

• Ne jamais ajouter un index sans mesurer l'impact sur les INSERT/UPDATE/DELETE (overhead écriture + espace).
• Ne jamais appliquer un hint de requête (NOLOCK, FORCESEEK) sans comprendre ses effets secondaires ; NOLOCK = lectures sales, données fantômes.
• Éviter les curseurs et boucles WHILE sur de gros volumes ; préférer les opérations set-based ou les TVP.
• Ne pas augmenter max server memory à 100 % de la RAM physique ; laisser au moins 4 Go pour l'OS (plus sur serveurs > 64 Go).
• Ne jamais désactiver auto update statistics sans un plan de mise à jour manuel rigoureux.
• Toujours capturer le plan d'exécution réel (actual), pas estimé : les card-estimations incorrectes sont invisibles sur le plan estimé.
• Tester tout changement (index, config, réécriture) en pré-production avec une charge représentative avant passage en prod.

Bonnes pratiques 2026

• Query Store activé sur toutes les bases de production (SQL 2016+) ; mode READ_WRITE, rétention 30 jours minimum.
• Intelligent Query Processing (IQP) : utiliser le compatibility level 150+ pour bénéficier de l'adaptive joins, batch mode on rowstore, memory grant feedback.
• Accelerated Database Recovery (ADR) : activer sur les bases avec longues transactions pour réduire le temps de recovery.
• Ledger tables (SQL 2022) pour les tables d'audit à intégrité garantie sans overhead applicatif.
• Monitoring : alerter sur PLE < 300 s, batch requests/sec chute > 30 %, wait stats PAGEIOLATCH croissants.