Update Stats Sample Rate does not work

 dans Sites Microsoft par

Create/Update statistics allows users to speicfy the sample rate. However, the sample rate may not work as you expected in some scenarios.

 

 

1.Tables that have not greater than 1024 pages

I’m going to use the table Production.Product  in AdventureWorks2019 to demonstrate

use AdventureWorks2019

go

create statistics IProductID on Production.Product(ProductID) with sample 20 percent

go

dbcc show_statistics(‘Production.Product’,’IProductID’)

 

In this script, Sample rate is set to 20%.  However, the ‘DBCC how_statistics’ shows that the ‘Rows’ equals to ‘Rows Sampled’, which means it’s 100% sampled.

 

 

Why?

Because for table with less than 1024 pages in the clustered