updating stats on large tables

Sam 1,371 Reputation points
2023-10-19T08:46:06.45+00:00

Hi All,

We have a big database ~5TB. We update the stats with full scan weekly once. It runs more than a day.Since maintenance window is limited, we exit out if it is taking more time. My question is, what is best approach to be taken to update stats for fairly large and highly volatile tables ??? Do we need to break all the small tables in a seperate job and put all the big tables and update stats as a separate job? Please give some ideas? I even tried to increase maxdop to 6 and changing the sample to 70. if I change the sampling to 70, we are seeing performance issues over that particular week. So, I had to revert it back to 100 sampling. 

We are also, do an exercise of cleaning up all the backup tables created by application team as part of their weekly agile releases and also archiving the LOG tables which have data more than 3 years.  Please provide some inputs on updating stats for large highly volatile tables. How to deal with them. Please share some of your real time experiences.

--below is the code schedule inside a sql agent job.

EXECUTE [SQLDBA_utils].[dbo].[IndexOptimize]
@Databases = 'dbname',
@MaxDOP = 4,
@UpdateStatistics='ALL',
@StatisticsSample = 100,
@OnlyModifiedStatistics='Y',
@TimeLimit=86400, -- 24 hours in secs -- if it runs more than 24 hours, exit .
@LogToTable = 'Y'

 

Regards,

Sam

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,899 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-19T21:48:42.95+00:00

    Updating statistics with fullscan on large tables can take quite some time, not the least for the non-indexed columns, since there is a full table scan for every such column.

    At the same time, do you really need fullscan statistics for all columns? It may sometimes be necessary for indexed column, but they are quicker to update (since the index is smaller). In many cases the sampled statistics serves you well enough.

    Robbie makes a good point that you may actually rebuilding/reorganizing.


0 additional answers

Sort by: Most helpful