都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

あるプロジェクト向けに統計を更新するストアドをのせてみる (メモです。)

 今日も移動の短い時間でブログを書きたいと思います。JAL SKY Wi-Fi 便利ですね。
ryuchan.hatenablog.com
ryuchan.hatenablog.com

USE [データベース名]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_updatestatistics_per_table]
    @param_schema_name NVARCHAR(256),
    @param_table_name  NVARCHAR(256),
    @sample_percent    NVARCHAR(3)
AS

    DECLARE @schema_name     NVARCHAR(256)
    DECLARE @table_name      NVARCHAR(256)
    DECLARE @statistics_name NVARCHAR(256)
    DECLARE @query           NVARCHAR(4000)

    DECLARE statistics_list CURSOR FOR
    SELECT 
        sch.name,
        t.name,
        s.name
    FROM 
        sys.stats s
        JOIN sys.tables t ON 
        s.object_id = t.object_id
        JOIN sys.schemas sch ON 
        t.schema_id = sch.schema_id
    WHERE 
        sch.name = @param_schema_name AND 
        t.name = @param_table_name AND
        s.auto_created = 0

    OPEN statistics_list 
    FETCH NEXT FROM statistics_list INTO 
        @schema_name,
        @table_name,
        @statistics_name

    WHILE(@@FETCH_STATUS = 0)
        BEGIN
            SET @query =
            N'UPDATE STATISTICS ' + 
            @schema_name + '.' + @table_name + ' ' + @statistics_name + 
            WITH SAMPLE ' + @sample_percent + ' PERCENT'

            BEGIN TRY
                EXEC(@query);
            END TRY
            BEGIN CATCH
                PRINT N'ERROR_NUMBER:' + CAST(ERROR_NUMBER() AS NVARCHAR) +
                      N',' + 
                      N'ERROR_SEVERITY:' + CAST(ERROR_SEVERITY() AS NVARCHAR) +
                      N',' +
                      N'ERROR_STATE:' + CAST(ERROR_STATE() AS NVARCHAR) +
                      N',' +
                      N'ERROR_MESSAGE:' + CAST(ERROR_MESSAGE() AS NVARCHAR) +
                      N','
	        RETURN -1
            END CATCH

	    PRINT @query

            FETCH NEXT FROM statistics_list INTO 
                @schema_name,
       	        @table_name,
                @statistics_name
        END
    CLOSE statistics_list
    DEALLOCATE statistics_list
    RETURN 0
GO

 使い方はこんな感じです。

USE [AdventureWorks2016CTP3]
DECLARE @param_schema_name NVARCHAR(256)
DECLARE @param_table_name  NVARCHAR(256)
DECLARE @sample_percent    NVARCHAR(3)

SET @param_schema_name = N'Production'
SET @param_table_name  = N'ProductInventory'
SET @sample_percent    = N'100'
exec dbo.sp_updatestatistics_per_table @param_schema_name,
                                       @param_table_name,
                                       @sample_percent

 こんなクエリかけば、いちいちテーブル単位に上記のコードを書く必要もありません。テーブル特性に応じてサンプリングを変更すればよいかと思います。

SELECT
    'SET @param_schema_name = N''' + sch.name + ''';' + 
    'SET @param_table_name = N''' + t.name + ''';' + 
    'SET @sample_percent = N''100''' + ';' + CHAR(10) +  
    'exec dbo.sp_updatestatistics_per_table @param_schema_name,@param_table_name,@sample_percent'
FROM 
    sys.stats s
    JOIN sys.tables t ON 
    s.object_id = t.object_id
    JOIN sys.schemas sch ON 
    t.schema_id = sch.schema_id
WHERE
    s.auto_created = 0 AND
    t.is_ms_shipped = 0
ORDER BY
    sch.name,
    t.name,
    s.name

 列統計は下記のように作成したらいいかと思います。

USE [AdventureWorks2016CTP3]
DECLARE @param_schema_name     NVARCHAR(256)
DECLARE @param_table_name      NVARCHAR(256)
DECLARE @param_statistics_name NVARCHAR(256)

DECLARE @schema_name           NVARCHAR(256)
DECLARE @table_name            NVARCHAR(256)
DECLARE @column_name           NVARCHAR(256)

DECLARE @query                 NVARCHAR(4000)

--uniqueidentifier
--date
--time
--datetime2
--tinyint
--smallint
--int
--smalldatetime
--real
--datetime
--float
--decimal
--numeric
--bigint
--char
--timestamp
--nchar
DECLARE table_column_list CURSOR FOR
SELECT
    s.name,
    t.name,
    c.name
FROM
    sys.tables t
    JOIN sys.schemas s ON 
    t.schema_id = s.schema_id
    JOIN sys.columns c ON
    t.object_id = c.object_id
WHERE
    t.type = 'U' AND
    t.is_ms_shipped = 0 AND
    t.is_filetable = 0 AND
    c.system_type_id IN(36,40,41,42,48,52,56,58,59,61,62,106,108,127,175,189,239)
ORDER BY
    s.name,
    t.name,
    c.name

OPEN table_column_list
FETCH NEXT FROM table_column_list INTO 
    @schema_name,
    @table_name,
    @column_name

WHILE(@@FETCH_STATUS = 0)
    BEGIN
    IF NOT EXISTS ( SELECT
                        *  
                    FROM 
                        sys.stats ss
                        JOIN sys.tables t ON
                        ss.object_id = t.object_id
                        JOIN sys.schemas s ON
                        t.schema_id = s.schema_id
                    WHERE 
                        s.name = @schema_name AND 
                        t.name = @table_name AND 
                        ss.name = @column_name
                   )
        BEGIN
            SET @query =
                'CREATE STATISTICS ' + 
                @column_name + ' ON ' +  
                @schema_name + '.' + 
                @table_name + '(' +
                @column_name + ') WITH SAMPLE 0 PERCENT';
	        EXEC (@query);
        END
        FETCH NEXT FROM table_column_list INTO 
            @schema_name,
            @table_name,
            @column_name
    END

 あとは、トレースとって Missing Column Statistics が発生していないか監視しておけばいいのではないでしょうか?

C# 6.0 のお勉強をしなければ...

C# 6 実践的プログラミング[入門]講座

C# 6 実践的プログラミング[入門]講座

Professional C# 6 and .NET Core 1.0

Professional C# 6 and .NET Core 1.0

Programming C# 6.0: Create Windows Desktop and Web Applications

Programming C# 6.0: Create Windows Desktop and Web Applications