あるプロジェクト向けに統計を更新するストアドをのせてみる (メモです。)
今日も移動の短い時間でブログを書きたいと思います。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 のお勉強をしなければ...
- 作者: 川俣 晶
- 出版社/メーカー: 技術評論社
- 発売日: 2015/08/25
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (1件) を見る
Professional C# 6 and .NET Core 1.0
- 作者: Christian Nagel
- 出版社/メーカー: Wrox
- 発売日: 2016/04/01
- メディア: Kindle版
- この商品を含むブログを見る
Programming C# 6.0: Create Windows Desktop and Web Applications
- 作者: Ian Griffiths
- 出版社/メーカー: Oreilly & Associates Inc
- 発売日: 2016/09/25
- メディア: ペーパーバック
- この商品を含むブログを見る