☰

SQL Server データベースで ID の増分が急増しています 質問する

tag-icon • tag-icon sql sql-server sql-server-2012 identity-column
SQL Server データベースで ID の増分が急増しています 質問する

SQL Server 2012 データベースの「ReceiptNo」列のテーブルの 1 つで、Fee次の 2 つのことに応じて、ID の増分が突然 1 ではなく 100 にジャンプし始めました。

  1. 1205446 の場合は 1206306 にジャンプし、1206321 の場合は 1207306 にジャンプし、1207314 の場合は 1208306 にジャンプします。次の図に示すように、ジャンプが発生するたびに最後の 3 桁は一定、つまり 306 のままであることに注意してください。

  2. この問題はコンピュータを再起動すると発生します

ここに画像の説明を入力してください

ベストアンサー1

この動作は、SQL Server 2012 以降のパフォーマンスの向上により発生しています。

IDENTITY列に値を割り当てるときにデフォルトで 1,000 のキャッシュ サイズが使用され、サービスを再起動すると未使用の値が「失われる」可能性があります ( /intのキャッシュ サイズは 10,000 です)。bigintnumeric

これについては、ドキュメント

SQL Server はパフォーマンス上の理由から ID 値をキャッシュすることがあり、割り当てられた値の一部はデータベース障害またはサーバーの再起動時に失われることがあります。このため、挿入時に ID 値にギャップが生じる可能性があります。ギャップが許容されない場合は、アプリケーションで独自のメカニズムを使用してキー値を生成する必要があります。NOCACHEオプション付きのシーケンス ジェネレーターを使用すると、コミットされないトランザクションにギャップを限定できます。

さらにこのギャップは一見起こるいくつかの種類のロールバックされたトランザクションの後。フィドル。

あなたが示したデータから、これは 12 月 22 日のデータ入力後に発生し、その後 SQL Server が再起動したときに値が予約されたように見えます1206306 - 1207305。12 月 24 日 - 25 日のデータ入力後に再度再起動が行われ、SQL Server は1207306 - 120830528 日のエントリに表示される次の範囲を予約しました。

異常な頻度でサービスを再起動しない限り、「失われた」値がデータ型で許可される値の範囲に大きな影響を与える可能性は低いため、心配しないのが最善策です。

何らかの理由でこれが本当に問題である場合、いくつかの回避策が考えられます...

  1. あなたはSEQUENCEたとえば、ID 列の代わりに小さいキャッシュ サイズを定義して、NEXT VALUE FOR列のデフォルトで使用します。
  2. または、トレース フラグ 272 を適用して、IDENTITY割り当てを 2008 R2 までのバージョンと同様にログに記録します。これは、すべてのデータベースにグローバルに適用されます。
  3. または、最近のバージョンの場合は、ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF特定のデータベースの ID キャッシュを無効にするために実行します。

これらの回避策のいずれもギャップがないことを保証しないことに注意してください。これは、IDENTITYテーブルへの挿入をシリアル化することによってのみ可能であるため、保証されたことはありません（このようなシリアル化がないと、制約違反などにより失敗する挿入ステートメントにアイデンティティ値が割り当てられる可能性があります。または、単にロールバックされるトランザクションに割り当てられる可能性があります）。本当にギャップのない列が必要な場合は、またはとは異なるソリューションを使用する必要がありIDENTITYますSEQUENCE（例）

おすすめ記事