By:Brady Upton|Updated:2016-06-22|Comments(5)|Related:More>断片化とインデックスのメンテナンス
Problem
データベース/インデックスの断片化について聞いたことがあります（そうでない場合はcontinuereading）が、それは何ですか？ それは問題ですか？ それが私のデータベースに存在するかどうかを知るにはどうすればよいですか？どうすれば修正できますか？ これらの質問はすべてそれ自体のヒントになる可能性がありますが、私はこのヒントでそれぞれのアイデアを与えようとします。
ソリューション
多くの詳細に入ることなく、SQL Serverは8KBページにデータを格納します。 テーブルにデータを挿入すると、SQL Serverは、挿入されたデータが複数のページにまたがる8KBを超えることなく、そのデータを格納するために1つのページを割り当各ページは1つのテーブルに割り当てられます。 10個のテーブルを作成すると、10個の異なるページが作成されます。
テーブルにデータを挿入すると、データは最初にトランザクションログファイルに移動します。 Thetransactionログファイルは、レコードを挿入、更新、および削除すると、これらのトランザクションが最初から最後まで記録されるシーケンシャルレコードの意味です。 一方、データファイルは連続的ではありません。 ログファイルは、すべての場所のページを作成するデータファイルにデータをフラッシュします。データがどのように格納されているかについてのアイデアが得られたので、これはfragmentationと何が関係しているのでしょうか？
断片化には、内部断片化と外部断片化の二つのタイプがあります。
SQL Server内部の断片化
SQL Server内部の断片化は、空き領域が多すぎるページが原因です。一日の初めに、40ページのテーブルが100％いっぱいですが、一日の終わりには、さまざまなdelete文とinsert文のために50ページのテーブルが80％いっぱいになります。これは、このテーブルから読み取る必要があるときに、パフォーマンスが低下する可能性がある40ページではなく50ページをtoscanするため、問題が発生します。のは、迅速かつ汚い例を見てみましょう。
FirstNameand LastNameに主キーと非クラスタ化インデックスを持つ次の表があるとしましょう。
このヒントの後半で断片化を分析する方法について説明しますが、今のところインデッ これはまったく新しいインデックスなので、0％の断片化です。
このテーブルに1000行を挿入しましょう。
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
さて、インデックス:
インデックスが75％断片化され、全ページの平均パーセント（ページの膨満感）が80％に増加 このテーブルはまだ非常に小さいので、75％の断片化はおそらくパフォーマンスの問題を引き起こさないでしょうが、テーブルのサイズとページ数が増加する また、この表が0ページから4ページになったことを上記のscreenshotから見ることができます。
SQL Serverの外部の断片化
外部の断片化は、ページが順不同であることが原因です。 私たちは完全に注文したテーブルを持っている一日の初めにpretendしましょう。 Thedayの間に私達は多分空スペースをononeのページに残し、他のページにスペースに合うことを試みる何百もの更新の声明を出す。 これは、私たちのストレージは、一方向に読み取るのではなく、必要なデータを取得するために飛び回る必要があることを意味します。
sql Serverの断片化の分析
断片化は問題ですか？ 私はそれがあると信じています。 データベース全体をメモリに保存できる場合、またはデータベースが読み取り専用の場合は心配しませんが、ほとんどの人はその贅沢を持っていません。私は何千ものサーバーで作業してきましたが、断片化レベルを分析することは私が最初に見たことの一つです。 実際、断片化を修正するだけで、クエリのパフォーマンスが最大200％向上しました。断片化レベルの分析といえば、これをどのように行うことができるのか疑問に思うかもしれません。
まあ、いくつかの方法があります…
DBCC SHOWCONTIG–この機能は古く、SQL Serverの将来のバージョンでは削除されますが、まだSQL Server2000以下を使用している場合は、これが役立 それについて書いているのではなく、私はあなたをここに指摘するか、Chad Boydのtiphereをチェックすることができます。 どちらも良い資源です。
sys.dm_db_index_physical_stats–SQL Server2005で導入されたこの動的managementview(DMV)は、指定されたテーブルまたはビューのデータおよびインデックスのサイ
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
これはおそらく断片化を分析する最も広く使用されている方法です。 上記のスクリーンショットから、98.83％断片化されたtheRPCustテーブルにIx_Rpcust_1という名前のインデックスがあることがわかります。 あなたはこのDMVherefrom Arshad Aliの詳細を見ることができます。
サードパーティ製のソフトウェア-サードパーティ製のソフトウェアは、あなたがする必要はありませんfragmentationsoを識別するのに役立ちます。 Greg Robidoux氏は、IderaのSQL Defrag Managerhereに関する良いヒントを提供しています。
SQL Serverの断片化の修正
データベースで断片化が見つかったので、どのように修正しますか？ インデックスの解析と同様に、複数の方法があります。
SQL Serverメンテナンスプラン–メンテナンスプランはSQLServerに同梱されており、一部のタスクに適しています。 私の意見では、インデックスの維持はそれらの一つではありません。 インデックスの再構築タスクまたはインデックスの再編成タスクをmaintenanceplanに追加できますが、問題は計画にロジックを実際に適用できないことです。 がなければ、計画はすべてのインデックスを再構築/再編成します。 一部のインデックスは断片化されていないため、再構築する必要はありません。 一部のインデックスには最小限の断片化がある場合がありますので、再編成で問題ありません。 Maintenanceplanでこれを指定する方法はありません。 InSQL Server2016断片化を管理するための新しいオプションがあります。
カスタムスクリプト–インデックスの断片化をチェックし、インデックスをスキップするか、再編成するか、または再構築するかを決定するロジック のスクリプトを使用しています。必要なことを正確に実行しています。 MSSQLTips。comには、SQL Serverの断片化とインデックスのメンテナンスのヒントもあります。
サードパーティ製のソフトウェア-サードパーティ製のソフトウェアは、あなたがする必要はありませんindexessoをデフラグするのに役立ちます。
次の手順
- このヒントでは、indexfragmentationに関係するコンポーネントのいくつかを説明します。 MSSQLTips.com 私がここに言及した科目のそれぞれに焦点を当てたより包括的なヒントを提供しています。
- 断片化に関する素晴らしいinsightinto多数のトピックを提供していますoutChadボイドのSQL Serverの断片化チュートリアルをチェックしてください
最終更新: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…