Smart UPDATE STATS Script

Selam ,

Aşağıda hazırşladığım script ile tüm istatistik süreçlerinizi istediğiniz şekilde gerçekleştirip loglamasını yapabilirisniz.

Scriptimizi incelediğimizde ilk etapta demo veritabanımızı oluşturuyoruz. Ardından bu veritabanı içerisinde Log lama yapacağımız tablomuzu CREATE ediyoruz. Ana Sistem Log tablomuzu da Create edip akıllı istatistik güncelleme procedure ümüzü oluşturuyoruz.

Akıllı prosedürümüzün EXEC yöntemleri ;

I :

EXEC Opt_ReStatistics_Operations  @ExistTime  ='07:00', @Full =0

Prosedürümüz iki adet değer almakta. Birinci değer @ExistTime bu değer prosedürün maksimum çalışacağı saatini gireceğiniz bölümdür ve o saate geldiğinde süreç kendini bitirir.

Diğer bir değer ise @Full değişkenidir. Bu değişkende iki adet parametre alır ;

0 ise sistemde ki tüm istatistikleri günceller,

1 ise sadece Index lere ait istatistikleri günceller . Yani _WA ile başlayan ındexler güncellenmez.

Yukarıda ki birinci örneğimizde sistemde ki tüm istatistikler güncellenecek ve saat 07.00 ‘ye kadar süreç devam ediyor ise tam 07:00 de süreç kesilecek.

II:

EXEC Opt_ReStatistics_Operations  @ExistTime  ='07:00', @Full =1

Bu örneğimizde de diğer örneğimiz de olduğu gibi 07:00 de tüm süreçlerimiz kesilecek ama burada diğerine fark ile sadece ındex lere ait istatistikler güncellenecek.

 

-- UPDATE STATISTICS AUTO SCRIPT : 12.20.1016 
-- Created by : Yusuf KAHVECI

-- New Database

CREATE DATABASE [Stats_SQLTR]
GO


USE [Stats_SQLTR]
GO

