none
update stats with full scan on database RRS feed

  • Question

  • Hi All,

    Is it possible to take update statics with full scan on database level instead of specifying table or index name.

    If it is possible please provide me the script.

    Thanks

    Krish735

    Wednesday, May 23, 2012 1:31 AM

Answers

  • Some people suggested sp_updatestats, but sp_updatetats has no guaranteed way to result in a FULLSCAN for all tables.

    But you can do:

    DECLARE @sql nvarchar(MAX);
    SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
                          quotename(s.name) + '.' + quotename(o.name) +
                          ' WITH FULLSCAN; ' AS [text()]
                   FROM   sys.objects o
                   JOIN   sys.schemas s ON o.schema_id = s.schema_id
                   WHERE  o.type = 'U'
                   FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
    PRINT @sql
    EXEC (@sql)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 23, 2012 12:13 PM
  • use the below query

    EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'

    Best Regards,

    Sandip Pani (MCTS, MCITP)

    SQLCommitted


    Sandip Pani http://SQLCommitted.com

    • Proposed as answer by Sandip Pani Wednesday, May 30, 2012 8:22 AM
    • Marked as answer by Stephanie Lv Monday, June 4, 2012 8:29 AM
    Wednesday, May 30, 2012 8:21 AM
  • Yes it is, I am using the below script

    DECLARE @tablename varchar(80),@shemaname varchar(80)
    DECLARE @SQL AS NVARCHAR(200)
    DECLARE TblName_cursor CURSOR FOR
    SELECT t.name,s.name FROM sys.tables t join sys.schemas s
    on s.schema_id=t.schema_id


    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor
    INTO @tablename,@shemaname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

    EXEC sp_executesql @statement = @SQL

       FETCH NEXT FROM TblName_cursor
       INTO @tablename,@shemaname
    END

    CLOSE TblName_cursor
    DEALLOCATE TblName_cursor


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Stephanie Lv Monday, June 4, 2012 8:29 AM
    Wednesday, May 30, 2012 8:25 AM
    Answerer

All replies

  • The BOL on the Update Statistics says to use sp_updatestats

    http://msdn.microsoft.com/en-us/library/ms187348.aspx

    execute sp_updatestats


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, May 23, 2012 2:36 AM
    Moderator
  • Just like Naomi suggested use sp_updatestats but use the resample argument:

    so: exec sp_updatestats 'resample'

    Wednesday, May 23, 2012 2:44 AM
  • See this

    http://msdn.microsoft.com/en-us/library/ms187348.aspx


    Many Thanks & Best Regards, Hua Min

    Wednesday, May 23, 2012 2:46 AM
  • Thanks for your responses.

    Please provide me a script to update statistics on all the tables and index  in a database with full scan.

    Thanks

    Krish

    Wednesday, May 23, 2012 3:41 AM
  • Thanks for your responses.

    Please provide me a script to update statistics on all the tables and index  in a database with full scan.

    Thanks

    Krish

    USE AdventureWorks2012;
    GO
    UPDATE STATISTICS Production.Product(Products)
        WITH FULLSCAN, NORECOMPUTE;
    GO


    Many Thanks & Best Regards, Hua Min

    Wednesday, May 23, 2012 3:44 AM
  • the example updates statistics only on a single table.

    I want update statistics on all the tables and indexes in a database

    Wednesday, May 23, 2012 3:46 AM
  • Hi Krish, as I said you can use the sp_updatestats to update stats for all tables in a database, but in order to do the full scan you need to include the resample argument. so to to update statistics on all the tables and index  in a database with full scan, use this:

    exec sp_updatestats 'resample'

    Wednesday, May 23, 2012 3:47 AM
  • the example updates statistics only on a single table.

    I want update statistics on all the tables and indexes in a database

    Syntax

              
    
    sp_updatestats [ [ @resample = ] 'resample']
            
    

    0 (success) or 1 (failure)

    [ @resample =] 'resample'

    Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement. If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling. resample is varchar(8) with a default value of NO.



    Many Thanks & Best Regards, Hua Min

    Wednesday, May 23, 2012 4:00 AM
  • Some people suggested sp_updatestats, but sp_updatetats has no guaranteed way to result in a FULLSCAN for all tables.

    But you can do:

    DECLARE @sql nvarchar(MAX);
    SELECT @sql = (SELECT 'UPDATE STATISTICS ' +
                          quotename(s.name) + '.' + quotename(o.name) +
                          ' WITH FULLSCAN; ' AS [text()]
                   FROM   sys.objects o
                   JOIN   sys.schemas s ON o.schema_id = s.schema_id
                   WHERE  o.type = 'U'
                   FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
    PRINT @sql
    EXEC (@sql)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 23, 2012 12:13 PM
  • use the below query

    EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'

    Best Regards,

    Sandip Pani (MCTS, MCITP)

    SQLCommitted


    Sandip Pani http://SQLCommitted.com

    • Proposed as answer by Sandip Pani Wednesday, May 30, 2012 8:22 AM
    • Marked as answer by Stephanie Lv Monday, June 4, 2012 8:29 AM
    Wednesday, May 30, 2012 8:21 AM
  • Yes it is, I am using the below script

    DECLARE @tablename varchar(80),@shemaname varchar(80)
    DECLARE @SQL AS NVARCHAR(200)
    DECLARE TblName_cursor CURSOR FOR
    SELECT t.name,s.name FROM sys.tables t join sys.schemas s
    on s.schema_id=t.schema_id


    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor
    INTO @tablename,@shemaname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

    EXEC sp_executesql @statement = @SQL

       FETCH NEXT FROM TblName_cursor
       INTO @tablename,@shemaname
    END

    CLOSE TblName_cursor
    DEALLOCATE TblName_cursor


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Marked as answer by Stephanie Lv Monday, June 4, 2012 8:29 AM
    Wednesday, May 30, 2012 8:25 AM
    Answerer