【SQL server】sp_who2について
SQL Serverの性能情報を取得するうえで、簡単にいろいろな情報を収集できるシステムストアドプロシージャであるsp_who2について、改めて内部を確認してみました。
※sp_who2はundocumentedなシステムストアドプロシージャであるため、変更されている恐れがあります。
まずは、内容を以下クエリで確認します。
sp_helptext 'sp_who2'
基本的にはsysprocesses_exから情報を取得しているようです。
このような名前のテーブルやビューは見つからないため、内部用のテーブルでしょうか。。。
同じようなストアドを作ることもできなさそうに見受けられるため、システムストアドプロシージャからのみ参照できるのでしょうか。。
似たようなテーブルとしてsysprocessesが存在し、こちらとほぼ同様な内容を取得していると思われます。
ちなみにsysprocessesに関しても、SQL server2000までの下位互換ビューのため、直接情報採取する場合は、以下を使用してください。
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_requests
※詳しくは以下を参照してください。
https://technet.microsoft.com/ja-jp/library/ms187997.aspx
また、環境によっては本番環境で自由に実行できるクエリに制限があり難しいかもしれませんが、
MSのtechnetにはさらに有益な情報を出力できるsp_who3も存在します。（MS標準で用意されているストアドとは異なるため、使用は自己責任で）
こちらはさらに、SQLのステートメントなども出力できるため、使用できる方は使ってみてもいいと思います。
https://gallery.technet.microsoft.com/SPWHO3-74fb1c35
話がそれてしまいましたが、sp_sho2の実行結果サンプルは以下となります。
どのような実行内容であったか詳細を知りたい場合は、上記の結果からSPIDを選び、
以下で検索します。
DBCC INPUTBUFFER(<SPID>)
以上のようなことはSSMSの利用状況モニターでも確認できます。
「プロセス」ペインを開きます。
対象を選択して、右クリック⇒詳細を選択します。
実行しているSQLが確認できます。また、強制終了を実行できます。
(コマンドで実行する場合は、Kill
以上のような内容は、パフォーマンス低下時の緊急時に確認が必要だったりするので、頭に入れておくと安心ですね。
ファイルグループへのテーブルの新規追加の手順
ファイルグループへのテーブルを新規で追加する手順の理解があいまいだったため、簡単に確認してみました。
ファイルグループを分けた場合、違うディスクに配置することで、IO負荷の分散が期待できます。
⓵DBに新規のファイルグループを追加します
-- ================================================ -- DBにファイルグループを新規追加 -- ================================================ ALTER DATABASE [SalesDB] ADD FILEGROUP [Customer_FG]
⓶DBにファイルを追加します
-- ================================================ -- ファイルグループにファイルを新規追加 -- ================================================ ALTER DATABASE [SalesDB] ADD FILE (NAME = N'SalesDB_Custmor' ,FILENAME = N'C:\temp\SalesDB_Custmor.ndf' ) TO FILEGROUP [Customer_FG]
⓷テーブル作成時にON FILEGROUPを指定して実行します
-- ================================================ -- ファイルグループにテーブルを新規追加 -- ================================================ USE [SalesDB] CREATE TABLE [CustomerTBL]( id INT , name NVARCHAR(20) ) ON [Customer_FG]
【SQL server】クエリ統計の履歴レポートについて
データコレクションの機能で、「クエリ統計の履歴」を実行し、
レポート表示させる場合の元スクリプトとテーブルについて
確認してみました。
内容に誤りがある場合は、ご指摘いただけると幸いです。
※変数の値はそれぞれの対象となるSQLによって異なります。
レポートに出力されている各項目におけるinputデータやモジュールから
把握することで、レポート内容をより正確に読み取ることを趣旨と
しています。
◆「クエリ統計の履歴」の初期表示時のスクリプト
exec snapshots.rpt_top_query_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016', -- インスタンス名
@end_time='2016-07-13 16:30:02.500', -- データコレクション収集最終時間
@time_window_size=240, -- レポートの期間（デフォルトは240分）
@order_by_criteria=N'CPU', -- レポートのTOP10のソート（デフォルトはCPUtime）
@database_name=NULL
◆「クエリ統計の履歴」のTOPxの特定のクエリを選択した場合のスクリプト
※レポート名は「クエリの詳細」
ここでは、対象SQLで使用されたの全ての実行プランの場合の以下値を合計して出力しているようです。
【出力項目】CPU、実行時間、物理読み取り、論理書き込み、実行回数
exec snapshots.rpt_query_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@end_time='2016-07-13 15:49:58',
@time_window_size=240,
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116
ここでは、対象SQLで使用されたそれぞれの実行プランの場合の以下値を合計してレポート期間で割った
1分当たりの各値を出力しているようです。
exec snapshots.rpt_query_plan_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@end_time='2016-07-13 15:49:58',
@time_window_size=240,
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116,
@order_by_criteria=N'CPU',
@plan_handle_str=NULL,
@plan_creation_time=NULL
◆「クエリの詳細」レポートにおけるSQLステートメント全文出力スクリプト
exec sp_executesql @stmt=N'DECLARE @sql_handle varbinary(64)
SET @sql_handle = snapshots.fn_hexstrtovarbin (@sql_handle_str)
SELECT TOP 1 sql.*
FROM core.snapshots AS snap
CROSS APPLY snapshots.fn_get_query_text (snap.source_id, @sql_handle, @statement_start_offset, @statement_end_offset) AS sql
WHERE
snap.instance_name = @instance_name
AND snap.collection_set_uid = @collection_set_uid
AND sql.query_text IS NOT NULL',
@params=N'@sql_handle_str NVarChar(max),
@statement_start_offset Int,
@statement_end_offset Int,
@instance_name NVarChar(max),
@collection_set_uid NVarChar(max)',
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116,@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@collection_set_uid=N'2DC02BD6-E230-4C05-8516-4E8C0EF21F95'
◆各プランの番号を押下して、「クエリプランの詳細」レポートを出力
「グラフィカルなクエリ実行プランの表示」を押下した場合のスクリプト
exec sp_executesql N'SELECT [query_plan]
FROM [snapshots].[notable_query_plan]
WHERE [plan_handle]=[snapshots].[fn_hexstrtovarbin](@PlanHandle)
AND [sql_handle]=[snapshots].[fn_hexstrtovarbin](@SqlHandle)
AND [statement_start_offset]=@StartOffset
AND [statement_end_offset]=@EndOffset
AND [creation_time]=@CreationTime',
N'@PlanHandle varchar(90),
@SqlHandle varchar(90),
@StartOffset int,
@EndOffset int,
@CreationTime datetimeoffset(7)',
@PlanHandle=N'0x06000B0031E46020B0EE6D667001000001000000000000000000000000000000000000000000000000000000',
@SqlHandle='0x0200000031E4602070C1E890AFDFEAF91D35396449B4F4A60000000000000000000000000000000000000000',
@StartOffset=0,
@EndOffset=1116,
@CreationTime='2016-07-13 15:36:44.4730000 +00:00'
【SQL server2012】特定の実行プランのみキャッシュクリアする方法
SQL server では、キャッシュクリアしたい実行プランのプランハンドルがわかれば、特定の実行プランのみをクリアすることができます。
何らかの原因で実行プランが変わり、急激なパフォーマンス低下が発生してしまった際等に、他の処理への影響を減らしつつ実行プランを変更する手法として利用できるかと思います。
削除テスト用のSQLを実行して、削除するための実行プランを作成します。
次に削除する実行プランのPlan_handleを取得します。
DBCCコマンドの引数として先ほどの削除する実行プランのPan_handleを渡して実行します。
【コマンド】DBCC FREEPROCCACHE(<plan_handle>)
*尚、ここでは試しておりませんが、MSDNライブラリを見る限り、Plan_handleだけでなく、sql_handleも指定できるため、sql_handleに紐づく実行プランも削除出来るようです。
プランキャッシュから先ほどの実行プランが削除されていることが確認できます。
プランキャッシュ上にはそのほかの実行プランがキャッシュクリアされずに、残っていることが確認できます。
SQL server 2016の新機能クエリストアについて
◆クエリストアでできること
SQL server の特定のクエリの実行プランは時間の経過とともに変化します。
プロシージャキャッシュ（プランキャッシュ）には、最新の実行プランのみが格納され、
メモリ負荷によるキャッシュアウトで実行プランがプランキャッシュから削除される場合があります。。
クエリストアを利用すると、1つのクエリにつき複数の実行プランをDMV情報と共に保持されるようになります。実行プラン毎の差分をグラフィカルに確認したり、クエリの特定の実行プランを使用するよう強制するプラン強制が可能になります。
ヒント句を用いるプラン強制と似ていますが、クエリの変更を伴わない（ユーザアプリケーションの変更を必要としない）ことがポイントですね。
実行プランの変更による急激なパフォーマンス低下時の暫定対処等で活躍が期待できそうです。
◆クエリストアの有効化
SSMS上のオブジェクトエクスプローラで、データベースのプロパティから操作モードをオフから変更する。
T-SQLの場合は以下を実行すれば、SQL server 2016以前でも使用できる模様ですが未検証です。。
ただしSSMSは2016である必要あり。
ALTER DATABASE <DB_name> SET QUERY_STORE = ON;
◆使用が想定されるシナリオ
・以前の推定の実行プランを強制して適用し、実行プランの変更に伴うパフォーマンス低下を修正
・特定のクエリのデータベースリソース使用パターンを分析
◆低下したクエリでは、以下のようなメトリックごとにソートしたDMV情報及び実行プランを確認することができます。
◆全体のリソース消費量では、データベース全体における累積のクエリ実行状況が確認できます。
◆追跡したクエリでは、クエリIDを使用して、特定のクエリについてクエリストアに保存されている実行プランをそれぞれ確認できるようです。
また、上部メニューの「プランの比較」では、それぞれの実行プランの差分を強調表示したり、予想行数やサイズの比較も行えます。
【SQL server】Max=min server memory設定時のバッファープール使用状況の把握について
Buffer Manager Max server memoryで設定できる値は内訳は以下となっているのでしょうか。。。
Max server memory
= Memory Manager\Total Server Memory (KB)
= Memory Manager\Stolen Server Memory (KB)
+ Memory Manager\Free Memory (KB)
+ Memory Manager\Database Cache Memory (KB)
実際にSSMSオブジェクトエクスプローラのサーバ【メモリ消費量】レポートで取得している MEMORYCLERK_SQLBUFFERPOOLの値と､perfmonで取得しているMemory Manager\Total Server Memory (KB) がほぼ同じ値であることから、上記式の内訳を監視して行けば、 Max server memory = min server memory の設定であってもバッファープール内でのメモリ使用量の大まかな傾向は見ることができそうですね。
◆【 メモリ消費量】レポート
実際にperfmonを取得した時のデータは以下のようになります。
※Max server memory = min server memory = 300MB
◆Max server memoryとmin server memory 設定値
【SQL server】パラメータスニッフィングによる実行プランのパフォーマンス低下
SQL server では、ストアドプロシージャ実行時に実行プランをハードパースする場合に、受け取ったパラメータに最適化する形で実行プランが作成されます。
パラメータスニッフィングにより不適切なパラメータで実行プランがハードパースされると効率の悪い実行プランで実行されてしまい、急激なパフォーマンス低下を発生させることがあります。
※Oracleでいうバインドピーク有効時の不適切なパラメータによる実行計画の不安定化と同じ仕組み？
SQL server が実行プランをハードパースする条件は、以下となります。
・クエリ(ALTER TABLE および ALTER VIEW)によって参照されるテーブルまたはビューに変更を加えた場合
・実行プランで使用されるインデックスに加えた場合
・UPDATE STATISTICS などのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合
・実行プランで使用されるインデックスを削除した場合
・sp_recompile を明示的に呼び出した場合
・クエリによって参照されるテーブルを変更する他のユーザが、INSERT ステートメントまたはDELETE ステートメントを使用して大量の変更をキーに加えた場合
・トリガーを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合
・WITH RECOMPILE オプションを使用してストアドプロシージャを使用して実行する場合
・ハードパースされた実行プランがキャッシュアウトされた場合
対応策としてはストアドプロシージャのクエリにヒント句として、以下のいずれかを追加します。
・RECOMPILEヒント句 ⇒ 実行の度にハードパースする
・OPTMIZE FORヒント句(プラン) ⇒ 実行する実行プランを強制する
・OPTMIZE FORヒント句(UNKNOWN) ⇒ 実行する際に入力パラメータによる最適化ではなく、統計情報を元にコンパイルを実行する
・クエリストアによるプラン強制(SQL server 2016から)(※)
※クエリ自体の修正は必要なく、GUIで設定できますが、こちらの場合はクエリストアに強制したい最適化パラメータの実行プランが格納されている必要があります。
基本的には、推定の実行プランのアクセスパスに基づいて実行されるため、本来であればIndex Seek箇所がIndex Scan、もしくはその反対となりうるカーディナリティのカラムについては注意する必要があると思います。
以下が最適であるシナリオにおいて、パラメータスニッフィングにより不適切な実行プランとなってしまっている例です。
パラメータ「1」 ⇒ Clusterd Index Scan
パラメータ「2」 ⇒ Index Seek
※以下例は、準備不足のためストアドプロシージャに対し、CONVERT_IMPLICIT(暗黙型変換)が発生しているため、インデックスがうまくきいていないことは無視してください…
INT型のデータ型に対し、ストアドプロシージャの変数をVARCHAR型で作成してしまっていることが原因です…
◆パラメータ「2」で最適化された実行プランに対し、パラメータ「2」で実行した場合の、推定の実行プラン
◆ パラメータ「2」で最適化された実行プランに対し、パラメータ「2」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、1/1000000件を取得する処理を効率的にIndex Seekで取得しています。
◆パラメータ「2」で最適化された実行プランに対し、パラメータ「1」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、999998/1000000件を取得する処理を1/1000000件を取得する処理と同様にIndex Seekで取得しています。
◆パラメータ「1」で最適化された実行プランに対し、パラメータ「1」で実行した場合の、推定の実行プラン
◆パラメータ「1」で最適化された実行プランに対し、パラメータ「1」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、999998/1000000件を取得する処理をClusterd Index Scanで取得しています。
◆パラメータ「1」で最適化された実行プランに対し、パラメータ「2」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、1/1000000件を取得する処理を999998/1000000件を取得する処理と同様にClusterd Index Scanで取得しています。
/***************************************************************************************************/
以下は検証に使用したクエリになります。
-- データベース作成
CREATE DATABASE [Param_testDB]
GO
-- これから作成するテーブルの存在確認
IF OBJECT_ID(N'Param_testDB..Param_test' , N'U') IS NOT NULL DROP TABLE Param_test;
USE [Param_testDB]
GO
-- 検証用テーブル作成
CREATE TABLE [Param_test](
[col1] INT IDENTITY PRIMARY KEY ,
[col2] INT ,
[col3] INT )
GO
-- 検証ストアドプロシージャ作成
CREATE PROCEDURE [sp_param]
@param_test VARCHAR(2)
AS
SET NOCOUNT ON;
SELECT * FROM [Param_testDB].[dbo].[Param_test]
WHERE [col3] = @param_test
GO
-- 100万件のデータを追加
-- col3 にはすべて「1」を INSERT
SET NOCOUNT ON
DECLARE @i int = 1
WHILE @i <= 1000000
BEGIN
INSERT INTO [Param_testDB].[dbo].[Param_test] VALUES(@i, 1)
SET @i += 1
END
SET NOCOUNT OFF
-- col3 列のデータのうち「2」「3」に1件ずつ変換
UPDATE [Param_testDB].[dbo].[Param_test] SET [col3] = 2 WHERE [col1] = 1
UPDATE [Param_testDB].[dbo].[Param_test] SET [col3] = 3 WHERE [col1] = 2
-- データの確認
SELECT * FROM [Param_testDB].[dbo].[Param_test]
-- データの個数の確認
SELECT
]col3],
COUNT(*) AS col_cnt
FROM [Param_testDB].[dbo].[Param_test]
GROUP BY col3
ORDER BY col3
-- col3 列にインデックスを作成
-- DROP INDEX idx_col3 ON Param_test
CREATE INDEX [idx_col3] ON [Param_testDB].[dbo].[Param_test](col3)
-- 最適化パラメータ「2」で実行プランを作成
EXEC sp_param 2;
-- 推定の実行プランを確認
SET SHOWPLAN_XML ON;
EXEC sp_param 2;
SET SHOWPLAN_XML OFF;
-- 実際の実行プランを確認
SET STATISTICS XML ON;
EXEC sp_param 2;
SET STATISTICS XML OFF;
-- 1 に変更
SET STATISTICS XML ON;
EXEC sp_param 1;
SET STATISTICS XML OFF;
-- 3 に変更
SET STATISTICS XML ON;
EXEC sp_param 3;
SET STATISTICS XML OFF;
-- =============================================
-- 実行プランを再作成するために プロシージャ キャッシュのクリア
-- =============================================
DBCC FREEPROCCACHE
-- 最適化パラメータ「1」で実行プランを再作成
EXEC sp_param 1;
-- 推定の実行プランを確認
SET SHOWPLAN_XML ON;
EXEC sp_param 1;
SET SHOWPLAN_XML OFF;
-- 実際の実行プランを確認
SET STATISTICS XML ON;
EXEC sp_param 1;
SET STATISTICS XML OFF;
-- 2 に変更
SET STATISTICS XML ON;
EXEC sp_param 2;
SET STATISTICS XML OFF;
-- 3 に変更
SET STATISTICS XML ON;
EXEC sp_param 3;
SET STATISTICS XML OFF;
