4 min read
A 64-core, 512GB RAM SQL Server instance was stalling at 25% CPU with hundreds of worker threads blocked on PAGELATCH_UP in database 2. Here is the architecture of TempDB allocation bottlenecks and how to resolve them.
Database Topic Archive
SQL Server Articles
Wait statistics, tempdb, Query Store, Always On availability groups, and SQL Server operations notes.
4 min read
A billing stored procedure that normally completes in 4ms suddenly took 14 seconds per call, pinning SQL Server CPU at 98%. Here is how parameter sniffing poisons Query Store and how to remediate it in production.
6 min read
At 04:50 on a Saturday an ETL package left a transaction open for six hours, the log grew to 512 GB, and error 9002 stopped every write in the database. Here is how I find what is holding the log…
7 min read
Month-end reporting pushed the OLTP pool to ninety seconds of queue depth for six hours straight, and the fix was not hardware — it was a classifier function and two resource pools. How Resource Governor actually throttles, the classifier bug…
7 min read
A nightly status-update batch crossed the 5,000-lock threshold, escalated to a table lock on Orders, and blocked checkout for eleven minutes while the deadlock monitor logged nothing. How lock escalation actually decides, why it is usually a design smell, and…
7 min read
An audit table created without a clustered index accumulated 40 million forwarded records over eighteen months, and its nightly scan went from four minutes to seventy. How forwarded records form, why rebuilds are a treadmill, and when a heap is…
7 min read
We migrated from SQL Server 2014 to 2019, flipped compatibility level to 150 with the upgrade, and three month-end queries regressed forty-fold under the new cardinality estimator. The rollback that worked, the Query Store methodology that made the second attempt…
7 min read
The vendor stored procedure picked a terrible plan the night after a statistics update, and the support contract forbade touching the code. A plan guide pinned the good plan in twenty minutes without a single line of application change. How…
7 min read
The monitoring tool showed Page Splits/sec at four hundred and the team wanted to rebuild every index in the database. The counter was counting end-of-index page allocations that cost nothing, while the real damage — mid-index splits on a random…
8 min read
A well-meaning ad-hoc log backup broke the shipping chain at 14:00 on a Friday, and the monitor's alert threshold was set so loose that nobody noticed until the secondary was nineteen hours stale. Log shipping is the oldest DR trick…