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

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.

updating stats on large tables

Accepted answer

Erland Sommarskog 107.1K 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.
Please sign in to rate this answer.
Sam 1,476 Reputation points

2023-10-20T05:17:43.9066667+00:00

okay. we tried to reduce sampling to 70%. doing so, reduce some of the query performance . so had to stick back to 100% sample.

Erland Sommarskog 107.1K Reputation points

2023-10-20T21:07:54.9533333+00:00

okay. we tried to reduce sampling to 70%. doing so, reduce some of the query performance

70 or 100 has little importance when it comes to the time for updating statistics. Even 10% is a high number for a large table.

. so had to stick back to 100% sample.

Then you need to analyse where you need FULLSCAN. Obviously, you don't afford to run on each and every statistics.

we have a separate job for rebuild indexes.

That's not smart. If you are rebuilding the index, you get FULLSCAN statistics (unless the table is partitioned, which I only learnt today), so it's a waste of cycles to update statistics separately.

Sam 1,476 Reputation points

2023-10-21T15:22:26.8333333+00:00

Two questions,

Q1) Then you need to analyse where you need FULLSCAN. Obviously, you don't afford to run on each and every statistics.

---How to tell which ones needs a full scan or which ones its okay with 70-80%

Q2)What would be the Sampling percent used, if I don't specify any value?

If I don't specify the value, will the same SAMPLE percent is used for all tables or will it be different sample for different tables based on different rows in each table? I checked the CommandLog, it doesn't show me the SAMPLE percentage.

select * from [master].[dbo].[CommandLog] ;

Command

UPDATE STATISTICS [dbo].[DEPT] [CL_DEPT_c1]

UPDATE STATISTICS [dbo].[DEPT] [NCL_DEPT_c2]

UPDATE STATISTICS [dbo].[EMP] [CL_EMP_c1]

UPDATE STATISTICS [dbo].[EMP] [NCL_EMP_c2]

Regards,

Sam

Erland Sommarskog 107.1K Reputation points

2023-10-21T16:51:40.2366667+00:00

Then you need to analyse where you need FULLSCAN. Obviously, you don't afford to run on each and every statistics. ---How to tell which ones needs a full scan or which ones its okay with 70-80%

Again, you never use 70-80% as a sampling rate. To keep things simple, I'd say that you either use the default sample rate (more about that later) or FULLSCAN. Also, I would suggest that the default sample rate is the normal thing, and you only use FULLSCAN for tables where you notice a regression with sampled statistics. And then you need to find which column that is the critical one.

This is not an analysis that is trivial to do, although it is not overly advanced. But it is a bit time-consuming, since you need to get hold of the plan with both sampled and FULLSCAN statistics and understand what is actually going on.

One case where I have found sampled statistics to be a problem is if I have a nullable indexed column, and there is a condition like WHERE nullablecol IS NULL. Say that this is a big table, several hundred million rows. The number of rows with NULL in this column is small fraction of this, but still as many as maybe 250000. The sample may miss those NULL values more or less entirely, and the optimizer gets the idea that there are only a handful of rows and picks the wrong plan. NULL values are particularly sensitive, since they are always at the edge of the histogram.

Q2)What would be the Sampling percent used, if I don't specify any value?

If you don't specify a sampling rate, SQL Server takes the decision. Basically, the bigger the table, the smaller the rate in per cent.
Sign in to comment

Share via

updating stats on large tables

0 additional answers