読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL Server】OPTIMIZE FOR UNKNOWNを使用した場合の統計について

ストアドプロシージャのパラメータスニッフィングによるパフォーマンス低下対策としてRECOPILEやOPTIMIZE FOR UNKNOWNを使うことが多いかと思いますが、OPTIMIZE FOR UNKNOWNで使われる統計がどのように算出されているのか検証してみました。


パラメータスニッフィングについては以下をご参照ください。
http://blog.hatena.ne.jp/utiowa/memorandom-nishi.hatenablog.jp/entry/2016/06/15/013111


MSDNにはOPTIMIZE FOR UNKNOWNについて次のような説明がされています。

OPTIMIZE FOR UNKNOWN
クエリ オプティマイザーでクエリをコンパイルおよび最適化するときに、強制パラメーター化によって作成されたパラメーターも含め、すべてのローカル変数に対して初期値の代わりに統計データを使用することを指定します。
場合 OPTIMIZE FOR @variable_name = literal_constant OPTIMIZE FOR UNKNOWN、同じクエリ ヒントで使用されると、クエリ オプティマイザーを使用して、 literal_constant特定の値および"不明"の残りの変数の値に指定されています。 これらの値はクエリを最適化する過程でのみ使用され、クエリの実行時には使用されません。

クエリ ヒント (Transact-SQL)


まずは検証用にテーブルとデータ、インデックスを用意します。

USE Param_testDB
GO

/***********************************/
-- テストテーブル作成
/***********************************/

DROP TABLE IF EXISTS [dbo].[STATS_ROWTEST]
GO

