DatabaseHealthPerformanceReport.sql

/*======================================================================================= 
    Name:       DatabaseHealthPerformanceReport.sql
    Author:     Alex McAnnally
    Last Edit:  November 2025

    Purpose:    Provide a one-stop database health and performance report for SQL Server.

                Sections:
                  1. Database and file size summary
                  2. Largest tables by size and row count
                  3. Index usage statistics (seeks, scans, lookups, updates)
                  4. Index fragmentation summary
                  5. Missing index recommendations
                  6. Unused / low-usage indexes
                  7. Top resource-consuming queries
                  8. Active sessions and blocking information

    Notes:
      - Run in the context of the database you want to analyze, unless noted otherwise.
      - Some sections require VIEW SERVER STATE permission.
      - This script is read-only and does not modify any data or schema.
=======================================================================================*/

----------------------------------------------------------------------------------------
-- 0. Set target database
--    Uncomment and set the database you want to analyze, or just use the current one.
----------------------------------------------------------------------------------------

-- USE YourDatabaseNameHere;
-- GO

SET NOCOUNT ON;

----------------------------------------------------------------------------------------
-- 1. Database and file size summary
----------------------------------------------------------------------------------------
SELECT 
    DB_NAME()                           AS DatabaseName,
    df.name                             AS LogicalFileName,
    df.type_desc                        AS FileType,
    df.physical_name                    AS PhysicalFileName,
    df.size * 8.0 / 1024                AS FileSizeMB,
    CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS BIGINT) * 8.0 / 1024 AS SpaceUsedMB,
    (df.size - FILEPROPERTY(df.name, 'SpaceUsed')) * 8.0 / 1024     AS FreeSpaceMB,
    df.growth,
    CASE 
        WHEN df.is_percent_growth = 1 THEN 'Percent'
        ELSE 'MB'
    END                                 AS GrowthType
FROM sys.database_files df
ORDER BY df.type_desc, df.name;

----------------------------------------------------------------------------------------
-- 2. Largest tables by size and row count
----------------------------------------------------------------------------------------
;WITH TableSizes AS
(
    SELECT 
        s.name                                       AS SchemaName,
        t.name                                       AS TableName,
        p.rows                                       AS TableRowCount,
        SUM(a.total_pages) * 8.0 / 1024              AS TotalSizeMB,
        SUM(a.used_pages)  * 8.0 / 1024              AS UsedSizeMB,
        SUM(a.data_pages)  * 8.0 / 1024              AS DataSizeMB
    FROM sys.tables t
    JOIN sys.schemas s
        ON t.schema_id = s.schema_id
    JOIN sys.indexes i
        ON t.object_id = i.object_id
       AND i.index_id IN (0, 1)                      -- heap or clustered
    JOIN sys.partitions p
        ON t.object_id = p.object_id
       AND i.index_id = p.index_id
    JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    WHERE t.is_ms_shipped = 0
    GROUP BY s.name, t.name, p.rows
)
SELECT TOP (50)
    SchemaName,
    TableName,
    TableRowCount,
    DataSizeMB,
    UsedSizeMB,
    TotalSizeMB
FROM TableSizes
ORDER BY DataSizeMB DESC, TableRowCount DESC;


----------------------------------------------------------------------------------------
-- 3. Index usage statistics
----------------------------------------------------------------------------------------
SELECT
    DB_NAME()                                      AS DatabaseName,
    OBJECT_SCHEMA_NAME(i.object_id)                AS SchemaName,
    OBJECT_NAME(i.object_id)                       AS TableName,
    i.name                                         AS IndexName,
    i.index_id,
    i.type_desc,
    COALESCE(us.user_seeks,   0)                   AS UserSeeks,
    COALESCE(us.user_scans,   0)                   AS UserScans,
    COALESCE(us.user_lookups, 0)                   AS UserLookups,
    COALESCE(us.user_updates, 0)                   AS UserUpdates,
    (COALESCE(us.user_seeks,0) 
     + COALESCE(us.user_scans,0) 
     + COALESCE(us.user_lookups,0))               AS TotalUserReads,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_lookup,
    us.last_user_update
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
       ON us.object_id  = i.object_id
      AND us.index_id   = i.index_id
      AND us.database_id = DB_ID()
WHERE i.object_id > 255
  AND i.is_hypothetical = 0
  AND i.index_id > 0
ORDER BY TotalUserReads ASC, UserUpdates DESC;

----------------------------------------------------------------------------------------
-- 4. Index fragmentation summary
----------------------------------------------------------------------------------------
DECLARE @dbid INT = DB_ID();

SELECT
    OBJECT_SCHEMA_NAME(ips.object_id)   AS SchemaName,
    OBJECT_NAME(ips.object_id)          AS TableName,
    i.name                              AS IndexName,
    ips.index_id,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.page_count
FROM sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, 'SAMPLED') ips
JOIN sys.indexes i
     ON ips.object_id = i.object_id
    AND ips.index_id  = i.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC, ips.page_count DESC;

----------------------------------------------------------------------------------------
-- 5. Missing index recommendations
----------------------------------------------------------------------------------------
SELECT TOP (50)
    DB_NAME(mid.database_id)                         AS DatabaseName,
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS SchemaName,
    OBJECT_NAME(mid.object_id, mid.database_id)      AS TableName,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    (migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS ImpactScore,
    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
WHERE mid.database_id = DB_ID()
ORDER BY ImpactScore DESC;

----------------------------------------------------------------------------------------
-- 6. Potentially unused or low-usage indexes
----------------------------------------------------------------------------------------
SELECT
    OBJECT_SCHEMA_NAME(i.object_id)            AS SchemaName,
    OBJECT_NAME(i.object_id)                   AS TableName,
    i.name                                     AS IndexName,
    i.index_id,
    i.type_desc,
    COALESCE(us.user_seeks,   0)               AS UserSeeks,
    COALESCE(us.user_scans,   0)               AS UserScans,
    COALESCE(us.user_lookups, 0)               AS UserLookups,
    COALESCE(us.user_updates, 0)               AS UserUpdates,
    (COALESCE(us.user_seeks,0) 
     + COALESCE(us.user_scans,0) 
     + COALESCE(us.user_lookups,0))            AS TotalUserReads
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
       ON us.object_id  = i.object_id
      AND us.index_id   = i.index_id
      AND us.database_id = DB_ID()
WHERE i.object_id > 255
  AND i.is_hypothetical = 0
  AND i.index_id > 1                     -- nonclustered only
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0
  AND (COALESCE(us.user_seeks,0) 
       + COALESCE(us.user_scans,0) 
       + COALESCE(us.user_lookups,0)) = 0
ORDER BY UserUpdates DESC;

----------------------------------------------------------------------------------------
-- 7. Top resource-consuming queries
----------------------------------------------------------------------------------------
SELECT TOP (50)
    DB_NAME(qt.dbid)                                 AS DatabaseName,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count        AS AvgCPUTime,
    qs.total_elapsed_time / qs.execution_count       AS AvgElapsedTime,
    qs.total_logical_reads / qs.execution_count      AS AvgLogicalReads,
    qs.total_logical_reads                           AS TotalLogicalReads,
    qs.total_worker_time                             AS TotalCPUTime,
    qs.total_elapsed_time                            AS TotalElapsedTime,
    qs.creation_time,
    qs.last_execution_time,
    SUBSTRING(qt.text,
              (qs.statement_start_offset / 2) + 1,
              (CASE WHEN qs.statement_end_offset = -1 
                    THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 
                    ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1
             )                                       AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.dbid = DB_ID()
ORDER BY AvgCPUTime DESC;

----------------------------------------------------------------------------------------
-- 8. Active sessions and blocking information
----------------------------------------------------------------------------------------
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.cpu_time,
    s.memory_usage,
    s.reads,
    s.writes,
    r.status                   AS RequestStatus,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.cpu_time                AS RequestCPUTime,
    r.total_elapsed_time      AS RequestElapsedTime,
    DB_NAME(r.database_id)    AS DatabaseName,
    SUBSTRING(t.text,
              (r.statement_start_offset / 2) + 1,
              (CASE WHEN r.statement_end_offset = -1 
                    THEN LEN(CONVERT(nvarchar(max), t.text)) * 2 
                    ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1
             )                 AS CurrentStatement
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
       ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1
ORDER BY r.cpu_time DESC, s.session_id;