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/

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...