What would cause a simple query to use tempdb after reindexing until you update statistics a second time?
Posted on 2014-11-10
This is in regards to Microsoft SQL 2012 Enterprise.
Immediately after updating Indexes and statistics (Manually and also using Hallengren's scripts), several fairly simple queries start using tempdb heavily to the point that it severally impacts the entire SQL Servers performance. Avg. Queue depth goes from .25 to 5 in this state on the tempdb disk on a mirror set. The worst running query goes from a 2 second to 120+ second average run time.
In order to correct the problem we have to manually rerun update statistics on one specific table involved in those queries. I can schedule a second update stats to correct the effects, but would rather fix the root issue if I can identify it.
- Auto Update and Create Statistics are ON in the database. Asynch options are OFF
- No other tables seem to have the problem. (Assumption based on query performance and Tempdb usage)
- Indexes show low fragmentation on the table in question.
- After the second run of Update Statistics, the system operates as expected until the next re-indexing.
Does anyone have any recommendations on how to Root cause this? I have been Googling on this for several weeks now.
I'm hoping I have missed something obvious.