Can you tell me the formula for the sampling rate that is applied by default when updating statistics in mssql 2019?

준영 이 40 Reputation points
2024-04-01T08:05:27.5366667+00:00

Can you tell me the formula for the sampling rate that is applied by default when updating statistics in mssql 2019?

I know that if the number of pages in a table is 8 MB or less, the sampling rate is applied at 100 percent.

SQL Server | Other
{count} votes

Accepted answer
  1. hossein jalilian 11,055 Reputation points Volunteer Moderator
    2024-04-01T20:02:16.6266667+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    For tables with more than 1024 pages:

    The default sampling rate is calculated using the formula:

    (15 * power(Rows, 0.55) / TotalRows * TotalPages + 1024) / TotalPages
    

    Where:

    • Rows is the total number of rows in the table
    • TotalRows is the total number of rows in the table
    • TotalPages is the total number of pages in the table

    This formula ensures that at least 1024 pages are sampled, even if the calculated sampling rate is less than that.

    For tables with less than 1024 pages:

    If the table has less than 1024 pages in the clustered index (or index ID 0 if the table is a heap), SQL Server ignores the sample rate specified and always uses a 100% sampling rate.


    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.