SQL Server はロックが大好きです。
SQL Server はデフォルトで「悲観」ロックを使用します – あなたのクエリーは当然防衛的になります。 これは、SQL Server のスケーリングを妨げるブロックの問題につながる可能性があります。
無料のビデオ トレーニング クラス – ブロックとデッドロックのトラブルシューティングを行う前に、「エンジンのように考える方法」から始めてください。 このクラスは、クラスタ化インデックスと非クラスタ化インデックスの内部動作について説明する 90 分の速いペースで進むクラスで、インデックスがブロッキングに与える影響を理解するために不可欠なものです。 すべてのSQL Serverプロフェッショナルは、ここからトレーニングを始めるべきだと考えているからです。 ロック競合を診断するツール
“Do I have lock waits right now?”（今、ロック待ちがありますか？ sp_BlitzFirst® を使用して、ロック待ちが現在 SQL Server に影響を与えているかどうかを診断します。
“どのテーブルが最もひどいブロックの問題を抱えていますか？” sp_BlitzIndex® を実行し、「Aggressive Index Disorder」を探して、どのテーブルで大量のロック待ちが発生しているかを特定します（現在メモリ内のテーブルとインデックスに対して）。
“Blocked Process Report を理解するには？” SQL Server Blocked Process Report（下のスクリプト）をキャプチャすると、Michael J Swart の Blocked Process Report Viewer が結果を解釈するのに役立つ無料ツールです。
“What if my application doesn’t catch deadlock errors?” Microsoft の CSS チームの Amit Banerjee は、SQL Server の拡張イベント System Health セッションを照会して詳細を確認するのに役立ちます (SQL Server 2008+).
Lock Related Performance Counters
These performance counters can be very useful to configure a perf counter based SQL Server Agent Alert so that SQL Server can notify you when blocking passes the threshold you set.
- These listed first OBJECT, then COUNTER
- SQL Server.Lock 関連パフォーマンスカウンターは、SQL Server のエージェント警告を設定するときに非常に役立ちます。 一般統計 – ブロックされたプロセス
- SQL Server: ロック – ロック待機時間 (ms)
- SQL Server: Locks – Number of Deadlocks/sec
SQL Server のその他の完璧なカウンターについては、こちらを参照してください。
ロック、分離レベル、およびデッドロックに関する記事を読む
Jeremiah による 2 フェーズ ロックとデッドロックの紹介を読むことができます。
Kendra から、どのロックが必要かを見つけるために、どのようにコードの変更をテストできるかを学びます。
Jeremiah から、デッドロックを発見し防止する方法を学びます。
Kendra から、SQL Server で楽観的ロックを実装する方法を学びます。
SQL Server の分離レベルについての詳細は、包括的なリンクとリファレンスのページを参照してください。
Blocked Process Report を使用するスクリプト
“Blocked Process Report” は SQL Server で長い間存在しており、今でも有用なツールです。 SQL Server のデフォルトでは、デッドロック検出器は 5 秒ごとに起動し、クエリーが「死の抱擁」状態にあるかどうかをチェックします。 このリソースを使用して長時間のブロックをチェックし、レポートを発行するように SQL Server に依頼するオプションをオンにすることができます。
WARNING: ブロックされたプロセスのレポートを発行するように構成するのは、5 秒以上の値に対してのみ行う必要があります。 Microsoft は、これを 1 ～ 4 に設定すると、デッドロック検出器が継続的に実行され、パフォーマンスが低下すると警告しています。
Blocked Process Report を使用するには、「エラーと警告」の下で「Blocked Process Report」イベントを検索するトレースを設定する必要があります。 サーバー サイド トレースを使用し、もちろん必要なときだけこれを実行することをお勧めします。 このセクションのスクリプトは、これらのタスクを実行する一般的な方法のサンプルです。 これは、20秒以上かかっているブロックを探します。
|
1
2
3
4
5
6
78
9
10
11
12
13
14
15
|
–保留中の変更がないことを確認する
SELECT *
FROM sys.configurations
WHERE value <> value_in_use;
GO
exec sp_configure ‘show advanced options’, 1.詳細オプションの表示、2.詳細オプションの表示、3.詳細オプションの表示、4.詳細オプションの表示、5.詳細オプションの表示、6.詳細オプションの表示。 GO
RECONFIGURE
ex sp_configure ‘blocked process threshold (s)’, 20;
GO
RECONFIGURE
|
ステップ2.プロセス・コントロール。 ブロックされたプロセスレポートをキャプチャするトレースをセットアップします。 サーバーサイドのトレースとして実行します。
- これは1つのイベントを含む必要があります。 Errors and Warnings -> Blocked Process Report.
- これには2つの列だけが必要です。 Text, spid
このサンプル サーバー サイド トレースは、5 分間実行され、自動的に停止します。 SQL Server が書き込み権限を持つ有効なディレクトリにパスを変更する必要があります。
|
1
2
3
4
5
6
7
8
910
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
— 作成者 SQL Server 2012 Profiler
— キューの作成
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
——— ここに機能を追加しました。
set @DateTime = DATEADD(mi,5,getdate()); /* 5分間実行 */
set @maxfilesize = 5
— InsertFileNameHere, を適切な
— ファイル名の前にパスがあるものに置き換えてください、e.N.Y.です。g., c:\MyFolderMyTrace. .trc 拡張子
— は、自動的にファイル名に付加されます。
— リモートサーバーからローカルドライブに書き込む場合は、UNCパスを使用し、サーバーが
— ネットワーク共有への書き込みアクセス権を持っていることを確認してください
———- ここにファイル名を設定してください。
exec @rc = sp_trace_create @TraceID output, 0, N’S:\TracesBlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc !) != 0) goto error
— Client side File and Table cannot be scripted
— Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Filterの設定
declare @intfilter int
declare @bigintfilter bigint
— トレース状態の開始
exec sp_trace_setstatus @TraceID.を設定します。 1
— display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
|
ステップ3：実行中のトレースをみてトレースIDを取得します。
|
1
2
|
SELECT * from sys.Traces.traces;
GO
|
トレースを停止させます。 if needed
|
1
2
3
4
5
|
-の場合。-上記のクエリから正しい traceid を挿入します
EXEC sp_trace_setstatus @traceid =2, status = 0; — トレースを停止します (トレース ID 2 と仮定)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2; — トレースを終了します (トレース ID 2 と仮定)
GO
|
ステップ 4: ブロックされているプロセスレポートデータを読み取ります。
このサンプルでは、Michael J Swart氏による無料のブロックされたプロセス レポート ビューアを使用します。
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\TracesBlockedProcessReportDemo.trc’;
GO
|
ステップ5：クリーンアップ！
この手順を忘れないでください。
|
1
2
3
4
5
6
7
89
10
11
12
13
14
15
16
17
18
|
–トレースが消えていることを確認する
SELECT * from sys.traces;
GO
–使用していないときは、ブロックされたプロセス レポートをオフにします
–保留中の変更がないことを確認します
SELECT *
FROM sys.LIBRARY.LIBRARY.LABEL
。configurations
WHERE value <> value_in_use;
GO
exec sp_configure ‘ブロックされたプロセスのしきい値 (s)’, 0;
GO
RECONFIGURE
ex sp_configure ‘blocked process threshold (s)’;
GO
|
