updating stats on a table with half the rows are deleted.

Heisenberg 261 Reputation points
2022-07-11T20:25:14.01+00:00

hi folks, Whats the best update statistics option on a table where half of the rows of the tables are deleted. i have 4-5 tables where half the rows are deleted. ranging for 140m to 20 million.

I'm also using ola hellengren's index optimize script for weekly index maintenance , so any parameter that you can suggest in this stored proc that will be great.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,437 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Erland Sommarskog 116.5K Reputation points MVP
    2022-07-11T21:16:38.567+00:00

    I'm not sure that I understand what the deleted rows have to do with it. They are gone, and there is no memory of them, once statistics have been updated.

    That is, if you insert 10 million rows and update statistics, or if you first insert 20 million rows and then delete half of them and update statistics, the result will be the same.

    0 comments No comments

  2. Heisenberg 261 Reputation points
    2022-07-11T21:22:39.137+00:00

    @Erland Sommarskog i guess i didnt explain it correctly. I have table A with 200m rows i will be deleting half the rows from this table so finally only 100m rows will be there. Same case for 5 other tables. So my question is running fullscan on these table is the best option? or should i run it with sample parameter.


  3. CathyJi-MSFT 22,346 Reputation points Microsoft Vendor
    2022-07-12T03:51:28.833+00:00

    Hi @Heisenberg ,

    When one of the following changes occurs, the statistic is considered outdated, and an update action is automatically triggered the next time the table is used.

    (1) The table has changed from no data to more than or equal to 1 data.
    (2) For tables with less than 500 rows of data, when the cumulative change of data in the first field of statistical information is greater than 500.
    (3) For tables with more than 500 rows of data, when the cumulative change of data in the first field of statistical information is greater than 500 + (20% × total amount of table data).

    Therefore, for large tables, only more than 1/5 of the data changes, SQL Server will recalculate the statistics.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".


  4. Tom Phillips 17,741 Reputation points
    2022-07-12T12:49:33.063+00:00

    There is nothing special you need to do after deleting 50% of a table.

    If you have auto update stats enabled it will fix the stats in the background after the delete. If not, you should run Ola's script to update the indexes/stats.

    0 comments No comments

  5. Heisenberg 261 Reputation points
    2022-07-13T17:24:14.073+00:00

    Thank you all for the valuable inputs.


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.