Ignite 2016 のセッションですが、Microsoft Ignite On-Demand で動画が公開されてています。
とりあえず、SQL Server のデータベースエンジン関連のセッションを流し見しているのですが、エンジン回りで関連しそうなセッションの紹介を。
最近、英語が苦手なのが致命的に響いてきていて、ひさしになんとかしてもらわないと生きていくのがつらいです…。
スライドについても公開されていて、 Ignite 2016 Slidedeck and Video downloader で一括でダウンロードできるようです。
Contents
■SQL Server 2016 の新機能
Accelerate SQL Server 2016 HTAP performance with Windows 2016 and HPE Persistent Memory technology
HTAP : Hybrid Transaction and Analytics Processing
- OLTP と OLAP を同一のデータベースで実行させる
- SQL Server 2016 の Operational Analytics (行ストア / 列ストア / In-Memory OLTP の使用) のシナリオOLTP に与えるインパクトを最小にして、DWH 用の DB を作成することなく、解析を実施するためのチャレンジ
より多くのトランザクションを最小のマッチ時間で実行
- SQL Server 2016 の Operational Analytics (行ストア / 列ストア / In-Memory OLTP の使用) のシナリオOLTP に与えるインパクトを最小にして、DWH 用の DB を作成することなく、解析を実施するためのチャレンジ
- Fast Storage : HDD → SATA SSD → NVMe SSD
- SQL Server 2016 "Tail Of Log" (Preview) on Persistent Memory
- NVDIMM-N (Byte) への書き込みが完了したタイミングでトランザクションををコミットとさせる
- ログブロックがフルになったらスケジュールされた I/O により、Block を永続化するために、SSD に書き込む
- 性能は、NVDIMM-N (Block) < NVDIMM-N (DAX) となり、10 倍近くの IOPS とレイテンシーの差がある
- DAX は DRAM 相当のパフォーマンスがある
- ログ書き込みのボトルネックを最小化する
- NVDIMM-N Support in Windows Server 2016
- DAX : Direct Acces)
- SCM : Storage Class Memory
- Windows Server 2016 では、NVDIMM-N を DAX (Byte) / Block で使用することができる
- Persistent memory disks
Accelerate SQL Server 2016 to the max: lessons learned from customer engagements
- SQL Server 2016 へのアップグレード
- 互換性レベルを 130 に変更することで使用できる新しい機能
- 並列化
- メモリ最適化テーブルに対してのクエリ実行時の並列化
- INSERT … SELECT の並列化
- 列ストアモードのバッチモードの拡張
- ソート / ウィンドウ集計
- シリアルプランでのバッチモード
- 基数推定に関してのいくつかの変更
- 並列化
- 互換性レベルを変更する際の検討事項
- クエリストアの有効化
- テストワークロードの実行しベースラインの確立
- 互換性レベルを 130 に変更
- テストワークロードを再実行し、ベースラインを比較
- クエリストアを使用して、退行しているクエリの検出
- プランの強制で退行したクエリのプランを強制的に変更することも検討
- 互換性レベルを 130 に変更することで使用できる新しい機能
- 列ストアインデックス
- ステージングテーブルを使用したファクトテーブルから既存テーブルへのデータの変換
- 一度ステージングテーブルに対して、INSERT SELECT WITH(TABLOCK) を使用して並列でロードするその後、ステージングテーブルから既存テーブルへデータをロードする
- 直接移行するより大きな処理性能の向上が見込めるケースがある
- データ変換のデモ
- WWImportesDWのFact.Sales を使用
- CCI が設定されているテーブルに対して、WWI のテーブルを INSERT INTO SELECT で全件移動
(TABLOCK ヒントは設定していない状態)- シリアルプランとして実行される
- INSERT INTO ~ WITH (TABLOCK) SELECT ~として、TABLOCK を指定して実行
- 並列プランとして実行される
- IDENTITY が設定されているテーブルに対して INSERT を実行すると、TABLOCK を指定してもシリアル操作となる
- IDENTITY_INSERT を有効にして、ROW_NUMBER を使用することで、並列プランとして INSERT することができる
- SSIS のデータフロータスクを使用する場合、「AutoAdjustBufferSize」の設定を意識する
- False にした場合、一度に BULK される行数が少なかったが、True にした場合は 単一の BULK となっていた
- 1048576 行 (デルタストアの最大サイズ) の BULK となっていた
- https://blogs.msdn.microsoft.com/sqlcat/2016/02/29/sql-server-2016-ssis-data-flow-buffer-auto-sizing-capability-benefits-data-loading-on-clustered-columnstore-tables/
- False にした場合、一度に BULK される行数が少なかったが、True にした場合は 単一の BULK となっていた
- 同時にマージステートメントを使用する場合
- 非クラスター化列ストアインデックスに KEY ロックのヒントを設定
- https://blogs.msdn.microsoft.com/sqlcat/2015/03/11/data-loading-performance-considerations-with-clustered-columnstore-indexes/
- スピンロックのトラブルシューティング
- 拡張イベント
- Windows / SQL Server のデバッグシンボル
- Windows Performance Toolkit
- sqlservr.exe > sqlmin.dll > Function : splinlock から確認
- %Weight から Spinlock がどの程度の比率を占めているか
- sqlservr.exe > sqlmin.dll > Function : splinlock から確認
- KB319588 (CU2 に含まれている) , KB3173472 (CU1 に含まれている) を適用することで、スピンロックによる CPU の使用率を低下させることが可能
- ステージングテーブルを使用したファクトテーブルから既存テーブルへのデータの変換
- In-Memory OLTP
- インデックスのタイプ
- 非クラスター化インデックス
- ハッシュ
- ログの切り捨ての違い
- サイズベースのチェックポイント
- 手動による任意のタイミングのチェックポイント
- https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/
- ラージチェックポイントは RTM ではデフォルトで有効であったが、CU1 では、デフォルトでは無効の状態となっている
- メモリ最適化テーブルデータ型を使用することで、通常のストアドプロシージャの一時的な INSERT 処理に対してのページラッチの削減
- インデックスのタイプ
- Temporal
- 最新のテーブルと履歴テーブル (Internal History Table) にメモリ最適化テーブルを使用し、非同期でディスクベースのテーブルに同期をするという構成
- https://msdn.microsoft.com/ja-jp/library/mt590207.aspx
- メモリ最適化テーブルを Temporal 化すると内部的に Internal 履歴テーブルが作成される
- 最終的に永続化されるテーブルに関してはディスクベースにする必要がある
- sys.sp_xtp_flush_temporal_history による手動のフラッシュも可能
- 最新のテーブルと履歴テーブル (Internal History Table) にメモリ最適化テーブルを使用し、非同期でディスクベースのテーブルに同期をするという構成
Upgrade to Microsoft SQL Server 2016: it just runs faster
- コンテンツ
- Just Runs Faster
- Auto Soft NUMA
- SQL Server 2016 はデフォルトで有効
- Dynamic Memory Object
- -T8048 は SQL Server 2016 では不要
- Dynamic Memory Object Scaling
- https://blogs.msdn.microsoft.com/sql_server_team/dynamic-memory-object-scaling/
- -T8074 を有効にすると Dynamic PMO をオフにできる
- デフォルトは有効
- Auto Soft NUMA
- Parallel INSERT
- INSERT INTO SELECT が並列 INSERT に対応
- ヒープと CCI に対して使用できる
- TABLOCK ヒントを付ける
- Parallel Redo
- xEvent の database_recovery_trace から複数の Redo Worker が実行されていることが確認できる
- Parallel redo manager
- xEvent の database_recovery_trace から複数の Redo Worker が実行されていることが確認できる
- DBCC CHECK Scalability
- MULTI_OBJECT_SCANNER
- Multiple tempdb files
- 論理コアに合わせた最大で 8 に自動構成
- トレースフラグ不要
- 瞬時初期化
- インストール時に設定可能
- Log I/O (Tail of log)
- NVDIMM (Direct Access : DAX)
- Windows Server 2016 で、DAX でフォーマット
- トレースフラグ -T9921 を設定
- 二つ目のトランザクションログファイルを新しいボリュームに置く
- DAX と Block のドライブの併用??
- Spatial の速度向上
- SqlServerSpatial130.dll を使用するように変更
- Unmanaged → Managed → Unmanaged という流れではなくなったため高速化されている
- -T6534 の使用
- SqlServerSpatial130.dll を使用するように変更
- Log 転送の改善
- ストリームログブロックの並列化
- 複数の LOG WRITER
- ログの REDO の並列化
- スピンロックの競合の削減のためのコード改善
- その他
- 列ストアのバッチモードの拡張
- デフォルトのデータベースサイズの変更
- Windows Server 2016 での大容量メモリサポート
- TDE の AES-NI サポート
- ソート最適化
- バックアップ圧縮
- SMEP
- Intel Platform Protection Technology (OS Guard) – SMEP
- Query Compilation Gateways
- In-Memory OLTP の拡張
Make it real with Always Encrypted, Stretch Database and Temporal Tables
- ストレッチデータベース
- 常に増えるデータベースに対してのチャレンジ
- コストを抑えてコールドデータをオンラインにしておく
- アプリケーションの変更なく、データにアクセスすることができる
- デフォルトでセキュアな状態にはなっているが (セキュリティを担保したデータ転送)、Always Encrypted と Row Level Security を利用することでさらにセキュリティを向上
- フィルターを設定してストレッチすることが可能
- バックアップをリストアした後は、sp_reauthorize をしてストレッチを有効化する
- Always Encrypted
- クライアント側での暗号化
- センシティブなデータを取り扱うためのキーは DB のシステムでは保持しない
- 暗号化されたデータへのクエリ実行
- 暗号化されたデータに対しての一般的な操作のサポート
- アプリケーションの透過性
- サーバー / クライアントライブラリの拡張による最小のアプリケーションの変更
- SSMS / SSDT (New) / PowerShell (New) で利用可能
- 暗号化キーのバックアップを忘れないこと
- クライアント側での暗号化
- Temporal テーブル
- 重要なデータの変更のトラッキング
- コードの変更は不要
- 特定の時間のデータを確認可能
- AlwaysEncrypted と併用し、データの常にセキュアな状態で取り扱うことも可能
- 重要なデータの変更のトラッキング
Manage your cold data with SQL Server Stretch database
ビデオ / スライド
- ストレッチデータベースはシンプルに SQL Server と SQL Database をハイブリッドで使用することができる
- 85% のエンタープライズのデータはコールドデータの状態
- SQL Server と SQL Database はセキュアな接続が行われている
- セキュアチャネルを経由してデータの移動が行われている
- TLS1.1 / 1.2
- AAD 認証をサポート
- セキュアチャネルを経由してデータの移動が行われている
- すべてのバージョンの SQL Server 2016 で利用可能
- バックアップとリストア
- DBA はローカルのホットデータに対してバックアップ / リストアを実施
- ストレッチはローカルとリモートで一貫性のあるトランザクションを確保する
- ローカルのリストアが完了するとストレッチデータベースはリモートのデータを自動的に調整する
- リストアの時間はリモートのデータサイズには依存しない
- AlwaysOn でも動作する
- セカンダリを利用して迅速にシードする
■Windows Server 2016
Learn how SQL Server 2016 on Windows Server 2016 are better together
- ドメイン不要のクラスター
- クラスターのシームレスなアップグレード
- Windows Server 2012 R2 から 2016 にインプレースアップグレードしながらのアップグレード
- ストレージレプリカを使用したマルチサイトのクラスターインスタンス
- クラウド監視
- Fast Transaction in SQL Server : Tail of Log
- Storage Space Direct
Maximize breakthrough server performance with Microsoft solutions from Lenovo
- Storage Space Direct
- NVMe のサポート
- ローカルの SATA ディスクの利用
- RDMA 対応の NIC
- Storage Space Direct の構成では NIC の性能も重要
- RDMA のありなしでは 3 割り近くスループットが異なる
- diskspd を使用した確認
- CPU の負荷も低くなる
- 分離についての概念
- テナントの分離
- SDN を使用した分離
- VXLAN : Windows Server 2016 の標準
- NVGRE も現状サポートされている
- NVGRE
- VXLAN : Windows Server 2016 の標準
- NIC のハードウェアオフロードも重要
- ネットワークの構成
- エントリーレベル
- シングルスイッチ / シングルポート
- データセンター
- マルチシャーシのスイッチ / デュアルポート
- レガシーなエンタープライズ
- デュアルスイッチ / デュアルポート
- エントリーレベル
- リモートデスクトップサービス
- vGPU の拡張
- OpenGL / OpenCL サポート
- ダイレクト GPC マッピング
- Gen2 仮想マシンでの、vGPUサポート
- VDI OS としての Windows Server
- サーバー OS での vGPU サポート
- vGPU の拡張
- Shielded VM
- 物理環境の管理者やホスターを信頼しているという考えを修正することができる機能
- VM ゲストをホストの管理者と分離する
- ホストガーディアンサービス
- vTPM
- 物理環境の管理者やホスターを信頼しているという考えを修正することができる機能
- TPC-H のベンチマーク結果
■Linux
See why Microsoft loves Linux and Open Source
- ひとまずSQL Server に関連するものだけ
- SQL Server on Linux についてのいくつかの発表
- スケジュール
- March 2016 : アナウンスとプライベートプレビューの開始
- May 2016 : プライベートプレビュー リリース #2
- June 2016 : プライベートプレビュー リリース #3
- Late 2016 : パブリックプレビュー
- Mid 2017 : RTM / GA
- SQL Server on Linux
- OS と SQL Server の各種機能の間に、SQL Platform Abstraction Layer (SQLPAL) という抽象化レイヤーを設けている
- 既存の SQL OS (SOS) が拡張 (SQL OS v2) されており、SOS v2 が、SQL OS API / Win32 API or Linux API が OS とのシステムコールを吸収する形になる
- パフォーマンスを重視するものについては、SQL OS API → SQLOS v2
- パフォーマンスの重視が低いものについては、Win32 API , Linux API SQLOS v2
- 開始時は、Ubuntu と RHEL
- スケジュール
■可用性
Design a private and hybrid cloud for High Availability and Disaster Recovery with SQL Server 2016
- AlwaysOn Failover Clustering
- AlwaysOn 可用性グループ
- オンプレミスと Azure にまたがった構成
- データベースレベルのフェールオーバートリガー
- データベースのオフラインの検知
- DTC サポート
- より優れた自動フェールオーバーのサポート
- 同期セカンダリの自動フェールオーバーを三台で設定可能
- ログ転送のパフォーマンス向上
- ロードバランスされた読み取りセカンダリ
- gMSA サポート
- gMSA は 2014 でサポートが始まったが、可用性グループについては 2016 でサポート
- 分散可用性グループ
- 可用性グループのシード処理
- ドメインを使用しない可用性グループのサポート
- Standard Edition での可用性グループ
- 2 ノード
- 可用性グループには 1 つのデータベース
- 読み取り可能 / バックアップとしてセカンダリを利用できない
Keep the lights on with Windows Server 2016 Failover Clustering
- ストレージ QOS
- 共有 VHDX 統合
- Cluster Shared Volume (CS) キャッシュ
- 診断の改善
- ダンプのサイズ削減
- Active memory Dump
- ゼロダウンタイムデバッグ
- サンダーボルトネットワーク
- 仮想マシンの弾力性 (VM Compute Resiliency)
- 障害が発生した際にダウンタイムを抑えて障害を回復させる
- 仮想マシンストレージの弾力性
- ノードのフラッピングの検疫
- シンプルな SMB マルチチャネル
- VM スタート順序
- マルチドメインクラスター
- ドメインレスクラスター
- クラウド監視
- サイトの認識
- フォルトドメインの認識
- VM ロードバランス
- VM ホスト内での VM の配置の調整
- VM のロードバランスの公平性の調整
- VM ホストがノードに結合されたら、すぐにロードバランスを開始するか、結合されてから 30 分後にロードバランスを開始するか
- ロードバランスの積極性の調整
- 3 段階でロードバランスの積極性を変更可能
- 混合バージョンの互換性
- クラスターノードのノードのインプレースアップグレード
- ストレッチクラスターを使用したディザスタリカバリー
- マルチサイトクラスターのストレージレプリカ
- ストレージスペースダイレクト
■クエリストア
Design seamless upgrades to SQL Server 2016 with Query Store
- Query Store
- SQL Server のフライトデータレコーダー
- 時間のかかっているクエリの識別
- 退行しているクエリの識別
- アップグレードプロセス
- SQL Server 2016 へのアップグレード
- 互換性レベルの維持
- クエリストアによるクエリの収集
- 互換性レベルを 130 に変更
- 退行しているクエリの識別
- クエリの実行プランの比較
- プロパティに表示される情報も確認する
- 互換性レベル 130 にすることによる利点
- INSERT SELECT ステートメントの並列プラン
- メモリ最適化テーブルの並列プラン
- 基数推定の改善
- 列ストアインデックスに対しての大きな改善
- 統計の改善
Dive deep into application performance using Query Store in SQL Server 2016 and Azure SQL Database
- Query Store のユーザー事例
- ORIFLAME
- DOMINION ENTERPRISES
- ChannelAdvisor
- VSO のテナントで、Query Store により最適なクエリパフォーマンスの確保を実施
- CPU のスパイクが発生した際に、Query Store による調査を実施
- SQL Server 2016 へのアップグレード
- Query Store を有効にしプランをキャプチャ
- 互換性レベルを 130 に変更
- 過去のプランを強制的に適用することによるクエリの実行速度の退行の処置を実行
- Query Store の基本的な構成
- コンパイル
- プランストアに情報を格納
- 実行
- ランタイム統計ストアに情報を格納
- コンパイル
- SQL Database の Query Store
- オンプレミスの Query Store と同一の互換性
- Query Capture のデフォルトが AUTO
- Power BI を使用したクエリストアの情報の可視化
■Operational Analytics
Enable operational analytics in SQL Server 2016 and Azure SQL Database
- OLTP と、データ解析を同一のデータベースを使用して、最小のデータの遅延で解析を実施
- Real Time Analytics
- 最小のデータの遅延 / ETL を使用しない / データウェアハウスの分割をしない
- 非クラスター化列ストアインデックス
- クラスター化インデックス / ヒープのテーブルに対して、非クラスター化列ストアインデックスを付与
- 2016 では非クラスター化列ストアインデックスは更新可能
- 分析用に作成された他のすべてのインデックスを削除する
- OLTP のアプリケーションの変更は不要
- クラスター化インデックス / ヒープのテーブルに対して、非クラスター化列ストアインデックスを付与
- 列ストアインデックスによるオーバーヘッド
- DELETE
- デルタストアへの変更
- DELETE 行バッファへのキーの挿入
- UPDATE
- 行の削除
- 行を削除して、デルタストアに新規の行を挿入する
- 行の削除
- 削除された行の回収のため ALTER INDEX REORGANIZE を実行 (オンライン処理)
- DELETE
- オーバーヘッドの最小化
- フィルター化した非クラスター化列ストアインデックスの利用
- 直近のホットデータを列ストアに含めず、コールドデータのみを列ストアに配置することで、最小のメンテナンスとする
- クエリはホットデータと列ストアの両方に透過的にアクセスされる
- 直近のホットデータを列ストアに含めず、コールドデータのみを列ストアに配置することで、最小のメンテナンスとする
- COMPRESSION_DELAY の利用
- デルタ行グループのデータ圧縮は Compression_Delay の待機時間を経過した際に、実行されるため、インデックスの断片化を最小限に抑えることができる
- 可用性グループのセカンダリレプリカを使用した、解析処理の実施
- フィルター化した非クラスター化列ストアインデックスの利用
- In-Memory OLTP テーブル + 列ストアインデックス
- Delta RG として、In-Memory OLTP を利用
- Compression Delay により、ホットデータを In-Memory OLTP に配置
Review In-Memory OLTP in SQL Server 2016 and Azure SQL Database
- Fast Transaction
- In-Memory OLTP
- Fast Analytics
- In-Memory DW : Column Store
- In-Memory OLTP
- メモリ最適化データ構造
- ロック / ラッチフリー
- ネイティブコンパイルされた T-SQL モジュール
- InーMemory OLTP の機能
- メモリ最適化テーブル
- メモリ最適化テーブル型
- インデックスはメモリにのみ存在する
- データベース起動時に再構築される
- データ行を持つのではなく、インデックスはデータ行へのポインタとなる
- 非クラスター化インデックス
- 行の変更により拡張 / 縮小する
- 範囲 / オーダースキャンに最適化
- ハッシュインデックス
- サイズは変更されない (バケットサイズ)
- ポイントルックアップと INSERT に最適化
- 列ストアインデックス
- データのセカンダリコピーとして、圧縮された列ストア形式
- 大量データのスキャンに最適化
Review ColumnStore Index in SQL Server 2016 and Azure SQL Database
- CCI は通常の行データと比較して、大きなデータ圧縮の効果がある
- 2.5GB のデータが 700MB 程度になっている。(sp_spaceused での確認結果)
- なぜ列ストアインデックスを使用するか
- 圧縮効率の改善
- パフォーマンスの改善
- I/O の削減
- TPC-H ベンチマークでは、同一ハードの 2014 と 2016 の結果では、2016 が 40 % ほどパフォーマンスが向上している
- 伝統的な DW の構成
- MOLAP (Multi Dimension)
- データをリフレッシュするまでのデータの状態の遅延
- レポートがキューブに制限される
- 定期的なデータのリフレッシュ
- Vertipaq (Tabuler)
- データをリフレッシュするまでのデータの状態の遅延
- 定期的なデータのリフレッシュ
- MOLAP (Multi Dimension)
- SQL Server 2016 を使用した DW
- Multi Dimension
- ROLAP
- Tabuler
- Direct Query
- Multi Dimension
- 列ストアへのデータローディング
- 外部ファイルからのインポート (BCP / Bulk Insert / SSIS)
- バッチサイズを 10 万行未満にした場合
- 行はデルタ行ストアに挿入され、新しい行グループとして圧縮される
- 複数のスレッドで並列に処理がされた場合に、各スレッドで処理された内容がどのようにデルタストアに格納されるかを意識しておく
- 最小ログ記録モード
- 行ストアの場合と異なり、TABLOCK を指定しな
- オープンされている行グループの圧縮
- ALTER INDEX REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
- ON : オープン/クローズの状態に関係なく、列ストアへの圧縮を強制する
- 行データは TOMBSTONE 状態となるが GC により解放される
- OFF : クローズされたものについて、列ストアへの圧縮を強制する
- この方法だと、各行グループがそのまま圧縮されるため、100000 / 100000 / 100000 という状態で、行グループでデルタストアが格納されていた場合、圧縮をすると 100000 / 1000000 / 1000000 という 3 つの列圧縮された行グループが作成された状態となっていた。
- ON : オープン/クローズの状態に関係なく、列ストアへの圧縮を強制する
- ALTER INDEX REORGANIZE
- 分割されていた行グループがマージされた状態となる
- 上述の状態の場合は 300000 という一つの行グループにマージされた状態となる
- 分割されていた行グループがマージされた状態となる
- ALTER INDEX REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
- バッチモード処理
- 効率化のため、複数の行をまとめて処理する
- 実行される命令を大幅に削減
- バッファサイズ :64 K
- 64 ~ 900 行をまとめて処理する
- SQL Server 2016 では、シングルスレッドでもバッチモードが実行可能
- バッチモードの処理対象も増加している
- 互換性レベルが 130 の必要がある
- 集計や文字列検索のための、処理効率を向上させるための効率化が実施されており、2014 と比較して、効率化が行われている
- 処理時に、すべての行をスキャンしなくても対応できるような改善が行われており、実行プランを見た時に、次の処理に流れている行数を確認すると 0 となっているところからその改善が確認できる
- ディクショナリ等のデータを使っているとのこと
- 処理時に、すべての行をスキャンしなくても対応できるような改善が行われており、実行プランを見た時に、次の処理に流れている行数を確認すると 0 となっているところからその改善が確認できる
- 情報の取得
- 新しい DMV
- sys.dm_column_store_object_pool
- sys.dm_db_column_store_row_group_operational_stats
- sys.dm_db_column_store_row_group_physical_stats
- sys.dm_db_index_phisical_stats
- sys.dm_db_index_operational_stats
- 新しい拡張イベント
- clustered_columnstore_index_rebuild
- column_store_expression_filter_apply
- column_store_expression_filter_bitmap_set
- column_store_index_build_low_memory
- 新しいパフォーマンスモニターのカウンター
- SQLServer:Columnstore
- 新しい DMV
- クラスター化列ストアインデックスの同時並行性の工業
- SI / RCSI をサポートしたとこでブロッキングの緩和
- 非クラスター化列ストアインデックスの DELETE / INSERT における行レベルロック
- 2014 では行グループレベルで実施されていた
Hear customer success stories with columnstore index in SQL Server 2016
- SQL Server 2016 の列ストアの改善
- パフォーマンス
- 文字列述語のスキャンノードのプッシュダウン
- 集計プッシュダウン
- バッチモードの処理対象となる操作のつい以下
- SIMD (Single Instruction MultipleData) aware
- データウェアハウスの機能
- 非クラスター化インデックスサポート
- 小規模の範囲のデータ取得
- 行レベルのロックと RCSI / SI のサポート
- AlwaysOn 環境の読み取りセカンダリによるレポーティング
- 非クラスター化インデックスサポート
- リアルタイムデータ解析の機能
- 更新可能な非クラスター化インデックス
- メモリ最適化テーブルでの列ストアインデックス
- インデックスフラグメンテーションの解消
- パフォーマンス
- 既存テーブルに対して非クラスター化列ストアインデックスを適用することで、アプリケーションの変更なしに列ストアを利用しパフォーマンスを向上させる
- 行ストアのページ圧縮しているテーブルと比較して、大幅なデータサイズ / CPU 使用率 / クエリの処理時間の削減の効果があった
Explore In-Memory OLTP architectures and customer case studies
- 種類
- メモリ最適化テーブル
- メモリ最適化テーブルデータ型
- ネイティブコンパイルストアドプロシージャ
- 永続化オプション
- 完全永続化
- DURABILITY = SCHEMA_AND_DATA
- 遅延永続化
- COMMIT WITH (DELAYED_DURABILITY = ON)
- BEGIN ATOMIC WITH (DELAYED_DURABILITY = ON)
- ALTER DATABASE CURRENT SET DELAYEDDURABILITY = FORCE
- 遅延永続化は可用性グループの自動フェールオーバーでも適用することができる
- 同期コミットの低レイテンシでの実行
- 永続化なし
- DURABILITY=SCHEMA_ONLY
- ステージングの一時テーブル等で使うと効果的
- 完全永続化
- In-Memory OLTP を使用しないケース
- 解析や BI のパフォーマンスの向上 : 列ストアの方が効果がある可能性がある
- パフォーマンスのボトルネックが SQL ではない場合
- 少ないトランザクションが実行されている
- リソースの上限
- メモリの使用状況がすでに上限近くになっている場合
- 大きな範囲でのデータのスキャン : 列ストアの方が効果がある可能性がある
- ショートトランザクションでログの I/O が低いケース
- 遅延永続化で解消できるケースもある
- テーブルのサイズがメモリーにフィットしない場合
- ネイティブコンパイルストアドを使用しないケース
- 解析用のクエリ
- OLTP 向けのトランザクションにはネイティブモジュールは適している
- 並列クエリ
- ネイティブモジュールはシングルスレッドで動作するらしい
- ハッシュ結合が適している
- ネイティブモジュールはネステッドループのみをサポート
- 解析用のクエリ
- In-Memory OLTP を利用するシナリオ
- 高スループットな OLTP で低レイテンシが求められる場合
- データをロードする際のバッファとしての利用
- 一度メモリ最適化テーブルに格納し、そこから定期的に列ストアのテーブルに移動させる
- 中間層のキャッシュとしてメモリ最適化テーブルを利用
- IoT のデータを格納
- IoT のデータを SCHEMA_ONLY のデータとして、格納し Excel で解析
- セッション情報のキャッシュ
- メモリ最適化テーブル型を使用 / 永続化しないメモリ最適化テーブルによる tempdb の置き換え
- 永続化しないテーブルによる ETL のステージングテーブルとしての利用
■SQL Database
Achieve developer productivity with SQL Server 2016 and Azure SQL Database
- DBaaS v2.0
- 統合されたデータベースサービス
- プラットフォームが DB の実行とチューニングを管理する
- Query Store / Query Insightよるクエリパフォーマンスの収集とモニタリング
- Query Store = DB のフライトレコーダー推奨事項に合わせたチューニング
- 自動チューニング
- SQL Database Advisor による実ワークロードを元にした、自動チューニング
- Ealstic DB pool により、DB のコストを効率化
- Set-AzureRmSqlDatabaseAdvisorAutoExecuteStatus によるスクリプトからの自動チューニング設定
Create elastic database patterns for SaaS applications in Azure
- 単一データベース vs Elastic Pool データベース
- EXTERNAL DATA SOURCE を TYPE=RDBMS で使用した、垂直パーティショニング
- EXTERNAL DATA SOURCE を TYPE=SHARD_MAP_MANAGER で使用した、水平パーティショニング
- Elastic Database Query によるデータベースを横断したクエリ実行
- シャード外部テーブルでは、分散方式を選択することができる
- SHARED(ColumnName) | REPLICATED | ROUND_ROBIN
- Elastic Job を使用した、Elastic Database Pool 内の複数のデータベースの管理
- Elastic Database クライアントライブラリ
- Elastic Transaction
- SQL Database の複数の DB に対してのトランザクション
- .NET 4.6.1 以降が必要
Learn Azure SQL Database best practices
- http://bit.do/luan-moreno
- パフォーマンスのベースライン
- ワークロードの解析
- サービス階層の推奨
- 移行のメソッド
- SSMS
- BacPac + Portal / PowerShell / SSMS
- SQL Azure Migration Wizard
- SSDT + BCP / SSIS
- Azure Data Factory
- Transactional Replication
- リンクサーバー
- ビジネスの継続性
- Backup / Restore
- Active Geo Replication
- 削除したデータベースのリストア
- BacPac によるインポート / エクスポート
Explore Azure SQL Database – the intelligent cloud database for app developers
- DB を使う多くのユーザーは DBA ではなく開発者
- アプリケーションのパフォーマンスをシンプルに改善
- SQL Databas Advisor
- 自動パフォーマンスチューニング
- マルチテナントの DB のコストの効率化
- Elastic データベースプール
- アプリケーションを最適にし、コストの低減
- Query Performance Insights
- 現状のデータベースを Elastic データベースに変更した場合の推奨のプールサイズや削減のコストを表示する機能が、ポータルに含まれている。
- データのセキュリティ
- セキュリティの推奨
- Azure セキュリティセンター
- データベース監視 / TDE / 驚異の検出 の設定状況の確認 / 設定ができる
- データの暗号化による物理ストレージの暗号化
- SQL Database の監査とモニタリング
- データベース監視 / TDE / 驚異の検出 の設定状況の確認 / 設定ができる
- 動的データマスク
- 驚異の検出 (パブリックプレビュー)
- SQL インジェクションの検出
- 異常なアクセスの検出
- リアルタイムな警告
- Azure セキュリティセンター
- セキュリティの推奨
Build smarter and scalable applications using Microsoft Azure Database Services
- PowerShell による ShardMap の操作
- Elastic Database Tool
- 動的データマスク
- 行レベルセキュリティ
- Document DB
- サンプル
- https://github.com/Microsoft/SaaSApp-ContosoShopkeeper
- https://github.com/Microsoft/azure-sql-security-sample
- https://github.com/Microsoft/Build-Mobile-App-with-chat
- https://github.com/ealsur/mvpstreambot
- https://github.com/liamca/AzureSearchOCR
- https://github.com/liamca/azure-search-machine-learning-text-analytics
■Data Warehouse
Explore SQL Server FastTrack Data Warehouse (FTDW)
- Data Warehouse Solution
- SQLServer 2016
- Data Warehouse Fast Track (DWFT)
- 2016 版の構成もいくつか出ている
- Analytics Patform System (APS)
- Balanced Platforms
- Fast Track Feature
- ページ圧縮
- 列ストアインデックス
- Operational Analytics
- バッチモード処理
- DWFT Reference Configuration
- CPU : Xeon E5-2600
- Memory : 256 GB
- Storage 1.1TB SSD × 8 : tempdb (RAID 1) / 400GB × 4 : log (RAID 10)
- このシステムで 25TB 程度のユーザーデータのキャパシティを持った環境となる
- 今後のサポート拡張
- 仮想マシン上での動作
- Azure / 他のクラウドでの利用
- SQL Server on Linux
- R
- Polybase
Understand how Attom Data Solutions is using SQL Server 2016 to accelerate their business
- 日のインプットのトランザクション量は4 ~ 5 百万行
- バルクデリバリーの領域に関しては、4 億 ~5 億行
- SQL Server 2012 のレガシーなシステムでは、データをデリバリーするジョブについては、日次のもので 1 日、月次のもので 5 日程度処理が行われている
- データウェアハウスを近代化するプロジェクト
- クラスター化列ストアインデックス
- バッチロードの高速化
- SQL Server 2016 では、バッチモードの操作対象の増加 (ソート / ウィンドウ集計関数)
- データサイズの大幅な削減 (テーブル内のデータサイズが増えるほど、列ストアによるサイズの削減の度合いが顕著になってくる)
- Read / Write に対しての I/O のレイテンシの削減
- スケールアウトワークロード
- AlwaysOn 可用性グループ
- SQL Server 2016 で、読み取り専用セカンダリでは、クラスター化列ストアインデックスをサポート
- 今回の環境では同期 / 自動フェールオーバー環境として利用
- 読み取りワークロードのスケール
- AlwaysOn 可用性グループ
- 改善による効果
- 1 日かかっていたデイリーの処理が 8 時間、5 日かかっていた月次の処理が 2時間 30 に短縮
- 列ストアのローディング動作の違いによる、 ETL プロセスの変更
- T-SQL のマージステートメントを大きなデータの Insert ストリーム / Update ストリームに置き換え
- 列ストアのいくつかの行グループに関しては、100 万行以下
- Temporal テーブルの利用
- クラスター化列ストアインデックス
- SQL Server 2016 の R 統合
- 今後
- Query Store によるクエリチューニング
- クラウドプラットフォームへの拡張
Examine Azure SQL Data Warehouse: scaling configuration and guidance
- Elastic Scale コンセプト
- 基本構成
- Control × 1
- 複数の Compute
- プレミアムストレージ上の複数のデータベース
- GEO 冗長化
- DWU
- 100 ~ 6000
- 基本構成
- Compute のスケーリング
- データベースサイズによって、推奨される DWU の開始サイズがある
- 0~4 TB であれば、100~400DWU で開始するのが推奨される DWU サイズ
- スケーリングの要素
- 同時実行性
- トランザクションサイズ
- データロード
- メモリ
- クエリの同時実行性
- 最大で 32 の同時実行
- DWU とリソースクラスによって、同時実行スロット同時実行のスロットの数が異なる
- トランザクションサイズ
- DWU に応じて最大トランザクションサイズが異なる
- データロード
- DWU サイズに応じて、最大の外部リーダー (Max External Readers) とライターの数 (Max Writers) が異なり、DWU サイズを変更することでロードのスケールも上がってくる (Polybase をうまく使う)
- メモリーの管理
- smallrc はサービス管理のアカウント用途で使用する
- 既定では smallrc になっている
- リソースグループと DWU サイズによって、ディストリビューションごとのメモリーサイズが異なってくる
- 各ディストリビューションにどの程度のデータが格納されているかを把握し、そのサイズに応じて DWU とリソースグループを調整する
- smallrc はサービス管理のアカウント用途で使用する
- データベースサイズによって、推奨される DWU の開始サイズがある
- ストレージサイジング
- ローカルストレージ / プレミアムストレージ (リモート)/ BLOB ストレージ (リモートかつ GEO 冗長) の 3 層構成
- プレミアムストレージと BLOB ストレージの部分が請求対象
- ローカルストレージ
- tempdb
- DWU サイズに応じて tempdb のサイズも変わってくる
- 100DWU /399
- BLOB
- Geo バックアップで使用
- プレミアムストレージ
- データファイル / ログファイル / スナップショット
- プレミアムストレージのサイズのキャパシティ : 240 TB
- 列ストアインデックスで 5 倍の圧縮効果があったとした場合は、最大で 1P バイト超の DB が上限となる
- 割り当て済み / 未割当 / インデックス / データ / ログのトータルサイズがストレージサイズとなる
- 割り当て済み / 未割当関係なく、プレミアムストレージのサイズとなる
- スナップショット
- 4 時間間隔で 7 日間保持
- RPO : 8 時間
- BLOB ストレージの Geo バックアップ
- 保持期間 : 1
- RPO : 24 時間
- ストレージの CAP
- DB 作成時に、MAXSIZE で指定することができ、ALTER DATABASE で変更可能
- サイズの確認
- データベースサイズ : sp_spaceused
- テーブルサイズ : DMV
- スナップショットサイズ : ポータルのトータルストレージサイズ – sp_spaceused の DB サイズ
- 未割当の領域 : sp_spaceused
- 価格
- コンピューティング : $900/100DWU/月
- ストレージ : 122.88TB/月
- データファイル/ログファイル/スナップショットが含まれる
- Geo 冗長バックアップ
- $0.12GB/月
- ローカルストレージ / プレミアムストレージ (リモート)/ BLOB ストレージ (リモートかつ GEO 冗長) の 3 層構成
Design for Big Data with Microsoft Azure SQL Data Warehouse
- 分散されたクエリ
- コントロールノードからコンピュータークラスターのノードに分散され実行され、分散されたストレージにアクセス実行される、結果をコントロールノードから返却される
- 各コンピュートノードで実行された結果がコントロールノードから返却される
- 監査と脅威検知によるセキュリティの実装
- データローディング
- 伝統的なローディングツールの利用
- Polybase の利用
- パイプラインツール (Azure Data Factory / Redgate Data Platform Studio) の利用
- 非構造化データへの Polybase/TSQL でのアクセス
- データレイクを使用した新しいビジネスの創出
- 既存のスキル / BI ツールの利用
- シンプルな ETL プロセスを使用した洞察の高速化
Query Big Data using the expanded T-SQL footprint with Polybase in SQL Server 2016
- RDBMS と HDFS を Polybase でつなぐ
- Polybase はスケーラブル
- T-SQL によりデータを取得できる
- APS / SQL Server 2016 は Hadoop と BLOB ストレージにアクセス可能
- SQL Data Warehouse は BLOB ストレージのみ
- Polybase の構成
- SQL SErver 2016 Enterprise Edition
- PolyBase Engine
- Windows Server
- 分散クエリ処理
- PolyBase DMS
- Windows Servic
- データの読み取り / 書き込みをを HDFS から SQL Serve に対して実施する
- PolyBase のデータベース
- DWConfiguration
- PolyBase エンジンと DMS の構成情報
- DWDiagnostic
- 分散クエリの診断情報
- DWQueue
- ロールバックするために必要となる情報
- これらのデータベースは変更しない
- DWConfiguration
- スケールアウトグループ
- ヘッドノードで SQL Server 2016 / PolyBase エンジン / PolyBase DMS が動作
- コンピュートノードでは、SQL Server 2016 / PolyBase DMS が動作
- それぞれのノードが、HDFS のデータにアクセスができるようになっている
- PolyBase クエリのライフサイクル
- クエリは外部テーブルを参照するヘッドノードに対して要求される
- HDFS データを格納するための一時テーブルをヘッドノードに作成
- 拡張プロパティをテーブルに設定
- 外部データの統計と既知の統計の更新
- ヘッドノードは DMS を経由して今ぴゅーとノードの情報を集計
- コンピュートノードで一時テーブルが作成される
- 分散クエリが実行される
- HDFS ブリッジを使用して、一時てテーブルにラウンドロビンでデータをインポート
- PolyBase DMS の後にデータをフィルタリング
- 一時テーブルまたはメモリにデータを書き込み
- データの返却とクリーンアップ
- ユーザーに結果を表示
- コンピュートノードの一時テーブルを削除
- ヘッドノードの一時テーブルを削除
- クエリは外部テーブルを参照するヘッドノードに対して要求される
- Hadoop の外部テーブルを作成し、その外部テーブルに対して、INSERT INTO し、Hadoop 上にデータをエクスポートするデモ
■マイグレーション
Understand how ChannelAdvisor is using SQL Server 2016 to improve their business
- ChannelAdvisor
- プライベートクラウドベースの e-コマースソリューション
- 環境
- 77 の稼働中の SQL Server 2016 CU1 を使用している
- 2200 以上のデータベース
- 928 コア / 22 TB のメモリを SQL Server で使用しており、100% 仮想化している
- オールフラッシュアレイを使用している
- 10GB から 8TB までのデータベースのサイズ
- ページ圧縮や列ストアを使用して I/O とメモリの使用を削減している
- SQL Server 2016 へのアップグレード
- 新規の SQL Server 2016 を仮想環境上に構築
- SQL Server 2014 の可用性グループから SQL Server 2016 の 可用性グループに
- クエリストアを使用したクエリプランの変更
- PowerShell を使用した再実行 / 並列によるアップグレードの処理
- 2 時間のメンテナンスウィンドウで 57 のデータベースサーバーをアップグレード
- 互換性レベル 120 でクエリストアを有効にし、130 に変更
- クエリストア
- 強制化の失敗したクエリを sys.query_store_plan から確認
- In-Memory OLTP
- ディスクベースのステージングテーブルを永続化しないメモリ最適化テーブルにリプレース
- 一時オブジェクトをメモリ最適化テーブル型を使用したテーブル型変数に変更
-
tempdb の競合の低下にもつながる
-
ネイティブコンパイルされたストアドプロシージャでしお湯している
-
-
管理
-
メモリ最適化テーブルのファイルグループは異なるドライブやマウントポイントに設定している
-
メモリ最適化オブジェクトは特定のスキーマに作成
-
リソースガバナーの使用
-
バケットカウントを sys.dm_db_xtp_hash_index_stats で確認し、ALTER INDEX REBUILD で適宜変更
-
すべてのトランザクションとクエリは、一つのデータベースで実行されるように設計
-
-
列ストアインデックス
-
SQL Server 2014 では、ファクトテーブルのみに列ストアを設定していたが、2016 では、データの大きいディメンションテーブルにも追加した
-
オンラインでの列ストアのメンテナンス
-
REORGANIZE (WITH COMPRESS ALL ROW GROUPS ON)
-
削除された行による劣化を時間の経過とともに発生するのを防ぐため
-
-
-
その他の機能
-
間接チェックポイント
-
TF1117/1118 の変更
-
tempdb はデフォルトで設定されている状態
-
ユーザーデータベースは、ALTER DATABASE による設定
-
-
ネイティブ圧縮関数
-
COMPRESS(ParametersGzippded)
-
CONVERT(NVARCHAR(MAX),DECOMPRESS(PARAMETER(GZipped))
-
-
AG の直接シード処理
-
TF9567 による圧縮の実施
-
-
証明書に RSA_1024 を使用している場合、互換性レベル 130 では廃止されている
-
Migrate from Oracle to Microsoft SQL Server
- Microsoft Assessment and Planning Toolkit (MAP) を使用した Oracle を使用している環境の検出
- SQL Server Migration Assistant for Oracle (SSMA) を使用したマイグレーション
- マイグレーション
- スキーママイグレーシン
- 自動スキーマ変換
- カスタムタイプマッピング
- 手動コード変換
- データマイグレーション
- SSMA
- SSIS
- スキーママイグレーシン
- アプリケーションマイグレーション
- インラインクエリ
- 動的 SQL
- スループットのプロファイリング
- SQL トレースファシリティ
- TKPROF
- クライアントからの接続
- OCI / JDBC / ODBC
- CDC を使用した移行
- Change Data Capture Service Configuration for Oracle by Attunity
- 初期ロード / 増分ロード
- https://msdn.microsoft.com/ja-jp/library/hh231087.aspx
- Oracle はアーカイブログモードにする
- Change Data Capture Service Configuration for Oracle by Attunity
- SSMA は Azure への移行もサポートしている
- Database Migratoin Assistant
- SQL Server Upgrade Advisor の置き換え
■セキュリティ
Secure your Microsoft Azure SQL database – it’s easier than ever
- データの保護
- TLS / TDE / Always Encrypted
- アクセス制御
- Azure Active Directory / 行レベルセキュリティ / 動的データマスク
- 積極的なモニタリング
- 監視 / 脅威検知
Secure your data at rest – on demand, now!
- SQL Server のセキュリティ
- 認証
- 従来からのアプリケーションの下位互換として、混合認証を使用する必要のない場合はWindows 認証を使用
- sysadmin (sa) アカウント
- インストール後にsa のアカウント名を変更する
- パスワード
- パスワードルールの強制と初回ログイン時の変更
- サービスアカウント
- 各サービスに異なるアカウントを使用
- それぞれのアカウントに強いパスワードを設定
- アカウントの権限は低いものとする
- sysadmin のメンバーシップ
- sysadmin の固定サーバーメンバーシップは慎重に選ぶ
- ゲストアクセスの無効化
- msdb を除きゲストアクセスはすべてのデータベースで無効化する
- パブリックな権限の制限
- 一部の拡張プロシージャやその他のストアドプロシージャで Public のロールの権限をチェック
- SQL Server のポート
- 必要に応じて既定のポートからの変更
- SQL Server Browser
- SQL Server Browser を有効にしない
- Microsoft Baseline Security Analyzer (MBSA) を使用したセキュリティの確認
- 認証
- SQL Server 2014 のセキュリティ拡張
- 暗号の拡張
- 暗号化キーの管理
- AES-256 のサポート
- PowerShell / SMO / SSMS/ T-SQL による管理
- 新しい権限
- CONNECT ANY DATABASE
- IMPERSONATE ANY LOGIN
- SELECT ALL USER SECUREABLES
- SQL Server 2016 のセキュリティ拡張
- 行レベルセキュリティ
- 動的データマスク
- Always Encrypted
- クラウドセキュリティ
- 登録海サーバーの情報が「C:\Users\masayuki.ozawa\AppData\Roaming\Microsoft\Microsoft SQL Server\130\Tools\Shell」に格納されているため、記録されている情報には注意する