Post reply

Update stats

  • December 28, 2007 at 7:37 am

    #121521

    Im about to move all database from SQL 2000 to SQL 2005

    Then need to update the following:

    1. Update Statistics

    2. Update Indexes

    3. DBCC UPDATEUSAGE

    4. Change to 90 Mode

    Im found script

    sp_MSForEachTable "DBCC DBREINDEX('?')"

    The below command is for updating the statistics with full scan.

    We also need to run this command in the context of each database.

    sp_MSForEachTable "UPDATE STATISTICS ? with fullscan"

    What does the ? mark mean

    If i have table with 90 fill factor and 80 fill factor will it use this value.

    Which order should i do this in from my 1, 2, 3

    Thanks

    Do you do the index rebuild then do the update stats.

  • December 28, 2007 at 8:06 am

    #763280

    If you rebuild the index, you don't need to update the statistics. It's done as part of the index rebuild.

    The ? is a place holder for the table name.

    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

  • December 28, 2007 at 8:36 am

    #763296

    I was trying to use

    USE database_name

    go

    DECLARE tables_cursor CURSOR FOR

    SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1

    OPEN tables_cursor

    DECLARE @tablename SYSNAME

    FETCH NEXT FROM tables_cursor INTO @tablename

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    --select Getdate()

    PRINT '----Updating Statistics for table ' + @tablename + ' '

    EXEC ('UPDATE STATISTICS ['+ @tablename + '] WITH FULLSCAN')

    FETCH NEXT FROM tables_cursor INTO @tablename

    END

    PRINT '----------------All Statistics are Updated----------------'

    CLOSE tables_cursor

    DEALLOCATE tables_cursor

    GO

    on adventureworks but it does not work because of schema name - anyone got one that i could try so i can see at what stage the update statistcs has got to.

  • December 28, 2007 at 8:40 am

    #763298

    EXEC sp_MSForEachTable 'UPDATE STATISTICS ? with fullscan; print ''?'' '

    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

  • December 28, 2007 at 10:42 am

    #763342

    So for the moving of databases from SQL 2000 to SQL 2005

    Just do the

    SP_MSFOREACHTABLE 'Update Statistics? WITH FULLSCAN'

    No need to do the rebuild of indexes.

    and then do the

    DBCC UPDATEUSAGE ('DBNAME')

    Once on SQL 2005

    Do you guys do this each night.

    I usually do the Rebuild indexes twice week.

    Then UPDATEUSAGE when i remember.

    Be very interested to hear your comments

  • December 28, 2007 at 1:52 pm

    #763425

    Yup. The indexes won't get fragmented during an upgrade. The stats need updating because SQL 2005 keeps more detailed stats than SQL 2000 did. The 2005 optimiser can use the 2000 stats, but not efficiently.

    As for index rebuilds, personally I have a job that runs once a week, checks for fragmentation levels and rebuilds any index that's more than 15% fragmented. You shouldn't need to rebuild every day. Depends on how fast the indexes get fragmented though.

    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

  • December 29, 2007 at 7:10 am

    #763504

    Thanks I will be starting next week or a dev SQL 2005 then March for production.

  • December 29, 2007 at 8:12 am

    #763508

    I was looking at the statistics on a table.

    Created one table

    DECLARE @COUNT INT

    SET @COUNT = 1

    WHILE @COUNT <= 1000

    begin

    insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_NAME)

    VALUES (@COUNT, '12345678901234567890')

    SET @COUNT = @COUNT + 1

    END

    Table has just customer_id and customer_name with clustered index on customer_id

    I added the records check statistics nothing updated the stats

    Looked at the statistics and saw they were updated.

    Then did another count of 1001 to 2000

    The auto stats are not getting updated when i view statistics

    I set the auto stats sync on too still not updated.

    If i do select * from customer where customer_id = 2000 then look at stats it get updated.

    Does the auto stats only update when you do a query against the table

    I was under the impression it do it after i insert data, delete

    I see the rowmodctrin sysindexes has the 1000 in here.

    Thanks

  • December 29, 2007 at 1:34 pm

    #763526

    Stats updates get triggered on a read. The updates just set the row mod. When a query is run against a table, the rowmodcnt is checked and, if necessary a stats update is triggered.

    The threshold for a stats update is somewhere around 20% of the total rows.

    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

  • December 30, 2007 at 7:03 am

    #763549

    Thanks for clarifying

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

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