best practise on statitics Update

sakuraime 2,321 Reputation points
2021-02-18T09:50:02.283+00:00

If I would like to implement a manual update statistics , what are the criteria on choosing which table stats are required to be update and with with sampling rate ?

is it a recommendation to use a threshold slightly less than the following as the indicator to choose the table need to be update stats?

Threshold = √((1000)*Current table cardinality)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,820 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,006 Reputation points
    2021-02-18T10:06:34.973+00:00

    Why reinventing the wheel, use Ola Hallengren script: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    1 person found this answer helpful.
  2. Erland Sommarskog 101.9K Reputation points MVP
    2021-02-18T22:34:55.17+00:00

    I agree with Olaf's and Tom's recommendation, but to answer your question: it depends on your workload.

  3. CarrinWu-MSFT 6,856 Reputation points
    2021-02-19T06:18:53.793+00:00

    Hi @sakuraime ,

    As Olaf's and Tom's recommendation, you can try the script. And we usually perform database maintenance such as index rebuild or index reorganize. SQL Server automatically updates the statistics after the index rebuild. It is equivalent to update statistics with FULL SCAN however; it does not update the column statistics. We should update column statistics after index rebuild as well.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments
  4. Erland Sommarskog 101.9K Reputation points MVP
    2021-02-19T22:14:56.97+00:00

    so what's the dependence ...

    Several things. For one thing, if you have disabled autostats, you will need to cover up for that.

    How is data updated and inserted? Randomly all over the existing data, for instance because the key is a guid? Or is it monotonically increasing like a date or an IDENTITY column? For something non-unique, how often does skewing change? All of a sudden one customer gets a lot more transactions.

    You will have to know your workload.

    Or depend on template numbers, but then we are back to Ola's scripts.

    0 comments