Given are two partitioned tables T1 and T2 with no automatic statistics (their statistics where updated WITH NORECOMPUTE option). T1 has only partition N populated and T2 has partition N empty. Now I need to switch partition X between T1 and T2.

With SQL Server 2008 statistics where unmergeable and would have to be recreated for T2 (T1 is empty after switching in my example).

SQL Server 2014 allows this but requires equal sampling rates for T1 and T2.

  • But how can I prevent the optimizer from changing the sampling rate which I provide to the UPDATE STATISTICS command?

  • Is the optimized sampling rate change maybe guaranteed to be small
    enough to never disrupt merging statitcs?

  • Is at least a FULLSCAN (i.e. 100% sampling rate) guaranteed to not be altered by the optimizer?

To my understanding the RESAMPLE keyword does not help me as it would require an update of statistics after SWITCHing (even though only for the new partition which is a great improvement over SQL Server 2008). One of the reasons for me to SWITCH is to never have T2 in maintainance mode where the user can not access it. In other words, I prepare the readonly data for the user in T1 but the user only accesses T2.

  • Please clarify what you mean by "auto statistics switched off". That could mean any of four different database settings. About how big are your partitions? Are you trying to avoid gathering statistics again because gathering statistics is too expensive? – Joe Obbish Nov 23 '17 at 3:06
  • @JoeObbish, I updated statistics with NORECOMPUTE option. – Patrick Fromberg Nov 23 '17 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.