October 17, 2005 at 11:22 am
I am looking for help with statistics maintenance. I have a table with 773K rows where records are archived 3 months after they are closed.
After I run Update Statistics with default values, DBCC SHOW_STATISTICS shows 125 fairly evenly distributed steps, and horrible query performance. When I run Update Statistics with FullScan, DBCC SHOW_STATISTICS shows 34 steps, heavily weighted to more recent rows and much improved performance.
We update statistics nightly, and fullscan will take
Is there a way to force SQL to look at the data distribution more intelligently without performing a FULLSCAN? Or, maybe to leave the existing statistics in place if the latest Rows Sampled is greater than the threshold used by default?
Thank you in advance.
Terry Duffy
October 18, 2005 at 12:11 pm
Do you run sp_ecompile for the table as well ? If not then the optimizer is still using the old execution plan,
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 19, 2005 at 3:49 pm
You cannot tell SQL Server to choose an intelligent statistics other than specifying a percentage to scan. Unfortunately, there have been a few bugs associated with the computation of statistics. It is usually a 100% scan which should be the most accurate one. If you run update statistics with a 100% sampling every night then the statistics would usually be the best. However, if you have auto update statistics , when the data changes beyond a certain percentage, auto update kicks in and recomputes the statistics with a random sample size(which will not be a 100% most of the times) and then the statistics might not be very accurate. You can override this behaviour by either turning off the suto update stats or by adding the 'norecompute' clause.
Dora
October 19, 2005 at 3:54 pm
Rudy,
We do follow with recompile.
Dora,
You confirm my findings. So far nightly 100% is not feasible. We are leaning towards less frequent 100% with norecompute.
Thank you both.
Terry
October 19, 2005 at 4:52 pm
I would think more info is needed:
Table structure including indexes and the SQL ran against it.
As far as maintenanace goes the update stats and running sp_recompile are great but the table is archive constantly. When is the last time you have rebuilt the indexes to reduce fragmentation then run stats and then sp_recompile.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply