PostgreSQL vs SQL Server: Performance Monitoring Compared

-- Server-level: CPU, memory, connections
SELECT
    cpu_count                     AS LogicalCPUs,
    physical_memory_kb / 1024     AS PhysicalMemoryMB,
    committed_kb / 1024           AS CommittedMemoryMB,
    sqlserver_start_time          AS LastRestart
FROM sys.dm_os_sys_info;

-- Active sessions with resource consumption
SELECT
    r.session_id,
    r.status,
    r.cpu_time,
    r.logical_reads,
    r.wait_type,
    r.wait_time,
    t.text                        AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running';

-- Server-level: connections and activity
SELECT
    numbackends                   AS ActiveConnections,
    xact_commit                   AS TotalCommits,
    xact_rollback                 AS TotalRollbacks,
    blks_read                     AS BlocksReadFromDisk,
    blks_hit                      AS BlocksFromCache,
    ROUND(100.0 * blks_hit /
        NULLIF(blks_hit + blks_read, 0), 2) AS CacheHitPct
FROM pg_stat_database
WHERE datname = current_database();

-- Active queries with wait events
SELECT
    pid,
    state,
    wait_event_type,
    wait_event,
    query_start,
    LEFT(query, 120)              AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY query_start;

-- Top 10 wait types by total wait time (excluding idle/benign waits)
WITH WaitFiltered AS (
    SELECT
        wait_type,
        wait_time_ms / 1000.0                     AS wait_sec,
        signal_wait_time_ms / 1000.0               AS signal_sec,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_sec,
        waiting_tasks_count
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
        'SQLTRACE_BUFFER_FLUSH','SLEEP_TASK','SLEEP_SYSTEMTASK',
        'WAITFOR','BROKER_TASK_STOP','BROKER_TO_FLUSH',
        'CHECKPOINT_QUEUE','XE_TIMER_EVENT','XE_DISPATCHER_WAIT',
        'FT_IFTS_SCHEDULER_IDLE_WAIT','HADR_FILESTREAM_IOMGR_IOCOMPLETION'
    )
      AND wait_time_ms > 0
)
SELECT TOP 10
    wait_type,
    CAST(wait_sec AS DECIMAL(18,1))     AS TotalWaitSec,
    CAST(resource_sec AS DECIMAL(18,1)) AS ResourceWaitSec,
    CAST(signal_sec AS DECIMAL(18,1))   AS SignalWaitSec,
    waiting_tasks_count                 AS WaitCount,
    CAST(wait_sec * 100.0 / SUM(wait_sec) OVER()
         AS DECIMAL(5,1))               AS PctOfTotal
FROM WaitFiltered
ORDER BY wait_sec DESC;

-- Snapshot current wait events across all backends
SELECT
    wait_event_type,
    wait_event,
    COUNT(*)                              AS backends_waiting,
    ROUND(100.0 * COUNT(*) /
        NULLIF(SUM(COUNT(*)) OVER(), 0), 1) AS pct_of_waiting
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event IS NOT NULL
  AND pid != pg_backend_pid()
GROUP BY wait_event_type, wait_event
ORDER BY backends_waiting DESC;

-- Wait event categories in PostgreSQL:
-- LWLock       - Lightweight locks (buffer mapping, WAL insert, etc.)
-- Lock         - Heavyweight locks (relation, row, advisory)
-- BufferPin    - Waiting for a buffer pin
-- Activity     - Background process waits (autovacuum, WAL writer)
-- Client       - Waiting for client (ClientRead, ClientWrite)
-- IPC          - Inter-process communication
-- Timeout      - Timer-based waits
-- IO           - Disk I/O waits (DataFileRead, WALWrite, etc.)

-- Index fragmentation report for tables with >1000 pages
SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) + '.' +
    OBJECT_NAME(ips.object_id)        AS TableName,
    i.name                            AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent  AS FragPct,
    ips.page_count,
    ips.avg_page_space_used_in_percent AS DensityPct
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i
    ON ips.object_id = i.object_id
   AND ips.index_id  = i.index_id
WHERE ips.page_count > 1000
  AND ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Table bloat estimation using pg_stat_user_tables
SELECT
    schemaname || '.' || relname       AS table_name,
    n_live_tup                         AS live_tuples,
    n_dead_tup                         AS dead_tuples,
    CASE WHEN n_live_tup > 0
         THEN ROUND(100.0 * n_dead_tup /
              (n_live_tup + n_dead_tup), 1)
         ELSE 0 END                    AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Detailed bloat analysis (requires pgstattuple extension)
-- CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('public.orders');

-- Retrieve cached plan for a known query hash
SELECT
    qs.query_hash,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count   AS avg_cpu_us,
    qs.total_logical_reads / qs.execution_count AS avg_reads,
    TRY_CONVERT(XML, qp.query_plan)            AS QueryPlan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.query_hash = 0xABCDEF0123456789
ORDER BY qs.total_worker_time DESC;

-- Query Store: detect plan regressions (SQL Server 2016+)
SELECT
    q.query_id,
    p.plan_id,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    p.is_forced_plan
FROM sys.query_store_query q
JOIN sys.query_store_plan p         ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.avg_duration > 1000000  -- > 1 second average
ORDER BY rs.avg_duration DESC;

-- Detailed execution plan with actual runtime statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