-- Statistics Full Log Table
CREATE  TABLE [dbo].[Statistics_MaintenanceLog](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[DBName] [VARCHAR](150) NULL,
[TableName] [VARCHAR](150) NULL,
[Statistic] [VARCHAR](200) NULL,
[StatsLastUpdated] [DATETIME2](7) NULL,
[RowsInTable] [BIGINT] NULL,
[RowsSampled] [VARCHAR](300) NULL,
[RowModifications] [BIGINT] NULL,
[RunText] VARCHAR(4000),
[CreationDate] [DATETIME] NULL DEFAULT (GETDATE()),
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- Statistics Run Log Table
CREATE TABLE [Stats_SQLTR].[dbo].[Statictics_RunLog]
    (
      [Id] [INT] IDENTITY(1, 1)
                 NOT NULL ,
      [RunText] [VARCHAR](300) NULL ,
      [StartTime] [DATETIME] NULL ,
      [EndTime] [DATETIME] NULL ,
      PRIMARY KEY CLUSTERED ( [Id] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY];

GO

-- Statistices Update SP_
CREATE PROCEDURE Opt_ReStatistics_Operations
    @ExistTime TIME ,
    @Full BIT
AS
/*

EXEC Opt_ReStatistics_Operations  @ExistTime  ='16:05', @Full =0
@ExistTime : En geç çalisabilecegi saati gireceksiniz.
@Full : 0 ise Index lere ait Istatistikler güncellenecek 1 veya bos ise tüm istatistikler güncellenecek.
*/

    DECLARE @c INT ,
    [email protected] TIME = '15:37' ,
        @cs INT= 1 ,
        @exec NVARCHAR(4000) ,
        @error NVARCHAR(4000) ,
        @end_date DATETIME;

    CREATE  TABLE #UpdateStatistics_Maintenance
        (
          Id INT IDENTITY(1, 1)
                 PRIMARY KEY CLUSTERED ,
          [DBName] VARCHAR(150) ,
          [TableName] VARCHAR(150) ,
          [Statistic] VARCHAR(200) ,
          [StatsLastUpdated] DATETIME2 ,
          [RowsInTable] BIGINT ,
          [RowsSampled] VARCHAR(300) ,
          [RowModifications] BIGINT ,
          [RunText] VARCHAR(300)
        );

    IF @Full = 0
        BEGIN
            INSERT  INTO #UpdateStatistics_Maintenance
                    ( [DBName] ,
                      [TableName] ,
                      [Statistic] ,
                      [StatsLastUpdated] ,
                      [RowsInTable] ,
                      [RowsSampled] ,
                      [RowModifications] ,
                      [RunText]
                    )
                    SELECT  DB_NAME(DB_ID()) AS DBName ,
                            [so].[name] AS [TableName] ,
                            [ss].[name] AS [Statistic] ,
                            [sp].[last_updated] AS [StatsLastUpdated] ,
                            [sp].[rows] AS [RowsInTable] ,
                            [sp].[rows_sampled] AS [RowsSampled] ,
                            [sp].[modification_counter] AS [RowModifications] ,
                            'UPDATE STATISTICS ['
                            + CONVERT(VARCHAR(200), DB_NAME(DB_ID()))
                            + '].[dbo].[' + [so].[name] + '] [' + [ss].[name]
                            + '] WITH FULLSCAN' AS RunText
                    FROM    [sys].[stats] [ss]
                            JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
                            JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
                            OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
                                                              [ss].[stats_id]) sp
                    WHERE   [so].[type] = 'U'
                            AND [sp].[modification_counter] > 0
                            AND [ss].[name] NOT LIKE '_WA{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}'
                    ORDER BY [sp].[last_updated] DESC;

        END; 
    ELSE
        INSERT  INTO #UpdateStatistics_Maintenance
                ( [DBName] ,
                  [TableName] ,
                  [Statistic] ,
                  [StatsLastUpdated] ,
                  [RowsInTable] ,
                  [RowsSampled] ,
                  [RowModifications] ,
                  [RunText]
                )
                SELECT  DB_NAME(DB_ID()) AS DBName ,
                        [so].[name] AS [TableName] ,
                        [ss].[name] AS [Statistic] ,
                        [sp].[last_updated] AS [StatsLastUpdated] ,
                        [sp].[rows] AS [RowsInTable] ,
                        [sp].[rows_sampled] AS [RowsSampled] ,
                        [sp].[modification_counter] AS [RowModifications] ,
                        'UPDATE STATISTICS ['
                        + CONVERT(VARCHAR(200), DB_NAME(DB_ID()))
                        + '].[dbo].[' + [so].[name] + '] [' + [ss].[name]
                        + '] WITH FULLSCAN' AS RunText
                FROM    [sys].[stats] [ss]
                        JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
                        JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
                        OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
                                                              [ss].[stats_id]) sp
                WHERE   [so].[type] = 'U'
                        AND [sp].[modification_counter] > 0
                --AND [ss].[name] NOT LIKE '_WA{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}'
                ORDER BY [sp].[last_updated] DESC;

    INSERT  INTO Stats_SQLTR.dbo.Statistics_MaintenanceLog
            ( [DBName] ,
              [TableName] ,
              [Statistic] ,
              [StatsLastUpdated] ,
              [RowsInTable] ,
              [RowsSampled] ,
              [RowModifications] ,
              [RunText]
            )
            SELECT  [DBName] ,
                    [TableName] ,
                    [Statistic] ,
                    [StatsLastUpdated] ,
                    [RowsInTable] ,
                    [RowsSampled] ,
                    [RowModifications] ,
                    [RunText]
            FROM    #UpdateStatistics_Maintenance;

    SELECT  @c = MAX(Id)
    FROM    #UpdateStatistics_Maintenance;


    WHILE ( @c > 0
            AND @ExistTime > CAST(GETDATE() AS TIME)
          )
        AND @cs < @c
        BEGIN
            BEGIN TRY
                SELECT  @exec = RunText
                FROM    #UpdateStatistics_Maintenance
                WHERE   Id = @cs; 
                INSERT  INTO Stats_SQLTR.dbo.Statictics_RunLog
                        ( RunText, StartTime )
                VALUES  ( @exec, GETDATE() );
                PRINT @exec;
                EXEC sp_executesql @exec;
                UPDATE  Stats_SQLTR.dbo.Statictics_RunLog
                SET     EndTime = GETDATE()
                WHERE   Id = ( SELECT   MAX(Id)
                               FROM     Stats_SQLTR.dbo.Statictics_RunLog WITH ( NOLOCK )
                             );
                SET @cs = @cs + 1;
            END TRY
            BEGIN CATCH
                SET @error = ERROR_MESSAGE();
                SET @end_date = GETDATE();
                PRINT @error;
            END CATCH;

        END;
    DROP TABLE #UpdateStatistics_Maintenance;

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *