SE の雑記

本投稿は 2018/4 時点の Public Preview の内容です。
一般提供開始時には変更されている可能性があります。
SQL Database Managed Instance (MI) は、インスタンスタイプのマネージドサービスのため、ユーザーが自由にデータベースを作成することが可能です。
しかし、単純にデータベースを作成すれば、インスタンスで最大限の性能を引き出すことができるかというと、汎用目的のサービス階層については、そういうわけではありません。
MI に適したデータベースの構成というものについては意識をしておく必要があります。
本投稿ではそれらについてまとめていきたいと思います。
(本投稿の内容は、「汎用目的 (汎用サービス階層) の場合」となります。ビジネスクリティカルについては本投稿とは異なる傾向になるかと思います。)

関連する情報としては次の内容となります。

• マネージ インスタンス (プレビュー) とは?

期待されるストレージ IOPS
データ ファイルあたり 500 から 7500 IOPS (データ ファイルによって異なる)。 Premium Storage に関するセクションを参照してください。

• 既知の問題と制限事項

それぞれのマネージ インスタンスが、Azure Premium ディスク領域用に予約された最大 35 TB の記憶域を持ち、各データベース ファイルは別個の物理ディスク上に配置されます。 ディスク サイズとして、128 GB、256 GB、512 GB、1 TB、4 TB のいずれかを指定できます。 ディスク上の未使用領域については請求されませんが、Azure Premium ディスクのサイズ合計が 35 TB を超えることはできません。 場合によっては、内部の断片化のために、合計で 8 TB を必要としないマネージ インスタンスが、記憶域のサイズに関する 35 TB の Azure での制限を超える場合があります。

これが、どのようなことを示しているかを理解することが、ユーザーデータベースの性能を適切に引き出すためのポイントとなります。
上記の「制限事項」では、「35 TB の記憶域を持ち」と書かれていますが、MI でこの 35 TB の領域をすべて使うことができるかというと、そういうわけではありません。
MI は、デプロイ時の設定として、「ストレージサイズ」を指定することができるようになっています。
(デプロイ後にコア数やストレージサイズを変更することも可能です)

このサイズが、データベースのデータファイル / ログファイルで使用できる領域となってきます。
ストレージサイズを超えて、ファイルの拡張を行おうとすると、次のようなエラーが発生します。

内部的には 35 TB を使用できる領域を確保していますが、データベースのファイルとして実際に使用できるのは、MI にしているストレージサイズまでとなり、使用しているストレージサイズが課金対象となります。
現時点の MI は、8TB までしかストレージを使用することができませんので「それなら、35 TB まで行かないのでは？」と思われた方もいるのではないでしょうか。
これについては、次の内容を考慮する必要があります。

たとえば、4 TB のディスクを使用するマネージ インスタンスに、サイズが 1.2 TB のファイルが 1 つあり、サイズが 128 GB の 248 個のディスクに、それぞれが 1 GB の 248 個のファイルが配置される可能性があります。 この例では、ディスク記憶域の合計サイズは、1 x 4 TB + 248 x 128 GB = 35 TB となります。 ただし、データベースのために予約される合計インスタンス サイズは、1 x 1.2 TB + 248 x 1 GB = 1.4 TB となります。 これは、特定の状況下では、非常に特殊なファイルの配分のために、マネージ インスタンスが、想定していなかった Azure Premium ディスクの記憶域の上限に到達する可能性があることを示しています。

MI のユーザーデータベースのファイルについては、ファイル単位で Premium Storage のディスクを使用することになります。
MI では、128 GB ～ 4TB までのディスクを使用することになっていますので、P10 ～ P50 のディスクが、ファイル単位で使用されることになります。