/*  Example output:
    Hash Join  (cost=1.52..234.67 rows=1842 width=52)
               (actual time=0.089..3.421 rows=1837 loops=1)
      Hash Cond: (o.customer_id = c.id)
      Buffers: shared hit=312
      ->  Seq Scan on orders o  (cost=0.00..198.50 rows=1842 width=24)
                                (actual time=0.021..1.832 rows=1837 loops=1)
            Filter: (order_date >= '2026-01-01' AND status = 'shipped')
            Rows Removed by Filter: 8163
            Buffers: shared hit=285
      ->  Hash  (cost=1.15..1.15 rows=30 width=36)
                (actual time=0.042..0.043 rows=30 loops=1)
            Buffers: shared hit=1
            ->  Seq Scan on customers c  ...
*/

-- Enable auto_explain to log slow query plans automatically
-- In postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '500ms'
-- auto_explain.log_analyze = true
-- auto_explain.log_buffers = true

-- Buffer cache hit ratio and page life expectancy
SELECT
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Buffer cache hit ratio',
    'Buffer cache hit ratio base',
    'Page life expectancy',
    'Page reads/sec',
    'Page writes/sec'
)
AND object_name LIKE '%Buffer Manager%';

-- Memory consumption by database in buffer pool
SELECT
    DB_NAME(database_id)           AS DatabaseName,
    COUNT(*) * 8 / 1024           AS CachedMB
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4  -- Exclude system databases
GROUP BY database_id
ORDER BY CachedMB DESC;

-- Cache hit ratio per table (from pg_statio_user_tables)
SELECT
    schemaname || '.' || relname    AS table_name,
    heap_blks_hit                   AS cache_hits,
    heap_blks_read                  AS disk_reads,
    CASE WHEN heap_blks_hit + heap_blks_read > 0
         THEN ROUND(100.0 * heap_blks_hit /
              (heap_blks_hit + heap_blks_read), 2)
         ELSE 100 END               AS hit_ratio_pct
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 1000
ORDER BY disk_reads DESC;

-- Checkpoint and background writer activity
SELECT
    checkpoints_timed,
    checkpoints_req                 AS checkpoints_forced,
    buffers_checkpoint,
    buffers_clean                   AS bgwriter_buffers,
    buffers_backend                 AS backend_direct_writes,
    ROUND(100.0 * buffers_backend /
        NULLIF(buffers_checkpoint + buffers_clean
               + buffers_backend, 0), 1) AS backend_write_pct
FROM pg_stat_bgwriter;

SELECT TOP 5
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    qs.execution_count,
    qs.total_logical_reads,
    SUBSTRING(st.text,
        (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2 + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

SELECT
    queryid,
    LEFT(query, 200)              AS query_text,
    calls                         AS execution_count,
    total_exec_time / calls       AS avg_ms,
    shared_blks_hit + shared_blks_read AS total_blocks
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

SELECT TOP 10
    OBJECT_SCHEMA_NAME(ios.object_id) + '.' +
    OBJECT_NAME(ios.object_id)     AS TableName,
    SUM(ios.page_io_latch_wait_count) AS PhysicalReads,
    SUM(ios.page_io_latch_wait_in_ms) AS IOWaitMs
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
GROUP BY ios.object_id
ORDER BY PhysicalReads DESC;

SELECT
    schemaname || '.' || relname   AS table_name,
    heap_blks_read                 AS disk_reads,
    heap_blks_hit                  AS cache_hits,
    ROUND(100.0 * heap_blks_hit /
        NULLIF(heap_blks_hit + heap_blks_read, 0), 1) AS hit_pct
FROM pg_statio_user_tables
WHERE heap_blks_read > 100
ORDER BY heap_blks_read DESC
LIMIT 10;

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    t.text                        AS QueryText,
    DATEDIFF(SECOND,
        r.start_time, GETDATE())  AS DurationSec,
    r.blocking_session_id,
    r.wait_type
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r       ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE DATEDIFF(SECOND, r.start_time, GETDATE()) > 30
ORDER BY DurationSec DESC;

SELECT
    pid,
    usename,
    client_addr,
    LEFT(query, 200)              AS query_text,
    EXTRACT(EPOCH FROM
        (NOW() - xact_start))     AS xact_duration_sec,
    wait_event_type,
    wait_event,
    state
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start < NOW() - INTERVAL '30 seconds'
ORDER BY xact_start;

-- Check for lock blockers
SELECT
    blocked.pid                   AS blocked_pid,
    blocked.query                 AS blocked_query,
    blocker.pid                   AS blocker_pid,
    blocker.query                 AS blocker_query
FROM pg_locks bl
JOIN pg_stat_activity blocked     ON bl.pid = blocked.pid
JOIN pg_locks kl
    ON bl.locktype = kl.locktype
   AND bl.database IS NOT DISTINCT FROM kl.database
   AND bl.relation IS NOT DISTINCT FROM kl.relation
   AND bl.page IS NOT DISTINCT FROM kl.page
   AND bl.tuple IS NOT DISTINCT FROM kl.tuple
   AND bl.transactionid IS NOT DISTINCT FROM kl.transactionid
   AND bl.pid != kl.pid
   AND NOT bl.granted
   AND kl.granted
JOIN pg_stat_activity blocker     ON kl.pid = blocker.pid;