Hi Everyone, this is John Sterrett. I am a SQL Server Consultant in Austin, TX. Last year I blogged about a feature called Persist Sample Percent. It had a nasty bug that could negatively impact performance. I have great news to share. The fix is now rolled into SQL 2016 SP2 CU17 and SQL 2019 CU10. Pedro Lopes let me know that with the fix now queued for SQL 2017 CU26, this becomes fixed in all versions.
Kudos to Pedro Lopes and the MSSQL Development team for this update. Make sure you are applying the latest updates so you can leverage all great enhancements, updates, and fixes.
Persist Sample Percent Matters
Okay, you might be wondering why should I consider utilizing persist sample percent? If you have large tables, auto update statistics might be hurting you instead of helping. Yup, that is not a typo. Also, if you update statistics and don’t provide a sample percent you can have the same problem. Worst case, you have a job that updates statistics with a good sample percent, data changes and auto-update uses a subpar percent.
By default, modern versions of SQL Server will utilize a smaller sample percent as your table row count grows. This can potentially give you bad execution plans.
Let’s take a look at the example below. It doesn’t take a whole lot of rows to get a sample percent under 10%
If you want to identify if this is a potential problem in your environment I am including the script below that we utilize in our SQL Server Health Check.
;with cte as (SELECT CAST(((rows_sampled * 1.00)/ [rows] )*100.00 AS NUMERIC(5,2)) AS SamplePCT, OBJECT_NAME(s.object_id) as TableNAME, s.name StatsName,
sp.*
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties (s.[object_id], s.stats_id) AS sp
JOIN sys.objects o on s.object_id = o.object_id and o.is_ms_shipped = 0
WHERE 1=1)
select * from cte where SamplePCT IS NULL or SamplePCT < 10
order by SamplePCT
The following is an example of this occurring. The only change we made was updating stats with a fixed sample rate.
Persist Sample Percent Is Your Friend
You can utilize persist sample percent as long as you are utilizing one of the cumulative updates (CU’s) provided above or a newer CU. Persist sample percent will lock in your sample percent. You will no longer need to worry about an index rebuild removing the persisted sample percent which puts you back at the default sample percent.
You can follow this demo to test this out on your own.
If you enjoyed this blog post subscribe to our newsletter so we can make sure to send you more free tips and videos.
The post Persist Sample Percent in SQL Server IS Fixed! appeared first on SQL Server Consulting & Remote DBA Service.