はじめに
DWHシステム用途に限定したSQL Serverのベストプラクティスを記述しています。
想定されるDWHシステムの特性は以下のとおりです。
・利用者からのアクセスは参照のみ
・バッチ処理でデータを更新
・データ量が多い
・可用性のレベルは高くない
なおSQL Serverのバージョンは2017を前提としています。
目次
・テーブル
・パーティション
・インデックス
テーブル
テーブルを作成するには以下に基づいて、データ型の選択・制約を設定する必要があります。
整数型もしくは日付型を選択することで、テーブルの結合列が有利になり、実行プランにおいて高速なBITMAPフィルター操作となる可能性が高くなります。
文字列のデータ型はUNICODEデータ型nchar,nvarcharを選択することでSSAS,SSRSではUNICODEデータ型だけのサポートを受けられます。
SSISもUNICODEデータ型への自動内部変換のオーバーヘッドが発生します。
また列ストア圧縮をすることでUNICODE圧縮機能が有効となるため、データベース上のサイズに関しては問題提起されることはありません。
ファクトテーブルの制約に関しては以下のようになります。
主キー制約・外部キー制約は更新性能に影響があるため、定義しない方が好ましいです。主キー制約が使用できない代わりに一意性はETLの実装によって確保されます。ETLが障害により異常終了した場合、障害の原因を除去し再実行すれば問題のない状態に戻せるように実装する必要があります。
既存のUpdateや新規行のInsertの場合、更新内容をそのまま再実行しても問題はないですが、更新するSQLがInsertだけの場合、Insert前に更新データの論理キーに一致する行をDeleteする必要があります。
パーティション
1つのテーブルのデータの格納場所をテーブルの範囲などで分割する処理のことです。パーティーションを使用することでDBのパフォーマンスが良くなることが見込まれます。
以下ファクトテーブルをパーティション分割する場合の特徴を記載します。
【時間軸でファクトテーブルをパーティション分割する場合】
・インデックスなしでスキャンする範囲を絞り込むことが可能
・パーティション単位のTruncateが可能
・パーティションあたり100万行以上
・クラスタ化列ストアインデックスの最適行数に適合させる
・パーティション単位でファイルグループを分けない
・データファイルのI/Oが均等に分散できない
・パーティションスキームで1つのファイルグループだけを指定する
※パーティション化したテーブルのイメージ
インデックス
インデックスとは、テーブルの特定の列に対して作成されたデータ構造で、データの検索や並び替えを高速化するために利用されるデータベース管理の仕組みの１つです。
SQL Serverではテーブルと一体となったインデックスに「クラスタ化」という用語を頭につけます。
例:)
・クラスタ化インデックス 行ストアテーブル
・クラスタ化列ストアインデックス = 列ストアテーブル
クラスタ化列ストアインデックス（列ストアテーブル）とはDWH用途に適した機能です。行ストアと比較して集計操作を伴うクエリーの高速化と列単位の圧縮による高い圧縮率の実現とそれによるI/Oの削減とメモリの有効利用やSelect句で選択された列だけを処理することによる所要リソースの削減、列が格納されるセグメント管理情報に列の値のmax,minを保持することによる高速フィルターが可能となっています。
クラスタ化列ストアインデックスの更新ではInsertまたはDelete操作が可能です。Update分の実行時は内部的にDelete/Insertを使用しての更新となります。
約100万行単位で行グループおよび列セグメントを構成し約10万行未満の一括挿入は行ストア形式のデルタストアに格納され、デルタストアが約10万行を超えると列セグメントに自動変換されます。インデックスが定義された行ストアテーブルよりも Insert 処理が高速であることも特徴です。
インデックスのベストプラクティスとして以下テーブルを利用することが好ましいです。
【ファクトテーブル】
・100万行以上のテーブルにクラスタ化列ストアインデックスを作成する
・主キー制約・外部キー制約は更新性能に影響があるため、定義しない方がよい
・一意性はETLの実装で担保する
・非クラスタ化インデックスは使用しない
・対象範囲の行数が多い集計クエリーでは実行プランで使用されない可能性が高い
・更新性能への影響と圧縮効率低下によるデータサイズ増大
【ディメンションテーブル】
・主キー制約を定義し、主キー列にクラスタ化インデクスを作成する
最後に
株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/?utm_source=qiita&utm_medium=referral&utm_campaign=qiita_zeuscloud_content-area