DatabaseHealthCheck.sql

/*
Purpose:
- Capture a compact, read-only health snapshot for the current SQL Server database.
- Report large tables, index activity, missing-index signals, untrusted foreign keys,
  and fragmentation worth reviewing.

Safety:
- Read-only. Some DMVs can scan metadata for large databases; tune the thresholds below.
- Missing-index and fragmentation results are observations, not automatic change requests.

Requirements:
- SQL Server 2012 or later.
- VIEW DATABASE STATE. Some environments also require VIEW SERVER STATE.

Customization:
- Set @TopTables, @MinPageCount, and @FragmentationThreshold for your workload.
- Run in the database you want to inspect; no database name is hard-coded.
*/

SET NOCOUNT ON;

DECLARE @TopTables INT = 20;
DECLARE @MinPageCount BIGINT = 1000;
DECLARE @FragmentationThreshold DECIMAL(5, 2) = 20.0;

-- 1. Largest user tables by reserved space.
SELECT TOP (@TopTables)
    s.name AS schema_name,
    t.name AS table_name,
    SUM(CASE WHEN p.index_id IN (0, 1) THEN p.rows ELSE 0 END) AS row_count,
    CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(18, 2)) AS reserved_mb,
    CAST(SUM(a.used_pages) * 8.0 / 1024 AS DECIMAL(18, 2)) AS used_mb,
    CAST((SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024 AS DECIMAL(18, 2)) AS unused_mb
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
JOIN sys.indexes AS i
    ON i.object_id = t.object_id
JOIN sys.partitions AS p
    ON p.object_id = i.object_id
   AND p.index_id = i.index_id
JOIN sys.allocation_units AS a
    ON a.container_id = CASE
        WHEN a.type IN (1, 3) THEN p.hobt_id
        ELSE p.partition_id
    END
WHERE t.is_ms_shipped = 0
GROUP BY s.name, t.name
ORDER BY reserved_mb DESC, schema_name, table_name;

-- 2. Index reads and writes since the relevant DMV counters were reset.
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc AS index_type,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates,
    COALESCE(us.user_seeks, 0) + COALESCE(us.user_scans, 0) + COALESCE(us.user_lookups, 0) AS total_reads,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
JOIN sys.indexes AS i
    ON i.object_id = t.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE t.is_ms_shipped = 0
  AND i.index_id > 0
ORDER BY total_reads ASC, user_updates DESC, schema_name, table_name, index_name;

-- 3. Missing-index signals retained by SQL Server for the current database.
SELECT TOP (25)
    CAST(
        (migs.user_seeks + migs.user_scans)
        * migs.avg_total_user_cost
        * (migs.avg_user_impact / 100.0)
        AS DECIMAL(28, 2)
    ) AS estimated_improvement,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY estimated_improvement DESC;

-- 4. Enabled foreign keys whose existing data is not trusted by the optimizer.
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    fk.name AS foreign_key_name,
    fk.is_not_trusted,
    fk.is_disabled
FROM sys.foreign_keys AS fk
JOIN sys.tables AS t
    ON t.object_id = fk.parent_object_id
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE fk.is_not_trusted = 1
  AND fk.is_disabled = 0
ORDER BY s.name, t.name, fk.name;

-- 5. Fragmentation for indexes large enough to justify review.
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    ips.partition_number,
    ips.page_count,
    CAST(ips.avg_fragmentation_in_percent AS DECIMAL(6, 2)) AS fragmentation_percent,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.tables AS t
    ON t.object_id = ips.object_id
JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE t.is_ms_shipped = 0
  AND ips.index_id > 0
  AND ips.page_count >= @MinPageCount
  AND ips.avg_fragmentation_in_percent >= @FragmentationThreshold
ORDER BY fragmentation_percent DESC, ips.page_count DESC;