kohera-logo-regular.svg

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

Vector_BG.png

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

Vector_BG.png

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/


 


						
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
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...