Index rebuild removes persisted sample percent
The UPDATE STATISTICS statement has a (relatively) new PERSISTSAMPLEPERCENT option, but when rebuilding an index which previously had statistics updated with this value set the persisted sample percent is reset back to default. I would expect the rebuild to create statistics with 100% sampling (which it does), but leave the persisted sample percent intact so that automatic statistics updates would use the previously specified value.
Please fix it!! We're having to create some clunky work-around because of this bug.
Index rebuilds were thought to perform full scans of index statistics (not column statistics) and so why it resets this essential piece of metadata and we end up with a tiny statistics sample is an unwanted surprise.
Chuck Hottle commented
John Sterrett commented
Why two years later there still is no response from Microsoft to this bug?
Andrew J. Kelly commented
The addition of Persisted sampling rates was a huge step forward in maintaining proper stats. This pretty much defeats all the benefits. This must be fixed. I am ok with a 100% sample rate when the index is rebuilt but we need the original persisted rate kept for the next update as is.
Ben Miller commented
This is very disruptive when you have a lot of time invested into managing statistics and have samples persisted. There is an option to keep NORECOMPUTE on or off, but nothing for the PERSIST_SAMPLE_PERCENT. It just takes it away.
Please don't leave us to roll some extravagant solution. Fix this issue.
The only workaround is to run update statistics again with that option on. For example:
update statistics table_name stat_name with sample x PERCENT , PERSIST_SAMPLE_PERCENT = ON.
Hope Microsoft can consider persisted sample percent as the same as fill factor and compression and retain it.
Gary H commented
An index will not be rebuilt with a full scan if it is a partitioned index.
I have encountered this issue myself (was about to create a post myself, but I saw this one). I would expect that when rebuilding an index, the persist_sample_percent value would remain, although the index should be rebuilt with a full scan, since it is an index rebuild.
scott dubose commented
Is there a workaround for this? It is quite disruptive and defeats the purpose of the setting.