SQL Azure

Azure SQL Databaseの監査ログを見るとき、知らないと面食らうというか上手く使えなくてしょんぼりすることがあります。
ちょっとしたポイントを知っておくと幸せになれます。

監査ログをAzure ポータルで参照しようと思うと最終的にはクエリエディターを起動することになります。クエリエディターのログインに使用するアカウントは管理アカウントになります。

クエリエディタが起動すると次のようなクエリが書かれています。

SELECT TOP 100 event_time, server_instance_name, database_name, server_principal_name, client_ip, statement, succeeded, action_id, class_type, additional_information
FROM sys.fn_get_audit_file(‘https://xxxxxxx.blob.core.windows.net/sqldbauditlogs/xxxxxxxxx/xxxxxxxx/SqlDbAuditing_Audit/2018-08-29/04_00_49_057_2108.xel’, default, default)
WHERE (event_time <= ‘2018-08-29T04:00:51.022Z’)
/* additional WHERE clause conditions/filters can be added here */
ORDER BY event_time DESC

Blobファイルを読み込んで検索していますね。
ファイルが細かく分かれているので、ほかのファイルも対象にしたくなります。

ドキュメント読んでワイルドカードとか試したのですが、あまりちゃんと動かず、結局たどり着いた結論は、「前方一致」したものが対象になるでした。

例えば、Blobファイルの指定を、「
https://xxxxxxx.blob.core.windows.net/sqldbauditlogs/xxxxxxxxx/xxxxxxxx/SqlDbAuditing_Audit/2018-08-29/ 」とすると、8/29のものがすべて。「
https://xxxxxxx.blob.core.windows.net/sqldbauditlogs/xxxxxxxxx/xxxxxxxx/SqlDbAuditing_Audit/2018-08-2」とすると、20日～29日が対象となります。（たぶんねｗ

データ量が多いと検索にえげつない時間かかります。
自分が試した環境だと10分程度でも結果出るのに1分とかかかりました。

つらいですね。
もう少し素敵な検索方法は、SQL Server Management Studioを使うことです。
ポータルでやるのはあきらめて、ここの真ん中あたりに記載がある「SQL Server Management Studio (SSMS 17 以降) で [監査ファイルの統合] を使用します。」の手順にのっとるのがいい。

これを実行すると、Blobストレージのアクセス権APIキーを設定した後、取り込みたい範囲を指定して、ローカルにダウンロードし、SSMSでフィルタリングとかをして調査をします。
めっちゃいい！と言い切れない部分もあるのですが、Azure Portalよりは断然ましです。

ドキュメント読んだり、説明されればそりゃーそうだっと納得できるのですが、説明されるまでは勘違いしていたのでメモしておく。

詳細説明は「公式ドキュメント」でされているので、そちらを参照して欲しい。

セカンダリへの反映は一定量の変更をセカンダリにトランザクションログを用いてします。
反映対象は、トランザクションが完了したものになります。

アクティブ geo レプリケーションは SQL Server の Always On テクノロジーを活用し、スナップショット分離を使用してプライマリ データベース上のコミットされたトランザクションを非同期的にレプリケートします。

特定の時点におけるセカンダリ データベースは、プライマリ データベースよりもわずかに古い可能性がありますが、セカンダリ データには部分トランザクションが含まれないことが保証されます。

トランザクションが完了すれば反映対象となるので、順番が前後するとつらい操作はトランザクションにする必要があります。

例えば、メンテナンス作業で、インデックスの再構成をする場合には、統計情報の自動更新が重ならないようにしておいたほうがいいので、統計情報の自動更新を停止することがあります。

マスターで、自動更新の停止、インデックスの再構成、自動更新の再開をすると、セカンダリーにも、自動更新の停止、インデックスの再構成、自動更新の再開が適用されます。

ただし、インデックスの再構成の実行時間によっては、セカンダリでは、自動更新の停止、自動更新の再開、インデックスの再構成の順番で適用されることがあります。
これがまずい場合には、「自動更新の停止、インデックスの再構成、自動更新の再開」で一括りのトランザクションにしましょう。

redash で、データソースにSQL Serverを選択し、Azure SQL Databaseに接続しようとすると以下のようなエラーが出ることがあります。

Cannot open server “1433D” requested by the login. The login failed.DB-Lib error message 20018, severity 20: General SQL Server error: Check messages
from the SQL Server DB-Lib error message 20002, severity 9: Adaptive Server connection failed (xxxxxxxxxxxxx.database.windows.net:1433)

これは、SQL Server とは違って、Azure SQL Databaseの実装上の違いによって生じています。SSMSであれば、明示的に指定しなくても自動的に裏側でしれくれるのですが、redashの場合には明示する必要があります。

接続先サーバー名が、xxxxServerName.database.windows.net と仮定すると、
Userには、「analysis@xxxxServerName」のように指定する必要があります。

もちろん、analysisの部分はあなたのDBのログインに置き換えてください。太字のように、@以下にサーバーのホスト名を指定します。

SQL Azure Database で、巨大テーブルのユニーク数の概算を取得するのに役立つ関数「APPROX_COUNT_DISTINCT」の一般プレビューがリリースされました。（紹介Blog）

SELECT COUNT(DISTINCT())

使用例としては、一千万行ぐらいのテーブルで、ダッシュボード表示用にCOUNT(DISTINCT())する場合が考えられます。
このケースで重要なは正確な数字ではなく、データ取得までの応答速度です。

「APPROX_COUNT_DISTINCT」は、NULLではないユニークな数の概算を取得する関数です。

「APPROX_COUNT_DISTINCT」は、大きなデータで使用する前提で設計されていて、次のようなケースに最適化されています。

• 数百万行以上のデータにアクセスする場合
• 多数の異なる値を持つ列をカウントする場合

この関数は、通常の使用用途では2%以内の精度を維持しつつ、かつどんなに稀な使用例でも悪くても20%以内の精度を維持されるべきだと考えています。

設計ポイント

「APPROX_COUNT_DISTINCT」は、非常に少ないメモリ使用量で算出できるように設計されています。COUNT DISTINCTで、メモリ内にデータが収まりきらずTempDBを使用して算出するケースでは、優位になります。「APPROX_COUNT_DISTINCT」は、基本的にTempDBを使用して算出することはありません。

Q & A

• 「APPROX_COUNT_DISTINCT」でクエリは高速化しますか？
  • 対象データによります。メモリに最適化しているので、メモリ内に収まりきらない場合には、かなりの応答優位性を発揮します。
  • メモリ内で収まる場合には、あまり差がありません。
• 精度が2%に収まらない最悪のレアケースでは、どれぐらいの精度になりますか？
  • 20%以内に収まるべきと考えています。
• 実行プランへの影響は？
  • COUNT(DISTINCT)では、Hash MatchとSort操作がはいります。INDEX SCANは95%程度ですが、「APPROX_COUNT_DISTINCT」では、98%の時間がINDEX SCANです。

 

実装方法

実装に採用されているアルゴリズムについて教えてもらったので追記しておく。
OracleやRedis、Redshifにも同様の関数があって、それらは「HyperLoglog」が使用されているので、Azure SQL Databaseでも恐らくそれだろう。

オリジナル論文は、2007年に公開されている。
なんとなく理解をするのなら、「HyperLoglogでcount distinctを速くする」を参照するとなんとなく理解できたような気ができる。

SQL Azure Database では、データ圧縮（行やPAGE圧縮）をすることができます。
この辺りは、ムッシュの「SQL Database で行/ページ圧縮が利用可能になったようです」で紹介されています。
今回紹介するのは地味につらいけど、あまりちゃんと説明されていない部分の注意点になります。

MSDNで「Data Compression」でツラツラ書かれている中で次のような注意事項があります。

行またはページの圧縮を有効または無効にするために必要なディスク空き容量は、インデックスを作成または再構築するために必要なディスク空き容量と同じです。

とてもしれっと、さりげなく書いていますが、とても重要なのです。
つまり、ストレージに十分な空き容量がないとデータ圧縮を有効にできません。

では十分な空き容量とはなんでしょうか？
対象となるのは、「デーベースファイル」と「トランザクションログファイル」の2つです。
それぞれで十分な空き容量が必要となります。

クラスタ化インデックスを作成するときに必要なディスク領域については、「インデックスのディスク領域の例」と「インデックス操作用のトランザクション ログのディスク領域」で解説されています。

オンラインでのクラスタ化インデックス作成（SORT_IN_TEMPDB=ONに設定されたオンラインインデックスの操作）だと、
元のテーブルが約363MBだとすると
作業中に必要な領域の合計サイズは1058MBで、
作成されたインデックスサイズは453MBになります。
作業途中では約2倍の空き領域が必要に（3倍じゃないのは1058MBにはもともとの容量が含まれているので）なりますね。
ただしTEMPDBが含まれているのでAzure SQL Databaseの場合には、
242MBが含まれるので810MBです。オリジナルの1.3倍ぐらいの追加空き容量があればOK。

さて、トランザクションログはというと「インデックス操作を確実にロールバックできるようにするには、インデックス操作が完了するまでトランザクション ログを切り捨てることができません。」や「SORT_IN_TEMPDB オプションを ON に設定することを検討します。この設定により、インデックス トランザクションが同時実行のユーザー トランザクションから分離されます。インデックス トランザクションは、tempdb データベースのトランザクション ログに格納され、」と書かれています。
SORT_IN_TEMPDBオプションは既定ではOFFなのでクエリで明示的に指定しなければOFFです。

ここがあまり理解できていないのですが、対象のデータベースでページ圧縮以外のトランザクションがなくてもトランザクションログは必要で、必要な容量はおそらくディスクデータ領域の数倍かと思われます。

どうやるのがベスト？

データ圧縮を有効にするには、データ容量とトランザクション容量の両方が必要になることを確認しました。

データ容量については、まぁ仕方ないので増やして対応しましょう。
しかし、トランザクションログだけは制限が入ってるので、対策をする必要があります。
Azure SQL Database の P15 でトランザクションログが1TB、P2でトランザクションログが340GBとなっています。そのため、100GBを超えるテーブルを圧縮するには確実に足りません。

そこで利用するのが、「再開可能なオンラインインデックス再構築」です。

再開可能なオンラインインデックス再構築のポイント

• 実行中は、更新系クエリに一桁パーセントのオバーヘッドがかかるかも。（参考）
• インデックス再構築の障害からの回復 (データベースのフェールオーバーやディスク領域の不足など)。（参考）
• インデックス再構築操作の間はトランザクション ログの切り捨てを有効にします (通常のオンライン インデックス操作に対してこの操作を実行することはできません)。
  • つまり、途中で止めてトランザクションログバックアップがされればトランザクションログが切り捨てされて幸せに！
  • 再開可能な再構築では実行時間の長いトランザクションを開いたままにする必要はなく、この操作の間のログの切り捨てと、より優れたログ領域管理が可能です。
  •  新しい設計では、必要なデータを、再開可能な操作を再開するために必要なすべての参照と共に、データベースに保持しています。
• 再構築中にReconfigurationが発生してもフェールオーバー後に途中から再開可能。
• 再構築中の進捗状況がクエリで取得可能。

テーブル圧縮とインデックス圧縮について

ALTER TABLEとALTER INDEXそれぞれでデータ圧縮ができますが、それぞれにどんな意味があって、挙動が違うのでしょうか。
それを知る手掛かりが、MSDNの「圧縮されたテーブルおよびインデックスの作成」で説明されています。

圧縮できる対象のデータベースオブジェクトが次のものです。

• ヒープとして格納されているテーブル全体。
• クラスター化インデックスとして格納されているテーブル全体。
• 非クラスター化インデックス全体。
• インデックス付きビュー全体。
• パーティション分割されているテーブルおよびインデックスの場合、パーティションごとに圧縮オプションを構成することができ、オブジェクトの各パーティションを同じ圧縮設定にする必要がありません。

つまり、ヒープテーブルへのALTER TABLEと、非ヒープテーブルのクラスター化インデックスへのALTER INDEXが同じ意味を持っていそうです。

Let’s Try

ムッシュがすでに「SQL Database で再開可能なオンラインインデックス再構築が Public Preview で利用可能となりました」で紹介されています。

そこから学ぶと以下のクエリでよさそうですね。

— 再開可能なオンラインインデックス再構築
ALTER INDEX [PK_T1] ON [dbo].[T1] REBUILD PARTITION = ALL
WITH (RESUMABLE = ON, ONLINE = ON, MAX_DURATION = 1, DATA_COMPRESSION = PAGE)