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 :