March 5, 2011 at 3:47 am
Under which circumstance do we need to update statistics?...whats its purpose?.. any real time scenario?...
Thanks in Advance.....
March 5, 2011 at 8:06 am
When the auto update doesn't do a good enough job and you have poorly performing queries due to incorrect cardinality estimations. Big tables typically
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
March 6, 2011 at 5:08 am
You'll have to have been observing consistent disparity between the actual data and the statistics. The most frequent place you'll catch this is in execution plans. But it's possible to simply look at the histogram on the statistics and compare that to actual data to begin to see the disparity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2011 at 9:24 am
And every once in a blue moon, especially in older versions of SQL server, the statistics can become corrupt, causing SQL server to retrieve data 'the slow way' (explained for simplicity sake).
The solution is not only to update the statistics yourself, but to specify WITH FULLSCAN. This will totally rebuild the stats and heal any stat errors. Course, this also causes a full read of all pages in that table so it'll take longer than the default sample size.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 7, 2011 at 6:45 am
Given the question the OP asked, I imagine he/she has little ability/knowledge about query plans and how statistics are used to generate them. Time for some reading, since this is a VERY deep (and critical) subject for achieving and maintaining an efficient SQL Server. I recommend this white paper as a start (there is an easily found one for 2005 as well): http://msdn.microsoft.com/en-us/library/dd535534(v=SQL.100).aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply