2007/1/17
大量データ(約200万件)が登録された状態でバッチ処理を連続して行っていたが、ある日速度が急激に低下していることに気が付いた。EnterpriseManagerで確認したところ、赤い印のブロッキングが頻繁に発生していた。インデックスの再構築を行ってみたらどうかと思い試してみたら、速度が元に戻った。２時間以上かかっていた処理が１時間以内で終わるようになった。
-----------------------------------------------------------
「トレースフラグ1204が吐き出す情報を解析する」に…
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?forum=26&topic=23790
dbcc traceon(1200, 1204, 1205, 3604)
1200 ロック情報を出力
1204 デッドロックに関係するロックのタイプと影響を受ける現行コマンドを戻す
1205 デッドロック時に実行中のコマンドに関する詳細情報を戻す
3604 トレース出力をクライアントに送信する
SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法http://support.microsoft.com/kb/271509/ja
第 3 章 トランザクション分離レベルの選択とデッドロックの問題
http://www.microsoft.com/japan/msdn/sqlserver/columns/webtech/webtech3.aspx
非クラスタインデックスを追加することにより回避されたという話題---海外のサイト
http://forums.databasejournal.com/showthread.php?t=32889&goto=nextnewest
【SQL Server Tips】トレースフラグ 1204 が吐き出す情報を解析する
http://blogs.timberlandchapel.com/blogs/timberlandchapel/archive/2005/06/28/22.aspx
SQL Server 技術情報 - デッドロックの解決方法
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=25489&forum=26&4
http://support.microsoft.com/kb/832524
松本 崇博様の PASSJ Conference 2004「パフォーマンス チューニング」セッション資料 (会員ＩＤが必要)
http://www.sqlpassj.org/conf2004/dl.aspx#Expert03
※メモリ使用量の内訳やメモリが解放されない理由、プロシージャキャッシュ、データバッファキャッシュ、接続メモリ、ロックエスカレーション、ロック待ち、デッドロックの監視方法など、初級者が勘違いしやすい動作を中心に説明されている。
SELECT RIGHT('00'+CAST(1 AS varchar),2)
下記参考
http://fukkey.dyndns.org/pins/sqls/010911/14708.html
http://fukkey.dyndns.org/pins/sqls/010911/14709.html
松本様のBLOGに、「tempdb データベースが使用される状況」が説明されていた
http://blogs.sqlpassj.org/matu_tak/archive/2004/05/18/2324.aspx
FILLFACTOR のデフォルトは SQL Server全体で0となっている 。この値は、サーバーのプロパティで変更できる。
SQL Server でロックのエスカレーションが原因で発生するブロッキング問題を解決する
http://support.microsoft.com/?id=323630
■ロックエスカレーションが問題かどうかをまず判断
他のユーザーをブロックしているロックが S (共有) または X (排他) のロック モードを使用する TAB (テーブル レベル) ロック以外の場合、ロックのエスカレーションは問題ではありません。
特に、TAB ロックがインテント ロック (ロック モードが IS、IU、または IX など) の場合、これはロックのエスカレーションによるものではありません。
ブロッキング問題の原因がロックのエスカレーションではない場合、トラブルシューティングの手順ついては資料 224453 を参照。
http://support.microsoft.com/kb/224453/
問題であることが分かった場合は、下記①、②を試す
■ロックのエスカレーションを防止する方法①
ロックのエスカレーションを防止する最も簡単で安全な方法は、トランザクションを短くして、負荷の高いクエリのロックの使用状況を削減し、ロックのエスカレーションのしきい値を超えないようにすることです。
大規模なバッチ操作をいくつかの小さな操作に分けます。たとえば、次のクエリを実行して監査テーブルから数十万個の古いレコードを削除すると、このクエリが他のユーザーをブロックするロックのエスカレーションの原因となることがわかります。
レコードを一度に数百個ずつ削除することによって、トランザクションごとに累積するロックの数を大幅に削減し、ロックのエスカレーションを防止できます。
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
SET ROWCOUNT 500
delete_more:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
■ロックのエスカレーションを防止する方法②
トレース フラグ 1211 を有効にして、ロックのエスカレーションを無効にできます。ただし、このトレース フラグは、SQL Server のインスタンスにあるすべてのロックのエスカレーション全体を無効にします。
ただし、ロックのエスカレーションは、数千のロックを取得および解放するオーバーヘッドにより速度が低下するクエリの効率を最大にするため、SQL Server の有用性が向上します。
サーバー起動時のパラメータを追加するには、SQL Enterprise Manager でサーバーを右クリックし、[プロパティ] をクリックします。次に、[全般] タブの [起動時のパラメータ] をクリックし、次のパラメータを正確に追加します。
-T1211
新しい起動時のパラメータを有効にするために SQL Server サービスを再開する必要があります。クエリ アナライザで次のクエリを実行すると、すぐにトレース フラグが有効になります。
DBCC TRACEON (1211, -1)
SQL Server 2005 Books Online ロックのエスカレーション
http://msdn2.microsoft.com/ja-jp/library/ms184286.aspx
SQL Server 2005 Books Online DBCC TRACESTATUS
http://msdn2.microsoft.com/ja-jp/library/ms187809.aspx
DBCC (Transact-SQL)
DBCC TRACEOFF (Transact-SQL)
DBCC TRACESTATUS (Transact-SQL)
トレース フラグ (Transact-SQL)
海外のサイトで似たような現象が…
4.5million行のデータ追加or更新処理が、14時間経っても処理がまだ動き続けていて、明らかにおかしいという話題。対処方法は読み取り中だが、48分で完了するよう改善できたらしい。
■updating million plus records in batches
http://www.mcse.ms/message1672252.html
it doesn't end. Last time I looked at it, it was at 14hours and still running. No activity in EM though...
結果は…
Here is what I did and it's working now. I was able to modify 4.5 million records in 48 minutes.
tempdb データベースの同時実行制御の強化（余り関係ないかも）
http://support.microsoft.com/kb/328551/ja
ONかOFFどちらかである必要があるのか、心配になり調べてみたが、二重引用符は使用していないため、気にする必要はなさそう。使う場合は注意しなくてはならない。
【参考サイト】
SET QUOTED_IDENTIFIER が ON の場合は、識別子を二重引用符で区切ることができます
http://msdn2.microsoft.com/ja-jp/library/ms174393.aspx
一時的に " を文字列の区切りに活用
http://blogs.sqlpassj.org/yoneda/archive/2004/12/28/6950.aspx
識別子を「'」で囲んでいるのは、CREATEとかTABLEとかいった、SQLのキー
>ワードとバッティングするテーブル名やカラム名も使えるようにするため
http://www2.xml.gr.jp/log.html?MLID=relax-users-j&TID=1626&F=0&L=10&R=0
QUOTED_IDENTIFIER と単一引用符を含む文字列について
http://support.microsoft.com/kb/156501/ja
作成したＤＢから、SQLを生成したところ、SET ANSI_NULLS ON、SET ANSI_NULLS OFF まちまちだった(特に指定はしていなかったのだが)。
この違いによって得られる結果が異なると心配だったので、調べてみた。
①下記のような条件をWhere句に記述していると、ONの場合に式が判定されない。
項目名 = NULL
項目名 <> NULL
②しかし、下記のように記述していると、このON、OFFは関係なく正しく動作するとのこと。
項目名 IS NULL
項目名 IS NOT NULL
今も昔も、②しか使用しないので、この件はどちらでも正しい結果が得られることが分かった。
②が無難。
【参考になったサイト】
http://www.microsoft.com/japan/msdn/sqlserver/columns/T-SQL/t-sql3.aspx
テーブルを作成後、「①テスト環境Ａサーバ」「②個人クライアント環境」のあるテーブルの列を比較したところ、下記が異なることに気が付いた。
①CODE [char] (5) NOT NULL ,
②CODE [char] (5) COLLATE Japanese_CI_AS NOT NULL ,
システムデフォルトは、「exec sp_helpsort」で調べることが出来るとのことで、クエリアナライザーで実行してみた。結果は日本語環境の通常と説明されている下記の通り。
Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
この省略表記は、「Japanese_CI_AS」であると他のサイトで説明されていた。
CIだと、「大文字、小文字を区別しない。全角アルファベットの大文字、小文字も区別しない
例: 「A」 と 「a」 を同一視する」
何れにせよ、今はデフォルトか、デフォルトで設定しているにも関わらず明示的に同じことを定義しているだけであるため、得られる結果は変わらない。
【参考情報】
照合順序 COLLATE に注意
http://www.sqlpassj.org/bunkakai/security/report/030911.aspx
照合順序の指定
http://www.kawabata.com/yoshihiro/blog/PermaLink.aspx/cf14534f-ff6e-4123-8946-8205986f7665
SQL Server 、VB.netなどについて覚書がかかれている
http://tukaenai-pg.cocolog-nifty.com/blog/sqlserver/index.html
SQL Serverで起動してるバッチ処理が、ＰＣによって異なるという現象が発生している。今回に限ったことではないが、何故同様のスペックで同じ処理をしているのに大きくことなるのかが分からない。
関係あるかどうかまだ分からないが、少し参考になる情報が見つかった。しかし、こんな不可解なことが頻繁に起きるのは不安。
http://d.hatena.ne.jp/ichiyu/20060126
耐えられないほど遅くなってきたのでもう一度良く調べてみた。C:\Tempではなく、C:\Documents and Settings\ユーザーID\Local Settings\Tempに入っている一時ファイルを削除しなくてはいけなかった…
SQL Serverに関する技術情報が多々説明されているサイト
非常に詳しく、分かり易く運用面、監視方法なども説明されているので、このサイトで十分かも…
http://hehao1.seesaa.net/article/8694648.html
マイクロソフトのサイトでは、この回避方法を説明しているが、根本的な原因が説明されていなかった。通常「SQL Server デバッグ」のチェックを行っておくのかどうかが分からないが、問題はあるのだろう。
不可解なのは、ユーザコネクションが3程度で、「タイムアウトに達しました。プールから接続を取得する前にタイムアウト期間が過ぎました。プールされた接続がすべて使用中で、プール サイズの制限値に達した可能性があります。」が表示されていること。原因は他のことと想定される。
★下記のＭＳのサイトを良くみると、「この動作は仕様です 」と書かれていた。チェックして使用する場合は、ソースへの記述が必要とのこと。
～以下、他のサイトからの情報～
Microsoft Visual Studio .NET 2003 で SQL Server のデバッグ機能を有効にした後、アプリケーションを実行すると、次のエラー メッセージが表示されます。
タイムアウトの時間切れです。プールから接続を取得する前にタイムアウト期間が過ぎました。プールされた接続がすべて使用中で、プール サイズの制限値に達した可能性があります。
http://support.microsoft.com/kb/830118/ja
コネクションもデータリーダもクローズしているのにどんどん
プールしている数が増えていって、同様に100を超えた時点で、
上限を超えた旨のメッセージが吐かれて、終了してました。
コマンドオブジェクトの破棄を行わない場合、プーリング数が増える一方だった
のに対し、破棄を行った場合、増えた分はきっちり減少し改善されました。
（エラーはもちろん発生しなくなりました。）
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=7927&forum=7
