SQLServer 2008: Actualizar estadísticas de tabla de forma dinámica en toda una base de datos

By il_masacratore on 3 Marzo, 2010 - 17:36
Plataforma de datos

Al igual que en Oracle existe una tabla donde se listan todas las tablas de la base de datos (dba_tables) y podemos usarla para realizar operaciones de mantenimiento de forma dinámica, en Sql Server podemos hacer lo mismo consultando la tabla [basededatos].dbo.sysobjects.

En el ejemplo inferior (como en otros que he colgado) actualizo las estadísticas de todas las tablas de una base de datos de Sql Server de forma dinámica consultando el diccionario de datos. Este se podría encapsular en un stored procedure o directamente ejecutarlo en un job del Agente de Sql Server para mantener actualizadas las estadísticas de todas las tablas de una base de datos de forma automática.

-- Declaración de variables
    DECLARE @dbName    sysname
    DECLARE @sample int
    DECLARE @SQL nvarchar(4000)
    DECLARE @ID int
    DECLARE @Tabla sysname
    DECLARE @RowCnt int

    --Filtro por base de datos y porcentaje para recalculo de estadísticas
    SET @dbName = 'AdventureWorks2008'
    SET @sample = 100

    --Tabla temporal
    CREATE TABLE ##Tablas
    (
     TableID INT IDENTITY(1, 1) NOT NULL,
     TableName SYSNAME NOT NULL
    )

    --Alimentamos la tabla con la lista de tablas
    SET @SQL = ''
    SET @SQL = @SQL + 'INSERT INTO ##Tablas (TableName) '
    SET @SQL = @SQL + 'SELECT [name] FROM ' + @dbName + '.dbo.sysobjects WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

    EXEC sp_executesql @statement = @SQL

    SELECT TOP 1 @ID = TableID, @Tabla = TableName
    FROM ##Tablas
    ORDER BY TableID

    SET @RowCnt = @@ROWCOUNT
   
    --Por cada tabla
    WHILE @RowCnt <> 0
    BEGIN

     SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @Tabla + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

     EXEC sp_executesql @statement = @SQL

     SELECT TOP 1 @ID = TableID, @Tabla = TableName
     FROM ##Tablas
     WHERE TableID > @ID
     ORDER BY TableID

     SET @RowCnt = @@ROWCOUNT

    END

--Eliminamos la tabla
DROP TABLE ##Tablas

 

SQL Server 2008
estadisticas
DBA

Subido por il_masacratore el 8 Marzo, 2010 - 11:49

La forma de proceder arriba indicada dará problemas para tablas que no esten contenidas en el esquema dbo. Por ello para solventar este problema primero debemos cambiar la forma de alimentar la tabla temporal para usar el siguiente bloque SQL:

use <base de datos a usar>

SET @SQL = 'INSERT INTO ##Tablas(TableName) '
    SET @SQL = @SQL + ' select ''['' + TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' from INFORMATION_SCHEMA.TABLES'
    EXEC sp_executesql @statement = @SQL

-- Luego solamente usamos el nombre de la tabla para actualizar estadísticas:
SET @SQL = 'UPDATE STATISTICS ' + @Tabla + ' WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'
     EXEC sp_executesql @statement = @SQL