データベースのファイルとして、次のようなファイル構成にした場合を考えてみます。

 
この場合、次のような Premium Storage が割り当てられることになります。

 
データベースのファイルとしては、500 GB なのですが、Premium Storage の領域としては、1.1 TB 消費していることになります。
MI は、複数のデータベースを作成することができ、この複数の DB の領域として、35 TB が上限となっていますので、データベースのファイルと、消費する Premium ストレージの領域の関係については意識をしておく必要があります。
MI のデータベースの最大ファイル数が 280 というのも、この 35 TB の制限からきているかと。
データベースのファイルのサイズが小さくても、ファイルに対して割り当てられる Premium Storage の最小サイズは 「128 GB」となります。
1 つのファイルサイズが、1MB のファイルを 280 個使用して DB を構成した場合、

• 128 GB × 280 = 35,840 GB = 35 TB

となります。
最小ファイルで構成した場合に、280 個で、35 TB に到達しますね。
これが、ファイル数の制約として効いてきているのかと。
全体のストレージの構成としては次のようになります。

 
Premium ストレージですが、最大の 4TB を使用した場合、250MB/sec / 7,500 IOPS が上限となります。
そのため、データベースのデータアクセスで最大限の I/O を引き出そうとした場合は、

• 4 TB のディスクが使用されるサイズをデータファイルの初期サイズとして設定する

だけでは足りません。
最大限の I/O を出そうとした場合は、

• 4 TB のディスクが使用されるサイズをデータファイルの初期サイズとして設定された「複数のデータファイル」でデータベースを構成する

ことがポイントとなります。
ログファイルについては「1 ファイルのみ」という制限がありますので、データ変更系のスループットを最大化するためには、「4 TB のディスクが使用される初期サイズでログファイルを構成する」ことが重要となってきます。
データファイルが 1 ファイルと 5 ファイルで構成されているデータベースに対して、同一のデータ参照をディスクから実施した場合の、処理時間の相対的な比較が下になります。

今回は大量データに対してのスキャンを実施した結果の相対的な比較となるのですが、5 ファイルで構成したデータベースの方が、1 ファイルで構成されたデータファイルと比較して、処理時間が短縮されていることが確認できますね。
(ワークロードによっては、分割した方がオーバーヘッドが出る可能性もありますので、今回は一例として、わかりやすい結果を上げています)
MI のユーザーデータベースの作成時には、通常の SQL Server と異なり、データベースのファイル数や、ファイルサイズを設定をすることができません。

単純にデータベースの新規作成を実行した状態では、次のような構成のデータベースが作成されることになります。

複数のデータファイルを構成するためにはどうすればいいかというと「データベースを作成した後に追加をする」という作業を実施します。

• SQL Server の T-SQL との相違点

CREATE DATABASE ステートメントの制限事項は次のとおりです。

• ファイルおよびファイル グループは定義できません。
• CONTAINMENT オプションはサポートされていません。
• WITH オプションはサポートされていません。
  ヒント
  対処法として、CREATE DATABASE の後に ALTER DATABASE を使用して、ファイルの追加またはコンテインメントの設定を行うデータベース オプションを設定します。

ALTER DATABASE ADD FILE (FILENAME='path') T-SQL ステートメントでは、ファイル パスは指定できません。 ファイルはマネージ インスタンスによって自動的に配置されるため、スクリプトから FILENAME を削除します。

に記載されている内容がポイントですね。
データベースにファイルを追加したい場合には、

ALTER DATABASE tpch2 ADD FILE (NAME='data_6', SIZE= 1 GB)

というようなステートメントを実行し、データベースの作成後にファイルの追加を行います。
ファイルパスについては、MI が管理しているため、パスの指定は行わずにファイルの追加を行うというステートメントで実行を行います。
データベースの作成 / 変更 / 削除という操作については、通常の SQL Server と異なり、MI の管理情報とも連携されているため、すぐに終わらないことがあります。
DB の作成状況については、「sys.dm_operation_status」で確認をすることができますので、要求が処理されているかは、この DMV を使用するとよいかと。
次の画像のような情報を取得することが可能です。

単純なユーザーデータベースの作成を見てみましたが、MI 特有の考え方というのは、意識しておく必要がいろいろとありますね。
(この辺の考え方は、SQL Server のデータベースのファイルを、データディスクではなく Premium Storage に直接配置する場合と同じだったりしますが)