Short answer: Yes, it does. Long answer:
What is persisting sampling rate and why does it exist?
When you create or update statistics (without specifying a sample rate) the sample rate is automatically calculated by SQL Server.
If you would want this statistic (or the whole table) to use a Flat sample rate, say 5 percent, then you could specify a persisting sample rate after updating your stats.
UPDATE STATISTICS dbo.test2 WITH SAMPLE 5 PERCENT, PERSIST_SAMPLE_PERCENT = ON
UPDATE STATISTICS dbo.test2 WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON
(I would not advise changing this to fullscan on big tables)
From Microsoft:
Applies to: SQL Server 2016 (13.x) (starting with SQL Server 2016 (13.x) SP1 CU4) through SQL Server 2017 (starting with SQL Server 2017 (14.x) CU1).
(https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017)
So, what if we are on an instance of sql server 2016 or higher with a database with a lower compatibility level than 2016?
In my example, I used the 110 compatibility level (sql server 2012)
select compatibility_level from sys.databases where name = 'test';
Create the table and index
create table dbo.test2 (id int identity(1,1),
value varchar(50));
create index IX_value on dbo.test2(value);
Insert the values
set nocount on;
insert into dbo.test2 VALUES ('BlaBLa');
go 5000
Result:
Beginning execution loop
Batch execution completed 5000 times.
Some more inserts ( we need a bigger dataset so that the sample rate is no longer 100%)
insert into dbo.test2
select value from dbo.test2;
go 7
Beginning execution loop
Batch execution completed 7 times.
Result: 640 000 Rows
Run a ‘complex’ query so no trivial plan is used and the stats are updated.
select a.value from dbo.test2 a
inner join dbo.test2 ac
on a.id = ac.id where a.value = 'BlaBLa' or a.value = 1;
Get the stats default sample percent
--Check the statistics
SELECT ss.name, persisted_sample_percent,
(rows_sampled * 100)/rows AS sample_percent
FROM sys.stats ss
INNER JOIN sys.stats_columns sc
ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac
ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
WHERE ss.[object_id] = OBJECT_ID('[dbo].[test2]');
Name | persisted_sample_percent | name sample_percent |
IX_value | 0 | 60 |
_WA_Sys_00000001_5CD6CB2B | 0 | 60 |
An extra check with DBCC SHOW STATISTICS
DBCC SHOW_STATISTICS(test2,IX_value)
As expected, we see no persisted sample percent, and a 60% Sample rate.
We rerun the commands after clearing the table and change the sample rate to fullscan (100%)
truncate table dbo.test2
set nocount on;
insert into dbo.test2 VALUES ('BlaBLa')
go 5000
UPDATE STATISTICS dbo.test2 WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON
insert into dbo.test2
select value from dbo.test2
go 7
select a.value from dbo.test2 a
inner join dbo.test2 ac on a.id = ac.id where a.value = 'BlaBLa' or a.value = 1
We check the stats again
--Check the statistics
SELECT ss.name, persisted_sample_percent,
(rows_sampled * 100)/rows AS sample_percent
FROM sys.stats ss
INNER JOIN sys.stats_columns sc
ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac
ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
WHERE ss.[object_id] = OBJECT_ID('[dbo].[test2]');
name | persisted_sample_percent | sample_percent |
IX_value | 100 | 100 |
_WA_Sys_00000001_5CD6CB2B | 100 | 100 |
An extra check with DBCC SHOW STATISTICS
DBCC SHOW_STATISTICS(test2,IX_value)
A test with sample rate of 70%:
UPDATE STATISTICS dbo.test2 WITH sample 70 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
(redo filling of tables after)
name | rows | rows_sampled | persisted_sample_percent | sample_percent |
IX_value | 640000 | 445043 | 70 | 69 |
_WA_Sys_00000001_5CD6CB2B | 640000 | 445043 | 70 | 69 |
A test with sample rate of 80%:
UPDATE STATISTICS dbo.test2 WITH sample 80 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
(redo filling of tables after)
name | rows | rows_sampled | persisted_sample_percent | sample_percent |
IX_value | 640000 | 512224 | 80 | 80 |
_WA_Sys_00000001_5CD6CB2B | 640000 | 512224 | 80 | 80 |
However, you cannot got below the sample rate that SQL Server chooses (same for compatibility level 140).
UPDATE STATISTICS dbo.test2 WITH sample 1 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
(this is the same on compat 140).
name | rows | rows_sampled | persisted_sample_percent | sample_percent |
IX_value | 640000 | 358150 | 1 | 55 |
_WA_Sys_00000001_5CD6CB2B | 640000 | 358150 | 1 | 55 |
In short, it still works, I tried it with compatibility level 100 aswell, which also works.
Source used:
https://blogs.msdn.microsoft.com/sql_server_team/persisting-statistics-sampling-rate/