それではどうしますか？ CREATE INDEX WITH（ONLINE = ON）の従来の方法は、たとえば、システムクラッシュやDBAの心臓発作を引き起こし、すべてのトップがシステムの応答時間を厳密に監視し、増加すると、あなたとDBAが話をするため、あなたには向いていません仕事に対する報酬の過大評価された数字について。
スクリプトと説明されたテクニックは、毎分40万リクエストの負荷を伴うシステム、SQL Server 2012および2016（Enterprise）のバージョンで使用されました。
インデックスを作成するには、テーブルのサイズに応じて2つの非常に異なるアプローチが使用されます。
ケース1。小さいながらも非常に人気のあるテーブル
5万件のレコード（小規模）のテーブルですが、非常に人気があります（1分あたり数千件のヒット）。 新しいインデックスと最小限のダウンタイムとテーブルのロックが必要です。
アプリケーションでは、データベースへのすべてのアクセスはプロシージャを介してのみ行われます。
エラーが発生した場合、アプリケーションはテーブルへのアクセスを再試行します。
このインデックスを単純に適用することの問題は何ですか？ WITH ONLINE = ONという文で（はい、私たちは幸運で、これはエンタープライズでした）。
実際、このようなアクティブなアクセスでは、ロックを取得するのに時間がかかります（with Online = ONオプションで必要な最小のものでも）。 待機のプロセスでは、新しいリクエストがキューに入れられ、キューが蓄積され、CPUが増大し、DBAが開発者に対して神経質に汗をかき、目を細めていますが、アプリケーション監視グラフでは応答時間は徐々にですが必然的に増加します。 Engeneeringの副社長は、この応答時間の増加により、ある種のシステムダウンタイムが発生するかどうか、また年末にアプリケーションの可用性が5ナイン（99,999）を下回らないが、それ以下になるかどうかに興味を持っています。 そして、会社は、可用性が低下した場合に契約、義務、高額の罰金を科されます。もちろん、評判の低下を忘れることはありません。
この不幸な状況を避けるために何をしましたか？
システムにはまだインデックスが必要です。
このテーブルの現在のセッションを除く全員から権利を取得しました。
インデックスを適用します。
はい、ソリューションにはマイナスがあります。これらの数秒でテーブルに目を向けたすべての人がアクセス拒否を受け取ります。 アプリケーションが通常このような状況を処理し、データベースへのクエリを繰り返す場合は、このオプションを詳しく調べる必要があります。 私たちのプロジェクトの場合、この方法はうまくいきました。 繰り返しますが、インデックスの作成中はセッションのみがテーブルにアクセスできることがわかっているため、ONLINE = ONを安全に削除できます。
インデックスを適用するためのコード：
REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User1] REVOKE EXECUTE ON [dbo].[spUserLogin] TO [User2] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User1] REVOKE EXECUTE ON [dbo].[spUserCreate] TO [User2] CREATE NONCLUSTERED INDEX IX_Users_Email_Status ON [dbo].[Users] ([Email],[Status]); GRANT EXECUTE ON [dbo].[spUserCreate] TO [User1] GRANT EXECUTE ON [dbo].[spUserCreate] TO [User2] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User1] GRANT EXECUTE ON [dbo].[spUserLogin] TO [User2]
負荷時のテスト中の応答時間とエラーの割合のスケジュール。
説明した場合のように、小さなテーブルがあり、ロードなしでインデックスが数秒で（または許容時間内に）作成されることがわかっている場合、このメソッドを適用できます。 同時に、上のグラフからわかるように、アプリケーションへの応答時間は増加しませんが、テーブルにアクセスしない場合のエラー率は秒単位で高くなっていることがわかります。
ケース番号2。大きなテーブル
大きなテーブルがあり、そのインデックスを変更する必要がある場合、販売する最も簡単な方法は、正しいインデックスを使用して隣にテーブルを作成し、データを徐々に新しいテーブルに転送することです。
2つの方法があります。
- テーブルを変更する特別な手順がある場合は、プロシージャコードを変更するだけで、新しいデータが新しいテーブルにのみ挿入され、削除は両方から行われ、更新も両方に適用され、選択はUNION ALLを使用して2つのテーブルから行われます。
- テーブル内のデータを変更できるコードのさまざまな部分がある場合、2つの一般的なトリックがあります：トリガーで表示するか、コードのすべての部分を書き換えて新しいテーブルにデータを挿入し、両方から削除し、両方のテーブルを更新します。 トリガー付きのビューは、2つのテーブルを含むビューを作成して名前を変更し、現在のテーブルの名前をTableOldに変更し、ビューをテーブルに変更する場合のオプションです。 次に、ビューへのすべてのテーブル呼び出しを自動的に取得します。ここでは、SchemaLockが必要なため、名前の変更でも問題が発生する可能性がありますが、名前の変更は非常に迅速に行われます。
新しいテーブルへの呼び出しの書き換えに関するもう少し詳細なバージョン：
- Ordersテーブルがあり、同じスキームで目的のインデックスを持つ新しいOrdersNewテーブルを作成します。 同時に、Indentityを使用する場合は、新しいテーブルの最初のID値を、古いテーブルの最大値+変更ステップまたはOrdersの最大値から逸脱する余裕があるギャップに等しく設定する必要があります。
- OrdersViewを作成し、その中にOrders UNION ALL OrdersNewから選択します
- ビューからデータを選択するためのすべてのプロシージャ/コールを変更し、OrdersNewに挿入して、両方のテーブルを削除および変更します。
- たとえば、次のように、古いテーブルから新しいテーブルにデータを移行します。
DECLARE @rowcount INT, @batchsize INT = 4999; SET IDENTITY_INSERT dbo.OrdersNew ON; SET @rowcount = @batchsize; WHILE @rowcount = @batchsize BEGIN BEGIN TRY DELETE TOP (@batchsize) FROM dbo.Orders OUTPUT deleted.Id ,deleted.Column1 ,deleted.Column2 ,deleted.Column3 INTO dbo.OrdersNew (Id ,Column1 ,Column2 ,Column3); SET @rowcount = @@ROWCOUNT; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; THROW; END CATCH; END; SET IDENTITY_INSERT dbo.OrdersNew OFF;
- 1つのテーブルを使用して、すべての手順を移行前のバージョンに戻します。 これは、変更または削除と作成の手順を介して行うことができ（権限を忘れないでください）、新しいテーブルの名前をOrdersに変更し、空のテーブルとビューを削除できます。
ステップ2では、ロードが許可されている場合、メインテーブルOrders-> OrdersOld、OrdersView-> Orders、およびビュー自体の名前をOrdersOld UNION ALL OrdersNewに変更することができました。その後、テーブルから選択があるすべての場所を変更する必要はありません。
あるテーブルから別のテーブルにブロックを移動すると、データが断片化されます。
変更中のテーブルがアクティブに読み取りに使用されているが、そのデータがめったに変更されない場合は、トリガーを再び使用できます-すべての変更のコピーを3番目のテーブルに書き込みます-bcp outおよびbcp in（または一括挿入）を介してテーブルから新しいテーブルにデータを転送します、データ転送後にインデックスを作成し、変更ログを使用してテーブルから変更を適用し、現在のテーブルをTableOldに変更し、新しいテーブルをTableNewからTableに変更します。
この状況でエラーが発生する可能性はわずかに高いため、この場合の変更の適用と異なるスイッチングケースをテストします。
説明されているオプションは唯一のものではありません。 それらは負荷の高いSQL Serverデータベースで使用され、アプリケーション中に問題を引き起こすことはありませんでした。DBAチームは満足しました。 通常、このようなバウンスは、最も活動が少ない時間に変更を安全に適用できる、より穏やかなロードモードのベースには必要ありません。 説明されたアプローチを使用したプロジェクトのユーザーはアメリカとヨーロッパにいて、平日と週末にアプリケーションを積極的に使用し、変更が適用されたテーブルは常に作業で使用されています。 開発者とDBAの1人がスクリプトをレビューした後、Redgate Toolkitを介して生成された自動スクリプトによって、より「静かな」オブジェクトが通常変更されました。
すべてに良い！ これらの方法のいずれかを使用した場合、または方法を説明した場合は、コメントで共有してください！ また、新しいコース「MS SQL Server Developer」の 公開レッスンと公開日にもご招待します