best practise on statitics Update

Question

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)

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.