Unit
- Throughput: The number of transactions or queries a database completes per second. For example, an e-commerce checkout system may require 500 transactions per second during peak hours.
- Latency: The time between sending a query and receiving its result. Sub-10 ms latency is typical for OLTP systems, while analytical queries may tolerate seconds.
- Concurrency: The number of simultaneous users or connections the database can handle without degradation. High concurrency often reveals locking and resource contention issues.
- Resource Utilization: CPU, memory, disk I/O, and network usage percentages. A database consistently using 90% CPU may need query optimization or hardware scaling.
- Cache Hit Ratio: Percentage of queries served from buffer cache rather than disk. A ratio below 90% indicates memory may be insufficient or queries are not cache-friendly.
- Deadlock Frequency: Number of deadlocks per hour. Even occasional deadlocks signal transaction design problems that can cascade into performance stalls.
- Response Time Distribution: Percentiles (p50, p95, p99) give a fuller picture than averages. A p99 of 2 seconds while p50 is 10 ms suggests a small percentage of slow queries need attention.
- Wait Statistics: System waits (disk I/O, locks, latches) indicate what the database is waiting on. High PAGEIOLATCH waits point to disk subsystem issues.
- Connection Pooling Efficiency: The ratio of reused connections to new connections created. A pool that frequently exhausts connections forces costly new handshakes and can increase latency. For example, a ratio below 80% suggests the pool size or timeout settings need tuning for the workload.
- Query Execution Plan Cache Hit Ratio: Percentage of queries that reuse cached execution plans instead of recompiling. Frequent plan recompilations waste CPU and can cause plan cache bloat. A ratio below 90% may indicate parameterized queries or plan stability issues.
- Transaction Rollback Rate: The percentage of transactions that are rolled back. A high rollback rate above 5 percent often indicates application errors, deadlock victims, or integrity violations that waste resources and increase contention. Monitoring this metric helps detect problematic transaction patterns.
- Index Fragmentation Level: The percentage of fragmentation in indexes. Fragmentation above 30 percent degrades scan performance and increases I/O. Regular maintenance such as rebuilding or reorganizing indexes can restore efficiency and improve query response times. For example, a fragmented index on a large table can cause excessive page splits and slow down queries.
Code Example
SQL
-- Query to check cache hit ratio in SQL Server
SELECT [object_name], [counter_name], [cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio';
-- In PostgreSQL
SELECT SUM(blks_hit) / (SUM(blks_hit) + SUM(blks_read)) AS cache_hit_ratio
FROM pg_stat_database;These queries retrieve the buffer cache hit ratio from two popular databases. A value below 90 indicates disk I/O could be reduced by increasing memory or improving query locality.
Quick Revision
- Key performance metrics include throughput, latency, concurrency, resource utilization, cache hit ratio, deadlock frequency, response time percentiles, and wait statistics.
- Throughput measures work completed per second; latency measures delay per operation.
- Cache hit ratio reflects how often data is found in memory versus needing disk access.
- Percentiles like p99 identify outlier queries that degrade user experience.
- Wait statistics pinpoint the subsystem causing delays (disk, CPU, locks).