SQL Server Tuner

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 :

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 AS avg_dur_us,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS stmt
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_cpu_us DESC;

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 :

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.