エラー666

概要 エラー666という滅多に見かけることがないエラーがあります。下記のようなメッセージのエラーとなります。 メッセージ 666、レベル 16、状態 2、行 1 パーティション ID が 72057594039828480 のインデックスで、重複するグループに対するシステム生成の一意値が最大値を超えました。この問題はインデックスを削除し、再作成することで解決できます。それ以外の場合は、別のクラスター化キーを使用してください。 目を引くエラー番号なのですが、どのような条件で発生するエラーなのか情報がないので、何故インデックスを再作成(再構築)することで解決するのか等々、よく判りませんでした。そこで、動きを確認して見ましたので、その結果をBlogで共有しておこうと思います。 なお、本検証結果はあくまでも検証ベースの結果であり、ソースコードの確認等で得られたものではありません。また、SQL Server 2008 R2 での確認になりますので、将来のバージョンでの動作については異なる可能性もあります。この点についはご理解いただいた上で、ご参照ください。 動作について まず、メッセージにある「重複するグループに対するシステム生成の一意値」が何か?というところですが、これは下記の SQL Server 2008 R2 Books Online に説明があります。 クラスタ化インデックスの設計ガイドライン UNIQUE プロパティを指定せずにクラスタ化インデックスが作成された場合、データベース エンジンにより、4 バイトの uniqueifier 列が自動的にテーブルに追加されます。必要があれば、各キーを一意にするため、データベース エンジンにより自動的に uniqueifier 値が行に追加されます。この列とその値は、内部的に使用されるもので、ユーザーが参照したりアクセスすることはできません。 平たく書くと、「一意でないクラスタ化インデックスでは、内部的に4バイトの列が追加されて、この値を一意性を保つのに使います」ということです。エラー666は、この値が最大値を超えてしまったために発生するということですね。 後は、この内部的な4バイトの列がどのように動作するか確認すればOKということで、検証してみました。どのような検証を行ったかは後述しますが、結論を書くと以下のような動作になりました。 1) この値は重複するグループ毎に振られる値です 例えば、以下のような構成のインデックスの場合、c1とc2の値が重複するレコードが同じグループとしてまとめられます。c1=1、c2=2、c3=1,C4=N’A’とc1=1、c2=2、c3=2、c4=N’A’というレコードをINSERTするとグループにまとめられ、2件目のレコードでこの値は1になるようです。c1=1、c2=3、c3=1,c4=N’A’というレコードは、違うグループになります。 create table test666(c1 int,c2 int,c3 int,c4 nchar(10)) go create clustered index idx_c1c2 on…

0

How To:Ghost Record を確認する方法

Ghost Record とは? SQL Server の Books Onlineでは、以下のように説明があるものです。 実体レコード (ghost record) 削除対象として指定されているものの、まだデータベース エンジンによって削除されていない、インデックスのリーフ レベルの行。 簡単に書くと、DELETE したけど、まだ実際には削除されていないレコードのことです。SQL Server はDELETEを実行する際に、パフォーマンスの観点から、対象レコードに「削除した」というマークだけを付けています。実際にこのマークされたレコードは、「Ghost Cleanup Task」という処理が削除してくれて、初めてその削除されたレコードが使っていた領域が、再利用可能になるという、そんな感じの仕組みです。なお、インデックスとありますが、Heap(クラスタ化インデックスがないテーブル)でもGhost Recordは発生します。 今回のポストは、このGhost Recordを確認する方法をご紹介します。 「何故確認方法が必要なのか?」といいますと、このGhost Recordが「Ghost Cleanup Task」によってCleanupされないという状況が発生する場合があるためです。この状況になっていると、レコードは全然ないのに圧縮できないといった状態になります。もし、「レコード件数は少ないのに、妙にテーブルのサイズが大きい」とか「レコード削除したはずなのに、データベースが思ったように圧縮されない」という現象を経験したら、Ghost Recordが大量に溜まっていないか、一度確認してみてください。 確認方法:SQL Server 2005/2008/2008 R2 sys.dm_db_index_physical_stats 動的管理関数を使用することで、確認が可能です。例えば、対象のデータベースで下記のように実行すれば、ghost_record_count が存在しているテーブルを確認することができます。 select object_name(object_id) as [Name],ghost_record_count,* from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’) where ghost_record_count>0 確認方法:SQL Server 2000 残念ながら公開されている確認方法はありません。 対処方法 対象のインデックスを再構築することで、大量に溜まったGhost Recordを消し去ることが可能です。 Heapの場合、一旦クラスタ化インデックスを作成し、削除することで対処可能です(断片化解消と同じ方法です)。

0

DO’s&DONT’s #13: 絶対にやってはいけないこと – ORDER BY が指定されていないクエリの結果が一定の順番に並んでいると仮定すること

  神谷 雅紀 Escalation Engineer   多くの場所で議論されている場面を見かけるため、ここで明確にしておきたいと思います。   ORDER BY のない SELECT の実行結果内の行の並び順は一切保証されない SELECT の実行結果の並び順が保証されるのは、最も外側 (最も上位) の SELECT に ORDER BY が指定されている場合だけです。 SELECT 内のサブクエリ内に ORDER BY が指定されていたとしても、また、ビューの中で ORDER BY が指定されていたとしても、それらを参照する最も外側の SELECT に ORDER BY が指定されていなければ、SELECT の実行結果内の行の並び順は不定です。 これは、クラスタ化インデックスがあるかどうか、非クラスタ化インデックスがあるかどうか、並列クエリであるのかどうか、どのような順番で行が INSERT されたかなど、いかなる要因にも左右されません。どのような場合であっても、SELECT の実行結果の並び順が保証されるのは、最も外側の SELECT に ORDER BY が指定されている場合だけです。 もし ORDER BY を指定していない SELECT の結果が期待した順番に並んでいたとしても、それに依存してアプリケーションやシステムを設計、実装、構築しないで下さい。期待する並び順があるのであれば、必ずそれを ORDER BY として指定することで、並び順を SQL…

0

DO’s&DONT’s #12: やった方がいいこと – max server memory を設定する

  神谷 雅紀 Escalation Engineer   サーバースローダウンなどの事象 SQL Server の一時的な無応答、処理の遅延 (サーバースローダウン)、クエリタイムアウト、ログインタイムアウト、ネットワークエラー、既存のクライアント接続の切断、MSFC フェールオーバーの発生、MSFC での SQL Server クラスタリソース再起動、ミラーリングフェールオーバー、ミラーリングパートナータイムアウト、CPU 高負荷 (使用率 100%) といった事象が発生することがあります。これらの原因のひとつがページング (paging) です。ページングにより SQL Server プロセスのワーキングセット (working set) が小さくなることで、SQL Server 上で行われるすべての処理が遅くなり、また、SQL Server が動作するサーバー全体のパフォーマンスが悪化します。 このような事象が発生した場合、定常監視項目として Memory:Pages/sec や Process:Working Set, SQLServer: Buffer Manager\Total pages パフォーマンスカウンタを採取している場合には、それらのカウンタを確認してみて下さい。問題の事象の発生と同時に Memory:Pages/sec の値が上昇し、sqlservr プロセスの Process:Working Set の値が大きく減少してるにも関わらず、SQLServer: Buffer Manager\Total pages がそれと同等の減少をしていない場合、その事象はページングが原因であると考えて間違いありません。   対応方法 ページングによるスローダウンを防ぐために、まず最初にすべきことは、max…

0

実行プランを読む – 活用編 (その 2) – クエリパフォーマンス悪化の原因

  神谷 雅紀 Escalation Engineer 今回は、急に実行プランが変わったと思われる場合に、確認すべき実行プラン中の項目をひとつ紹介します。   同じクエリの実行プランが変わるということは、最適化時 (実行プラン選択時) に参照されるデータが変わったということです。参照データのひとつであるクエリ内のパラメータ値は、ParameterCompiledValue で確認可能です。   ParameterCompiledValue パラメータ化クエリやストアドプロシージャの実行時に、XML 実行統計 (SET STATISTICS XML ステートメント や Showplan XML Statistics Profile イベント) を採取すると、最適化時に使用されたパラメータ値と実行時に使用されたパラメータ値が含まれています。キャッシュされている実行プランが再利用された場合、これらの値は異なる可能性があります。 例 <ParameterList> <ColumnReference Column=”@p1″ ParameterCompiledValue=”(10)” ParameterRuntimeValue=”(50000)” /> </ParameterList> ParameterCompiledValue がこの実行プラン選択時に指定されていたパラメータ値です。ここに示される値を用いて、実行プランが変わった原因がパラメータ値にあるのかどうかを切り分けることができます。 DBCC FREEPROCCACHE で対象クエリの実行プランをプロシージャキャッシュから削除した後に、ParameterCompiledValue に示されているパラメータ値でクエリを実行した場合には想定しない実行プランが生成され、ParameterCompiledValue に示されている値ではない典型的な値で実行した場合には想定どおりの実行プランが生成されるようであれば、問題の実行プランとなった原因はパラメータ値にあることになります。 この確認を行う際に注意しなければならないのは、クエリを実行する接続の SET オプションです。 SET オプションが異なれば、同じクエリを実行しても、実行プランは異なります。実行プラン選択時に指定されていた SET オプションは、XML 実行統計内の StatementSetOptions に示されます。 例 <StatementSetOptions QUOTED_IDENTIFIER=”true” ARITHABORT=”true”…

1

変数とパラメータの違い

  神谷 雅紀 Escalation Engineer   2ヶ月ほど前に、「DO’s&DONT’s #10: やらない方がいいこと – クエリの条件句で変数を参照する」というタイトルで、クエリの条件句で変数を使用することは避け、変数の変わりにパラメータを使用した方がよいという内容を投稿しましたが、変数とパラメータが混同されていることがあるようですので、今回は、変数とパラメータの違いについて説明します。   変数とは 変数 (variable, local variable) は、declare によって宣言され、declare、SET、SELECT ステートメントによって値が設定されます。 変数の例 : 青字が変数 — 変数宣言 declare @local_variable_1 nvarchar(10) declare @local_variable_2 nvarchar(10) = ‘abc’ — 宣言と同時に値の設定 — 値の設定 set @local_variable_1 = ‘abc’ select * from db1.sch1.tab1 where col1 = @local_variable_1 実際に変数に値が設定されるのは、コンパイルやクエリの最適化時ではなく、値を設定するステートメントの実行時です。上の例では、select * from db1.sch1.tab1 where col1…

0