Can stats be auto-updated at less than 20%

Question

Can stats be auto-updated at less than 20%

Lil SQL

SSC Rookie

Points: 38
More actions
December 30, 2009 at 9:46 am

#228776

We are running SQL Server 2005, SP2. Auto Update Statistics is on for the database in question. We also have a job that updates stats on all tables on a weekly basis.
We have a table with about 15,000 records in it. When we have a relatively small number of records updated (about 900, or about 6%), the server comes to its knees. We have multiple processes that query this table regularly, with joins to other tables. These SELECT queries end up taking minutes to complete.
But as soon as we rebuild stats on this table, the problem goes away. We can rebuild stats on this table with a fullscan, since it's such a small table.
This table has 9 columns - 4 nvarchar with a length below 50, an ntext, 2 ints, and 2 datetimes. The only index is clustered, for the primary key (integer).
Does anybody know why such a small amount of data changing causes problems?
Could the problem actually be with stats on other tables, even though rebuilding stats for this table fixes the problem?
Is there a way to force SQL Server to auto-update stats when less than 20% of the data changes?

Viewing 10 posts - 1 through 10 (of 10 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

It sounds like you have a problem with an execution plan. Updating the stats resets those. That's the more likely culprit.

When the queries are running slowly, save the execution plan for one of them. Then run the same query using "with recompile", and see if the execution plan is substantially different.

To answer your direct question, yes, you can update stats at any point. Auto-update will only go after a certain percentage of inserts/updates/deletes, but you can manually update any time. But, most likely, that's not the real problem here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

I'm going to guess...

Ascending date column and the query's filtering for the latest records. Right?

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

Lil SQL SSC Rookie Points: 38 More actions · Answer 3

Gail - Part of the filter is absolutely looking at the two date columns to grab the latest records.

Amazing insight! What does this mean to you?

Lil SQL SSC Rookie Points: 38 More actions · Answer 4

GSquared - Thanks for the tip! If we discover it is a problem with the execution plan, would you suggest simply using the RECOMPILE hint as part of the query? (This isn't a stored proc, but embedded SQL that gets submitted from a .NET application).

If so, should we be concerned about performance issues with the RECOMPILE hint? I'd guess this query gets run between 30 and 100 times per minute during peak usage.

Or would the (possibly) different execution plans just point us in the direction of the real cause?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Lil SQL (12/30/2009)
Gail - Part of the filter is absolutely looking at the two date columns to grab the latest records.
Amazing insight! What does this mean to you?

Not really amazing insight. The most common situation for bad plans when only a small portion of the table has been updated is the case of the ascending column, usually a datetime column. What happens is that, because your query is always looking for the latest rows, it doesn't take long (maybe 3 days if there's no stats update) for the values you're looking for to be so far beyond the end of the stats histogram that the optimiser says 'no rows'. Since there are rows, that leads to near-disastrous execution plans

If you do have this situation, the solution is to update those stats (and probably just those stats) on a daily basis. Just stick an overnight job with the following.

UPDATE STATISTICS <Table Name> WITH FULLSCAN

Since it's a small table, it'll be quick to update.

I think the root cause of this is bad statistics, it's a situation I've seen more than once.

If you're interested, there's a powerpoint slide deck on my blog. It's from a stats presentation I did last month and there's a slide on the 'ascending date, stale stats' problem.

http://sqlinthewild.co.za/resources/ (1st one under PASS Community Summit 2009)

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: 1004683 More actions · Answer 6

Lil SQL (12/30/2009)
GSquared - Thanks for the tip! If we discover it is a problem with the execution plan, would you suggest simply using the RECOMPILE hint as part of the query? (This isn't a stored proc, but embedded SQL that gets submitted from a .NET application).
If so, should we be concerned about performance issues with the RECOMPILE hint? I'd guess this query gets run between 30 and 100 times per minute during peak usage.
Or would the (possibly) different execution plans just point us in the direction of the real cause?

I just went through this on someone's query at work last week. Was causing 330,000 reads per run just to find the single latest date in a paltry 4 million rows and was taking anywhere from 20 seconds to 2 minutes to run.

I added the correct index and the reads dropped to 12 and the run times dropped to sub-millisecond.

As time wear's on, the number of reads have increased a bit... up to 20 after a week on this highly transactional table. Proper regular maintenance on the indexes took care of that.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

I have seen scenarios where stats needed to be updated multiple times a day to ensure optimal plans.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lil SQL SSC Rookie Points: 38 More actions · Answer 8

Thanks everybody for all of your tips.

We have a scheduled job that updates stats on this table multiple times per day, and we haven't had a problem since then.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 9

Very cool! Thanks for the feedback.

--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)