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.
Alex updated stats:
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'SalesDB'; Result: LOG_BACKUP . Wait—backups were running fine. But why? Guru Guide To Sql Server Architecture And Internals.pdf
SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID('SalesDB'); He saw that 40 GB of the buffer pool was filled with old data from a morning report. The ETL’s needed pages (the clustered index of Orders ) were being paged in from disk— couldn’t save it because the scan had already caused random I/O earlier. Index stats were stale
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.
Alex updated stats:
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'SalesDB'; Result: LOG_BACKUP . Wait—backups were running fine. But why?
SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id = DB_ID('SalesDB'); He saw that 40 GB of the buffer pool was filled with old data from a morning report. The ETL’s needed pages (the clustered index of Orders ) were being paged in from disk— couldn’t save it because the scan had already caused random I/O earlier.