PostgreSQL Topic Archive
Wait statistics, tempdb, Query Store, Always On availability groups, and SQL Server operations notes.
The order database froze writes for forty-one seconds at peak load because the data file grew by 32 GB and SQL Server zeroed every byte of it first. Percent-growth compounding, Instant File Initialization, and the pre-sizing discipline that makes growth boring.
The instance had 128 GB of memory and the buffer pool could not hold fifteen minutes of hot data, because 41 GB of the plan cache was plans executed exactly once. Measuring single-use bloat and the three levers that actually fix it.
Setting MAXDOP to 1 because of CXPACKET waits amputates a feature to treat a symptom. NUMA-aware MAXDOP guidance, why the default threshold of 5 is ancient history, and where each setting should actually live.
The backups had been succeeding for eight months; the restores had never been tried. FULL/DIFF/LOG chain design as an RPO/RTO contract, what silently breaks the log chain, and why only a restore drill proves anything.
Columnstore cut a report from four minutes to six seconds until trickle inserts rotted the rowgroups. Rowgroups, segment elimination, the deltastore lifecycle, batch mode on rowstore, and when a plain btree still wins.
The index existed and the seek was in the plan, but the estimate said one row and the table said fourteen million. How auto-update thresholds, sampling, and the ascending-key trap produce bad estimates, and how to triage them with sys.dm_db_stats_properties.
The sync job double-counted rows for three weeks because someone said 'change tracking' and someone else heard 'CDC'. What each feature actually captures, what each one costs, and a four-question test for picking the right one.
The dashboard said SYNCHRONIZED while the readable secondary served twenty-minute-old data. Send queue and redo queue are different numbers; here is what each one means, what synchronous commit really guarantees, and how to measure RPO.
Every weekday at 9:05 the order system's p99 went from forty milliseconds to eleven seconds, caused by one report holding a twelve-gigabyte memory grant. How grants are sized, how to read the queue in sys.dm_exec_query_memory_grants, and how to fence the pattern.
The nightly purge had been deleting for six hours and generating 400 GB of log when they called me; the sliding-window replacement runs in 200 milliseconds. Partition functions, aligned indexes, SWITCH mechanics, and when partitioning is the wrong tool.
The app said the database was down; the database said CPU was twelve percent and forty-three sessions were queued behind one sleeping connection holding a lock. How I walk a blocking chain to its head and decide whether to kill or wait.
The parameter was nvarchar, the column was varchar, and four million rows got converted one at a time. How CONVERT_IMPLICIT turns seeks into scans, why ORMs cause it so reliably, and where to fix it for good.
A failover that should have taken two minutes took forty-seven because the log had 30,000 virtual log files. How autogrowth wrote that layout, what log_reuse_wait_desc is telling you, and how to rebuild the layout exactly once.
The order database froze writes for forty-one seconds at peak load because the data file grew by 32 GB and SQL Server zeroed every byte of it first. Percent-growth compounding, Instant File Initialization, and the pre-sizing discipline that makes growth boring.
The sync job double-counted rows for three weeks because someone said 'change tracking' and someone else heard 'CDC'. What each feature actually captures, what each one costs, and a four-question test for picking the right one.
The nightly purge had been deleting for six hours and generating 400 GB of log when they called me; the sliding-window replacement runs in 200 milliseconds. Partition functions, aligned indexes, SWITCH mechanics, and when partitioning is the wrong tool.
The instance had 128 GB of memory and the buffer pool could not hold fifteen minutes of hot data, because 41 GB of the plan cache was plans executed exactly once. Measuring single-use bloat and the three levers that actually fix it.
The app said the database was down; the database said CPU was twelve percent and forty-three sessions were queued behind one sleeping connection holding a lock. How I walk a blocking chain to its head and decide whether to kill or wait.
Same procedure, two seconds for one tenant, thirty-second timeouts for another. How to prove parameter sniffing with plan variance, and the fix toolkit ranked from better indexes to 2022's Parameter Sensitive Plan optimization.
A week of error 1205s and a proposed retry loop that would fix nothing. How to pull deadlock graphs from system_health, read victim, owner, and waiter nodes, and kill the three patterns behind most deadlocks.
One database setting ended our reader-writer blocking overnight and introduced two bugs we only caught in a concurrency soak. RCSI mechanics, the honest migration checklist, and the version-store monitoring that keeps tempdb safe.
The deploy that doubled p95 left its fingerprints in sys.query_store_runtime_stats. How to configure Query Store so the data is there when you need it, compare intervals to find the regressed plan, and force a plan without fooling yourself.
The nightly REBUILD job I inherited burned 90 GB of transaction log and fixed nothing. What fragmentation still means on flash, what REBUILD actually buys over REORGANIZE, and why updating statistics is usually the real win.
A batch job that got slower as we added workers was queueing on three pages in tempdb. How to tell PAGELATCH from PAGEIOLATCH, size the data files, weigh memory-optimized metadata in 2019, and keep the version store from eating the drive.
Since-restart wait totals average your quietest night and your worst deploy into one meaningless number. Here is how I diff sys.dm_os_wait_stats over an interval, drop the benign noise list, and branch on what CXPACKET, PAGEIOLATCH, WRITELOG, and LCK_M_* are actually telling you.