Hi Team,

I've tried unsuccessfully researching how the execution of UPDATE STATISTICS (no options) against one statistic can have a massive impact on query execution vs running UPDATE STATISTICS (WITH FULLSCAN) against the same statistic.
Scenario: A specific tab selection within an application UI (when performing as expected), can return data within 1 sec. Over time the same tab selection degrades to the extent that data is returned after approx. 30secs. After extracting the associated TSQL using profiler, the code was run with using SET STATISTICS IO ON. The output (which completed in approx. 30sec.), revealed several 100s of thousands of logical reads for most of the involved tables. We chose to run UPDATE STATISTICS (without options) against the smallest table's key statistic followed by running the same TSQL. To our good luck and amazement, the results returned in 2 secs and with logical reads down to singe-digit values against ALL the associated tables involved in the query. Then we ran UPDATE STATISTICS WITH FULLSCAN against the same table stat and re-executed the same TSQL. Error! The execution returned back to its poor runtime of approx. 30secs AND the logical reads were back to 100s of thousands for each of the involved tables in the query!! If I then re-run UPDATE STATISTICS (without options), and then re-run the TSQL it completes again in approx. 2secs!!
No research to-date can explain 'WHY'. If I run UPDATE STATISTICS (against the one target stat) WITH FULLSCAN and then SHOW_STATISTICS I get the same results as using ALTER INDEX (to REBUILD the index associated with the target stat) followed by SHOW_STATISTICS. ie the total rows vs rows sampled are the same. For the TSQL that completes in approx. 2secs. running UPDATE_STATISTICS (without options) against the target stat and then running SHOW STATISTICS against the target stat reveals rows sampled is about 1% of the total rows with a much lower 'All Density' value.
can you explain or direct me to some esoteric MS doco that details why query performance is optimized after running UPDATE STATISTICS (no options) and not after running UPDATE STATISTICS WITH FULLSCAN?

Thanks in Advance.
John

Hello ,

Difference lies here sp_updatestats will update a statistic as long as it has had one row modified.sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

But when you do Update stats with full SCAN it compute statistics by scanning all rows in the table or indexed view.And if table huge which i assume is your case it is most likely to take lot of time.

A better method to manage statistics because you do need to manage statistics is to implement a scheduled job that updates based on the percentages of rows that have been modified.

You should use Ola hellegren's script to manage index rebuild and update stats.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Rebuilding an index will update statistics with the equivalent of a full scan for that column.