執筆者 : Carl Rabeler 適切なキューブ デザイン、効率的な多次元式 (MDX)、および十分なハードウェア リソースは、SQL Server 2005 Analysis Services インスタンスに対して発行する MDX クエリのパフォーマンスの最適化に欠かせないものです。この記事では、Analysis Services のクエリ パフォーマンスの最適化に関して Microsoft SQL Server 開発チームが推奨する最も一般的な 10 項目のベスト プラクティスを紹介します。クエリ パフォーマンスに関連する Analysis Services のベスト プラクティスの詳細については、『The Analysis Services Performance Guide』および「OLAP Design Best Practices for Analysis Services 2005」(英語) を参照してください。  キューブおよびメジャー グループのデザインを最適化する
- 連鎖している属性リレーションシップ (日 > 月 > 四半期 > 年など) を定義し、各ディメンション内の関連属性のユーザー階層 (自然階層と呼ばれる) をデータに応じて適切に定義します。
自然階層に含まれる属性は、ディスク上の階層ストアに格納され、自動的に集計候補と見なされます。レベルを構成する属性が、連鎖している属性リレーションシップによって関連付けられていない限り、ユーザー階層は自然階層とは見なされません。SQL Server 2005 Service Pack 2 (SP2) では、各ユーザー階層が自然階層として定義されていない場合、Business Intelligence Development Studio に警告が表示されます。 - 属性間の冗長なリレーションシップを削除し、クエリ実行エンジンで適切なクエリ プランを生成できるようにします。属性のキー属性に対するリレーションシップは、直接または間接のいずれか一方のみである必要があります。
- 必要なメジャー グループのみを含めるようにして、キューブ空間をできる限り小さくします。
- 同時にクエリされるメジャーは、同じメジャー グループに配置します。複数のメジャー グループからメジャーを取得するクエリの場合、複数のストレージ エンジン操作が必要になります。同時にクエリされない多数のメジャー セットを個別のメジャー グループに配置してキャッシュの使用を最適化することを検討してください。ただし、メジャー グループの数は増やしすぎないようにします。
- 大規模な親子階層の使用を最小限に抑えます。親子階層では、集計は、キー属性および有効な最上位の属性 (All 属性など) に対してのみ作成されます。したがって、中間レベルのセルを返すクエリはクエリ時に計算されるので、大規模な親子ディメンションの場合は時間がかかる可能性があります。大規模な親子階層 (メンバ数が 250,000 を超える) を使用したデザイン シナリオでは、階層の一部またはすべてを再編成するソース スキーマを、レベル数を固定したユーザー階層に変更することを検討します。
- 多対多ディメンションのパフォーマンスを最適化します (使用する場合)。データ メジャー グループを多対多ディメンションでクエリする場合、メジャー グループに共通して存在する各ディメンションの粒度属性を使用してデータ メジャー グループと中間メジャー グループの間で実行時の “結合” が行われます。可能であれば、中間メジャー グループの基になる中間ファクト テーブルのサイズを小さくします。実行時の結合を最適化するには、中間メジャー グループの集計のデザインを見直し、集計に多対多ディメンションの属性を含めるようにします。
ディメンションを最適化してクエリ パフォーマンスを向上させる方法については、『SQL Server 2005 Analysis Services Performance Guide』および「OLAP Design Best Practices for Analysis Services 2005」(英語) を参照してください。デザインがベスト プラクティスに従っているかどうかを分析するには、2007 年 2 月リリースの Community Technology Preview (CTP) の SQL Server 2005 Best Practices Analyzer を参照してください (最終バージョンは近日リリースされます)。  効率的な集計を定義する
- 集計を定義して、クエリ実行のためにストレージ エンジンがディスクからスキャンする必要があるレコード数を削減します。キャッシュから解決されないユーザー クエリの大半が集計読み取りではなくパーティション読み取りで解決されることが SQL Server Profiler トレースに示されている場合は、Aggregation Manager サンプル アプリケーションを使用してカスタム集計をデザインすることを検討してください。このサンプルは CodePlex (http://www.codeplex.com/ MSFTASProdSamples) から入手できます。また、コミュニティによる更新版のこのサンプルは CodePlex (http://www.codeplex.com/bidshelper) から入手できます。
- 大量の集計をデザインしないでください。集計の数を必要以上にデザインすると、処理パフォーマンスが低下し、場合によってはクエリ パフォーマンスも低下します。最適な集計の数は場合によって異なりますが、SQL Server ベスト プラクティス チームの経験では、ほとんどの場合最適な数は数十であり、数百や数千では多すぎます。
- Analysis Services クエリ ログでユーザー クエリ パターンをキャプチャできるようにして、集計のデザイン時にこのクエリ ログを使用します。詳細については、「Configuring the Analysis Services Query Log」(英語) を参照してください。
集計をデザインしてクエリ パフォーマンスを向上させる方法については、『SQL Server 2005 Analysis Services Performance Guide』および「OLAP Design Best Practices for Analysis Services 2005」(英語) を参照してください。  パーティションを使用する
- データ キャッシュまたは集計からクエリを解決できない場合には、Analysis Services でクエリするデータを少なくするようにパーティションを定義します。また、クエリ解決時に並列処理を増やす
場合もパーティションを定義します。 - パフォーマンスを最適化するために、一般的なクエリに適合する方法でデータをパーティション分割します。パーティションには、時間の要素 (日、月、四半期、年、時間の要素の組み合わせなど) を選択することが最も一般的です。パーティション分割の際には、分割後に実行するほとんどのクエリにおいて、多数のパーティションを使用することなくクエリを解決できるようにしてください。
- 通常、パーティションに含めるレコードは 2,000 万件未満にし、各メジャー グループに含めるパーティションは合計で 2,000 個未満にします。また、パーティションを定義する際は 200 万件以上のレコードを含めるようにします。パーティションが多すぎるとメタデータの操作に時間がかかり、パーティションが少なすぎると並列処理が行われなくなる可能性があります。
- リアルタイム データ用に ROLAP パーティションを個別に定義し、リアルタイム ROLAP パーティションを独自のメジャー グループに配置します。
パーティションをデザインしてクエリ パフォーマンスを向上させる方法については、『SQL Server 2005 Analysis Services Performance Guide』、Microsoft SQL Server Customer Advisory Team のブログ、および「OLAP Design Best Practices for Analysis Services 2005」(英語) を参照してください。  効率的な MDX を記述する
 クエリ エンジンのキャッシュを効率的に使用する
- クエリ結果を格納しておける十分なメモリが Analysis Services コンピュータにあることを確認し、後続のクエリの解決に再利用できるようにします。監視には、MSAS 2005: Memory/Cleaner Memory Shrinkable DB および MSAS 2005: Cache/Evictions/sec パフォーマンス
モニタ カウンタを使用します。 - MDX スクリプトの計算を定義します。MDX スクリプトの計算のスコープは、クエリに関連するキャッシュを同じセキュリティ権限のセッション間で共有できるグローバル スコープです。ただし、ユーザー クエリ内で CREATE MEMBER および WITH MEMBER を使用して定義された計算されるメンバのスコープはグローバル スコープではなく、クエリに関連するキャッシュをセッション間で
共有することはできません。 - 任意のツールを使用して定義済みクエリ セットを実行することで、キャッシュ ウォーミングを行います。また、CREATE CACHE ステートメントを使用してキャッシュ ウォーミングを行うこともできます。CREATE CACHE ステートメントの使用に関する詳細については、「How to Warm up the Analysis Services data cache using Create Cache statement」(英語) を参照してください。SQL Server 2005 Integration Services を使用してキャッシュ ウォーミングを行う方法については、「Build Your Own Analysis Services Cache-Warmer in Integration Services」(英語) を参照してください。
- 任意図形を含む MDX クエリを書き換えてキャッシュを最適化します。たとえば、WHERE 句ではなく FROM 句のサブセレクトを使用することで、非キャッシュ ディスク アクセスを必要とするクエリを書き換えて、キャッシュから完全に解決できるようにすることが可能な場合があります。場合によっては、WHERE 句の方が適切なこともあります。
 クエリへの応答に Flexible 集計を使用できるようにする
- ディメンションで ProcessUpdate を使用してディメンションを増分更新すると、更新と削除の影響を受けるすべての Flexible 集計が削除され、既定では次の完全処理まで再作成されません。
- 影響を受けたオブジェクトを処理するか、レイジー処理を構成するか、影響を受けたパーティションに対して ProcessIndexes を実行するか、影響を受けたパーティションに対して完全処理を実行することによって、集計が再作成されるようにします。
Flexible 集計が削除されないようにする方法については、『SQL Server 2005 Analysis Services Performance Guide』(英語) を参照してください。  メモリ使用量をチューニングする
- 割り当てられた仮想メモリ量が Analysis Services サーバーの物理メモリ量を超える場合、Analysis Services サーバーのページング ファイルのサイズを大きくするかメモリを追加して、
メモリ不足エラーが発生しないようにします。 - SQL Server 2005 (32 ビット) を使用している場合は、SQL Server 2005 Enterprise Edition (または SQL Server 2005 Developer Edition) で Microsoft Windows Advanced Server® または Datacenter Server を使用して、Analysis Services が最大 3 GB のメモリに対応できるようにします。いずれかのエディションで Analysis Services が 2 GB を超える物理メモリに対応できるようにするには、boot.ini ファイルで /3GB スイッチを使用します。boot.ini ファイルで /3GB スイッチを設定する場合、Windows オペレーティング システムにもシステム サービス用の十分なメモリが確保されるようにするため、サーバーには少なくとも 4 GB のメモリが必要です。
- 複数の Analysis Services インスタンスを実行する場合、または同じコンピュータ上で他のアプリケーションを実行する場合は、Memory/LowMemoryLimit プロパティの値を 75% 未満に下げます。
- 複数の Analysis Services インスタンスを実行する場合、または同じコンピュータ上で他のアプリケーションを実行する場合は、Memory/TotalMemoryLimit プロパティの値を 80% 未満に下げます。
- Memory/LowMemoryLimit プロパティと Memory/TotalMemoryLimit プロパティの
値の間に差を設けます (多くの場合、20%)。 - マルチユーザー環境でクエリのスラッシングが検出された場合は、MemoryHeapType の変更について Microsoft サポートに問い合わせてください。
- Non-Uniform Memory Access (NUMA) アーキテクチャでの実行時に、VirtualAlloc が結果を返すのに時間がかかりすぎる場合や VirtualAlloc が応答しなくなる場合は、SQL Server 2005 SP2 にアップグレードして、NUMA メモリの事前割り当てに適した設定について Microsoft サポートに問い合わせてください。
既定のメモリ使用量の変更を検討するタイミングについては、『SQL Server 2005 Analysis Services Performance Guide』および Microsoft SQL Server Customer Advisory Team のブログ
(英語) を参照してください。  プロセッサ使用率をチューニングする
- 複数のプロセッサが搭載されたサーバーでクエリ実行時の並列処理を増やすには、Threadpool\Query\MaxThreads オプションと Threadpool\Process\MaxThreads オプションを、サーバーのプロセッサ数に依存した数になるように変更することを検討します。
- 通常、Threadpool\Query\MaxThreads は、サーバーのプロセッサ数の 2 倍以下の値に設定することをお勧めします。たとえば、8 プロセッサ サーバーの場合、一般的にはこの値を 16 程度に設定します。実際には、Threadpool\Query\MaxThreads オプションの値を増やしても、特定のクエリのパフォーマンスが著しく向上するわけではありません。むしろ、このプロパティの値を大きくする利点は、同時に処理できるクエリの数を増加できることです。
- 通常、Threadpool\Process\MaxThreads オプションは、サーバーのプロセッサ数の 10 倍以下の値に設定することをお勧めします。このプロパティは、クエリ操作時および処理操作時にストレージ エンジンで使用されるスレッド数を制御します。たとえば、8 プロセッサ サーバーの場合、
一般的にはこの値を 80 程度に設定します。既定値は 64 ですが、特定のサーバーのプロセッサ数が 8 より少ない場合、並列処理を減らすために既定値を小さくする必要はありません。 - Threadpool\Process\MaxThreads プロパティや Threadpool\Query\MaxThreads プロパティを変更すると、クエリ実行時の並列処理を増やすことができますが、CoordinatorExecutionMode オプションへの影響も考慮する必要があります。たとえば、4 プロセッサ サーバーで、CoordinatorExecutionMode の設定が既定値の -4 である場合、すべてのサーバー操作に関して一度に実行できるジョブ数は、合計で 16 です。したがって、並列で実行されるクエリが 10 個で、必要な合計ジョブ数が 20 の場合でも、一度に起動できるジョブ数は 16 だけです (そのときに実行されている処理操作がないと仮定した場合)。ジョブがしきい値に達すると、その後のジョブは、新しいジョブを作成できるようになるまで、キューに待機状態となります。そのため、ジョブ数が操作のボトルネックになる場合、スレッド数を増加させても、必ずしも全体的なパフォーマンスが向上するとは限りません。
 可能であれば拡張する
- すべての大規模なシステムで 64 ビット アーキテクチャを使用します。
- メモリおよびプロセッサ リソースを追加し、ディスク I/O サブシステムをアップグレードして、
単一システムのクエリ パフォーマンスのボトルネックを軽減します。 - サーバー間でディメンションまたはメジャー グループをリンクすることは避け、可能であればリモート パーティションは使用しないようにします。これらのソリューションは最適に機能しません。
 拡張できない場合はスケールアウトする
- 単一のシステムにおいて、パフォーマンス ボトルネックがプロセッサ使用率であり、それが複数
ユーザーによるクエリ ワークロードから生じたものである場合、クエリ要求の応答に Analysis Services サーバーのクラスタ化を使用することで、クエリ パフォーマンスを向上させることができます。要求は 2 台の Analysis Services サーバー間で負荷分散でき、多数の同時ユーザーをサポートできます (これは、サーバー ファームと呼ばれます)。負荷分散クラスタは、一般的に線形に拡張されます。 - Analysis Services サーバーのクラスタを使用してクエリ パフォーマンスを向上させる場合は、単一の処理サーバーで処理を実行した後、XMLA Synchronize ステートメントを使用して
処理サーバーとクエリ サーバーを同期するか、Robocopy などのファイル コピー ユーティリティを使用してデータベース ディレクトリをコピーするか、SAN ストレージ ソリューションの高速コピー
機能を使用します。
|
sql, server, best practices, whitepapers, analysis services, data mining, olap, datawarehouse, datawarehousing, availability, clustering, capacity, collation, data types, data warehouse, database, design, index, mirroring, optimization, partitions, performance, precision, processing, querying, scalability, security, reporting services, integration services
|
