Alex updated stats:
The buffer pool is a shared resource. Morning report’s KEEP hints or large scans polluted the cache. Guru Guide To Sql Server Architecture And Internals.pdf
I can’t directly open or read the contents of a specific PDF file like Guru Guide To SQL Server Architecture And Internals.pdf . However, I can give you a based on the typical themes found in that book—focusing on SQL Server’s core architecture (query processor, storage engine, buffer pool, transaction log, and locking). Alex updated stats: The buffer pool is a shared resource
Alex killed the orphaned transaction (after confirming with the dev), shrunk the log safely, and set up alerting for long-running open transactions. However, I can give you a based on
That open transaction was preventing the transaction log from truncating. The log had grown to 200 GB. The ETL’s large update inside FactSales_Load had to wait for log space, causing log autogrowth events (zero-initialization → slow).
Index stats were stale. The query optimizer thought the scan was cheaper because it didn’t know the table had grown massively since the last stats update.
UPDATE STATISTICS Orders; The plan switched to an index seek. The ETL dropped to 12 minutes. Good, but not great. Why not 8 minutes? Alex dug deeper. During the ETL, he monitored: