蒼の王座

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)

Azure VM 上で、Active Directory を構成して、ドメインコントローラーなどを立てた時に、うっかりするとデフォルトのセキュリティ設定では、ユーザーパスワードが期限切れをしてしまいます。
しかも悲しいことに、ローカルアドミン、ドメインアドミン両方で発生します。
そんな時の対処方法について説明します。

「Your password has expired and must be changed.」てエラーが表示されるのですが変更する方法がわからず辛い。
Azure Portalでパスワードをリセットしようとしても、「VMAccess Extension does not support Domain Controller」とエラーになります。

仕方ないので、スクリプトを実行する方法で対応します。
Azure Portalで、VM Extensionを使って、スクリプトをアップロードして実行します。
スクリプトで、パスワードをリセットします。

スクリプトは次のような感じ。
“` reset.ps1
net user adminユーザー名 新しいパスワード（複雑性に注意）
“`

Azureポータルで、VMメニューからextensionを選択し、AddからCustom Script extensionを選びます。
reset.ps1を選びます。
しばらくすると、CustomScriptExtensionに「Provisioning succeeded」と表示されて完了します。
めでたしめでたし。

噂によると、スクリプト作成に失敗することがあり、その時にはblobアカウントが必要で求められているアカウントを作成してからスクリプトを作ったらうまくいくとか。

特徴

SQL Serverと100%に近い互換性があり、ネイティブでVNETに対応しています。
オンプレからクラウドへの移行を容易にします。
PaaSと同じインフラで、全てのPaaSの機能（自動バッチ、バージョンアップ、バックアップ、高可用性）を提供します。

・オンプレミスのSQL Server 2005から最新バージョンまで互換性があリます。
（バックポートして互換性を持たせたので、SQL Server 2005以降全てのバックアップファイルをリストアできます）
・通常のSQL Serverのリストアに対応
・SQL Agent
・SQL Profiler
・ログシッピング
・トランザクションレプリケーション
・データベースをまたぐクエリ
・Database Mail
・Service Brocker
・SQL CLR
・SSIS（もうまもなく）
・99.99% SLA
・自動バックアップ
・ポイントタイムリストア

細かい情報

プロパティ	値	備考
@@VERSION	Microsoft SQL Azure (RTM) – 12.0.2000.8 2018-03-07 Copyright (C) 2018 Microsoft Corporation.	SQL Databaseと同じ
SERVERPROPERTY (‘Edition’)	SQL Azure	SQL Databaseと同じ
SERVERPROPERTY(‘EngineEdition’)	８	Managed Instance専用
@@SERVERNAME, SERVERPROPERTY (‘ServerName’)	フォーマット：..database.windows.net	例：my-managed-instance.wcus17662feb9ce98.database.windows.net

Azure Database Migration Service(preview)

SQL Server からAzure SQL Database Managed Instanceに簡単に移行できるように設計されています。
DocuSignもこれを使って移行しました。

サービス帯

Public Preview中は、General Purposeが提供されます。
基本的な可用性、共通的なIOレイテンシーようです。

・基本的な性能要求と可用性
・Azure Premium Storage(8TB)
・１インスタンス100DB
・8/16/24 vCore
・ストレージは３２GB〜８TB
・500-7500IOPS/１データファイル
・１データベースのデータファイル数は複数
・ログファイルのデータファイルは１つ
・自動バックアップ
・可用性は、リモートストレージとAzure Service Fabric
・インスタンスとデータベースのモニタリングと計測
・自動パッチ
・監査ログ
・ポータル対応

メトリックス画面

監査ログと脅威分析

vCoreについて

vCore(Virtual Core)は、論理CPUで、２世代のハードウェアで提供されます。
・4世代：Intel E5-2673 v3 (Haswell) 2.4 GHz プロセッサー
・5世代：Intel E5-2673 v4 (Broadwell) 2.3 GHz プロセッサー

vCore、ストレージサイズのスケールアップダウンが必要に応じてできるようになっています。
(ポータルから設定できますが、ダウンタイムなどがあるのかは確認が必要ですねぇ)
データベースファルは全て分離されたPremiumストレージ上に配置されます。

オンプレミスとの違い

詳細な機能比較は、「こちら」

・可用性は組み込みで予め設定されています。Always On 可用性機能はSQL IaaS実装と同じ方法では公開されません。
・自動バックアップ、ポイントタイムリストア。バックアップチェインに影響与えない、コピー専用バックアップを出力できます。
・物理パスの指定ができません。Bulk insertはAzure Blobのみの対応です。
・Windows認証の代わりにAzure AD認証を提供します
・インメモリOLTPオブジェクトが含まれるXTPファイルグループとファイルは自動で管理します。

バックアップ

・Azure Blob認証

“`
CREATE CREDENTIAL [https://myacc.blob.core.windows.net/testcontainer]
WITH IDENTITY=’SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2014-02-14&sr=c&sig=GV0T9y%2B9%2F9%2FLEIipmuVee3jvYj4WpvBblo%3D&se=2019-06-01T00%2A%3AZ&sp=rwdl’;
“`

・コピー専用バックアップ

“`
BACKUP DATABASE tpcc2501
TO URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak’
WITH COPY_ONLY
“`

・ストライプバックアップ
Blobは２００GBのサイズ制限があります。次のようにしてファイルを分割してバックアップします。

“`
BACKUP DATABASE tpcc2501
TO URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak’
WITH COPY_ONLY
“`

・MAXTRANSFERSIZE
大きなデータベースの時には、MAXTRANSFERSIZE=4194304を指定するといいでしょう。
１ファイルあたり、48GBを超えないようにする。
COMPRESSIONで圧縮して帯域を節約します。
CHECKSUM or STATS = で正しくバックアップできたか確認してもいいでしょう。

“`
BACKUP DATABASE tpcc2501
TO URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-1.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-2.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-3.bak’,
URL = ‘https://myacc.blob.core.windows.net/testcontainer/tpcc2501-4-4.bak’
WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION
“`

価格

Preview版のお値段については、「ここ」に記載があります。
目的別に2種類のサービス帯が提供さえる予定なのですが、現時点では一般的なワークロード用のGeneral Purposeのみが提供されています。

「BASE RATE PRICE WITH AZURE HYBRID BENEFIT (% SAVINGS)」は、SQL Serverのライセンスを持っていて有効なSA権を持っている場合に、ライセンスをクラウドに持ち込んだ場合の価格です。
ですので、通常は「LICENSE INCLUDED PREVIEW PRICE」を選ぶことになります。

２４vCoreを選択すると、東日本リージョンで375.44円/時間かかり、１日9,010円、1ヶ月279,327円かかります。
ストレージが最初の３２GBがvCore代金に含まれていて、以後月1GB毎に7.73円かかり、最大８TBまでいけます。1TBで月７９１５円。
バックアップデータは月1GB毎に5.6円かかるので、月3000円。
IO１００万毎に13.44円なので、50,000IOPSで、月58060円。

月間、高くても35万円あたりでしょうか。
プレビュー中はNW転送量に課金されません。

バックアップ費用の見積もり方

利用しているサーバーストレージサイズと同じ容量までは追加費用がかかりません。
それを超えると費用が加算されます。
１００GBのデータベースの場合、100GBのバックアップまでは無料、１１０GBの場合は１０GBが費用発生します。
2018/6/30まではバックアップ費用は発生しません。

提供されているリージョン

20リージョンで提供されています。
Canada Central
Canada East
Central US
East Asia
East US
East US 2
Japan East
Korea Central
Korea South
North Central US
North Europe
South Central US
South India
Southeast Asia
UK South
West Central US
West Europe
West India
West US
West US 2

2018年2月に開催されたTokyo Jazug Nightで登壇しました。
テーマは、http://github.com/azure で公開されているリポジトリについてでした。