-- 1. Informations sur l'instance
SELECT 
    SERVERPROPERTY('MachineName') AS MachineName,
    SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ProductVersion') AS Version,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

-- 2. Paramètres importants
SELECT 
    name, value_in_use
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'cost threshold for parallelism');

-- 3. TempDB : nombre de fichiers, taille
USE tempdb;
SELECT name, physical_name, size * 8 / 1024 AS SizeMB, is_percent_growth, growth
FROM sys.database_files;

-- 4. Always On activé ?
SELECT 
    is_hadr_enabled AS AlwaysOn_Available
FROM sys.dm_hadr_cluster;

-- 5. Index manquants
SELECT 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Impact,
    mid.statement AS TableName,
    mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mid.index_handle = mig.index_handle
ORDER BY Impact DESC;

-- 6. Index inutilisés
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.index_id = s.index_id AND i.object_id = s.object_id
WHERE database_id = DB_ID()
  AND OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND i.name IS NOT NULL
  AND s.user_seeks + s.user_scans + s.user_lookups = 0
ORDER BY s.user_updates DESC;

-- 7. Statistiques obsolètes
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatName,
    STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM sys.stats s
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY LastUpdated ASC;

-- 8. Fragmentation des index
SELECT 
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.index_id,
    ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.avg_fragmentation_in_percent > 10
ORDER BY ps.avg_fragmentation_in_percent DESC;

-- 9. Requêtes lentes (Query Store activé requis)
SELECT 
    qs.query_id,
    qt.query_sql_text,
    rs.avg_duration / 1000 AS AvgDuration_ms,
    rs.avg_cpu_time / 1000 AS AvgCPU_ms,
    rs.execution_type_desc
FROM sys.query_store_query_text qt
JOIN sys.query_store_query qs ON qt.query_text_id = qs.query_text_id
JOIN sys.query_store_plan p ON qs.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.avg_duration > 5000000 -- > 5s
ORDER BY rs.avg_duration DESC;