ホワイトペーパー（日本語）

Analysis Services のクエリパフォーマンスに関するベストプラクティストップ 10

admin — Sat, 18 Oct 2008 16:08:43 GMT

執筆者 : 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 を記述する

結果セットから空の組を削除して、クエリ実行エンジンによる結果セットのシリアル化にかかる時間を短縮します。
MDX 計算の実行時のチェックは実行パスの低速化につながるので避けてください。CASE および IF ステートメントを使用して、クエリの解析中に何度も判定が必要な条件チェックを実行すると、実行パスは低速になります。このようなクエリは SCOPE ステートメントを使用して書き換え、MDX 計算が参照する計算領域を迅速に削減します。詳細については、「Budget Variance - A study of MDX optimizations: evaluation modes and NON_EMPTY_BEHAVIOR」、「Comparing Levels in MDX and CONDITION vs. SCOPE in cell calculations」、および「Multiselect friendly MDX calculations」(英語) を参照してください。
可能であれば Non_Empty_Behavior を使用し、クエリ実行エンジンで一括評価モードを使用できるようにします。ただし、Non_Empty_Behavior を不適切に使用すると、正しい結果が返されません。詳細については、「Budget Variance - A study of MDX optimizations: evaluation modes and NON_EMPTY_BEHAVIOR」および「Averages, ratios, division by zero and NON_EMPTY_BEHAVIOR」(英語) を参照してください。
実行パスの低速化を防ぐには、メンバのプロパティに対してフィルタ処理ではなく EXISTS を使用します。NonEmpty 関数と Exists 関数を使用し、クエリ実行エンジンで一括評価モードを
使用できるようにします。
StrToMember や StrToSet などの文字列操作関数を使用するのではなく、サーバー側の ADOMD.NET を使用して Analysis Services ストアドプロシージャ内で文字列操作を
実行します。
LookupCube 関数を使用するのではなく、可能であれば同じキューブ内の複数のメジャー
グループを使用します。
可能であれば、任意図形を含む MDX クエリを書き換えて余分なサブクエリを削減します。任意図形セットは、単一階層のセットの crossjoin として解決できないメンバのセットです。たとえば、セット {(Gender.Male, Customer.USA), (Gender.Female, Customer.Canada)} は
任意セットです。多くの場合、Descendants 関数を使用すると、他の関数を使用して記述された同じ結果を返すクエリよりも少ないサブクエリで任意図形を解決できます。
可能であれば、余分なプリフェッチを発生させる MDX クエリを書き換えます。プリフェッチとは、クエリ実行エンジンが (データ) 予測効率上の理由から目下のクエリ解決に必要な量以上の情報をストレージエンジンに要求するケースの説明に使用される用語です。通常、プリフェッチは最も効率的なデータ取得方法です。ただし、常に効率的とは限りません。WHERE 句のセットではなく FROM 句のサブセレクトでクエリを書き換えることによって、余分なプリフェッチを排除できる場合があります。余分なプリフェッチを排除できないときは、プリフェッチを無効にして、CREATE CACHE ステートメントを使用してキャッシュウォーミングを行うことが必要になる場合があります。詳細については、「How to Warm up the Analysis Services data cache using Create Cache statement」(英語) を参照してください。
セットを crossjoin で使用する前にフィルタ処理し、キューブ空間を縮小してから crossjoin
を実行します。

クエリエンジンのキャッシュを効率的に使用する

クエリ結果を格納しておける十分なメモリが 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 ストレージソリューションの高速コピー
機能を使用します。

ストレージに関するベストプラクティストップ 10

admin — Sat, 18 Oct 2008 16:04:56 GMT

I/O サブシステムの適切な構成は、SQL Server システムのパフォーマンスと操作の最適化に欠かせないものです。ここでは、SQL Server のストレージ構成に関して SQL Server チームが推奨する最も一般的なベストプラクティスをいくつか紹介します。

SQL Server の I/O 特性とアプリケーションの特定の I/O 要件や特性を理解する

SQL Server アプリケーションのストレージを適切にデザインして配置するには、アプリケーションの I/O 特性と SQL Server の I/O パターンの基本を理解する必要があります。既存のアプリケーションに関するこの情報をキャプチャするには、パフォーマンスモニタが最適です。次に、ここで確認する必要がある点をいくつか示します。

アプリケーションの読み取りと書き込みの比率。
通常の I/O 速度 (1 秒あたりの I/O 数、I/O の MB/秒とサイズ)。次の perfmon カウンタを監視します。

1 秒あたりの平均読み取りバイト数、1 秒あたりの平均書き込みバイト数
1 秒あたりの読み取り数、1 秒あたりの書き込み数
1 秒あたりのディスク読み取りバイト数 (Avg. Disk Read Bytes/sec)、1 秒あたりのディスク書き込みバイト数 (Avg. Disk Write Bytes/sec)
ディスク読み取りの平均秒数 (Avg. Disk sec/Read)、ディスク書き込みの平均秒数 (Avg. Disk sec/Write)
ディスクキューの平均の長さ (Avg. Disk Queue Length)

実際のシーケンシャル I/O とランダム I/O の量。このアプリケーションが主に OLTP アプリケーションとリレーショナルデータウェアハウスアプリケーションのどちらとして使用されるか。

SQL Server の I/O の主な特性については、「SQL Server 2000 I/O Basics」(英語) を参照してください。

スピンドルを増やすかその速度を上げるとパフォーマンスが向上する

許容可能な待機時間と I/O 要件をサポートできる、十分な数のスピンドルを確保します。
バックアップ、復元、データベースの部分可用性などの管理要件のためにファイルグループを使用します。
データファイルを使用して、データベースを特定の I/O 構成 (物理ディスクや LUN など) 全体に “ストライプ化” します。

ストレージのデザインを “必要以上に” 最適化しようとしない (一般的に、シンプルなデザインの方がパフォーマンスや柔軟性に優れている)

アプリケーションをよく理解していない場合は、オブジェクトを個別のスピンドルに選択配置して I/O を必要以上に最適化しようとしないでください。
あらかじめデータ増加に備えた戦略を立てておきます。データサイズが大きくなると、データファイル、LUN、および RAID グループの増加の管理が難しくなります。後から運用配置でデータファイルまたは LUN のバランスを再調整するよりも、あらかじめデータ増加に備えてデザインすることをお勧めします。

配置前に構成を検証する

SQL Server の配置前に、I/O サブシステムの基本的なスループットテストを実行します。このテストで、許容可能な待機時間と I/O 要件を実現できることを確認します。このテストに使用できるツールの 1 つに SQLIO があります。このツールにはドキュメントが付属しており、I/O サブシステムのテストの基礎が記載されています。SQLIO ディスクサブシステムベンチマークツールをダウンロードしてください。
SQLIO テストを実行する目的は、SQL Server の正確な I/O 特性をシミュレートすることではなく、一般的な SQL Server I/O タイプの I/O サブシステムで実現できる最大スループットをテストすることにあります。
SQLIO の代わりに IOMETER を使用することもできます。

常にログ ファイルを RAID 1+0 (または RAID 1) ディスクに配置して次のことを実現する

