kohera-logo-regular.svg

Does Persisting sampling rate work on Databases with compatibility levels below 130?

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/


 


						
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...
featured-2302
How to easily parse JSON in Power BI Desktop
Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want...
header-2301
Power BI reports as monitoring tool
Who has not created monitoring reports or beautiful dashboards in Power BI? Would it not be great to show off...
your-modernization-journey-starts-now-header
Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all...