February 15, 2010 at 5:55 am
Hi,
I have migrated SQL server 2000 Database to SQL server 2008 successfully then i checked consistancy of database by DBCC checkdb
now i like to know whether it is necessary to UPDATE stats or not not
by using UPADATE STATICS command.
Regards,
Shivrudra W
February 15, 2010 at 6:42 am
Shivrudra (2/15/2010)
Hi,I have migrated SQL server 2000 Database to SQL server 2008 successfully then i checked consistancy of database by DBCC checkdb
now i like to know whether it is necessary to UPDATE stats or not not
by using UPADATE STATICS command.
Yes,you need to run the statistics this will be help and improve the query performance.
In my suggestion rebuild the all indexes after complete the migration.
Did you run the updateusage ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 15, 2010 at 7:02 am
yes I fired UdateUsagesand it is successfull.
is Dbcc CheckdB atomaticaly update stats or not
Regards,
Shivrudra W
February 15, 2010 at 7:05 am
DBCC CheckDB will not update any statistics.
If You are rebuilding the indexes ,then You need not update the statistics.
February 15, 2010 at 7:17 am
Shivrudra (2/15/2010)
yes I fired UdateUsagesand it is successfull.is Dbcc CheckdB atomaticaly update stats or not
Answer is NO.
CheckDB is Checks the logical and physical integrity of all the objects in the specified database
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 15, 2010 at 7:18 am
Thanks.....
Regards,
Shivrudra W
February 15, 2010 at 7:19 am
Shivrudra (2/15/2010)
Thanks.....
Ur Welcome 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 15, 2010 at 8:16 am
vky3 (2/15/2010)
If You are rebuilding the indexes ,then You need not update the statistics.
Not precisely true. Rebuilding indexes will update the stats associated with those indexes, but there's still the column stats (which aren't associated with a index) that need updating.
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
February 15, 2010 at 11:34 am
In SQL version is 2005\2008 Auto update statistics option is set 'on' by default at database level, no need to update stats frequntely, and also remember updating statistics causes queries to recompile.
Query optimizer checks for stats before compiling and updates stats if necessary. Check this if you need more info.
http://msdn.microsoft.com/en-us/library/ms190397.aspx
EnjoY!
February 15, 2010 at 11:46 am
GT-897544 (2/15/2010)
In SQL version is 2005\2008 Auto update statistics option is set 'on' by default at database level, no need to update stats frequntely, and also remember updating statistics causes queries to recompile.
However the threshold for updating is (on larger tables) 20% + 500 rows, which can be too high in some cases.
Also, that's regular usage. It is general good practice to manually update all stats when upgrading the DB version.
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
February 15, 2010 at 2:31 pm
GilaMonster (2/15/2010)
GT-897544 (2/15/2010)
In SQL version is 2005\2008 Auto update statistics option is set 'on' by default at database level, no need to update stats frequently, and also remember updating statistics causes queries to recompile.However the threshold for updating is (on larger tables) 20% + 500 rows, which can be too high in some cases.
Also, that's regular usage. It is general good practice to manually update all stats when upgrading the DB version.
I agree if it's one time which is only during upgrade, the reason i say this because we have been running update stats manually for while and the jobs will be for hung for 10 - 15 hours and recompile job also hung because waiting on update stats job and application team start complaining about timeouts, so we got rid off these stats are almost up to date now.
EnjoY!
February 15, 2010 at 2:49 pm
I'm sure gail will be able to complete my answer with usefull code... but make sure you update ONLY the stats that needs to be updated.
That way you probably cut down your maintenance window by 50%-90% instead of updating everything wether it needs to or not.
February 15, 2010 at 9:58 pm
GT the risk that you're running there is that if that manual stats update was needed, some queries may now exhibit slow performance occasionally, when the stats are stale enough to result in bad execution plans but not stale enough to cause a stats update. Common problem with queries that filter for latest rows on ascending date/int column
The problem is, there's no programmatic way to identify stats that need manual updates. You can update once a certain % of rows have changed, that's what SQL does, but what's the right %? Any % that you pick (other than 0) will be too high for some cases, too low for others.
What I generally recommend is to analyse the system, look for queries that sometimes go slow and are fixed by an index rebuild, and add those tables to manual stats update jobs, repeating until you have all the susceptible tables. If you don't have time to do that, then a manual rebuild of all stats on a regular basis is a good idea.
Also, unless you're running a multi-TB database, there's no way that stats updates should be taking 10-12 hours. Maybe there's some poor IO performance happening.
Recompile job?
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
February 23, 2010 at 3:22 pm
Thank You Gail ,for the valuable information.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply