Microsoft SQL Server insights

Low buffer cache hit ratio forces excessive physical I/O, visible as high ReadIOPS and ReadLatency. Indicates working set doesn't fit in memory or inefficient query access patterns.

Disk queue depth buildup indicates I/O subsystem cannot keep pace with request volume, causing WriteLatency/ReadLatency spikes and degraded query performance.

When long-running transactions hold locks, downstream queries accumulate in blocking chains, causing perceived system-wide slowness even when CPU and memory are healthy.

Insufficient memory forces queries to wait for compilation grants (RESOURCE_SEMAPHORE_QUERY_COMPILE), creating cascading slowness as new queries queue behind compile-blocked statements.

ASYNC_NETWORK_IO waits indicate SQL Server is waiting for client to consume result sets, often caused by slow network, client-side processing bottlenecks, or large unbuffered result sets.

Rising deadlock rate indicates contention patterns that require immediate query redesign, as deadlocks force transaction rollbacks and degrade throughput.

Uncontrolled transaction log growth (sqlserver_database_log_growths or sqlserver_transaction_log_growth) causes performance degradation during autogrow operations and risks disk space exhaustion.

High sqlserver_batch_sql_compilation_rate and sqlserver_sql_recompilations_per_sec indicate excessive plan compilation overhead, often from parameter sniffing, non-parameterized queries, or schema changes.

Elevated sqlserver_lock_timeouts_per_sec indicates queries abandoning lock waits, often masking underlying blocking issues and causing application-level retry storms.

Excessive sqlserver_page_checkpoint_flush_rate overwhelms I/O subsystem with dirty page writes, causing latency spikes for user transactions and log flush operations.

High sqlserver_access_page_splits indicates inefficient index maintenance, causing extra I/O and potential lock escalation as pages reorganize during write operations.