RMJCS Logo
 

sp_updatestats does not update indexed view stats

 
 

If you are using indexed views and use sp_updatestats (see BOL entry) to keep your stats fresh then you should be aware that sp_updatestats does not do anything for stats on indexed views. Stats on indexed views are maintained through the normal auto-update process but only with the normal data change thresholds which in many circumstances are too conservative.

The reason why indexed view stats are not updated is that the WHERE clause in the cursor definition restricts objects to type 'U' and 'IT' (user defined table and internal table respectively) in SQL 2005 and 'U' only in SQL 2000. Interestingly, the source for sp_updatestats in SQL 2000 includes a comment suggesting that indexed view stats will be processed but then fails to do so. This hasn't changed in the Feb CTP of SQL Server 2008.

The script below can be used to update indexed view stats, either as a one-off or as a scheduled job.

 
     
 

Script to update indexed view stats

 
 
/*
* Rhys Jones, 7th Feb 2008
*
* Update stats in indexed views because indexed view stats are not updated by sp_updatestats.
* Only does an update if rowmodctr is non-zero.
*
* No error handling, doesn't deal with disabled clustered indexes.
* Doesn't respect existing sample rate.
* sys.sysindexes.rowmodctr is not completely reliable in SQL Server 2005.
*
*/

DECLARE @view_name NVARCHAR(1024)
DECLARE @sql NVARCHAR(MAX)

PRINT N'Updating stats for indexed views in ' + DB_NAME() + N' . . .'

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
   SELECT
      QUOTENAME(ss.name, N'[') + N'.' + QUOTENAME(so.name, N'[') AS view_name
      --MAX(ssi.rowmodctr) AS max_rowmodctr,
      --MAX(DATEDIFF(hh, STATS_DATE(so.object_id, si.index_id), GETDATE())) AS max_age_hours
   FROM
      sys.objects so
         INNER JOIN sys.schemas ss ON ss.schema_id = so.schema_id
         INNER JOIN sys.indexes si ON si.object_id = so.object_id
         INNER JOIN sys.sysindexes ssi ON ssi.id = si.object_id
                                      AND ssi.indid = si.index_id
   WHERE
      so.type = 'V'
   GROUP BY
      QUOTENAME(ss.name, N'[') + N'.' + QUOTENAME(so.name, N'[')
   HAVING
      MAX(ssi.rowmodctr) > 0

OPEN cur

FETCH NEXT FROM cur INTO @view_name

WHILE (@@fetch_status = 0)
BEGIN
   PRINT N'   Updating stats for view ' + @view_name
   SET @sql = N'UPDATE STATISTICS ' + @view_name
   EXEC (@sql)
   FETCH NEXT FROM cur INTO @view_name
END

CLOSE cur
DEALLOCATE cur

PRINT N'Finished updating stats for indexed views in ' + DB_NAME() + N'.'
GO
 
     
Copyright 2007-2015 RMJCS Ltd