Gianluca Sartori / Grant Fritchey,
There is a daily sp_update stats (on db which is 4TB) that is causing high CPU. To reduce the CPU, it is requiring us to perform Update Statistics of 5 specific tables manually. Query causing high CPU after the sp_update stats has been identified. It is performing an index scan instead of index seek. Once the Update Statistics on 5 tables is performed, query performs an index seek. Why is SP_update stats causing this issue and Update statistics is not? Shouldn't they both be doing the same?
It is a high OLTP system with 2.5 million transactions per hour with heavy reads/writes.
How do we find a balance between SP_update stats versus Update Statistics?
We want to make sure statistics on the database is up to date for optimal query optimization.
Thoughts??
Thanks!
Prakash B