Массовое обновление статистики

DECLARE @DateNow DATETIME

SELECT @DateNow = Dateadd(dd, 0, Datediff(dd, 0, Getdate()))

DECLARE @SQL NVARCHAR(max)

SELECT @SQL = (SELECT
              '     UPDATE STATISTICS ['
                      + Schema_name(o.[schema_id]) + '].[' + o.NAME
                      + '] [' + s.NAME + ']         WITH FULLSCAN' + CASE WHEN
              s.no_recompute = 1
                                    THEN
                      ', NORECOMPUTE' ELSE '' END + ';'
               FROM   (SELECT [object_id],
                              NAME,
                              stats_id,
                              no_recompute,
                              last_update = Stats_date([object_id], stats_id)
                       FROM   sys.stats WITH(nolock)
                       WHERE  auto_created = 0
                              AND is_temporary = 0) s
                      JOIN sys.objects o WITH(nolock)
                        ON s.[object_id] = o.[object_id]
                      JOIN (SELECT p.[object_id],
                                   p.index_id,
                                   total_pages = Sum(a.total_pages)
                            FROM   sys.partitions p WITH(nolock)
                                   JOIN sys.allocation_units a WITH(nolock)
                                     ON p.[partition_id] = a.container_id
                            GROUP  BY p.[object_id],
                                      p.index_id) p
                        ON o.[object_id] = p.[object_id]
                           AND p.index_id = s.stats_id
               WHERE  o.[type] IN ( 'U', 'V' )
                      AND o.is_ms_shipped = 0
                      AND ( last_update IS NULL
                            AND p.total_pages > 0
                             -- never updated and contains rows
                             OR last_update <= Dateadd(dd, CASE
                                                             WHEN
                                               p.total_pages > 4096
                                                           -- > 4 MB
                                                           THEN -2
                                                             -- updated 3 days ago
                                                             ELSE 0
                                                           END, @DateNow) )
               FOR xml path(''), type)

.value('.', 'NVARCHAR(MAX)')
PRINT @SQL

EXEC sys.Sp_executesql
  @SQL 

Комментарии закрыты