SQL Server Performance

updatestats

Discussion in 'General DBA Questions' started by Raulie, Mar 29, 2004.

  1. Raulie New Member

    Does anyone have any scripts to run update stats on all databases on a server?
  2. bradmcgehee New Member

    I don't have a script handy for this, but have you tried using the Maintenance Wizard? It can create a job to do exactly this.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. ChrisFretwell New Member

    or you can write a simple select against the sysobjects table to generate your own. Here is a sample


    DECLARE @strSPName VARCHAR(100)

    DECLARE CUR_SP_Name CURSOR FAST_FORWARD FOR
    SELECT [name] FROM sysobjects (nolock) WHERE xtype = 'u' AND status >= 0 ORDER BY [name]

    OPEN CUR_SP_Name FETCH NEXT FROM CUR_SP_Name INTO @strSPName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC update statistics @strSPName
    FETCH NEXT FROM CUR_SP_Name INTO @strSPName
    END

    CLOSE CUR_SP_Name
    DEALLOCATE CUR_SP_Name
    GO
  4. Raulie New Member

    Thanks,

    Is there a way to script out a maintenance plan?
  5. derrickleggett New Member

    You can set up a job to have this run in each database. Save the results of the job to a table and you will also have an audit log of things each night.

    CREATE PROCEDURE sp_system_admin_maintenance

    --Name:sp_system_admin_maintenance
    --Purpose: This stored Procedure(SP) will be used for running nightly maintenance on
    --all databases. It resides in the master database of each database server.
    --1)UPDATE STATISTICS on all tables.
    --2)UPDATE USAGE on the database.
    --3)DBCC CHECKDB on the database.
    --
    --Format:EXEC sp_system_admin_maintenance
    --
    --Example:EXEC sp_system_admin_maintenance
    --
    --Action:Author:Date:Comments:
    ---------------------- --/--/--------------------------------------------
    --CreatedDerrick Leggett11/11/2003 Initial Development
    --ModifiedDerrick Leggett02/17/2004Added shrinkdatabase logic into maintenance.
    --

    AS

    SET ARITHABORT ON

    --Record start time
    PRINT 'START TIME: ' + CAST(GETDATE() AS VARCHAR(255))

    --Declare needed variables.
    DECLARE
    @int_error_counter INT,
    @int_max INT,
    @int_counter INT,
    @txt_sql NVARCHAR(4000),
    @txt_name NVARCHAR(4000),
    @txt_owner NVARCHAR(255),
    @txt_db NVARCHAR(255)

    --Declare table variable to hold table or procedure names.
    DECLARE @tbl_names TABLE (
    int_id INT IDENTITY(1,1) PRIMARY KEY,
    txt_name VARCHAR(255),
    txt_owner VARCHAR(255))

    --Insert into table all user tables.
    INSERT @tbl_names(
    txt_name,
    txt_owner)

    SELECT
    so.name,
    su.name
    FROM
    sysobjects so
    INNER JOIN sysusers su ON so.uid = su.uid
    WHERE
    so.xtype = 'U'
    AND so.name NOT LIKE 'dt%'

    --Set up loop to run DBCC commands against all user tables.
    SELECT
    @int_max = (SELECT MAX(int_id) FROM @tbl_names),
    @int_counter = (SELECT MIN(int_id) FROM @tbl_names)

    WHILE @int_counter <= @int_max
    BEGIN

    SELECT @txt_name = (
    SELECT '[' + txt_owner + '].[' + txt_name + ']'
    FROM @tbl_names
    WHERE int_id = @int_counter)

    ----Reindex all user tables.
    --SELECT @txt_sql = 'DBCC DBREINDEX(''' + @txt_name + ''')'
    --
    --PRINT @txt_sql
    --EXEC ( @txt_sql )

    --Update the statistics on all user tables.
    SELECT @txt_sql = 'UPDATE STATISTICS ' + @txt_name

    PRINT @txt_sql
    EXEC ( @txt_sql )

    SELECT @int_counter = @int_counter + 1
    END

    SELECT @txt_db = DB_NAME()

    --Update the usage on the database.
    PRINT 'DBCC UPDATEUSAGE (' + @txt_db + ') WITH NO_INFOMSGS'
    DBCC UPDATEUSAGE (@txt_db) WITH NO_INFOMSGS

    --Update the usage on the database.
    PRINT 'DBCC CHECKDB (' + @txt_db + ') WITH NO_INFOMSGS'
    DBCC CHECKDB (@txt_db) WITH NO_INFOMSGS


    --Record end time.
    PRINT 'END TIME: ' + CAST(GETDATE() AS VARCHAR(255))

    GO


    Derrick Leggett

  6. Raulie New Member

    Thanks I will add these to my bag of tricks.
  7. Raulie New Member

    Chris I modified the code so that it will run on each database.

    DECLARE @strDBName VARCHAR(100)

    DECLARE CUR_SP_Name CURSOR FAST_FORWARD FOR
    SELECT [name] FROM sysdatabases (nolock) ORDER BY [name]

    OPEN CUR_SP_Name FETCH NEXT FROM CUR_SP_Name INTO @strDBName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXECUTE ('USE '[email protected]+' EXEC sp_updatestats')
    FETCH NEXT FROM CUR_SP_Name INTO @strDBName
    END
    CLOSE CUR_SP_Name
    DEALLOCATE CUR_SP_Name
    GO

Share This Page