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

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)
4 answers
Sort by: Most helpful
-
-
Erland Sommarskog 78,746 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.
-
CarrinWu-MSFT 6,811 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.
-
Erland Sommarskog 78,746 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.