5

---

✅ 1) Script – Baseline: Top 50 Queries Mais Pesadas

SELECT TOP 50
    qs.total_elapsed_time/1000000.0 AS total_seconds,
    qs.execution_count,
    (qs.total_elapsed_time/qs.execution_count)/1000000.0 AS avg_seconds,
    qs.total_logical_reads,
    qs.total_worker_time/1000000.0 AS total_cpu_seconds,
    SUBSTRING(qt.text,1,2000) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_elapsed_time DESC;

---

✅ 2) Script – Baseline de Waits do Servidor

SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

---

✅ 3) Script – Levantamento de I/O do Banco

SELECT DB_NAME(database_id) AS database_name,
       file_id,
       num_of_reads,
       io_stall_read_ms,
       num_of_writes,
       io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY io_stall_read_ms DESC;

---

✅ 4) Script – Atualizar Estatísticas (todas as tabelas)

EXEC sp_updatestats;

---

✅ 5) Script – Estatísticas com FULLSCAN para tabela específica

UPDATE STATISTICS dbo.SuaTabela
WITH FULLSCAN;

---

✅ 6) Script – Listar Índices Não Usados

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 s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans + s.user_lookups ASC;

---

✅ 7) Script – Verificar Fragmentação dos Índices

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 ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 5
ORDER BY ps.avg_fragmentation_in_percent DESC;

---

✅ 8) Script – Reorganizar Índices (fragmentação 5%–30%)

ALTER INDEX ALL ON dbo.SuaTabela REORGANIZE;

---

✅ 9) Script – Rebuild de Índices (fragmentação > 30%)

ALTER INDEX ALL ON dbo.SuaTabela 
REBUILD WITH (ONLINE = ON);  -- online somente Enterprise

---

✅ 10) Script – Habilitar READ_COMMITTED_SNAPSHOT (reduz bloqueios)

ALTER DATABASE SeuBanco SET READ_COMMITTED_SNAPSHOT ON;

---

✅ 11) Script – Ajustar MAXDOP

EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 4;  -- ajuste recomendado
RECONFIGURE;

---

✅ 12) Script – Ajustar Max Server Memory

EXEC sp_configure 'show advanced options', 1;  
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)', 32768; -- exemplo: 32GB
RECONFIGURE;

---

✅ 13) Script – Adicionar Arquivo ao TempDB (evitar contenção)

ALTER DATABASE tempdb 
ADD FILE (
    NAME = tempdev2,
    FILENAME = 'E:\MSSQL\TempDB\tempdb_mssql2.ndf',
    SIZE = 2048MB,
    FILEGROWTH = 512MB
);

---

✅ 14) Script – Checar Integridade do Banco (CHECKDB)

DBCC CHECKDB('SeuBanco') WITH NO_INFOMSGS;

---

✅ 15) Script – Job de Manutenção de Índices e Estatísticas (completo)

Você pode usar este script direto no SQL Agent.

-- Manutenção automatizada de índices e estatísticas

DECLARE @TableName VARCHAR(255);

DECLARE cur CURSOR FOR
SELECT table_name = t.name
FROM sys.tables t
WHERE t.is_ms_shipped = 0;

OPEN cur;
FETCH NEXT FROM cur INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Reorganizando e atualizando estatísticas de: ' + @TableName;

    DECLARE @SQL NVARCHAR(MAX);

    -- Reorganize/Rebuild
    SET @SQL = '
        DECLARE @frag FLOAT;

        SELECT @frag = avg_fragmentation_in_percent
        FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(''' + @TableName + '''), NULL, NULL, ''LIMITED'')

        IF @frag BETWEEN 5 AND 30
            ALTER INDEX ALL ON [' + @TableName + '] REORGANIZE;

        IF @frag > 30
            ALTER INDEX ALL ON [' + @TableName + '] REBUILD WITH (ONLINE = ON);
    ';
    EXEC(@SQL);

    -- Atualizar estatísticas
    SET @SQL = 'UPDATE STATISTICS [' + @TableName + '] WITH FULLSCAN;';
    EXEC(@SQL);

    FETCH NEXT FROM cur INTO @TableName;
END

CLOSE cur;
DEALLOCATE cur;

---

⭐ Tudo pronto.