SQL Server のことなら SQL Quality SQL Server パフォーマンス チューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム > 技術情報 > SQL Server 2012 自習書 No.12 Analysis Services によるインメモリ BI 入門

SQL Server 2014 実践シリーズ (HTML 版)
「No.1 インメモリ OLTP 機能の実践的な利用方法」

松本美穂と松本崇博が執筆した SQL Server 2014 実践シリーズの「No.1 インメモリ OLTP 機能の実践的な利用方法」の HTML 版です。 日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

4.10 性能検証をする上での注意点

インメモリ OLTP では、性能検証をする上での注意点が多くあるので、ここでまとめておきます。また、性能検証結果(ベンチマーク結果)を第3者へ公開することは、SQL Server 2014 の使用許諾契約書違反になるので、検証結果をブログやツイッター、Facebook などの SNS、雑誌記事、技術カンファレンス/セミナーで公開することも規約違反になります。今回のドキュメントでは、一部実測値を掲載していますが、これは許可をいただいています。

◆ 性能検証の注意点

性能検証をする上では、次の点に注意する必要があります。

  • SQL Server では、インメモリ OLTP だけでなく、従来ながらのディスク ベースでも、単一ステートメントの実行速度はマイクロ秒レベルになる。
    測定ツールや測定コマンドは、ミリ秒単位での測定しかできないものが多い(例えば、.NET Stopwatch クラスや SET STATISTICS TIME コマンドでは、ミリ秒単位での測定しかできず、マイクロ秒での計測ができない。詳しくは後述)
  • .NET アプリケーションで計測する場合は、デバッグ実行ではなく、Release ビルド(.exe)で実行しないと本当の性能差を確認できない(詳しくは後述)
  • SELECT ステートメントの検証では、結果件数が 0件になるような検索では、正しい性能検証にはならない(乱数を利用する場合には、結果件数が 1件以上になるように考慮する必要がある)
  • Management Studio のクエリ エディターSELECT ステートメントの検証を行う場合には、結果件数が多い場合に、描画の負荷がかかることや、結果をファイルへ書き込む負荷があることに注意する(描画やファイル書き込みがボトルネックになって、本来の性能差を確認することができない)
  • Transact-SQL ステートメントで WHILE ループを利用する場合は、SET NOCOUNT ON を付けて、件数カウントを無効にすることで、件数カウントのオーバーヘッドを軽減しておく。また、SET STATISTICS TIME ON していると、速度計測および計測結果を描画するオーバーヘッドが発生するので、利用しないようにする(詳しくは後述)
  • SQL の直接実行(Ad hoc)と sp_executesql(パラメーター化)では性能差が異なるので注意する(詳しくは後述)
  • ostress ツールでは負荷のかけ方が弱いことがある(詳しくは後述)
  • 市販/オープン ソースの負荷テスト ツールには、負荷のかけ方が弱いものがある(負荷のかけ方が弱いと、本当の性能差を確認することができない)
  • BUCKET_COUNT(HASH インデックスのバケット数)を適切な値に設定しておかないと、本来の性能が出ないことに注意する(前項を参照)
  • ネイティブ コンパイル SP は、SQL Server サービスの再起動(データベースの復旧)を行った場合に、最初の実行時(ストアド プロシージャの呼出し時)に DLL の再作成(再コンパイル)が行われる(=初回実行が遅くなる)。なお、作成時(CREATE PROC 時)にも DLL は作成される。
    詳しくは、オンライン ブックの「メモリ最適化テーブルのクエリ処理のガイド」を参照
    http://msdn.microsoft.com/ja-jp/library/dn205319.aspx
  • インデックスを付与していない列GROUP BY WHERE 句の検索条件に指定すると、最初の実行時に統計が自動作成される(=初回実行が遅くなる)。
    統計については、オンライン ブックの「メモリ最適化テーブルの統計」も参照
    http://msdn.microsoft.com/ja-jp/library/dn232522.aspx
  • SQL Server への接続の Open/Close をしているかどうか、接続プール(Connection Pool)を利用しているかどうかで性能が変わってくる
  • アプリケーションからの実行方法の違いによって、性能差が現れる(文字列連結での実行だと遅くなる/SqlParameter を利用しないと遅くなるなど。第2章を参照)
  • データ型の違いによって、性能差が現れる(暗黙の型変換が発生すると、桁違いに遅くなるなど。第2章を参照)
  • ラッチ待ちロック待ちは、多重度を上げたテストでないと確認することができない
  • ファイルの自動拡張が発生すると、性能が大きく低下するので、事前にサイズを大きくしておくようにする(特に、トランザクション ログ ファイルの自動拡張が発生した場合は、その間のトランザクションが完全にブロックされてしまうので、自動拡張が発生しないように、事前に大きくしておくことが重要)
  • 瞬時初期化を有効にしておかないと、データ ファイルへの書き込みが遅くなるので、有効化しておくことが重要(第3章を参照)
  • データ ファイルログ ファイルを異なる RAID セットに配置するかどうかで性能が変わってくる(特に、チェックポイント処理で性能差が現れる)
  • HDD SSD では、性能結果が変わってくる(特に、チェックポイント処理で性能差が現れる。∵ディスク ベースでのチェックポイント処理はランダム書き込みのため)
  • 新しいデータベースRESTORE DATABASE ではなく、CREATE DATABASE で作成した新規 DB、復旧モデルが完全)で検証する場合は、一度もバックアップを行っていない場合には、ログの切り捨て(チェックポイント時のログ切り捨て)が行われるので、実際のアプリケーションおよびログの肥大化を正確にシミュレートするには、一度フル バックアップを取得しておく必要がある
  • 数年前のノートPC を利用している場合には、Intel SpeedStep テクノロジーが有効になっていると、正しい実行時間を計測できない場合があるので、正確な計測を行うには、この機能を無効化しておく必要がある

このように、インメモリ OLTP を検証する上では、多くの注意点があります。インメモリ OLTP やディスク ベースにおける SQL ステートメントの実行は、本来の性能とは関係ないものがボトルネックになっている場合があり、それに足を引っ張られて、性能差を確認できないことがあります。特に SQL Server をインストールしただけのデフォルト構成や、何も指定せずにデータベースを作成した場合にはこの状態になります。例えば、ログ ファイルの自動拡張が発生してしまえば、それがボトルネックになって、インメモリ OLTP でもディスク ベースでも同じような性能結果になってしまいます。また、.NET アプリケーションでデバッグ実行を利用していると、デバッグ実行のオーバーヘッドがボトルネックになり、本当の性能差を確認することができません。

人間の目で見た感じでは同じような実行速度に見えても(例えば、目視だと 100ミリ秒も 10マイクロ秒も同じように見えます)、インメモリ OLTP とディスク ベースの性能差は、マイクロ秒レベルでの差になるので、非常に大きな性能差になっているという場合があります。例えば、100ミリ秒と 10マイクロ秒では 1万倍もの差があるわけです。この性能差は、同時実行数が増えれば増えるほど、大きな差になるので、クリティカルなアプリケーションであれば見逃すことができなくなります。

◆ マイクロ秒の測定ができないツールやコマンドに注意

SQL Server では、OLTP 系の小さいトランザクションの単一ステートメントは、マイクロ秒レベル(ミリ秒以下)で実行が完了します。測定ツールや測定コマンドによっては、ミリ秒単位での測定しかできないものが多いので注意が必要です。

例えば、SET STATISTICS TIME コマンドではマイクロ秒の計測ができないので、マイクロ秒で完了したステートメントは、次のように 0ミリ秒と表示されます。

00314

また、.NET Stopwatch クラスも、マイクロ秒の計測ができません

00315

マイクロ秒で完了したステートメントは、0ミリ秒と表示されます。

このように、単体のステートメントの性能を測定する場合には、マイクロ秒での計測ができるものを利用する必要があることに注意してください。

SQL Server の標準のツールとしては、SQL Server Profiler ツールを利用することで、マイクロ秒の測定をすることが可能です(既定ではミリ秒単位)。SQL Server Profiler ツールでは、次のように[ツール]メニューの[オプション]から「実行時間列の値をマイクロ秒で表示する」をチェックすることで、マイクロ秒の測定ができるようになります。

00316

なお、数年前のノート PC などで検証する場合には、Intel SpeedStep テクノロジーが有効になっていると、SQL Server Profiler を利用しても、正しい実行時間を計測できない場合があるので、正確な計測を行うには、この機能を無効にしておく必要があります。

◆ .NET アプリはデバッグ実行ではなく Release ビルドにすること

.NET アプリケーションで、性能を測定する場合は、デバッグ実行ではなく、Release ビルド(.exe)で実行しないと、本当の性能差を確認できないことに注意する必要があります。例えば、次のように、[デバッグ]メニューから[デバッグ開始]をクリックすると、マイクロ秒で完了する処理も、ミリ秒以上かかってしまいます。

00317

Release ビルド(.exe)を作成するには、次のように[ビルド]メニューから[構成マネージャー]をクリックして、[アクティブ ソリューション構成]を「Release」へ変更します。

00318

◆ Transact-SQL の WHILE ループでの注意点

Transact-SQL ステートメントで WHILE ループを利用して、性能を計測する場合は、既定では1つの SQL ステートメントの実行ごとに「~件処理されました」メッセージが表示されるので、これによって実行速度が遅くなります。

00319

これを回避するには、SET NOCOUNT ON を実行して、件数カウントを無効にするようにします。

また、SET STATISTICS TIME ON している場合も、1つの SQL ステートメントごとに、速度計測および計測結果を描画するオーバーヘッドが発生するので、WHILE ループを利用している場合は、SET STATISTICS TIME は利用しないようにしましょう。

00320

◆ SQL の直接実行と sp_executesql では性能差が異なることに注意する

SQL の直接実行(Ad hoc)と sp_executesql(SQL のパラメーター化実行)では、Ad hoc のほうが良い性能になるので、注意が必要です。これは次のような状況です。

00321

このように、sp_executesql を利用した場合は、直接実行した場合よりも遅くなります。しかし、.NET Java アプリケーションから SQL を実行する場合には、内部的に sp_executesql に変換されて実行されているので(第2章を参照)、アプリケーションの性能検証が目的である場合には、sp_executesql を利用するのが正確な結果になります。

00322

◆ ostress ツールによる負荷テスト実行時の注意点

ostress ツールは、RML Utilities に含まれる負荷テスト ツールで、次の URL からダウンロードすることができます。

Cumulative Update 2 to the RML Utilities for Microsoft SQL Server Released
http://blogs.msdn.com/b/psssql/archive/2013/10/29/cumulative-update-2-to-the-rml-utilities-for-microsoft-sql-server-released.aspx

00323

ostress ツールは、次のように実行することができます(ナンバリングの処理のところでも少し紹介しました)。

ostress.exe -n多重度 -r繰返し回数 -Sサーバー名 -E -dデータベース名 -q -Q"クエリ"

-n で多重度、-r で繰り返し実行する回数、-S で接続先となる SQL Server、-E で Windows認証、-d でデータベース、-q で quietモード、-Q へ負荷テストをしたいクエリを記述します。

例えば、次のように実行できます(RML Cmd Prompt から実行)。

00324

ostress.exe -n400 -r125 -S-E -dHekatonTestDB_HDD2 -q -Q"INSERT INTO OnDisk DEFAULT VALUES"

これは、400多重で、それぞれ 125回繰り返し実行、ローカル SQL Server へ接続(. と指定)、HekatonTestDB_HDD2 データベースへ接続、OnDisk テーブルへ INSERT を実行するものです。これにより、400*125=50,000回INSERT OnDisk テーブルに対して実行されます(以下)。

00325

このように、ostress ツールを利用すると、簡単に多重実行を試すことができるのですが、次の注意点があります。

  • SQL の直接実行sp_executesql(パラメーター化)では、実行速度が異なる
  • 日本語のオブジェクト名は [ ] で囲む、日本語の文字列には N を付ける必要がある
  • 多重度が低い場合(1~100ぐらいまで)は、負荷のかけ方が弱いことがある(実行するクエリが軽い場合には、リソースを持て余してしまう)
  • クエリの実行ごとに接続の Open と Close を行わない-r の多重度に応じて、最初に接続を作り、それをキープして、-n の回数分それぞれの接続からクエリを実行する)

SQL の直接実行sp_executesql の違いは、次のとおりです。

00326

-Q へ指定するクエリに sp_executesql を利用するかどうかで、実行結果が変わってくる(SQL の直接実行のほうが性能が良い)ので、アプリケーションをシミュレーションする場合には、sp_executesql を利用するようにしないと正確な性能測定ができません。

ostress ツールでは、日本語のオブジェクト名は [ ] で囲み、日本語の文字列には N プレフィックスを付けて実行する必要があります。これを行わない場合は、次のようにエラーになります。

00327

ostress ツールの負荷のかけ方が弱いことについては、第1章で説明した 5,000万件の Insert を例を説明します。これと同じ Insert ostress ツールで実行すると次のようになります。

00328

このテストでは、多重度 40~100 ぐらいまでは、負荷のかけ方が弱いことが分かります(200 では、NonDurable への負荷のかけ方が弱い)。

弊社の負荷テスト ツール(接続キープ版)での結果と比較すると、次のようになります。

00329

弊社の負荷テスト ツールのほうが、すべてのパターンで処理時間が速いことを確認できると思います。400多重の結果は、近い実行時間になっているので、単純なクエリ(このテストは 10列のテーブルに対する乱数を利用した INSERT。詳細は第1章を参照)では、多重度を上げたほうが良い結果となります。

