When do statistics update?

Question

Post reply

When do statistics update?

Alan G-436699

SSC-Addicted

Points: 484
More actions
January 12, 2008 at 2:49 am

#75836

I have a stored procedure that has suddenly started taking longer to execute. One of the possibilities I want to check is the issue of statistics.

The sp has one INSERT followed by about 20 UPDATES. The INSERT follows a TRUNCATE statement on that table and loads about 2.5m rows.

The question: As I have truncated the table and so I'm loading it from scratch, will the statistics automatically be updated on the table? Also, what about the index statistics?

What is best practise for this kind of sp? Should I remove indexes prior to the INSERT and then rebuild them from scratch after the INSERT has completed? If I do this, will the index rebuild automatically re-calculate the index statistics?

Thanks for your help in advance.

Regards, Alan.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

If you have auto statistics set, then SQL Server will calculate them. I think this is done when you create the index, but I'm not 100% sure. You can force an update of statistics if needed.

Typically after the load and updates, you'd run a reindex or build the indexes then

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

I would recommend that you do a stats update after the truncate, as truncate doesn't affect the stats, or the rowmodctr.

If you have auto update stats on, then stats are updates on a read of the table, if the number of rows that have changed since the last update exceed a certain percentage of the table.

I can't remember the threshold for tables with less than 500 rows, but greater than 500 rows, it's 20% of the table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan G-436699 SSC-Addicted Points: 484 More actions · Answer 3

Are you suggesting doing the update Stats after the TRUNCATE but before the INSERT?

Is this because the stats facility after the TRUNCATE still thinks there's 2.5m rows in the table and so when I then load the 2.5m rows, the stats facility thinks that nothing has changed and hence no auto stats update occurs? If so, then this is a crucial point because this table is truncated and loaded with 2.5m rows 365 days per year and therefore the stats could be in a terrible state by now!

Is there a simple way to determine if auto stats are on or off?

I'd rather look at a simple tick box then do a table trace!

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 4

It is possible the stats are out of date for the insert, but that won't matter. They aren't used for an insert. They're used on the next SELECT. Running stats after the truncate will be quick as they'll be nothing to check and current stats will be booted.

Does the data change substantially every day? Stats are merely in idea of how the data is distributed, so the optimizer knows whether an index might be useful for a query.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

I have a stored procedure that has suddenly started taking longer to execute.

Before you do anything, you must determine exactly what changed... and, trust me, something changed. Things like this don't just happen... there's ALWAYS a reason and correct identification of the problem is 90% of the solution. Remember... it WAS working just fine... something changed and you shouldn't waste your time trying to tune something that might not be able to be tuned... ya GOTTA find out what changed!

The usual cause of this particular ailment is that the number of rows has increased to a point where a "tipping point" has been reached for your server. For example, if you run a simple update on a million rows, it'll usually take less than a minute. Therefore, you would expect it to take less than 10 minutes to update 10 million rows and you'd be dead wrong... it could (and frequently will) take hours. I'm not sure exactly why this occurs but it does.

The fix, in such cases, is to create a control loop that updates the table about a million rows at a time. Anything over 2 million rows in an update or delete and you take the chance of hitting the "tipping point".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Alan G (1/13/2008)

Are you suggesting doing the update Stats after the TRUNCATE but before the INSERT?

Is this because the stats facility after the TRUNCATE still thinks there's 2.5m rows in the table and so when I then load the 2.5m rows, the stats facility thinks that nothing has changed and hence no auto stats update occurs? If so, then this is a crucial point because this table is truncated and loaded with 2.5m rows 365 days per year and therefore the stats could be in a terrible state by now!

Is there a simple way to determine if auto stats are on or off?

I'd rather look at a simple tick box then do a table trace!

Or after the insert, before the updates. Doesn't really matter. If you update the stats before the insert, then an addition of 2.5 million rows will be more than enough to trigger a stats update.

You can easily check how accurate the stats are. DBCC SHOW_Statistics ('table name', index name)

As for auto stats, it's a database level property.

Echoing Jeff, find out if any mods have been done to table/proc/indexes since it was running well. I think I've had maybe 3-4 procs in the time I've been doing optimisation where a sudden slowdown was stats related, and those were odd cases. Most of the time it's been because someone changed something somewhere.

You can use profiler and check the SP:StmtCompleted event to see which of the statements in the proc are taking the longest. Should help you narrow down the cause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

I strongly suspect that the only mod that has been done is the number of rows have increased. 2.5 million rows is right at the threshold of the "tipping point" for SQL Server. Do a SET ROWCOUNT 1000000 and see if I'm right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Alan G-436699 SSC-Addicted Points: 484 More actions · Answer 8

Alan G-436699

SSC-Addicted

Points: 484

January 14, 2008 at 12:18 pm

#767799

Thanks for your advice. I've put a log entry to include sysdate between each SQL statement in the sp and this will run in tonight's batch so I will understand which statement(s) are taking the longest.