Short answer: Yes, it does. Long answer:
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)
In my example, I used the 110 compatibility level (sql server 2012)
select compatibility_level from sys.databases where name = 'test';
create table dbo.test2 (id int identity(1,1),
value varchar(50));
create index IX_value on dbo.test2(value);
set nocount on;
insert into dbo.test2 VALUES ('BlaBLa');
go 5000
Result:
Beginning execution loop
Batch execution completed 5000 times.
insert into dbo.test2
select value from dbo.test2;
go 7
Beginning execution loop
Batch execution completed 7 times.
Result: 640 000 Rows
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;
--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 |
DBCC SHOW_STATISTICS(test2,IX_value)
As expected, we see no persisted sample percent, and a 60% Sample rate.
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
--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 |
DBCC SHOW_STATISTICS(test2,IX_value)
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 |
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 |
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/
© 2022 Kohera
Crafted by
© 2022 Kohera
Crafted by