ハードウェア障害に対する保護の強化。
書き込みパフォーマンスの向上。
注 : 通常、RAID 1+0 を使用すると、書き込みを集中的に行うアプリケーションのスループットが向上します。パフォーマンスの向上レベルは、ハードウェア製造元の RAID 実装によって異なります。RAID 1+0 の代わりに最もよく使用されるのは RAID 5 です。通常、RAID 1+0 を使用すると、データ保護に使用する他のすべての RAID レベル (RAID 5 を含む) よりも書き込みパフォーマンスが向上します。

物理ディスク レベルでデータとログを分離する

分離できない場合は (統合 SQL 環境など)、I/O 特性を検討して同様の I/O 特性 (すべてのログ)
を共通のスピンドルでグループ化します。
異種ワークロード (I/O や待機時間の特性がまったく異なるワークロード) を組み合わせると、全体的なパフォーマンスが低下する可能性があります (たとえば、Exchange と SQL のデータを同じ物理スピンドルに配置した場合など)。

TEMPDB データベースの構成を検討する

SQL Server のインストール後に、TEMPDB を適切なストレージに移動してサイズを設定しておきます。
TEMPDB を RAID 1+0 に配置すると、(TEMPDB の使用量によっては) パフォーマンスが向上します。
TEMPDB データベースでは、次の項目 8 で説明するように、CPU ごとに 1 つのデータファイルを作成
します。

データ ファイル数と CPU 数を合わせると、割り当てが集中的に行われるワークロードでスケーラビリティが
得られる

ホストサーバー上の CPU ごとに 0.25 ～ 1 のデータファイル (ファイルグループ別) を作成することをお
勧めします。
このことは、特に TEMPDB に当てはまります。TEMPDB では、CPU ごとに 1 つのデータファイルを作成することをお勧めします。
デュアルコアは 2 つの CPU と見なされますが、論理プロセッサ (ハイパースレッディング) は見なされません。

SQL Server の基本を確認する

SQL Server では、空き領域が多いファイルへの割り当てを優先する比例入力アルゴリズムが使用されるので、データファイルのサイズは均等にします。
データとログファイルのサイズを事前に設定します。
AUTOGROW は使用せずに、これらのファイルのサイズ増加には手動で対応します。安全上の理由から AUTOGROW をオンのままにすることはできますが、データファイルのサイズ増加を事前に管理する必要があります。

ストレージ構成の基本を確認する

ストレージ製造元が推奨する最新の HBA ドライバを使用します。
HBA 製品の Web サイトから入手したストレージ製造元固有のドライバを使用します。
I/O ボリュームに応じて HBA ドライバ設定をチューニングします。通常、ストレージ製造元からドライバ固有の設定が指定されます。ただし、多くの場合、キューの深さの既定値は SQL Server の I/O ボリュームをサポートするには不十分です。
ストレージアレイファームウェアが最新の推奨レベルであることを確認します。
マルチパスソフトウェアを使用して HBA および LUN 全体のバランスを保ち、適切に機能していることを確認します。
構成を簡略化し、可用性を高めます。
Microsoft Multipath I/O (MPIO) : 製造元は、Microsoft が提供する Driver Development Kit を利用して Device Specific Module (DSM) を構築します。

大規模リレーショナルデータウェアハウスを構築するためのベストプラクティストップ 10

admin — Sat, 18 Oct 2008 04:59:42 GMT

寄稿者 : Stuart Ozer、Prem Mehra、Kevin Cox

テクニカル レビュー担当者 : Lubor Kollar、Thomas Kejser、Denny Lee、Jimmy May、Michael Redman、Sanjay Mishra

大規模リレーショナルデータウェアハウスの構築は複雑な作業です。この記事では、SQL Server での効率的な
大規模リレーショナルデータウェアハウスの設計に役立つデザイン手法について説明します。ほとんどの大規模データウェアハウスでは、テーブルとインデックスのパーティション分割を使用するので、ここで示す推奨事項の多くにもパーティション分割が含まれています。これらのヒントの大部分は、SQL Server 2005 での大規模データウェアハウスの
構築経験に基づいています。

大きなファクト テーブルのパーティション分割を検討する

50 ～ 100 GB 以上のファクトテーブルのパーティション分割を検討します。
パーティション分割すると、管理が容易になり、多くの場合はパフォーマンスも向上します。
- 高速で、詳細なインデックス管理。
- 柔軟なバックアップと復元のオプション。
- データの高速な読み込みや削除。
1 つのパーティションに限定した場合、クエリの処理が高速になります。
一般に、日付キーに基づいてファクトテーブルをパーティション分割します。
- スライディングウィンドウが可能になります。
(クエリプランによる) Partition Elimination が可能になります。

ファクト テーブルの日付キーにクラスタ化インデックスを作成する

効率的なクエリによって、キューブにデータを格納したり、履歴データスライスを取得したりできます。
特定のバッチウィンドウのデータを読み込む場合は、ファクトテーブルのクラスタ化インデックスに対して、オプションの ALLOW_ROW_LOCKS = OFF と ALLOW_PAGE_LOCKS = OFF を使用します。これにより、クエリ時のテーブルスキャン操作が高速になり、大量更新時の過度なロック動作を回避できます。
各外部キーに非クラスタ化インデックスを作成します。これにより、選択度が高いディメンションの述語に基づいて行をピンポイントで抽出するクエリを実行できます。バックアップと復元、データベースの部分的な可用性などの管理要件に対応してファイルグループを使用します。

パーティションの粒度を慎重に選択する

ほとんどのユーザーは、月、四半期、または年を使用しています。
削除を効率的に行うには、1 つのパーティション全体を一度に削除する必要があります。
1 つのパーティション全体を一度にロードすると、速度が向上します。
- 毎日のデータロードに対して日単位のパーティションを使用することが有効である場合もあります。
- ただし、1 つのテーブルのパーティション数は最大 1,000 個である点に注意してください。
パーティションの粒度はクエリの並列処理に影響します。
- 1 つのパーティションにアクセスするクエリは、MAXDOP (並列処理の最大限度) まで並列化できます。
- 複数のパーティションにアクセスするクエリでは、MAXDOP まで、パーティションごとに 1 つのスレッドを使用します。
MAXDOP の並列処理 (MAXDOP = 4 以上と仮定) が必要な場合は、パーティションの設計において、使用頻度の高いクエリが 2、3 個のパーティションのみにアクセスすることがないようにしてください。

ディメンション テーブルを適切にデザインする

日付ディメンション以外のすべてのディメンションに整数の代理キーを使用します。ディメンションの代理キーで使用できる最小の整数を使用します。これにより、ファクトテーブルの列数を少なくすることができます。
DATETIME データ型から派生できる整数型のわかりやすい日付キーを使用します (例 : 20060215)。
- 日付ディメンションには代理キーを使用しないでください。
- この列に WHERE 句を適用するクエリを簡単に作成できます。これにより、ファクトテーブルの範囲外のパーティションをクエリ対象から除去できるようになります。
各ディメンションテーブルのビジネスキー (代理キーではなく) にクラスタ化インデックスを作成します。
- ファクトテーブルの読み込み中の高速参照がサポートされます。
- タイプ 2 の変化するディメンションを管理する既存のディメンション行の高速参照がサポートされます。
各ディメンションテーブルのディメンションキー (代理キー) に非クラスタ化主キーインデックスを作成します。
他の頻繁に検索されるディメンション列に非クラスタ化インデックスを作成します。
ディメンションテーブルのパーティション分割は行わないでください。
高速なデータロードを行うために、ファクトテーブルとディメンションテーブルの間で外部キー参照制約を結ばないようにしてください。NOCHECK オプションを用いて外部キー制約を作成することができますが、整合性の関係は結ばないでください。参照変換によって整合性を確保するか、データソースに対して整合性チェックを実行します。

パーティションを除去するための効率的なクエリを作成する

できる限り、ファクトテーブルのパーティション分割キー (日付ディメンションキー) にクエリ述語 (WHERE
条件) を直接指定します。

スライディング ウィンドウの手法を使用してデータを管理する

ファクトテーブルへのオンラインアクセスのローリングタイムウィンドウを管理します。最新のデータをロードし、最も古いデータをアンロードします。
パーティション範囲の両端に空のパーティションを常に保持して、パーティションの分割 (新しいデータのロード前) とパーティションのマージ (古いデータのアンロード後) でデータの移動が発生しないようにします。
データが格納されているパーティションの分割またはマージは行わないでください。データが格納されているパーティションを分割またはマージすると、生成されるログが 4 倍以上になり、過剰なロックが発生することもあるため、非常に効率が悪くなる可能性があります。
データをロードするパーティションと同じファイルグループに、いったんデータをロードするための作業テーブル (ステージングテーブル) を作成します。
削除するパーティションと同じファイルグループに、アンロード用の作業テーブルを作成します。
最新のパーティション全体を一度にロードするのが最速ですが、これはパーティション粒度がデータをロードする頻度と同じである場合にのみ可能となります (日単位のパーティションを使用し、1 日 1 回データを読み込む場合など)。
パーティションサイズがデータの読み込み頻度と異なる場合は、最新パーティションの増分読み込みを行います。
パーティションテーブルに一括データロード (バルクロード) するためのさまざまなオプションが、ホワイトペーパー (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/loading bulk
data partitioned table.mspx) で説明されています。
必ず、一度に 1 つのパーティションをアンロードします。

初期データを効率的に読み込む

初期データの読み込み時に、SIMPLE または BULK LOGGED 復旧モデルを使用します。
クラスタ化インデックスを使用してパーティションファクトテーブルを作成します。
インデックス付けされていない作業テーブル (ステージングテーブル) をパーティションごとに作成し、各パーティションにデータを格納するためのソースデータファイルを分割します。

作業テーブル (ステージングテーブル) にデータを並列に格納します。
- 複数の BULK INSERT、BCP、または SSIS タスクを使用します。
  - I/O のボトルネックがなければ、CPU 数と同じ数だけ、並列に実行するデータロードスクリプトを作成します。I/O 帯域幅が制限されている場合は、並列に使用するスクリプトを少なくします。
  - データロードでは 0 のバッチサイズを使用します。
  - データロードでは 0 のコミットサイズを使用します。
  - TABLOCK を使用します。
  - ソースが同じサーバー上のフラットファイルである場合、BULK INSERT を使用します。データがリモートコンピュータからロードされる場合は、BCP または SSIS を使用します。
各作業テーブル (ステージングテーブル) にクラスタ化インデックスを作成し、適切な CHECK 制約を作成します。SORT_IN_TEMPDB オプションは使用しないでください。
すべてのパーティションをパーティションテーブルに切り替えます。
パーティションテーブルに非クラスタ化インデックスを作成します。
スループットが 14 GB/秒 (インデックス付けされていないテーブル) の SAN に対応した 64 CPU サーバーでは、1 TB のデータを 1 時間未満で読み込むことができます。詳細については、SQLCAT のブログ記事 (http://blogs.msdn.com/sqlcat/archive/2006/05/19/602142.aspx) を参照してください。

古いデータを効率的に削除する

できる限りパーティション切り替えを使用します。
数百万行を非パーティションのインデックス付きテーブルから削除する場合、次の点に注意します。
- DELETE FROM ...WHERE ... は使用しないでください。次の問題が発生する可能性があります。
  - 大規模なロックとログの問題。
  - 削除をキャンセルした場合の長時間のロールバック。
- 通常は以下の方法で高速化します。
  - 保持するレコードをインデックス付けされていないテーブルに挿入します。
  - テーブルにインデックスを作成します。
  - 新しいテーブルの名前を変更して元のテーブルと置き換えます。
次の処理をループで繰り返して少しずつ削除する方法もあります。
DELETE TOP (1000) ...;
COMMIT

さらに、行を削除済みとしてマークするように更新して、後で、負荷の少ない時間帯に削除する方法もあります。

統計を手動で管理する

パーティションテーブルの統計は、テーブル全体として管理します。
新しいデータを読み込んだ後、大きなファクトテーブルの統計を手動で更新します。
パーティションのインデックスを再構築した後、統計を手動で更新します。
定期的なデータロード後に統計を定期的に更新する場合は、そのテーブルの統計保守機能をオフにしてもかまいません。
これは、最新データのみを読み取る必要があるクエリを最適化するために重要です。
増分データロード後に小さなディメンションテーブルの統計を更新すると、パフォーマンスが向上する場合もあります。ディメンションテーブルの統計の更新で FULLSCAN オプションを使用すると、クエリプランがより正確になります。

効率的なバックアップの方針を検討する

非常に大規模なデータベースの場合、データベース全体のバックアップにはかなりの時間を要します。
- たとえば、SAN 上で 2 TB のデータベースを 10 スピンドルの RAID 5 ディスクにバックアップすると、2 時間かかる場合があります (275 MB/秒の転送レート)。
SAN テクノロジを使用したスナップショットバックアップは有力なオプションです。
定期的にバックアップするデータの量を減らします。
- 履歴パーティションのファイルグループは、READ ONLY としてマークできます。
- ファイルグループが読み取り専用になったときに、ファイルグループのバックアップを一度だけ実行します。
- 読み取り/書き込みファイルグループについてのみ定期的なバックアップを実行します。
読み取り専用ファイルグループの復元は並列で実行できない点に注意してください。

SAP 向けの SQL Server のメンテナンスに関するベストプラクティストップ 10

admin — Sat, 18 Oct 2008 03:44:04 GMT

執筆者 : Takayuki Hoshino
執筆協力者 : Juergen Thomas
テクニカル レビュー担当者 : Sanjay Mishra

SQL Server は、SAP アプリケーションのデータベースプラットフォームとして非常に適しています。以下の推奨事項は、SAP 実装向けの SQL Server データベースのメンテナンスに関するベストプラクティスの概要をまとめたものです。

データベースの完全バックアップを毎日実行する

技術的には、SAP データベースは何の問題もなくオンラインでバックアップできます。これは、エンドユーザーや夜間のバッチジョブが問題なく SAP アプリケーションを使い続けられることを意味します。SQL Server のバックアップは CPU リソースをほとんど消費しません。ただし、SQL Server は使用されているエクステントをすべてバックアップデバイスに読み取ろうとするため、I/O 帯域幅は必要になります。ビジネスデータ、メタデータ、ABAP アプリケーションなど、SAP に必要なものはすべて “<SID>” という名前の 1 つのデータベースに含まれています。場合によっては、完全バックアップの所要時間 (通常は数時間) が問題になることがあります。特に SQL Server 2000 では、オンラインデータベースバックアップの実行中はトランザクションログバックアップを作成できないため、その可能性が高くなります。この問題は、SQL Server 2005 では発生しません。
SAN テクノロジを使用してオンラインバックアップをより高速に作成できるように、SQL Server には、スナップショットバックアップの実行や SQL Server データベースの複製に使用できる SAN ベンダ向けのインターフェイスが用意されています。ただし、数テラバイトのデータを毎晩バックアップすると、バックアップインフラストラクチャに負荷がかかりすぎる可能性があります。他には、SAP データベースの差分バックアップを毎日実行し、完全バックアップは週末だけ実行するという方法もあります。

トランザクション ログ バックアップを 10 ～ 30 分間隔で実行する

実稼働サーバーで障害が発生した場合に最新の状態を復元できるように、データベースのオンラインバックアップや差分バックアップに加え、できる限り障害発生の直前の時点をカバーする一連のトランザクションログバックアップを使用できるようにしておくことが重要になります。そのためには、トランザクションログバックアップの定期的な実行が不可欠です。トランザクションログバックアップを
2 時間に 1 回しか作成していないと、障害が発生した場合に、最大 2 時間分のコミット済みビジネストランザクションが復元できなくなります。したがって、障害発生時にコミット済みビジネストランザクションが大量に失われるリスクを軽減できるよう、十分な頻度でトランザクションログバックアップを実行することが重要になります。成果の上がっている数々の顧客シナリオでは、10 ～ 30 分という間隔が無難であることが証明されていますが、SQL Server のログ配布と組み合わせると、2 分または 5 分間隔で SQL Server のトランザクションログバックアップを作成できます。SQL Server のトランザクションログバックアップを最も短い間隔で実行できるのは、SQL エージェントでバックアップスケジュールを毎分に設定した場合です。トランザクションログバックアップには、ビジネストランザクションが失われるリスクを軽減する効果に加え、SQL Server トランザクションログのログデータを切り捨てて、トランザクションログがいっぱいになることを防ぐ効果もあります。

構成を変更した場合はシステム パーティションをバックアップする

構成になんらかの変更を加えた場合は、システムパーティションをバックアップしてください。システムパーティションの復元には、Windows Server 2003 の自動システム回復 (ASR) またはその他のツール (Symantec Ghost や SAN ブートなど) を使用します。

構成を変更した場合はシステム データベースをバックアップする

構成になんらかの変更を加えた場合は、システムデータベース (master、msdb、model)
をバックアップしてください。SQL Server 2005 では、リソースデータベースは変更されず、
SQL Server 2005 のインストール時にインストールされるようになっているため、リソース
データベースをバックアップする必要はありません。

DBCC CHECKDB を定期的に実行する (データベースの完全バックアップの前が理想的)

データベースのオンラインバックアップの実行前に DBCC CHECKDB による整合性確認を実行するのが理想的ですが、DBCC CHECKDB は多大な時間とリソースを消費する処理であり、
特に 1 テラバイトを超えるデータベースでは SAP の実稼働システムに高い負荷がかかります。優れた I/O サブシステムを備えた汎用ハードウェアで達成できるスループットは 1 時間あたり 100 ～ 150 GB 程度です。このような I/O スループットに加え、10 テラバイト以上に及ぶ SAP データベースが少なくないという事実を踏まえると、実稼働システムで DBCC CHECKDB を実行するのは必ずしも現実的でないことは明らかです。DBCC CHECKDB を実行しない人が多いのはこのためです。ハードウェアおよびソフトウェアの各コンポーネントの信頼性はいずれもこの 10 年で高まっていますが、物理的な破損の可能性がなくなったわけではありません。物理的な破損の原因の
1 つに、ハードウェアコンポーネントのバッテリバックアップがない状態での突発的な停電があります。もう 1 つの原因としては、接続やハードウェアコンポーネントの物理的な損傷が考えられます。大多数のケースでは、バックアップに戻って SAP データベースを復元し、トランザクションログを最新のものに至るまですべて適用する以外に方法はありません。ただし、物理的な不整合を早い段階で検出したり、バックアップ方式の信頼性を確認したり、物理的な破損の影響を最小限に抑える
ために、以下の 3 つを主な方策として検討する必要があります。
- DBCC CHECKDB の定期的な実行を検討します。実稼働環境の復元イメージを
  実行している開発 (テスト) 環境での実行が考えられます。こうしたシステムでは、DBCC CHECKDB の実行にかかる時間とリソースを気にする必要はなく、実稼働シ
  ステムのユーザーに影響が及ぶことはありません。
- オンラインバックアップまたは差分バックアップとトランザクションログバックアップから SAP データベースを実際に復元できるかどうかをテストします。テープにバックアップがあっても、テープ上で整合性が確保されているとは限らず、テープから読み取れるとも限りません。年月が経てばテープハードウェアの故障やテープカセットの劣化が発生する可能性があります。テープはあってももはや読み取れないという状況は避ける必要があります。バックアップを保存してあっても、障害発生時に復元できるかどうかはまた別の問題です。バックアップを読み取れるかどうかの検証も必要になります。
- テラバイト規模のデータベースの場合は、ログ配布またはデータベースミラーリングのいずれかを使用して、最新状態のデータベースのコピーをもう 1 つ保持しておきます。これらはいずれも可用性の高い方式であり、ハードウェアコンポーネントが分離されるため、待機系サイトにおいて、実稼働データベースと物理的に整合性が保たれたイメージを
  提供することができます。

セキュリティ更新プログラムを毎月評価し、必要に応じてインストールする

可用性は、大多数の SAP 顧客にとって最も重要な要件です。特に、1 つの SAP インスタンスでグローバルにサービスを提供する必要がある場合、セキュリティ更新プログラムの適用のために SAP サーバーを停止して再起動するのは望ましくありません。さらに、こうした環境では、セキュリティ更新プログラムのインストールに先立って必ず一定のテストを行う必要があります。したがって、SAP 顧客にとっては、セキュリティ更新プログラムを慎重に評価してインストールの頻度を減らし、できる限りゼロに近づけるというのが 1 つの現実的なシナリオになります。不要なパケットのフィルタ処理や、不要なサービスの無効化などの方法がセキュリティ対策として有効です。
ウイルス対策としてリアルタイム監視を行っている場合は、SQL Server データベースファイル (データファイル、トランザクションログファイル、tempdb などのシステムデータベースファイルを含む) をリアルタイム監視の対象から除外することをお勧めします。ディスクへのバックアップを実行する場合は、データベースバックアップファイルとトランザクションログバックアップファイルも除外してください。

ハードウェア ドライバとファームウェアの更新モジュールを評価し、必要に応じてインストールする

汎用サーバー内のハードウェアドライバやファームウェアのバグにより、重大な問題が発生するケースが見られます。この種の問題を Microsoft 内部で検出するのは困難な場合があり、さらに、ハードウェア会社が汎用サーバーの顧客に十分なサポートサービスを提供しないこともあるため、顧客側の責任でドライバやファームウェアの定期的な更新管理を行う必要があります。実稼働の汎用サーバーのドライバを更新する際は、あらかじめテストシステムや開発システムで十分な
テストを実施しておく必要があります。他のソフトウェアコンポーネントにはほとんど類例のない現象ですが、ホストバスアダプタ (HBA) や SCSI カードのドライバの小さな不具合はデータベース内の不整合の原因になることがあります。

最も大きなテーブルの統計情報を毎週または毎月更新する

SQL Server には、統計情報を最新に保つためのオプションとして、auto create statistics と auto update statistics の 2 つが用意されています。この 2 つのオプションは、既定で ON になっています。SAP では、これらは ON のままにしておくことを推奨しています。場合によっては、auto update statistics では十分なパフォーマンスを得られないことがあります。具体的には、過去に SAP BW で発生したケースがあります。この問題は、SAP OSS ノート 849062 に記載されている SAP BW の機能によって解決されました。auto update statistics が実行されるのは 500 行を超えるテーブルに限られる点に注意してください。データが 1 方向にのみ展開される一部の非常に特殊なケースでは、テーブルの特定の列を対象として、スケジュールを基に明示的に統計情報の更新を実行することをお勧めします。ただし、通常の手動による統計情報の更新は実行しないでください。パフォーマンスの問題を分析した結果、
根本的な原因がインデックスにあるか、または一部の列統計が最新ではないことにあると判明した場合は、特定の列またはインデックスの統計情報の更新頻度を上げるだけで問題を解決できることがよくあります。

最も重要なインデックスを再構築またはデフラグする

テーブルやインデックスの再構成がパフォーマンスに与える影響は、実行されるクエリの種類や、
システムで利用できる I/O 帯域幅に大きく依存します。単に (1) 平均ページ密度 < 80%、(2) 論理スキャンの断片化 > 40% というようなメジャーをしきい値として再構成を開始するのでは、時間とリソースが有効に活用されません。その理由は次のとおりです。
- 一部の SAP キューテーブルは常に断片化の度合いが高いテーブルとして表示されます。
- SAP クエリの大多数は 1 行または少数の行を読み取るクエリであり、テーブルを再
  構成してもメリットがありません。
- データベースサーバーに SQL Server 用の I/O 帯域幅とメモリが十分にある場合、テーブルの断片化の影響は限定的なものにとどまると考えられます。

クエリのパフォーマンス向上を目的としたテーブルの再構成を一度も実行しない人は珍しくありませんが、その一方で、SAP データのアーカイブ後に圧縮目的でテーブルを再構成する人もいます。すべてのテーブルの構成や並べ替えがアーカイブ条件に従って行われるわけではありません。したがって、テーブルの 25% を削除したにもかかわらずサイズが 10% しか減らないということも起こり得ます。アーカイブ後に使用領域を最小限に圧縮するには、作業を行ったテーブルで DBCC INDEXDEFRAG を実行します。DBCC INDEXDEFRAG により、テーブルのページのデータコンテンツが圧縮されます。DBCC INDEXDEFRAG では、1 つのページに対する一連の行の移動はすべて単一のトランザクションとして扱われます。したがって、DBCC INDEXDEFRAG
を実行すると、小さなトランザクションが多数発生することになります。これはインデックスを作成するとインデックス作成作業全体が 1 つの大きなトランザクションとして扱われるのとは対照的です。DBCC INDEXDEFRAG で消費される CPU リソースは多くありませんが、I/O トラフィックは
大量に発生します。このため、あまり多くの DBCC INDEXDEFRAG コマンドを並列実行しないようにしてください。クラスタ化インデックスの再作成による完全な再構成を大きなテーブルで実行すると膨大な量のトランザクションログが生成されるため、大きなテーブルでは実行しないようにしてください。

パフォーマンス、可用性などの状態を確認する監視ツールを使用する

予定外のダウンタイムがどれだけ長引くかは、管理者がいかに早くシステム障害の通知を受け、
いかに速やかに復旧処理を開始できるかによって決まります。可用性の確保のため、SAP 管理者は、Microsoft クラスタサービス (MSCS) やデータベースミラーリング (DBM) の自動フェールオーバーメカニズムを利用してシステムの継続的な可用性を提供できることを認識しておく必要があります。ただし、フェールオーバー自体がもたらす結果として、データベース側で開かれたトランザクションのロールバックが発生し、それによってさらに SAP 側でビジネストランザクションのロールバックが発生します。こうしたバッチ処理の中断によりデータを使用できなくなると、深刻な影響が発生することがあります (給与計算への影響などが考えられます)。そのような場合、システムの監視とフェールオーバー後の通知は、中断された SAP ビジネスプロセスをできるだけ早く再開するうえできわめて重要な意味を持つことになります。

OLTP アプリケーションに関する SQL Server 2005 パフォーマンスの最もよくある問題

admin — Sat, 18 Oct 2008 03:39:39 GMT

OLTP のワークロードの特徴は、類似した小規模なトランザクションが大量にあるという点です。

この特徴を念頭に置いて、データベースデザイン、リソース使用状況、およびシステムパフォーマンスの重要性について調べる必要があります。OLTP アプリケーションのパフォーマンスに関して上位に挙げられるボトルネックや問題について以下で説明します。

データベース デザインに関する問題の場合

頻繁に使用されるクエリのテーブル結合が多すぎる。OLTP アプリケーションで結合を使用しすぎると、
クエリの実行時間が長くなり、システムリソースを浪費することになります。一般的には、データベースを
再設計して、頻繁に実行される操作で 5 以上のテーブル結合が行われないようにする必要があります。
頻繁に更新される (挿入、更新、および削除を含む) テーブルのインデックスが多すぎるため、インデックス
メンテナンスのオーバーヘッドが余分に発生する。一般的に、OLTP データベースのデザインでは、インデックス数を機能に支障がない範囲で最小限に抑える必要があります。これは、類似したトランザクションが大量にあり、インデックスメンテナンスのコストが発生するためです。
インデックスの欠落によるテーブルスキャンや範囲スキャンなどの大きな I/O。定義上、OLTP トランザクションは大きな I/O を必要としないので、調査する必要があります。
未使用のインデックスによって、ユーザーに利点のない、挿入、更新、および削除のためのインデックスメンテナンスのコストが発生している。未使用のインデックスは除去する必要があります。使用されたインデックス
(選択、更新、または削除で使用) は、sys.dm_db_index_usage_stats に表示されます。したがって、この DMV に含まれていない定義済みインデックスは、SQL Server を最後に再起動してから使用されていません。

CPU ボトルネックの場合

シグナル待機が合計待機時間の 25% を超える。シグナル待機と合計待機時間については、sys.dm_os_wait_stats を参照してください。シグナル待機では、実行可能なキューの CPU 待ち
時間が計測されます。シグナル待機の数値が高い場合は、CPU ボトルネックを示しています。
プランの再使用が 90% 未満。クエリプランは、クエリを実行するために使用されます。(類似または同一のトランザクションに対して) 同じプランを再作成すると CPU リソースが浪費されるので、OLTP のワークロードではプランを再使用するようにします。SQL Server SQL Statistics の Batch Requests/sec と SQL Compilations/sec を比較します。プランの再使用の計算式は、“プランの再使用 = (バッチ要求数 - SQL コンパイル数)/バッチ要求数” です。プランの再使用に関するルールの特別な例外は、SQL Server 2005 SP2 ではゼロコストプランはキャッシュされない (再使用されない) 点です。ゼロコスト
プランを使用するアプリケーションは、プランの再使用を低下させますが、パフォーマンスの問題にはなりません。
並列待機の種類の cxpacket が合計待機時間の 10% を超える。並列処理は、実行速度のために CPU リソースを浪費します。大規模な OLTP である場合、並列クエリは、通常、OLTP のスループットを低下させるので、使用しないでください。待機統計については、sys.dm_os_wait_stats を参照してください。

メモリ ボトルネックの場合

ページの平均有効期間が常に短い。Perfmon オブジェクトの SQL Server Buffer Manager にある Average Page Life Expectancy Counter を参照してください (このカウンタは、ページがキャッシュに保持される平均秒数を表します)。OLTP では、ページの平均有効期間は 300 (5 分) です。これを
下回る場合、メモリ不足、インデックスの欠落、またはキャッシュのフラッシュを示している可能性があります。
ページの有効期間の急激な短縮。OLTP アプリケーション (小規模なトランザクションなど) では、通常、ページの有効期間は安定 (または緩やかに延長) しています。Perfmon オブジェクトの SQL Server Buffer Manager を参照してください。
保留中のメモリ許可。Perfmon オブジェクトの SQL Server Memory Manager で、Memory Grants Pending カウンタを参照してください。小規模な OLTP トランザクションでは、通常、大きなメモリ許可は必要としません。
SQL キャッシュヒット率の急激な低下またはこの値が常に低い。OLTP アプリケーション (小規模なトラン
ザクションなど) では、通常、キャッシュヒット率は高くなります。OLTP トランザクションは小さいので、通常、
(1) SQL キャッシュヒット率の急激な低下、(2) 常に低いキャッシュヒット率 (90% 未満) のいずれも発生しません。キャッシュヒット率の低下や値の低さは、メモリ不足やインデックスの欠落を示している可能性があります。

I/O ボトルネックの場合

ディスクの読み取りあたりの平均秒数が長い。I/O サブシステムがキューに登録されると、ディスクの読み取りあたりの秒数が長くなります。Perfmon の論理ディスクまたは物理ディスク (PhysicalDisk またはLogicalDisk オブジェクトの Avg. Disk sec/Read カウンタ) を参照してください。通常、I/O 負荷がない場合、1 回の読み取りを完了するのに 4 ～ 8 ミリ秒かかります。多数の I/O 要求により I/O サブシステムに負荷がかかっている場合は、1 回の読み取りの完了に要する平均時間が長くなります。これは、ディスクキューの影響を示しています。(Avg.) Disk sec/Read の値が定期的に高くなることは、アプリケーションの多くで許容できます。高パフォーマンスの OLTP アプリケーションでは、高度な SAN サブシステムによって、I/O 使用率の急激な上昇に対処するうえで優れた I/O のスケーラビリティや回復性が提供されます。(Avg.) Disk sec/Read の値が高いままである場合は (15 ミリ秒超)、ディスクボトルネックを示しています。
ディスクの書き込みあたりの平均秒数が長い。パフォーマンスモニタの論理ディスクまたは物理ディスクを参照してください。大規模な OLTP アプリケーションのスループットは、高速なシーケンシャルトランザクションログ書き込みに依存します。トランザクションログ書き込みは、高パフォーマンスの SAN 環境では 1 ミリ秒以下になります。アプリケーションの多くでは、高度な SAN サブシステムの高いコストを考慮すると、Avg. Disk sec/Write の定期的な急上昇は許容できます。ただし、Avg. Disk sec/Write の値が高いままである場合は、ディスクボトルネックの可能性があります。
インデックスの欠落によるテーブルスキャンや範囲スキャンなどの大きな I/O。

sys.dm_os_wait_stats の待機統計の上位項目は、ASYNCH_IO_COMPLETION、IO_COMPLETION、LOGMGR、WRITELOG、PAGEIOLATCH_x などの I/O に関連しています。

ブロッキング ボトルネックの場合

インデックス競合。sys.dm_db_index_operational_stats でロック待機およびラッチ待機について
調べます。ロック要求およびラッチ要求と比較します。
行ロックまたはラッチの平均待機時間が長い。行ロックまたはラッチの平均待機時間は、ロックおよびラッチの待機時間 (ミリ秒) をロックおよびラッチの待機数で除算することによって算出されます。sys.dm_db_index_operational_stats から算出されたロックの平均待機時間 (ミリ秒) は、
各ブロックの平均時間を表します。
ブロックプロセスレポートに長時間のブロックが表示される。sp_configure の “blocked process threshold” の値と、Profiler の Errors and Warnings イベントにある “Blocked Process Report” の値を参照してください。
待機統計の上位項目が LCK_x である。sys.dm_os_wait_stats を参照してください。
デッドロックの数が非常に多い。Locks イベントで Profiler の “Graphical Deadlock” を参照して、デッドロックに関係するステートメントを特定します。

ネットワーク ボトルネックの場合

アプリケーションでデータベースへのラウンドトリップが多数発生し、ネットワークの待機時間が長い。
ネットワーク帯域幅が限界まで使用されている。パフォーマンスモニタの Netwrok Interface オブジェクトの Packets/sec と Current Bandwidth カウンタを参照してください。TCP/IP フレームの場合、実際の帯域幅の計算式は、“パケット/秒 * 1500 * 8/1000000 Mbps” になります。

Hyper-V 環境における SQL Server 2008 のベストプラクティスとパフォーマンスの推奨事項

admin — Thu, 09 Oct 2008 20:04:00 GMT

Published: 2008年10月

Writers: Lindsey Allen, Mike Ruthruff, Prem Mehra

Reviewers: Cindy Gross, Burzin Patel, Denny Lee, Michael Thomassy, Sanjay Mishra, Savitha Padmanabhan, Tony Voellm, Bob Ward

ハイパーバイザテクノロジをベースにする Windows Server 2008 オペレーションシステム (以下、OS) の仮想化機能である Hyper-V はハードウェアと OS との間のソフトウェアの薄いレイヤーであり、ホストコンピュータ上にて複数のOSを変更なしに同時に実行することが可能です。Hyper-Vは強力な仮想化テクノロジで、企業の IT 部門が使用頻度の低いサーバーを統合することで TOC の削減、サービスの品質を維持、向上させることが可能です。Hyper-V はその他の目的、例としてハードウェアが手元にないが、開発・テスト環境が必要な場合にも有効です。一般的に現状のワークロードを統合し、将来のワークロード増加を考慮した正しいハードウェアのサイジング計画は困難です。仮想化が含まれる場合はさらに複雑になります。本ドキュメントの目的は Hyper-V 環境における Microsoft® SQL Server® を実行する以下2つのキーポイントにフォーカスし、それらの解決を手助けすることです: • Hyper-V 環境にてSQL Serverを実行した場合のシステム・リソースのオーバーヘッド • Hyper-Vがどのように効果的に SQL Server 2008をスケールさせるか本ホワイトペーパーには Hyper-V環境にてSQL Server を使用する代表的なシナリオを実行した一連のテスト設定、そのテスト結果、見解、推奨項目が含まれます。私たちのテスト結果では Hyper-V環境における SQL Server 2008は安定したパフォーマンスとスケーラビリティを提供することが確認できました。Windows Server 2008 の Hyper-VはSQL Server 2008の適切なワークロードにとって強力なプラットフォームを提供します。Hyper-V環境において運用環境でのワークロードをゲスト仮想マシンの許容範囲にて実行することは実用的です。

詳しい内容は、本ホワイトペーパーを参照してください: Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations.

照合順序の変更および非 Unicode から Unicode データ型への変更の影響 (Word ファイル, 199KB)

denny.lee — Thu, 17 Jan 2008 23:33:00 GMT

ホワイトペーパー「照合順序の変更および非 Unicode から Unicode データ型への変更の影響」のダウンロード (Word ファイル, 199KB)

このホワイトペーパーでは、テーブル列を非 Unicode データ型から Unicode データ型に変更するのにかかる時間、およびデータベースの照合順序を変更するのにかかる時間を検証します

SQL Server 2005 におけるパフォーマンス問題のトラブルシューティング (英語 - Troubleshooting Performance)

admin — Mon, 17 Dec 2007 02:05:00 GMT

SQL Server ベストプラクティスアーティクル

公開日 : 2005 年 10 月 1 日
執筆者 : Sunil Agarwal、Boris Baryshnikov、Tom Davidson、Keith Elmore、Denzil Ribeiro、Juergen Thomas

SQL Server データベースで一時的に処理が遅くなるという状況は珍しくありません。多くの場合、適切に設計されていないデータベース、またはワークロードを十分考慮して構成されていないシステムが、このようなパフォーマンス問題の原因になっていると考えられます。管理者は問題の発生を回避する、または最小限に抑える措置を事前に行う必要があります。また、問題が発生した場合は、原因を診断して問題を修正するための処理を実行する必要があります。このホワイトペーパーでは、SQL Server 2005 の SQL Server Profiler、システムモニタ、新しい動的管理ビューなど公開されているツールを使用して、一般的なパフォーマンス問題の診断およびトラブルシューティングの手順についてガイドラインを示します。

詳細については、「SQL Server 2005 におけるパフォーマンス問題のトラブルシューティング」ホワイトペーパーを参照してください。

TEMPDB の容量計画、およびインデックス作成と再構築の同時実行に関する考慮事項 (英語)

admin — Mon, 17 Dec 2007 02:04:00 GMT

SQL Server ベストプラクティスアーティクル

公開日 : 2007 年 9 月
執筆者 : Sanjay Mishra
テクニカルレビュー担当者 : Tom Davidson、Mark Souza、Prem Mehra、Sunil Agarwal、Mike Ruthruff

インデックスの作成および再構築は、最も一般的なデータベース管理タスクの一部です。アプリケーション要件およびデータ量の変化に応じて、データベース管理者は定期的なインデックスメンテナンススケジュールを作成する必要があります。インデックスのメンテナンス計画を立てるときに考慮すべき主要な要素には、パフォーマンス、同時実行、必要なリソースなどがあります。SQL Server 2005 では、パフォーマンス、同時実行、およびリソースの要件を効果的に満たすことができるように、インデックスの作成や再構築のためのオプションがいくつか用意されています。

詳細については、「TEMPDB の容量計画、およびインデックス作成と再構築の同時実行に関する考慮事項」ホワイトペーパーを参照してください。

SQL Server 2005 での待機とキュー (英語 - Waits and Queues)

admin — Mon, 17 Dec 2007 02:03:00 GMT

SQL Server ベストプラクティスアーティクル公

開日 : 2006 年 11 月
執筆者 : Tom Davidson
更新者 : Danny Tambs
テクニカルレビュー担当者 : Sanjay Mishra

このホワイトペーパーでは、「待機とキュー」と呼ばれる手法を使用した Microsoft® SQL Server™ のパフォーマンスチューニングの複雑な領域について説明します。この手法を使用すると、パフォーマンスを向上させるための最適な機会を特定することができ、いわゆる "コストに見合う結果" が得られます。改善点として、パフォーマンスチューニングにかかる時間を短縮できるというメリットが期待できます。この手法では問題を 2 つの方向から見ることで、パフォーマンスが遅くなっている領域を識別します。2 つのソース (待機とキュー) からのデータを相互に関連付けることにより、問題のある領域を特定することができます。待機の分析の結果には、SQL Server で待機の時間が長くなっている場所が示されます。また、待機が最大化するポイントは、当該ワークロードの最も重要な、関連性のあるキュー (つまり、パフォーマンスモニタのカウンタなどのデータ) を示しています。待機分析を相互検証することにより、最も重要なパフォーマンスカウンタ以外をすべて消去し、特定のリソースにかかっている負荷を明確に示すことができます。

つまり、待機とキューの手法を使用したパフォーマンスチューニングは、アプリケーションのパフォーマンスに関する問題を迅速に識別して解決する効果的な方法といえます。この手法により、ユーザーは、アプリケーションやソリューションで発生する新しい問題または潜在的な予期しない問題の領域を検出できます。このとき、問題解決の作業で生じる根拠のない推測を行う必要はありません。詳細については、「SQL Server 2005 での待機とキュー」ホワイトペーパーを参照してください。

SQL Server 2005 における大きなカタログのフルテキストクエリ : 注意点 (英語)

admin — Mon, 17 Dec 2007 02:02:00 GMT

公開日 : 2007 年 6 月 11 日執筆者 : Ron Talmage (Solid Quality Learning)

テクニカルレビュー担当者 : Prem Mehra、Jingwei Lu、James Podgorski、Sanjay Mishra

このホワイトペーパーで示される調査結果は、SQL Server Customer Lab で実施されたテストに基づいています。このテストは、フルテキスト検索をスケールアップし、より多くのボリュームに対して実行することを必要としている顧客向けに行われたものです。このホワイトペーパーでは、そのような顧客に関するシナリオを紹介し、クエリ結果に関連する SQL Server 2005 のフルテキストの概念について簡単に説明します。また、大きなカタログでフルテキストクエリを使用する場合の注意点および推奨事項についても説明します。このホワイトペーパーの内容

概要
顧客シナリオ
SQL Server のフルテキスト検索について
推奨事項
結論

このホワイトペーパーへのリンク : SQL Server 2005 における大きなカタログのフルテキストクエリ : 注意点

Web ホスティング環境での SQL Server 2005 の配置ガイダンス (英語)

admin — Mon, 17 Dec 2007 02:01:00 GMT

更新日 : 2007 年 7 月執筆者 : Alex DeNeui

テクニカルレビュー担当者 : Bala Neerumalla、Rusland Ovechkin、Faith Allington、Mark Lium

SQL Server 2005 は、共有および専用の Web ホスティング環境において理想的なデータベースプラットフォームです。このホワイトペーパーでは、セキュリティ、テナントの分離、およびホストする SQL Server 2005 の配置のパフォーマンスの最適化を目的とした、SQL Server 2005 を構成するためのベストプラクティスについて説明します。共有ホスティングで使用できるようにユーザーとデータベースを準備するためのサンプルスクリプトも示します。このガイダンスに従うと同時に、SQL Server 2005 に関する慎重な計画と準備が必要です。このホワイトペーパーの内容

概要
サーバーの準備
ユーザーの準備
まとめ
付録 : 準備用スクリプト

完全なホワイトペーパーへのリンク : Web ホスティング環境での SQL Server 2005 の配置ガイダンス

SAN スナップショットを使用した Analysis Services でのスケールアウトクエリ (英語)

admin — Mon, 17 Dec 2007 01:59:00 GMT

SQL Server ベストプラクティスアーティクル

公開日 : 2007 年 9 月
執筆者 : Carl Rabeler
執筆協力者 : Eric Jacobsen、Ron Talmage、Jon Danskin
テクニカル レビュー担当者 : Denny Lee、Eric Jacobsen、Nicholas Dritsas、Bill Plein

この SQL Server ベストプラクティスホワイトペーパーでは、Microsoft SQL Server 2005 Analysis Services 用の負荷分散されたスケーラブルなクエリ環境で仮想コピー SAN スナップショットを使用し、クエリごとの応答時間を短縮して Analysis Services サーバーに対する同時実行クエリの数を増やす方法について説明します。負荷分散されたクエリは、利用可能なサーバー間ですべてのクエリの負荷を分散させます。仮想コピー SAN スナップショットを使用すると、Analysis Services 処理サーバーと 1 つ以上の Analysis Services 専用クエリサーバーとの間でデータをコピーする必要がなくなります。このホワイトペーパーは、「Analysis Services でのスケールアウトクエリ」ホワイトペーパーを補完するものです。詳細については、「SAN スナップショットを使用した Analysis Services でのスケールアウトクエリ」ホワイトペーパーを参照してください。

SAP と Microsoft SQL Server 2005 : 可用性、パフォーマンス、スケーラビリティを高めるベストプラクティス (英語)

admin — Mon, 17 Dec 2007 01:58:00 GMT

SQL Server ベストプラクティスアーティクル

公開日 : 2006 年 6 月 30 日 (第 2 版)
執筆者 : Juergen Thomas
テクニカルレビュー担当者 : Bernardo Zamora、Sanjay Mishra、Elke Bregler、Clas Hortien、Martin Merdes Dr. Christian Hiller

このホワイトペーパーでは、お客様、システムインテグレータ、およびパートナーが SQL Server 2005 を使用することによって、信頼性と可用性を高めた SAP 実装を設計およびインストールし、パフォーマンス、スケーラビリティ、およびセキュリティを向上させることができるベストプラクティスについて説明します。このホワイトペーパーでは、標準的なアーキテクチャ、インストール、構成、およびパフォーマンス監視とパフォーマンスチューニングを取り上げています。また、SAP BW および 64 ビットコンピューティングの構成に固有の注意点も示されています。詳細については、「SAP と Microsoft SQL Server 2005 : 可用性、パフォーマンス、スケーラビリティを高めるベストプラクティス」ホワイトペーパーを参照してください。

配置前の I/O のベストプラクティス (英語)

admin — Mon, 17 Dec 2007 01:56:00 GMT

公開日 : 2007 年 6 月 5 日

執筆者 : Mike Ruthruff

執筆協力者 : Michael Thomassy、Prem Mehraテクニカルレビュー担当者 : Robert Dorr、Stuart Ozer、Juergen Thomas、Lindsey Allen、Burzin Patel、Bill Emmert、Bruce Worthington、Sanjay MishraMicrosoft® SQL Server™ のパフォーマンスに影響を与える主要なコンポーネントの 1 つは I/O サブシステムです。SQL Server 用の新しいサーバーを構成する場合、または既存のシステムのディスク構成に追加や変更を行う場合は、SQL Server を配置する前に I/O サブシステムの容量を確認しておくことをお勧めします。このホワイトペーパーでは、I/O サブシステムの容量を検証し、確認するためのガイダンスを提供します。このタイプのテストを実行するときに使用できるツールがいくつかあります。このホワイトペーパーでは、SQLIO.exe ツールを中心に説明します。また、利用可能なすべてのツールについても比較して解説します。このホワイトペーパーに示した多くの情報は、SQLIO ツールのダウンロードに含まれているドキュメントに記載されていますが、このホワイトペーパーには既存の情報の更新および追加情報が含まれています。たとえば、最新の調査に基づいた、SQL Server に関連する基本的な I/O 構成のベストプラクティスなどです。このホワイトペーパーの内容

概要
I/O の容量の確認
ディスク構成のベストプラクティスおよび一般的な問題点
SQLIO
システムモニタを使用した I/O パフォーマンスの監視
結論
リソース

完全なホワイトペーパーへのリンク : 配置前の I/O のベストプラクティスその他のリソース

SQLIO ディスクサブシステムベンチマークツール (Microsoft ダウンロードセンター)
SQLIOSim ユーティリティを使用してディスクサブシステム上の SQL Server の動作をシミュレートする方法 (サポート技術情報の記事)
IOMeter (IOMeter サイト)
SQL Server Always On パートナープログラム (Microsoft SQL Server サイト)
SQL Server 都市伝説 (製品サポートサービス (PSS) エンジニアブログ)
SQL Server 2000 I/O Basics
SQL Server I/O Basics 第 2 章
Windows のディスクサブシステムパフォーマンス分析 (Windows Hardware Developer Center)
Windows Server 2003 の Storport : ハードウェア RAID およびストレージエリアネットワークにおける管理の容易性とパフォーマンスの向上 (Windows Storage Server ホワイトペーパー)

ホワイトペーパー（日本語）

Analysis Services のクエリ パフォーマンスに関するベスト プラクティス トップ 10

ストレージに関するベスト プラクティス トップ 10

大規模リレーショナル データ ウェアハウスを構築するためのベスト プラクティス トップ 10

SAP 向けの SQL Server のメンテナンスに関するベスト プラクティス トップ 10

OLTP アプリケーションに関する SQL Server 2005 パフォーマンスの最もよくある問題

OLTP のワークロードの特徴は、類似した小規模なトランザクションが大量にあるという点です。

Hyper-V 環境における SQL Server 2008 のベストプラクティスとパフォーマンスの推奨事項

照合順序の変更および非 Unicode から Unicode データ型への変更の影響 (Word ファイル, 199KB)

SQL Server 2005 におけるパフォーマンス問題のトラブルシューティング (英語 - Troubleshooting Performance)

TEMPDB の容量計画、およびインデックス作成と再構築の同時実行に関する考慮事項 (英語)

SQL Server 2005 での待機とキュー (英語 - Waits and Queues)

SQL Server 2005 における大きなカタログのフルテキスト クエリ : 注意点 (英語)

Web ホスティング環境での SQL Server 2005 の配置ガイダンス (英語)

SAN スナップショットを使用した Analysis Services でのスケールアウト クエリ (英語)

SAP と Microsoft SQL Server 2005 : 可用性、パフォーマンス、スケーラビリティを高めるベスト プラクティス (英語)

配置前の I/O のベスト プラクティス (英語)

Analysis Services のクエリパフォーマンスに関するベストプラクティストップ 10

ストレージに関するベストプラクティストップ 10

大規模リレーショナルデータウェアハウスを構築するためのベストプラクティストップ 10

SAP 向けの SQL Server のメンテナンスに関するベストプラクティストップ 10

SQL Server 2005 における大きなカタログのフルテキストクエリ : 注意点 (英語)

SAN スナップショットを使用した Analysis Services でのスケールアウトクエリ (英語)

SAP と Microsoft SQL Server 2005 : 可用性、パフォーマンス、スケーラビリティを高めるベストプラクティス (英語)

配置前の I/O のベストプラクティス (英語)