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:
Alex killed the orphaned transaction (after confirming with the dev), shrunk the log safely, and set up alerting for long-running open transactions. 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:
SELECT last_user_seek, last_user_scan, modifications FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('SalesDB') AND object_id = OBJECT_ID('Orders'); The result: last_user_seek was yesterday. modifications was over 50,000.
Here’s a story that teaches a real-world lesson from those internals. The Case of the Midnight Slowdown Index stats were stale
He ran:
The transaction log is a circular log. It can’t reuse space if any active transaction holds onto a VLFL (virtual log file) even if it’s old. The ETL dropped to 12 minutes
From action games, to racers, to casual puzzle games, we’ll cover all of the obscure indie titles that gamers can look forward to.