いずれにしても、負荷テストに関しては、各システム/実際のアプリケーションで利用している状況をシミュレートできるものを利用することが一番です。市販/オープン ソースの負荷テスト ツールでは、負荷のかけ方が弱いものが多くあるので、いかに実際の利用状況をシミュレートできるかどうかが重要になります。

目次へ | 前のページへ | 次のページへ

事例1

MPNロゴ


SQLQualityは執筆とセミナーを通じて技術の啓蒙やエンジニアの育成支援も行っています
最新刊
SQL Server 2012 の教科書
SQL Server 2012 の教科書(ソシム)

弊社オリジナル制作の
SQL Server 2012 自習書も
マイクロソフトのサイトで公開中!
ロングセラー
ASP.NET でいってみよう  SQL Server 2000 でいってみよう
ASP.NET でいってみよう
第7刷 16,500 部発行
SQL Server 2000 でいってみよう
第12刷 28,500 部発行


セミナー風景
セミナー風景

弊社執筆の
SQL Server 2012 自習書
マイクロソフトのサイトで公開中
全30冊
ダウンロードはこちら
弊社執筆の
SQL Server 2008 R2 自習書
マイクロソフトのサイトで公開中
全30冊
目次はこちら
松本美穂のコラム
(公開活動などのお知らせ)

第 51 回:PASS Summit と MVP Summit で進化を確信!
第 50 回:新しくなった Power BI(2.0)の自習書を作成しました!
第49 回:Excel 2016 の Power Query を使う
第 48 回:新しくなった Microsoft Power BI ! 無料版がある!!
第 47 回:「Microsoft Azure SQL Database 入門」 完成&公開!
第 46 回:Microsoft Power BI for Windows app からの Power BI サイト アクセス
第 45 回:Power Query で取得したデータを PowerPivot へ読み込む方法と PowerPivot for Excel 自習書のご紹介
第44回:「SQL Server 2014 への移行とアップグレードの実践」ドキュメントを作成しました
第43回:SQL Server 2014 インメモリ OLTP 機能の上級者向けドキュメントを作成しました
第42回:Power Query プレビュー版 と Power BI for Office 365 へのクエリ保存(共有クエリ)
第41回:「SQL Server 2014 CTP2 インメモリ OLTP 機能の概要」自習書のお知らせです
第40回: SQL Server 2012 自習書(HTML版)を掲載しました
第39回: Power BI for Office 365 プレビュー版は試されましたか?
第38回: SQL Server 2014 CTP2 の公開
第37回: SQL Server 2014 CTP1 の自習書をご覧ください
第36回: SQL Server 2014 CTP1 のクラスター化列ストア インデックスを試す
第35回: SQL Server 2014 CTP1 のインメモリ OLTP の基本操作を試す
第34回: GeoFlow for Excel 2013 のプレビュー版を試す
第33回: iPad と iPhone からの SQL Server 2012 Reporting Servicesのレポート閲覧
第32回: PASS Summit 2012 参加レポート
第31回: SQL Server 2012 Reporting Services 自習書のお知らせ
第30回: SQL Server 2012(RTM 版)の新機能 自習書をご覧ください
第29回: 書籍「SQL Server 2012の教科書 開発編」のお知らせ
第26回: SQL Server 2012 の Power View 機能のご紹介
第25回: SQL Server 2012 の Data Quality Services
第24回: SQL Server 2012 自習書のご案内と初セミナー報告
第23回: Denali CTP1 が公開されました
第22回 チューニングに王道あらず
第21回 Microsoft TechEd 2010 終了しました
第20回 Microsoft TechEd Japan 2010 今年も登壇します
第19回 SQL Server 2008 R2 RTM の 日本語版が公開されました
第18回 「SQL Azure 入門」自習書のご案内
第17回 SQL Server 2008 自習書の追加ドキュメントのお知らせ
第16回 SQL Server 2008 R2 自習書とプレビュー セミナーのお知らせ
第15回 SQL Server 2008 R2 Reporting Services と新刊のお知らせ
第14回 TechEd 2009 のご報告と SQL Server 2008 R2 について
第13回 SQL Server 2008 R2 の CTP 版が公開されました
第12回 MVP Summit 2009 in Seattle へ参加

技術コミュニティでも活動中

Microsoft MVP for SQL Server

松本美穂松本崇博

松本崇博 Blog(SQL Server Tips)
松本美穂ブログ(SQL Serverノート)