March 28, 2018 at 10:13 am
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. After the sp_update stats job, query starts using high CPU. Query starts 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??
Prakash B
March 28, 2018 at 11:55 am
Is it possible that calling this through sp_updatestats vs UPDATE STATISTICS is using a different sampling size? The docs say sp_updatestats will use the default sampling if 'resample' is not specified.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updatestats-transact-sql
What settings where you using for each command?
March 28, 2018 at 1:02 pm
Below query will give the rows sampled during update stats.
SELECT * FROM sys.dm_db_stats_properties (object_id('TableName'), 1);
Default sampling size was 0.5 when Update Statistics was used.
Prakash B
March 29, 2018 at 7:20 am
In your situation, you need to be using UPDATE STATISTICS, not sp_update_stats. The latter is just a shortcut to performing the former. You can look at the code. All it does is see if any data has been modified, any at all, and then it updates statistics using a generic sampling rate. UPDATE STATISTICS is for direct control, including ensuring when and how a FULL SCAN is performed, the exact sample rate, etc. Based on your issues, it sounds like the default sampling isn't cutting it and you need better. That's UPDATE STATISTICS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply