3

Hi I have been working on a script to update statistics of all tables on all my DBs. The idea is to parametrise it later on, but just as a quick fix, and not wanting to implement Ola's scripts today, follow the script below.

I have tested it on a few servers, but before I schedule it to run on a live server Sunday morning, would like to get some ideas and share it with you.

SET NOCOUNT ON

DECLARE @DBS TABLE (I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    DBNAME SYSNAME NOT NULL )

DECLARE @I INT
        ,@Z INT
        ,@SQL VARCHAR(1008) 
        ,@j INT
        ,@Y INT
        ,@MYDB SYSNAME

INSERT INTO @DBS
SELECT s.name 
FROM sys.databases s
INNER JOIN SYS.master_files F ON S.DATABASE_ID = F.database_id  AND F.data_space_id = 1
WHERE S.STATE = 0 -- online
  AND S.database_id > 3 -- exclude master, tempdb and model -- I left msdb 
  AND S.is_read_only = 0 -- read/write
  AND S.user_access = 0 -- multi_user
  AND S.NAME NOT IN ('TableBackups',
                     'Troubleshooting')
ORDER BY F.SIZE DESC


SELECT @Z = @@ROWCOUNT
SELECT @I = 1



WHILE @I <= @z BEGIN


    BEGIN TRY DROP TABLE #T END TRY BEGIN CATCH END CATCH
    CREATE TABLE #T( I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, MYSQL VARCHAR(1008) NOT NULL )

    SELECT @MYDB = QUOTENAME (S.DBNAME )
           ,@SQL = 'USE ' + QUOTENAME (S.DBNAME ) + ';' + CHAR(13) + ' 
                    ' + CHAR(13) +
                   'INSERT INTO #T(MYSQL)  SELECT ''UPDATE STATISTICS '' + QUOTENAME(NAME) FROM SYS.TABLES WHERE TYPE = ''U'' ' + CHAR(13)  

      FROM @DBS s
        WHERE s.I = @I 


    PRINT CAST (@SQL AS NTEXT)
       BEGIN TRY

            EXEC (@SQL)
            SELECT @Y = @@ROWCOUNT

       END TRY
       BEGIN CATCH
           SELECT @Y = 0
       END CATCH



    SELECT @J = 1

    WHILE @J <= @y BEGIN

       SELECT @SQL = 'USE ' + @MYDB + ';' + CHAR(13) + MYSQL 
       FROM #T 
       WHERE I= @J

       BEGIN TRY

            EXEC (@SQL)

       END TRY
       BEGIN CATCH
       END CATCH


       PRINT CAST (@SQL AS NTEXT)

       SELECT @J = @J + 1

    END 

    SELECT @I = @I +1

END /*WHILE*/
5