CREATE TABLE [dbo].[STATS_ROWTEST](
	[col1] [int] IDENTITY(1,1) NOT NULL,
	[col2] [int] NULL,
	[col3] [int] NULL
PRIMARY KEY CLUSTERED 
(
	[col1] 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

/***********************************/
-- テストデータ作成
/***********************************/

SET NOCOUNT ON
DECLARE @i int = 1
WHILE @i <= 10000000
	BEGIN
		INSERT INTO [dbo].[STATS_ROWTEST]
		VALUES ((CONVERT(INT, RAND()*10)),(CONVERT(INT, RAND()*100)))
			SET @i += 1
	END
SET NOCOUNT OFF
GO

-- =========================
-- col3を偏りを発生させるため、一部の値で更新
-- =========================
UPDATE [dbo].[STATS_ROWTEST]
SET col3 = 50
WHERE col3 
	BETWEEN	0
	  AND 20


/***********************************/
-- index作成
/***********************************/

CREATE NONCLUSTERED INDEX [NonClusteredIndex_col3] ON [dbo].[STATS_ROWTEST]
(
	[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

続いてインデックスの統計情報を更新し、統計情報を確認します。

/***********************************/
-- 統計情報を更新
/***********************************/

UPDATE STATISTICS [dbo].[STATS_ROWTEST] [NonClusteredIndex_col3]
GO

/***********************************/
-- 統計情報が更新されていることを確認
/***********************************/

DBCC SHOW_STATISTICS ([STATS_ROWTEST], [NonClusteredIndex_col3]) WITH NO_INFOMSGS
GO

f:id:utiowa:20170528235659p:plain


通常のストアドプロシージャとOPTIMIZE FOR UNKNOWNを使用したストアドプロシージャを作成します。

/***********************************/
-- UNKNOWNなし
/***********************************/
CREATE PROCEDURE usp_select_test
	@Param1 int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT col3 FROM [dbo].[STATS_ROWTEST]
	WHERE col3 = @Param1
END
GO


/***********************************/
-- UNKNOWNあり
/***********************************/

CREATE PROCEDURE usp_select_test_unknown
	@Param1 int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT col3 FROM [dbo].[STATS_ROWTEST]
	WHERE col3 = @Param1
	OPTION (OPTIMIZE FOR UNKNOWN)
END
GO

OPTIMIZE FOR UNKNOWNを使用していない場合

まずはUNKNOWNが無い場合のクエリを実行した場合の予測行数は次のとおりとなります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

f:id:utiowa:20170528235746p:plain
f:id:utiowa:20170528235753p:plain
f:id:utiowa:20170528235735p:plain
f:id:utiowa:20170528235740p:plain

この場合、パラメータスニッフィングが有効となっており実行プラン作成時のパラメータで最適化されているため、初回実行時のcol3=50の予測行数でプランが作成されていることが分かります。

実行プランを削除してからパラメータを変更して実行プランを確認するとプラン作成時のパラメータに応じて予測行数が変わっていることがわかります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

f:id:utiowa:20170528235812p:plain
f:id:utiowa:20170528235828p:plain
f:id:utiowa:20170528235837p:plain
f:id:utiowa:20170528235846p:plain

OPTIMIZE FOR UNKNOWNを使用した場合

UNKNOWNが有る場合のクエリを実行した場合の予測行数は次のとおりとなります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

f:id:utiowa:20170528235904p:plain
f:id:utiowa:20170528235912p:plain
f:id:utiowa:20170528235920p:plain

先ほどのパラメータを使用しているにも関わらず、予測行数が異なっています。先ほど同様に実行プランを削除してパラメータを変更して実行してみます。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

f:id:utiowa:20170529000015p:plain
f:id:utiowa:20170529000028p:plain

UNKNOWNが無い場合とは異なり、初回実行時のパラメータに左右されることなく一定の予測行数となっていることがわかるかと思います。

ヒストグラムを確認

通常時の予測行数の算出は以前の記事に記載がありますので、詳しくはそちら(【SQL Server】統計情報のヒストグラムと実行プランの予測行数 - 小物SEのメモ帳)を見ていただければわかるかと思いますが、UNKNOWN時の場合は以下のように全てのユニークな値の分布(All density)から全体行(Rows)に対し何%含まれているか計算して予測行数としているようです。

予測行数 = All density(1 / distinct values) * Rows

/***********************************/
-- 統計情報を確認
/***********************************/

DBCC SHOW_STATISTICS ([STATS_ROWTEST], [NonClusteredIndex_col3]) WITH NO_INFOMSGS
GO

f:id:utiowa:20170529001419p:plain
先ほど確認した実行プランの予測行数と一致することがわかります。

よってOPTIMIZE FOR UNKNOWNを使用する場合、統計情報から各データのユニークな値の分布と全体行数を考慮して平均的な行数を予測した実行プランを作るといえるかと思います。
ただし、値毎の出現頻度までは考慮していないように見えます。今回でいうとcol3=50は件数が多いはずですが、col3のユニークな値と全体行数からしか予測行数を算出していないため。念のためヒストグラムを可視化しておくと以下の通りとなります。
f:id:utiowa:20170529000410p:plain


特定のパラメータの実行頻度が高い場合は、そのパラメータで最適になるようヒント句でパラメータを指定することもありかと思いますが、平均的な実行プランで処理させたい場合はOPTIMIZE FOR UNKNOWNを使う検討をするのもありかと思います。
#その分パラメータによっては最速なプランは作成されないためトレードオフではありますが。

【SQL Server】テーブルのデータをINSERT文付きでエクスポート

テーブル内のデータをINSERT文付きでエクスポートする手順です。
データ移行用のデータ抽出などで利用できるかと。

SSMSの左ペインのオブジェクトエクスプローラのデータベース名で右クリック、「タスク」を選択し、「スクリプト生成」を選びます。
f:id:utiowa:20170522011018p:plain

今回は特定のテーブルのデータのみ抽出したいため、「特定のデータベース オブジェクトの選択」を選び、エクスポートしたいテーブル名を選択。
f:id:utiowa:20170522011027p:plain

「詳細設定」を押下します。
f:id:utiowa:20170522011035p:plain

オプションの「全般」にある「スクリプトを作成するデータの種類」で「データのみ」を選択。
f:id:utiowa:20170522011046p:plain

「次へ」を選択し、次画面で「完了」を押下します。
f:id:utiowa:20170522011106p:plain

指定したフォルダにINSERT文付きのテーブルデータがエクスポートされます。
f:id:utiowa:20170522011113p:plain


スクリプトを作成するデータの種類」の際に「スキーマとデータ」を選択すれば、テーブル作成や制約付きでエクスポートすることができますし、「統計のスクリプトを作成」で「統計とヒストグラムスクリプトを作成します」を選択すれば現時点の統計情報も出力できるので別環境での検証の際にも使えそうですね。
(商用環境から抽出する場合などのデータの取り扱いには十分注意が必要ですが。)
f:id:utiowa:20170522011132p:plain

【SQL Server】よく忘れる構文備忘録

たまに使用する際に毎回調べて書いていたので、備忘録&サンプルとして。

ストアドの実行結果をINSERT

ストアドの結果セットが一つであれば、INSERT INTO Table_name SELECT…と同じ要領で記述できます。

INSERT INTO Table_name 
	EXEC sp_lock

他のテーブルのデータでテーブルを更新する

UPDATE FROMを使用して他のテーブルの値で更新する構文です。

-- 事前準備
CREATE TABLE Table_x (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

CREATE TABLE Table_y (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

INSERT INTO [dbo].[Table_x]
VALUES 
	 ('001'	,'a001'	,'x000001')
	,('002'	,'a002'	,'x000002')
	,('003'	,'a003'	,'x000003');

INSERT INTO [dbo].[Table_y]
VALUES 
	 ('001'	,'a001'	,'y000001')
	,('002'	,'a002'	,'y000002')
	,('003'	,'a003'	,'y000003');

準備したデータは以下のとおり。

[Table_x]

Column1 Column2 Column3
001 a001 x000001
002 a002 x000002
003 a003 x000003

[Table_y]

Column1 Column2 Column3
001 a001 y000001
002 a002 y000002
003 a003 y000003


Table_xのColumn1が001であるものについて、Table_xのColumn3をTable_yのColumn3の値で更新してみます。

UPDATE Table_x
SET [Table_x].[Column3] = [Table_y].[Column3]
FROM  [Table_y]
WHERE [Table_x].[Column2] = [Table_x].[Column2]
  AND [Table_x].[Column1] = '001'

f:id:utiowa:20170515030647p:plain

INNER JOINを組み合わせることもできます。

UPDATE Table_x
SET [Table_x].[Column3] = [Table_y].[Column3]
FROM       [Table_x] 
INNER JOIN [Table_y]
	ON [Table_x].[Column2] = [Table_x].[Column2]
WHERE [Table_x].[Column1] = '001'

UPDATE対象のデータの処理前、処理後を出力する

SET句の後ろにOUTPUT句で記述します。

UPDATE [dbo].[Table_x]
SET Column2 = 'b001'
OUTPUT   deleted.*
	,inserted.*
WHERE Column3 IN ('x000001','x000003');

一時テーブルが存在するならば、削除する

一時テーブルにデータを作成⇒データ格納をしながら繰り返し試行錯誤する際に便利です。

IF OBJECT_ID(N'tempdb..#temp_TBL', N'U') IS NOT NULL
    DROP TABLE #temp_TBL;

一時テーブルが存在するならば、削除する【SQL Server2016】

簡潔に書くことができます。

DROP TABLE IF EXISTS tempdb..#temp_TBL;

テーブルが存在するならば、削除する

IF EXISTS (
	SELECT *
	FROM sys.objects
	WHERE object_id = OBJECT_ID(N'Table_name')
	)
  DROP TABLE Table_name;

テーブルが存在するならば、削除する【SQL Server2016】

とてもシンプル。見やすく書きやすい。

DROP TABLE IF EXISTS Table_name;

カラムが存在するならば、削除する

IF EXISTS (
	SELECT *
	FROM sys.columns
	WHERE NAME = 'Column_name'
	 AND object_id = OBJECT_ID('[dbo].[Table_name]')
	)
	ALTER TABLE [dbo].[Table_name] DROP COLUMN [Column_name] 
GO

カラムが存在するならば、削除する【SQL Server2016】

こちらもシンプルに記述できて便利です。

ALTER TABLE [dbo].[Table_name] DROP COLUMN IF EXISTS [Column_name] 
GO

DROP xxx IF EXISTSは、PROCEDUREやFUNCTION等にも使えるため、様々なDDL文に使用できます。

カラムが存在しないならば、追加する

単に削除の反対ですが。

IF NOT EXISTS (
	SELECT *
	FROM sys.columns
	WHERE NAME = 'Column_name'
	   AND object_id = OBJECT_ID('[dbo].[Table_name]')
	)
  ALTER TABLE [dbo].[Table_name] ADD [Column_name] VARCHAR(50)
GO

新規ならINSERT、存在するならUPDATE処理をする

データ更新時に行が存在すればUPDATE、新規であればINSERTをする場合はMRGER構文を使います。(ただし、SQL Server2008以降)
さらに処理した行の結果をOUTPUT句にて出力します。

-- 事前準備
DROP TABLE IF EXISTS Table_x;
DROP TABLE IF EXISTS Table_y;

CREATE TABLE Table_x (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

CREATE TABLE Table_y (
	 Column1 VARCHAR(50)
	,Column2 VARCHAR(50)
	,Column3 VARCHAR(50)
	)

INSERT INTO [dbo].[Table_x]
VALUES 
	 ('001'	,'a001'	,'x000001')
	,('002'	,'a002'	,'x000002')
	,('003'	,'a003'	,'x000003');

INSERT INTO [dbo].[Table_y]
VALUES 
	 ('001'	,'a001'	,'y000001')
	,('003'	,'a003'	,'y000003')
	,('004'	,'a001'	,'y000004')
	,('005'	,'a002'	,'y000002')
	,('006'	,'a003'	,'y000006');

Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加してみます。

MERGE Table_x AS [Target]
USING (SELECT Column1,Column2,Column3 FROM Table_y) AS [Source]
  ON [Target].[Column1] = [Source].[Column1]
  WHEN MATCHED THEN 
      UPDATE SET [Target].[Column3] = [Source].[Column3]
  WHEN NOT MATCHED BY Target THEN
      INSERT (
         [Column1]
        ,[Column2]
        ,[Column3]
        ) 
      VALUES (
           [Source].[Column1]
          ,[Source].[Column2]
          ,[Source].[Column3]
          )
  OUTPUT deleted.*, inserted.*, $action;
  

f:id:utiowa:20170515030310p:plain

Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加し、条件にあてはまらないTable_yの行を削除する場合。

MERGE Table_x AS [Target]
USING (SELECT Column1,Column2,Column3 FROM Table_y) AS [Source]
  ON [Target].[Column1] = [Source].[Column1]
  WHEN MATCHED THEN 
      UPDATE SET [Target].[Column3] = [Source].[Column3]
  WHEN NOT MATCHED BY Target THEN
      INSERT (
         [Column1]
        ,[Column2]
        ,[Column3]
        ) 
      VALUES (
           [Source].[Column1]
          ,[Source].[Column2]
          ,[Source].[Column3]
          )
  WHEN NOT MATCHED BY Source THEN
      DELETE 
  OUTPUT deleted.*, inserted.*, $action;
  

[参考情報]
MERGE (Transact-SQL)
OUTPUT 句 (Transact-SQL)

【SQL Server】並列処理時のパフォーマンスについて

前回は、並列処理の設定確認や変更方法を記しましたが、今回は並列実行されていることの確認や、その効果検証についてです。
memorandom-nishi.hatenablog.jp

並列処理時の確認

動的管理ビューのdm_exec_query_statsにSQL Server2016以降から並列度の次数(dop)やスレッド数が追加されています。

total_dop 並列処理の次数の合計
last_dop 最後に実行したときの並列処理の次数
min_dop 累積のうち最小の並列処理の次数
max_dop 累積のうち最大の並列処理の次数
total_used_threads 並列処理時のスレッド数の合計
last_used_threads 最後に実行したときの並列処理時のスレッド数
min_used_threads 累積のうち並列処理時の最小のスレッド数
max_used_threads 累積のうち並列処理時の最大のスレッド数

#dopとthreadの違いはMSのblogに記載があります。
https://blogs.msdn.microsoft.com/jpsql/2013/10/15/dop/


直近の実行での並列度や平均並列度は上記動的管理ビューを見ることでわかります。
例えば以下のようなSQLで確認することができます。

#以前作ったCPU、IO負荷の高いクエリの使いまわしですが。

SELECT TOP 100
        rank() over(order by total_dop/execution_count desc,sql_handle,statement_start_offset) AS [row_no]
,       db_name(st.dbid) AS [database_name]
,       creation_time
,       last_execution_time
,       (total_worker_time+0.0)/1000 AS [total_worker_time(ms)]
,       (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime(ms)]
,       (total_elapsed_time+0.0)/1000 AS [total_elapsed_time(ms)]
,       (total_elapsed_time+0.0)/(execution_count*1000) AS [AvgElapsedTime(ms)]
,       total_logical_reads AS [LogicalReads(page)]
,       total_logical_writes AS [logicalWrites(page)]
,       total_logical_reads+total_logical_writes AS [AggIO(page)]
,       (total_logical_reads+total_logical_writes)/(execution_count + 0.0) AS [AvgIO(page)]
,       execution_count
,       total_rows
,       max_dop
,       total_dop
,       total_dop/execution_count AS [AvgDOP]
,       st.text AS [batch_query_text]
,   CASE 
        WHEN sql_handle IS NULL
        THEN ' '
        ELSE ( SUBSTRING(st.text,(qs.statement_start_offset+2)/2,(CASE 
                                                                      WHEN qs.statement_end_offset = -1        
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX),st.text))*2      
                                                                      ELSE qs.statement_end_offset    
                                                                  END 
        - qs.statement_start_offset) /2  ) )
    END AS [statement_query_text] 
,       plan_generation_num
,       qp.query_plan
FROM sys.dm_exec_query_stats   AS [qs]
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st]
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp]
WHERE total_worker_time > 0 
ORDER BY total_dop/execution_count  DESC,sql_handle,statement_start_offset
OPTION (RECOMPILE)

グラフィカル表示の実行プランを確認する場合、次のアイコンがあれば並列実行される可能性があります。

アイコン 並列処理の物理操作 説明
f:id:utiowa:20170506001418p:plain Distribute Streams 1つのインプットに対しパラレル処理
f:id:utiowa:20170506001427p:plain Repartition Streams 複数のパラレル処理結果に対しパラレル処理
f:id:utiowa:20170506001433p:plain Gather Streams 複数パラレル処理を1つに統合


実際の実行プランを取得するとアイコンにカーソルを合わせると実行回数として並列度が確認できます。
f:id:utiowa:20170506001532p:plain

それぞれのスレッドでの処理行数やCPU時間や実行時間も見ることができます。
f:id:utiowa:20170506001601p:plain

今回は並列処理のGather Streamsなので、イメージ的には次のような動きかと。
f:id:utiowa:20170506001625p:plain

ただし、「実際の実行プラン」でなければこの情報は見ることができないため、拡張イベント等の仕込みが無い場合は後から見ることができません。その場合は上記で説明した動的管理ビューからの平均・最大並列数で確認するのが現実的でしょうか。


並列処理した場合のパフォーマンスコスト

並列処理した場合のクエリを比較して負荷の違いを見てみます。
試しにMaxdopを1~4にした場合の各5回の実行からの平均統計で比較すると次のような結果となります。

お試しクエリ

-- キャッシュをクリア
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


SET STATISTICS IO ON
SET STATISTICS TIME ON


SELECT TOP 10000
	A.[col1]
    ,	A.[col2]
    ,	A.[col3]
    ,	B.*
  FROM			[Param_testDB].[dbo].[Parallel_test] AS A
  INNER JOIN		[Param_testDB].[dbo].[Parallel_test] AS B
  ON A.col2 = B.col2
  ORDER BY NEWID(),4
  OPTION (MAXDOP 1)
--  OPTION (MAXDOP 2)
--  OPTION (MAXDOP 3)
--  OPTION (MAXDOP 4)

GO

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

f:id:utiowa:20170506001832p:plain

非並列時の読み取りページと実行プラン
f:id:utiowa:20170506002649p:plain
f:id:utiowa:20170506002351p:plain

並列時の読み取りページ実行プラン
f:id:utiowa:20170506002712p:plain
f:id:utiowa:20170506002339p:plain

上記結果から、並列度が高いとCPU負荷が高くなるかわりに実行時間が少し早くなることがわかるかと思います。
特にCPUTimeが実行時間(ElapsedTime)を超えている場合は、並列で処理している結果であると言えそうです。

また、わずかですが並列無しに比べ、読み取りページもわずかに増えておりメモリにも多少なりとも影響を与えていると言えるでしょう。

【SQL Server】並列処理MaxDOPの設定確認と変更

SQL Serverでの並列クエリ処理

複数のCPUでクエリを処理して並列処理される場合、実行時間を短縮することができます。
並列クエリの実行では、取得するデータセットを小さく分割して、分割されたデータセットをそれぞれのワーカー・スレッドが並列処理することでクエリ実行の実行時間を短縮することができます。

並列クエリとなる基準は以下の通りです。
1.-数のCPUが搭載されている環境か
2.十分な数のスレッドが使用できるか
3.並列プランを使用するに適しているクエリ、もしくはインデックスか
4.処理行が十分に多いか
5.分布統計が使用できるか

MSに詳しい説明があります。
並列処理の次数

設定値の確認と変更

インスタンス全体にかかる設定としてsp_configure max degree of parallelismオプションを使用して設定するか、特定のクエリの並列を最大並列度(MAXDOP)で指定することができます。
尚、デフォルトは0で、この場合論理プロセッサに対し最大限に使用することがある状態となります。

インスタンス全体にかかる設定の確認をクエリにて行う場合は次のように確認できます。

-- 拡張オプションを表示できるよう設定を変更
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

-- 拡張オプションを表示
EXEC sp_configure 
GO

f:id:utiowa:20170505170021p:plain

または、プロパティからも確認することができます。
左ペインのオブジェクトエクスプローラからサーバを右クリックし、「プロパティ」を選択。
f:id:utiowa:20170505165940p:plain

「ページの選択」で「詳細設定」を選択して、「並列処理」項目の「並列処理の最大限度」を確認。
f:id:utiowa:20170505170000p:plain


設定を変更する場合、次のように変更できます。

-- 並列度を1にする場合
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO


特定のクエリに対するMAXDOPを変える場合は、オプション句でMAXDOPを以下のように指定して実行します。
この場合、上記のsp_configureの設定値を超えた並列度を設定することができます。

SELECT TOP 50000 
   [col1]
    , [col2]
    , [col3]
  FROM [Param_testDB].[dbo].[Parallel_test]
  ORDER BY NEWID()
  OPTION (MAXDOP 2)


並列実行時は実行時間が早くなる分、CPUやメモリへの影響もありますので注意が必要ですが、MSとしてのMaxDOPは推奨値が8以下となっているようです。以下は参考になるかと思いますので、目を通しておくとよいでしょう。
[SQL Server2005以降の推奨値]
https://support.microsoft.com/ja-jp/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server


[その他参考]
64 個を超える CPU を搭載したコンピューター上で SQL Server を実行する場合のベスト プラクティス
最適なパフォーマンスを実現するための max degree of parallelism オプションの設定

【PowerShell】【SQL Server】SQLの結果を条件にイベントログに書き込む

完全に自分用の備忘録です。
SQL Serverに対しストアドを作ることなく、SQLの戻り値を使ってイベントログに書き込みをするスクリプトです。

イベントログへの書き込み

事前にイベントログにソースを登録したことがなければNew-Eventlogにて作成しておきます。
今回はTemp_alertとしておきます。

New-Eventlog -LogName Application -Source "Temp_alert"

イベントログへの書き込みはWrite-Eventlogにて行います。

Write-Eventlog -Logname Application -Source Temp_alert -EntryType Information -EventId 9999 -Message "test"

f:id:utiowa:20170428002208p:plain

イベントログに書き込みができていることを確認します。
f:id:utiowa:20170427010715p:plain

PowerShellからのSQLの実行

PowerShellからSQLを実行するので、Invoke-Sqlcmdを使用します。

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Username $User -Password $Password -InputFile $Input_count 

その他詳しいオプションは下記を参照してください。
https://msdn.microsoft.com/ja-jp/library/cc281720.aspx

今回は単純に数をカウントするだけのSQLと詳細情報を参照する以下クエリを用意しました。

Sqlquery_count.sql

SET NOCOUNT ON

SELECT 
       COUNT([JOB_ID]) AS [Count]
  FROM [dbo].[JOBS]

Sqlquery_detail.sql

SET NOCOUNT ON

SELECT 
      [JOB_ID]
      ,[JOB_TITLE]
      ,[MIN_SALARY]
      ,[MAX_SALARY]
  FROM [dbo].[JOBS]

PowerShellからSQLを実行してイベントログへ書き込み

SQLでのカウントが0の場合は問題ないことをログに書き込み、0でない場合エラーとしてイベントログに書き込むサンプルです。

以下のようなフォルダ構成で実行します。
f:id:utiowa:20170428002334p:plain


PowerShellは以下の通りです。


$Server = "YUUSUKE-VAIO\INS_NISHI2016"
$Database = "sales"
$User = "sa"
$Password = "system"

$Hostname = hostname
$Log_Level = "ERROR"

$Scriptpath = Split-Path $myInvocation.Mycommand.Path -Parent
$Scriptpath = Split-Path -Parent $Scriptpath
$Input_count = $Scriptpath + "\scripts\Sqlquery_count.sql"
$Input_detail = $Scriptpath +  "\scripts\Sqlquery_detail.sql"
$Output_detail = $Scriptpath + "\output\Sqlquery_detail.log"

# タイムスタンプはyyyy MMM dd HH:mm:ss形式
$us = New-Object system.globalization.cultureinfo("en-US")
$Date = (Get-date).ToString("yyyy MMM dd HH:mm:ss", $us)
$Message_header = "XXXXX"
$Message_body = "target_count is "

# 対象のカウント
$Resultset = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Username $User -Password $Password -InputFile $Input_count 

$Message = $Message_header + " " + $Date + " " + $Hostname + " " + $Log_Level + " " + $Message_body + [string]$Resultset['Count']

# 条件に応じてイベントログに出力
if ($Resultset['Count'] -eq 0 ){
  Write-Eventlog -Logname Application -Source Temp_alert -EntryType Information -EventId 9999 -Message $Message
}else{
  $Resultset = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Username $User -Password $Password -InputFile $Input_detail
  $Resultset | Out-File $Output_detail
  Foreach ($item in $Resultset){
    $items += ',' + $item['JOB_ID']
  }
$Message = $Message + $items
Write-Eventlog -Logname Application -Source Temp_alert -EntryType Warning -EventId 9999 -Message $Message
}

結果は以下のようにイベントログに書き込めていることがわかります。
f:id:utiowa:20170427011005p:plain

本番環境だと申請なしにストアドを作成するわけにもいかないこともありますのでストアドを使わない方法として一時しのぎに使おうかと。

【SQL Server】【Windows】セキュリティイベントログにログイン失敗のログを記録する

イベントログの「セキュリティ」にSQL Serverログインの失敗を記録する際の手順です。

以下参考のURLを実際に実行してみたものになります。
https://msdn.microsoft.com/ja-jp/library/cc645889.aspx


まず、auditpol.exeで成功・失敗の監査を有効にします。
ただし、上記URL記載のコマンドを実行してもエラーとなりますので、一部修正する必要があります。

auditpol.exe /set /subcategory:”Application Generated” /failure:enable /success:enable

f:id:utiowa:20170326001834p:plain


私の環境では、サブカテゴリ名が日本語になっており、コマンド内でも日本語にする必要がありました。

auditpol.exe /set /subcategory:”生成されたアプリケーション” /failure:enable /success:enable

f:id:utiowa:20170326001848p:plain


次に"セキュリティ監査の生成"権限をSQL Serverの起動ユーザに許可します。
「ファイル名を指定して実行」で"gpedit.msc"を入力します。
f:id:utiowa:20170326002046p:plain

ローカル セキュリティ ポリシー ツールで、 [セキュリティの設定]、 [ローカル ポリシー]の順に展開し、 [ユーザー権利の割り当て]をクリックして、右ペインに表示される[セキュリティ監査の生成]をダブルクリックします。
f:id:utiowa:20170326002240p:plain

[ローカル セキュリティの設定] タブの [ユーザーまたはグループの追加]をクリックします。
f:id:utiowa:20170326002306p:plain

追加するユーザアカウントとしてSQL Serverの起動ユーザを選択します。
f:id:utiowa:20170326002311p:plain

設定が完了したらSQL Serverに反映させるためにインスタンスを再起動します。


次にSQL Server側で監査を追加します。監査ログをイベントログのセキュリティに記録するようにします。
今回はSQL Serverインスタンス全体を監査対象とするサーバーレベルの監視を作成してみます。

オブジェクトエクスプローラインスタンス名配下のツリー直下にある「監査」を選択し、右クリック、「新しい監査」を選択します。
f:id:utiowa:20170326002521p:plain

「監査の出力先」をセキュリティログにします。
f:id:utiowa:20170326002533p:plain

尚、監査ログはイベントログのアプリケーション、もしくは別ファイルに記録することもできます。
f:id:utiowa:20170326002549p:plain

作成したら有効化しておきます。
f:id:utiowa:20170326002650p:plain

どのイベントを監査ログとして記録するかという仕様は、"サーバー監査の仕様"にて設定します。

オブジェクトエクスプローラインスタンス名配下のツリー直下にある「サーバー監査の仕様」を選択し、右クリック、「新しいサーバー監査の仕様」を選択します。
f:id:utiowa:20170326002932p:plain

ログイン失敗を監査するために、監査アクションの種類で「FAILED_LOGIN_GROUP」を選択します。f:id:utiowa:20170326003141p:plain

ここでは、他にも以下のように監査仕様を選べます。
f:id:utiowa:20170326003215p:plain

作成が完了したらこちらも有効にしておきます。


実際にログイン失敗が記録されているか確認をしてみます。
監査ログの表示は、SSMS上であれば作成した監査を右クリックして、「監査ログの表示」を選択します。
f:id:utiowa:20170326003457p:plain

ユーザIDの誤りによるログ
f:id:utiowa:20170326004122p:plain

パスワード誤りによるログ
f:id:utiowa:20170326004134p:plain


実際にイベントログを確認してみると次のように記録できていることがわかります。

ユーザIDの誤りによるログ
f:id:utiowa:20170326003645p:plain

パスワード誤りによるログ
f:id:utiowa:20170326003702p:plain


データベースレベルを監査対象とする監査仕様は別途こちらから設定できますので、個別DB毎の監査仕様はこちらで定義するとよいでしょう

f:id:utiowa:20170326002907p:plain