Post reply

statistics

  • March 9, 2010 at 10:46 am

    #141881

    How do i update statistics on whole database like i did below for each table, though i set Auto Update ON but still sometimes i have to do manually to improve performance.

    UPDATE STATISTICS tableA

    WITH FULLSCAN

  • March 9, 2010 at 10:52 am

    #1130822

    I believe you can use sp_updatestats

  • March 9, 2010 at 11:00 am

    #1130826

    Use sp_MSforeachtable

    EnjoY!

  • March 9, 2010 at 2:44 pm

    #1130993

    sp_updatestats will do all user defined tables.

  • March 10, 2010 at 8:20 am

    #1131439

    how do i do this on all tables, all databases in a server.

  • March 10, 2010 at 8:26 am

    #1131442

    Create a maintenance plan within SSMS is your quickest way of doing it. It will allow you to select All user databases or those that you want to run against.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • March 10, 2010 at 9:10 am

    #1131480

    Or you can go for a following simple script =>

    use databasename

    go

    select 'update statistics ' + name +' with fullscan ' from sysobjects where type='U'

    copy the o/p and run.

  • March 10, 2010 at 10:13 am

    #1131527

    Updating all statistics within a database seems to be easy but it may be costly, especially when the database is large. The primary reason is that updating statistics consumes significant amount of computer resources and thus you may want to minimize this negative impact on other critical processes or jobs running in the database.

    We should determine which statistics indeed need to be updated. If a table is static or shows little growth over time, the statistics based on this table unlikely need update because the base data used to generate the statistics has not been changed to a significant level to demand statistics update . Tables that experience large data growth should be the major focus on statistics update. If a database has Auto Create Statistics and Auto Update Statistics turning on, DBCC Show_Statistics will show when the last statistics updated and it will usually show no recent statistics update in tables with no or little growth.

    For best practices, updating statistics should be customized according to the actual status of the statistics and this is especially important for large databases.

  • March 10, 2010 at 10:21 am

    #1131537

    Also, I'd recommend that the ensure that the stats are updated asynchronously, otherwise the queries running against them have to wait until the updates are completed before they can access the data - which could impose a nasty hit on a large, busy, table where the update takes some time. In async - the system uses a copy of the old stats which are used while the update takes place so the queries can still run - which means you may get a suboptimal query plan, but the query isn't blocked in the meantime.

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

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