Right off the bat, your script completely ignores schema, so it will fail unless all of your production servers only use dbo explicitly as the namespace for all tables (and nobody has a default schema other than dbo). You're also using a deprecated data type (NTEXT) for seemingly no reason. Also I don't think the WHERE clause against sys.tables is necessary (I don't know how anything but a U could ever be in there). Not sure why you bother ordering by file size; the job isn't going to finish faster if you handle the biggest databases first (also the join could return multiple rows for a database if it has more than one file in data_space_id = 1, which means you would potentially process that database multiple times).

Finally, I have to wonder if it is really wise to blindly update stats on every table in every database across your entire instance, regardless of size... and why you don't bother checking for indexed views, which also require maintenance. You could save quite a bit of work if you add some logic to exclude any tables that don't need it.

My partial solution

This code will be slightly harder to maintain, doesn't have the TRY/CATCH wrappers yours does, and doesn't add any additional logic about which tables to actually pursue, but since I get a kick out of making automation tasks out of dynamic SQL (and really enjoy solutions that cause no horizontal or vertical scrollbars on this site), here you go. When you're happy with the output, comment the print and uncomment the exec.

DECLARE @sql NVARCHAR(MAX) = N'', @stats NVARCHAR(MAX) = N'';

SELECT @sql += N'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @stats;'
  FROM sys.databases 
  WHERE [state] = 0 AND user_access = 0; -- and your other filters

SET @stats = N'DECLARE @inner NVARCHAR(MAX) = N''''; 
  SELECT @inner += CHAR(10) + N''UPDATE STATISTICS '' 
    + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + '';'' 
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s 
    ON t.[schema_id] = s.[schema_id];
  PRINT CHAR(10) + DB_NAME() + CHAR(10) + @inner;
  --EXEC sys.sp_executesql @inner;'

EXEC [master].sys.sp_executesql @sql, N'@stats NVARCHAR(MAX)', @stats;
| improve this answer | |
  • 2
    Now that's some impressive dynamic SQL. – Kenneth Fisher Oct 31 '14 at 18:52
  • 1
    @Kenneth thanks. At my last job, I had multiple instances with 500+ databases that had identical schema ... so I quickly became very adept at working out automation logic, and that almost always involved at least two layers of dynamic SQL. There are some pretty neat tricks you can play. – Aaron Bertrand Oct 31 '14 at 18:54
  • 1
    I play with dynamic SQL frequently (for similar reasons) and find the challenge rather fun. I've never tried the EXEC sp_executeSQL inside the dynamic SQL though. I'll have to add that to my bag of tricks. – Kenneth Fisher Oct 31 '14 at 18:59
  • How your answer differs from sp_updatestats. Because updating statistics of table without sample options will also update the table statistics with default sampling. or am I missing anything here – Pரதீப் Aug 5 '16 at 10:10
  • @Prdp I wasn't trying to solve any sampling issues. I was trying to solve the OP's issue. – Aaron Bertrand Aug 5 '16 at 11:09
4

Unless you want to implement some really powerful filters in your logic, the following code will do the job just fine:

exec sp_MSforeachdb 'use [?]; if db_name() not in (''master'', ''model'', ''tempdb'', ''msdb'') exec sp_updatestats;';
go
| improve this answer | |
  • Care to explain the downvoting?.. – Roger Wolf Nov 2 '14 at 4:55
0

I did notice a couple of things.

  1. I highly recommend using more than one character variable & temp table names. It makes it harder to read and next year when you are reading this again (or someone else is) you won't remember what they mean. It's not that hard to use @DBCounter instead of @I for example.

  2. Your DB list query at the top will create duplicates (causing duplicate work) if there is more than one data filegroup. Also you won't be looking at the DB size, just the file size in your ordering. I would change the end of the query to this:

    GROUP BY s.name
    ORDER BY SUM(F.SIZE) DESC
    
  3. Is there a reason you aren't using sp_updatestats on each database rather than running UPDATE STATISTICS on each table? It also has the benefit of only updating the statistics on tables that need to have it run.

  4. Last thing and not a huge issue. Don't CAST to NTEXT. It's an old data type and will be removed in a future version of SQL Server. Use NVARCHAR(MAX) instead.

| improve this answer | |
  • FWIW Erin Stellato repeatedly pushes people away from sp_updatestats. See sqlperformance.com/2013/07/sql-statistics/statistics-updates, for example. – Aaron Bertrand Oct 31 '14 at 18:48
  • @AaronBertrand I wasn't aware of that. I'll keep in in mind in the future. Although to be fair her complaint (in that post) is that it updates tables that maybe shouldn't be because only a few rows (or even 1) have been modified. This solution is updating every table regardless, so I'm not sure it matters much. – Kenneth Fisher Oct 31 '14 at 18:58
  • Right, I tried to address that in my answer, too. It can be like whack-a-mole. – Aaron Bertrand Oct 31 '14 at 18:59
  • regarding #3 I was following the an article by Brent Ozar you can see here: brentozar.com/archive/2014/01/… – Marcello Miorelli Oct 31 '14 at 19:24
  • 1
    Actually I believe she mentioned sp_updatestats at the bottom. Her main problem was the FULLSCAN which isn't happening in either case. Watch out for #2 though, you never know when things are going to change. It's always best to code for what's possible not what is right now. Although I'll admit I don't always manage it myself. – Kenneth Fisher Oct 31 '14 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.