不動の鳥の勉強記録

時間があるときに勉強したことをメモします。

SQL Server用のテストフレームワークtSQLtのインストール

SQL Server tSQLt

■はじめに

SQL Server上で動くモジュールの品質を高めるためのテストフレームワークにtSQLtというものがあると聞いたので勉強しました。
環境面での安定稼働は進めてきたので、今後はビジネスロジックにも手を伸ばして品質向上を進めていきたいと思っています。

■tSQLtとは

tSQLtはSQL Server用の単体テストフレームワークのオープンソースです。下記サイトが公式サイトです。

tsqlt.org

SQL Server 2005 Service Pack 2エディション以降のすべてのエディションのSQL Serverとの互換性があるとのことです。SQL Server 2000を利用している人は…早く利用しなくする活動をしましょう…

■インストール方法

サイトからダウンロードしたzipファイルを解凍すると5つのファイルがあります。
それぞれ下記内容となっています。

  • Example.sql: サンプルのsqlファイルです。
  • Lincense.txt: ライセンス事項が記載されているファイルです。
  • ReleaseNotes.txt: リリースノートが記載されているファイルです。
  • SetClrEnabled.sql: tSQLtの実行で必要なclr enabledを有効にするクエリが記載されています。
  • tSQLt.class.sql: tSQLtの必要セットがインストールされるsqlファイルです。

まず、tSQLtを利用するには、SQL Server構成オプションのclr enabledが有効になっている必要があるため、必要に応じて、SetClrEnabled.sqlを実行します。SQL Server 2017ではclr strict securityというサーバ構成オプションも追加されており、SQL Server 2017の環境ではこのサーバ構成オプションを無効にする必要があります。

次にtSQLt.class.sqlを実行します。これでインストールは完了です。
初めて利用するので公式サイトに記載のQuick Startを実行してみます。

■チュートリアル 1 FIX THE FAILING TEST

Quick Startに記載のチュートリアルに従い実行してみます。 ここでは、テスト用のDBを作成しテストケースの実行とバグフィクスを体験できます。

tsqlt.org

まずExample.sqlを実行します。このファイルを実行すると、tSQLt_ExampleというDBがインスタンス上に作成されます。このDB内にtSQLt.class.sqlの内容と同時にチュートリアル用の処理（スキーマがAcceleratorのオブジェクト）が作成されます。

tSQLtを実行してみます。

EXEC tSQLt.RunALL;

実行するとエラーメッセージが表示されます。

-- 抜粋
[AcceleratorTests].[test ready for experimentation if 2 particles] failed: Expected: <1> but was: <0>

内容はそのまま[AcceleratorTests].[test ready for experimentation if 2 particles]というストアドプロシージャで期待値と異なる値が返ってきたというものになります。
次に[AcceleratorTests].[test ready for experimentation if 2 particles]を見てみます。

ALTER PROCEDURE 
  AcceleratorTests.[test ready for experimentation if 2 particles]
AS
BEGIN
  --Assemble: Fake the Particle table to make sure 
  --          it is empty and has no constraints
  EXEC tSQLt.FakeTable 'Accelerator.Particle';
  INSERT INTO Accelerator.Particle (Id) VALUES (1);
  INSERT INTO Accelerator.Particle (Id) VALUES (2);
  
  DECLARE @Ready BIT;
  
  --Act: Call the IsExperimentReady function
  SELECT @Ready = Accelerator.IsExperimentReady();
  
  --Assert: Check that 1 is returned from IsExperimentReady
  EXEC tSQLt.AssertEquals 1, @Ready;
  
END;

中を見てみると、Accelerator.Particleテーブルに2行データを入れて、Accelerator.IsExperimentReady()という関数を実行しその結果が1と等しいか確認するテストケースです。Accelerator.IsExperimentReady関数を見てます。

CREATE FUNCTION Accelerator.IsExperimentReady()
RETURNS BIT
AS
BEGIN 
  DECLARE @NumParticles INT;
  
  SELECT @NumParticles = COUNT(1) FROM Accelerator.Particle;
  
  IF @NumParticles > 2
    RETURN 1;

  RETURN 0;
END;

この関数では、Accelerator.Particleの行数をカウントし、2を超える場合は1を返却し、2以下の場合は0を返すとなっています。テストケースを正とすると、この関数では2の場合に1を返却する想定となっており、「IF @NumParticles > 2」の判定式が誤りとわかります。そのためAcclerator.IsExperimentReady関数の判定式を 「IF @NumParticles >= 2」に修正を行います。

そして再度テストケースを実行してみます。

EXEC tSQLt.RunALL;

実行するとエラーメッセージの出力がなくなりました。

■チュートリアル 2 WRITE YOUR OWN NEW TEST

こちらでは自分のテストケースを作成する方法が紹介されています。

まずテストクラスを作成します。

EXEC tSQLt.NewTestClass 'TryItOut';
GO

これを実行するとTryItOutという名前のテストクラスが作成されます。このテストクラス名をスキーマとし、テストケースを作成していきます。

必ず失敗が記録されるテストケースを1つ作ります。

CREATE PROCEDURE TryItOut.[test this causes a failure]
AS
BEGIN
    EXEC tSQLt.Fail 'This is what a failure looks like';
END;
GO

続いて成功のテストケースを1つ作成します。

CREATE PROCEDURE TryItOut.[test this one passes]
AS
BEGIN
    DECLARE @sum INT;
    SELECT @sum = 1 + 2;

    EXEC tSQLt.AssertEquals 3, @sum;
END

テストを実行してみます。

EXEC tSQLt.RunAll;
GO

実行結果は下記のようになります。

[TryItOut].[test this causes a failure] failed: This is what a failure looks like

+---------------------+
|Test Execution Sumary|
+---------------------+

|No|Test Case Name                         |Result |
+--+---------------------------------------+-------+
|1 |[TryItOut].[test this one passes]      |Success|
|2 |[TryItOut].[test this causes a failure]|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 2 test case(s) executed, 1 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

TryItOutスキーマ配下のテスト用のストアドプロシージャが2つ実行されたことが確認できます。

最後にテストケースを削除します。

EXEC tSQLt.DropClass 'TryItOut';
GO

先ほど作成した2つのストアドプロシージャが削除されていることが確認できました。

■おわりに

まずはtSQLtを使うにはどうやったらいいかというところで、tSQLtのインストールとチュートリアルを記載しました。次回はtSQLtが用意している関数などを見ていきたいと思います。

SQL Serverのsp_whoisactiveについて

備忘録 SQL Server

■はじめに

SQL Serverで実行しているクエリを調査するときにsp_whoやsp_who2など利用することがあると思いますが、海外ではsp_WhoIsActiveが有名だと聞いたので勉強してみました。

■sp_WhoIsActiveとは

sp_whoやsp_who2、DMVなどSQL Serverが提供されている調査情報を一括で取得する機能を持った調査用のストアドプロシージャのようです。SQL Serverが提供されている機能だと、sys.dm_exec_requestをみて、sys.db_dm_os_wait_statsをみてといったように複数の動的管理ビューの情報を個別に調査して分析を行う必要があります。sp_whoisactiveを利用すれば一括で必要な情報を取得することができます。

ソースコードは下記にあります。

github.com

また、ドキュメントは下記になります。

whoisactive.com

ドキュメントの中にはインストール方法や設計概念などいろいろ記載があるので、見ていただけたらどんなものかわかるのではないかと思います。その中からいくつかピックアップして学んだことを記載していきます。

■インストール方法

githubからwho_is_active.sqlをダウンロードして実行します。このクエリを実行すると、dbo.sp_WhoIsActiveという名前のストアドプロシージャが作成されます。
どのデータベースに作成するか？ですが、ドキュメントにもありますがデータベース特有の機能ではなく、サーバ全体の状況を把握するもののため、masterに作成するのが良いのではないかと思います。
また、実行するにはview server state権限が必要になります。sp_whoisactiveを実行したアカウントで権限がなかったら追加してください。

■実行パラメータ

sp_whoisactiveは24のパラメータで取得したい情報を細かく制御することができます。すべての状況を俯瞰してみるよりは見たい情報を抽出してみるべきという概念で作成されているとのことです。
例えばいくつかのパラメータを見てます。パラメータの制御内容はwho_is_active.sqlに記載されています。

   --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
    --0 does not pull any sleeping SPIDs
    --1 pulls only those sleeping SPIDs that also have an open transaction
    --2 pulls all sleeping SPIDs
    @show_sleeping_spids TINYINT = 1,

この@show_sleeping_spidsではデフォルトが1となっており、1だとトランザクションを開いていればsleepingの状態のトランザクションも表示する設定になります。すべてのsleeping中のspidを表示したければ2に設定すれば表示することができるようになります。

    --Get associated query plans for running tasks, if available
    --If @get_plans = 1, gets the plan based on the request's statement offset
    --If @get_plans = 2, gets the entire plan based on the request's plan_handle
    @get_plans TINYINT = 0,

また、@get_plansでは、1を設定するとクエリの実行プランを取得することができます。
他にも多くのパラメータがあるので、検証を行いながら必要な情報を取得するお気に入りのパラメータが見つかるのではないかと思います。全部の情報を取得しようとすると分析も大変になってしまうので、発生している事象と調査項目は分けておくとよいと思います。

■おわりに

普段問題なくサーバが動作しているとこのような調査は不要です。ただし、時々トラブルが発生する場合があり調査の知見を必要とすることがあります。そういったときに調査方法などを事前に整理/訓練しておくことはとても重要なので、今回のsp_whoisactiveを有事の際にすぐに利用できるように検証機で検証作業を進めていきたいと思います。

SQL Serverの仮想ログファイル（VLF）の数が多くなりすぎたときの対応

備忘録 SQL Server

■はじめに

いままでSQL Serverを運用してて気が付かなったことに先日気が付いたので記事にまとめたいと思います。 ログファイルの容量などはいままで監視していたのですが、仮想ログファイル（VLF）の数が多すぎるという見たことがないアラートを検知したので何だろうと調べました。

■仮想ログファイル（VLF）とは

単語だけで調べるとマイクロソフト社の下記ページがヒットします。

docs.microsoft.com

仮想ログファイル（VLF）とは、上記サイトの中ほどにある「トランザクション ログの物理アーキテクチャ」に記載されていました。

  • 物理ファイルは内部的に多くの仮想ログファイル（VLF）に分割されていること
  • ログファイルの作成時や拡張時にデータベースエンジンにより動的に選択されること
  • 管理者が仮想ログファイルのサイズや数を構成または設定できない

という性質のファイルです。注意には仮想ログファイルの数の増え方が記載されています。これをみると自動拡張サイズが1MBなどの初期の自動拡張サイズにしたまま、自動拡張をさせ続けると仮想ログファイル数が尋常じゃない数になりそうです。さらに注意点としては

このような状況では、データベースの起動、ログのバックアップ操作、およびログの復元操作の速度が低下する場合があります。

と記載されていることは新発見でした。普段常に動かし続けているうえでは、定期的なログのバックアップは必須なため対策が必要と感じました。

■仮想ログファイル数の減らし方

下記サイトが参考になります。

blogs.msdn.microsoft.com

ページ中ほどにある「対処」に対応方法が記載されています。
簡単にまとめると、

  1. ログファイルをシュリンクして圧縮する。
  2. 適切なファイルサイズまで明示的に拡張する。

となります。試しにアラートが出ていたデータベースに対して対処をやってみたところ、確かにファイル数が激減したことを確認しました。仮想ログファイルの数が尋常じゃないデータベースは、自動拡張のサイズも規定値（1MBの自動拡張サイズ）だったりするので、今後同様のことが発生しないよう自動拡張ファイルのサイズも変更しておくとよいです。

トランザクションログの自動拡張サイズについての、マイクロソフト社のベストプラクティスは、1,024MB以下とすることなのでそれ以上にせず丁度良い時間に設定するのがよいです。具体的にどのようなサイズに設定すればよいかは次のスクリプトを実行すると結果が返ってきますので参考にしてみてはいかがでしょうか。

github.com

■終わりに

いままで見ていないアラートを検知したので調査してみました。これ以外にも実は見ないといけない項目が潜んでいそうなので継続的に勉強しなきゃいけないなと感じました。

SQL Server 2017の新機能のダイジェストを見てみる

備忘録 SQL Server

■はじめに

SQL Server 2008 R2のEOSL対応で、SQL Server 2017にアップグレードするので、
自習書のダイジェストをもとに2012からの新機能を勉強します。
SQL Server 2016は前回記事作ったので、SQL Server 2017の新機能を見てます。

hiyo-ac.hatenablog.com

www.microsoft.com 上記リンクから、「SQL Server 2017 の自習書シリーズ No.1」をもとにしました。

下記の番号は自習書の目次の番号に合わせ、私の個人的なメモを記載します。

2.1 Dockerを利用したSQL Server on Linux

Dockerイメージで提供されているとのことで、簡単にLinux環境で動作できるようになったとのことです。
簡単に起動できるので便利ですね。

2.2 Linux環境へのSQL Server 2017のインストール

Dockerではなく直接Linux環境にインストールできるとのことです。

2.3 SQL Server 2017 on Linuxのセキュリティ

Windows版と同様のセキュリティ機能が利用できるとのこと。
OSレイヤーの層とMWレイヤーのAPI等を分けてあれば、依存せずに動くのでしょうか。

2.4 SQL Server 2017 on Linuxで利用できる機能／利用できない機能

Linuxを利用せざるをえない状況になったら詳しく勉強します。

3.1 Machine Learning Servicesの概要／インストール方法

以前はRをサポートしていましたが、今度はPythonができるようになったとのことでした。

4.1 グラフデータベース（Graph Database）

グラフデータベースを利用できるようになったとのことでした。
ただグラフデータベースの設計って難しそうです。

4.2 自動チューニング（Automatic Tuning）

クエリストアの履歴を用いて自動で実行プランを選択する機能とのことでした。
使い方によってはとても便利そうな機能です。

4.3 Adaptive Query Processing（適応型クエリ処理）

列ストアインデックスを利用しており、おかつ互換性レベル140の場合に利用できる機能とのことで、
性能向上した実行プランが動作できる処理のようです。適応型Joinというイテレータが実行プランに登場したらこの機能のようです。

4.4 データベースの互換性レベル140

SQL Server 2017では互換性レベル140が登場したとのことです。

4.5 クエリストア機能の強化（クエリのWait情報の記録）

クエリストアでWait情報を記録することができるようになったとのことで、原因特定が早く行えそうですね。

4.6 データベースエンジンチューニングアドバイザーの強化

いままでインデックスを作成するとコストが減るよというアドバイスを提案してくれていましたが、
列ストアインデックスなどもチェックしてくれるようになったとのことでした。

4.7 列ストアインデックスの強化

SQL Server 2017では非クラスター化列ストアインデックスはオンライン再構築ができるようになったとのことで、メンテナンス性が大きく向上したそうです。

4.8 インメモリ OLTP機能の強化

インメモリOLTPの機能がもろもろ強化されているようです。メモリで処理する時代になりつつある感じがします。

4.9 再開可能なオンラインインデックス再構築

これは個人的には待望の機能です。オンラインインデックス再構築をしているときに、長走して停止しなけばならないということがあり、結局オフラインインデックス再構築をしているということがありました。
利用するには、WITHオプションで RESUMABLE=ONを指定すればよいとのことです。
進行状況を見れたりできるのも嬉しいですね。

4.10 AlwaysOn 可用性グループの強化

DTCのサポートや非クラスター環境下での可用性グループ構築ができるようになったとのことでした。

5.1 Transact-SQLの強化

新しい関数の追加などがされているとのことです。個人的には一括操作でのFORMAT=CSVによるCSVファイルの取り込みのサポートが便利そうでした。

5.2 セットアップ時の変更点（tempdbファイルの設定）

インストール時のtempdb設定で設定ファイルサイズなどが変更になっているとのことでした。
気にしたことなかったのですが便利なのかもしれないです。

5.3 スマートバックアップ

これは個人的には奇怪な機能です。
運用設計時にいつの時点まで復旧できるのか決めて、バックアップスケジュール作成すると思うのですが…
たとえ効率が良くても、そのスケジュールが動的に変わってしまうと困惑が生まれそうです。

5.4 新しいDMV（動的管理ビュー）

多くの動的管理ビューが強化、登場しているようです。
動的管理ビューも勉強しないといけないですね…

■おわりに

SQL Server 2017の目玉はLinux対応とは聞いていました。ただ、他にもいろいろな機能が追加されているようで勉強になりました。

SQL Server 2016の新機能のダイジェストを見てみる

備忘録 SQL Server

■はじめに

SQL Server 2008 R2のEOSL対応で、SQL Server 2017にアップグレードするので、
自習書のダイジェストをもとに2012からの新機能を勉強します。
SQL Server 2014は前回記事作ったので、SQL Server 2016の新機能を見てます。

hiyo-ac.hatenablog.com

www.microsoft.com 上記リンクから、「SQL Server 2016 の新機能の概要」をもとにしました。

下記の番号は自習書の目次の番号に合わせ、私の個人的なメモを記載します。

2.1 Operational Analysticsの概要～OLTPとデータ分析の両立～

インメモリOLTPで列ストアインデックスが利用できるようになったこと、非クラスタ化列ストアインデックスに対して更新ができるようになったことが強みのようでした。

3.2 動的データ マスクによる情報漏洩対策

一部の人にはマスキングしたデータを閲覧させたいという要件はどこにでもあります。
またプロダクション環境は全部見せていいが、そうではない環境はマスキングすることなど会社によってセキュリティポリシーもそれぞれかと思います。
マスキングする際はアプリケーションで毎回やっていたのが、DBで処理できるのはとても便利そうです。

3.3 行レベル セキュリティによるセキュリティ強化

接続するユーザに対して行単位で閲覧可否を制御する仕組みとのことです。
ニッチな機能の感じですが、使う機会はどこかにありそうで、これはユーザ定義関数とセキュリティポリシーを作らないといけないが難しそうでした。

3.4 Always Encryptedによる列データの暗号化

テーブルの列データを暗号化する機能で、セキュリティ規定が厳しい環境ではとても便利そうです。
暗号化には内部で証明書を利用しているとのことで、他サーバから接続する場合はその証明書を複製する必要があるとのことです。

3.5 TDE（透過的なデータ暗号化）の性能向上、インメモリOTLP対応

データベースファイルの暗号化を行う機能がすでにSQL Server 2008から提供されていたとのことで、この機能の性能向上とのことでした。いまの現場だとそんな要件がないのですが、もっとセキュリティがちがちなところだとファイル自体も暗号化せよといわれるの言われるのでしょうか。

3.6 テンポラル テーブルによるAudit（監査証跡）

テンポラルテーブルが登場したのはSQL Server 2016からのようです。
過去の履歴を追う時に使いやすいとのことでちょっと気になっています。
マスタデータを別テーブルに過去のバックアップとしてSELECT INTOで複製するという手法を提案する方が多いので、テンポラルテーブルがあると無造作にDB上にテーブルが増えることもなく、過去の特定時点のデータを参照できるのでよさそうです。

4.1 SQL Server R Services（R統合）

Transact-SQLステートメントを利用してRスクリプトが記述できるとのことで、時代の流れに乗っていそうな機能です。 利用するにはインストール時に「R Services（データベースエンジン内）」をチェックしてインストールするとのこと。

4.2 JSON対応（FOR JSON、OPENJSON、JSON_VALUEなど）

各アプリケーションでJSON形式のデータを扱うことが多くなりましたので、この機能は個人的にとてもありがたいですね。
いままでd3.jsでグラフ作っていたのですが、いったんCSV形式に吐き出して、JSONに加工してみたいなことを一部やっていたのでいきなりJSON形式でデータ作れたらその中間処理の手間がなくなります。

4.3 Stretch Databse ~アクセス頻度の低いデータをクラウドへ~

この機能を使うとテーブルのデータをAzureへ配置することができるとのことです。
テープなどの安価な媒体がない場合にデータを長期間保持せよとなると、保管場所に困るので便利な機能かもしれません。

4.4 Azure バックアップURLの性能向上（ブロックBLOB対応）

AzureのブロックBLOB領域にデータを格納できるとのこと。

4.5 PolyBaseでHadoopアクセス（Hortonworks、HDInsight）

PolyBase は、Hadoop ファイル システム （HDFS ： Hadoop File System）上にあるデータを、 SQL Server からアクセスできる機能です

とのことで、なるほど。Hadoop利用している人は目からうろこの機能なのでしょうか。　　 この機能を利用したいときは「外部データ用 PolyBase クエリサービス」をインストールするとのことです。

5.1 SQL Server 2014からの主な変更点

他にも機能が追加されているとのことでした。気になったのは下記です。

  • tempdbの拡張
  • MAXDOPやCE（基数推定）をDB単位で設定可能に（DBスコープ構成）
  • INSERT ... SELECT のパラレル処理

tempdbの拡張はインストール後にパラメータシートに基づき設定変更していましたが、インストール画面で設定ができるようになったとのことで便利そうです。
MAXDOPについてはサーバスコープ構成でしたが、DBスコープ構成になったので負荷を制限したいときには便利そうですね。

5.2 ライブクエリ統計（Live Query Statistics）

これはとても便利な機能で利用頻度は高そうです。いままでクエリを実行しているときにどこの処理まで動いているのかを追うのは職人技でした。これを使うと入門者でも簡単に実行状況がみれるのはうれしいですね。
利用方法は、Management Studioのツールバーの「ライブクエリ統計を含む」をクリックするだけとのことです。

5.3 クエリストアで性能監視、プラン固定

いままでdm_exec_query_statsでプランハンドルなど取得できクエリのプラン変更を確認できましたが、
クエリストアという機能で永続的に保管できるとのことです。
クエリストアを有効かするには、DBのプロパティから設定を行うとのことです。 クエリストアの最大容量に達すると、読み取り専用モードになり新しい情報は登録されなくなるとの点は注意が必要そうです。

5.4 Transact-SQL（T-SQL）の強化

DROP…IF EXISTSが登場したのがこのバージョンとのことです。
そのほか、DBCC CHECKDBなどにMAXDOPオプションを利用可能になったとのことで、停止できないシステムでもメンテナンス作業がしやすくなったのではないかと思います。

5.5 AlwaysOn 可用性グループの拡張

StandardエディションでもAlwaysOnが2台で組めるようになったなど、AlwaysOnの拡張がされているようです。
ログ転送速度が向上したとのことで、遅延云々言われるのがなくなったのではないかと思います。

5.6 BI関連の強化

SSRS、SSAS、SSISなどが強化されたとのことでBI系が便利になったようです。
Datazenの統合でSSRSのグラフ表示などがとてもきれいになったとのことで、BI系に興味出てきました。

■おわりに

SQL Server 2016の目玉はOperational Analysticsということは初めて知りました。 またセキュリティ関連機能が多く、重要なトレンドの機能を盛り込んでいる感じがうかがえました。 新しい機能を利用することで性能が向上するのは良いことなのですが、開発者のスキルが低いと悲惨なことになりそうです…
新しい機能が増えれば増えるほど便利にはなりますが、その分運用は複雑になってしまうのは難しい。

いまさらSQL Server 2014の新機能のダイジェストを見てみる

備忘録 SQL Server

■はじめに

SQL Server 2008 R2のEOSL対応で、SQL Server 2017にアップグレードするので、
自習書のダイジェストをもとに2012からの新機能を勉強します。
SQL Server 2012は前回記事作ったので、SQL Server 2014の新機能を見てます。

hiyo-ac.hatenablog.com

www.microsoft.com 上記リンクから、「SQL Server 2014 の新機能の概要」をもとにしました。

下記の番号は自習書の目次の番号に合わせ、私の個人的なメモを記載します。

2.1 インメモリ OLTP 機能の概要

テーブルやストアドプロシージャの機能をメモリに載せてメモリ内で処理させる機能です。 ディスクアクセスがなくなる分、高速な処理が期待でしますがメモリが必要になるのでメモリ確保の仕組みが気になります。 下記ページを後でじっくり読みたいと思います。

インメモリ OLTP (インメモリ最適化) - SQL Server | Microsoft Docs

3.1 更新可能な列ストアインデックスの概要

SQL Server 2012では読み取り専用だったのが、更新可能になったとのことでした。

この更新可能な列ストア インデックスは、「クラスター化列ストア インデックス」（CCSI: Clustered Column-store Index）と呼ばれ、従来ながらの読み取り専用の列ストア インデック スは、「非クラスター化列ストア インデックス」と呼ばれます。

とのことで、新しい短縮用語が出てきたのはしっかり押さえておきたいところでした。 列ストアインデックスはデータの圧縮がかかっているのでディスクアクセスは減るが、
データの圧縮率によってCPUパワーを余計に消費する点は勉強になりました。

4.1 バッファプール拡張（SSDをバッファプールとして利用可能に）

ドライブの一部の領域をバッファプールに割り当てられるとのことです。 SSDなどを割り当てるとディスクを読み取るより早いので性能向上に役立つとのこと。
メモリに載せたいデータと、拡張領域に載せたいデータを明示的に分けるのは難しそうで、
性能事故の元になりそうなので利用することはなさそうですね…

4.2 Power View (パワービュー)機能の強化

SQL Server 2012から登場したPower Viewの機能強化がされました。
BIに力を入れていることがうかがえます。

4.3 クラウド対応（Microsoft Azure 連携）

AzureへのバックアップやAzure上にmdfファイルを置けるようになったとのことで、 クラウド化が始まりました。

4.4 SQL Server AlwaysOnのパワーアップ

下記3つのパワーアップがあったそうです。

2点目については、SQL Server 2012ではクォーラムが損失するとデータベースへのアクセスができなかったのがアクセスできるようになった点がグッドです。

4.5 クエリ処理エンジンの進化（SELECT INTOのパラレル処理など）

下記4つの機能が追加になったとのことです。

  • SELECT INTO のパラレル処理
  • 基数推定の進化（新しいアルゴリズム）
  • 統計の増分更新
  • リソース ガバナーでI/O数による制限が可能に

統計の増分更新は気になります。SQL Server 2016と2017でどんな処理しているのか詳細探してみたいと思います。

4.7 インデックス再構築時のロックの優先度変更

オンラインインデックス再構築時にロックの優先度が設定できるとのこと。オンラインリビルドの時は開始と終了時の時点のみロックがかかる仕組みだったかと思うのだけれど、何が変わったのだろうか…

■終わりに

SQL Server 2014といえば、インメモリOLTPですが他にもいろいろな機能があって勉強になりました。
DMVやデータコレクションなどの機能もしっかり見ていきたいと思いました。

いまさらSQL Server 2012の新機能のダイジェストを見てみる

SQL Server 備忘録

■はじめに

SQL Server 2008 R2のEOSL対応で、SQL Server 2017にアップグレードするので、
自習書のダイジェストをもとに2012からの新機能を勉強します。
まずは、SQL Server 2012の新機能を見てます。

www.microsoft.com 上記リンクから、「SQL Server 2012 新機能ダイジェスト」をもとにしました。

下記の番号は自習書の目次の番号に合わせ、私の個人的なメモを記載します。

2.1 AlwaysOn 可用性グループによる可用性の向上

SQL Server 2012の一番有名な機能ではないかと個人的には思います。リアルタイムでデータ同期した読み取り可能なセカンダリサーバを作成することができる機能です。

  • WSFCよりも早く、10秒～20秒でフェールオーバーを行えること
  • リスナーを通して透過的にサーバにアクセスできること

などのメリットもあります。自習書には

共有ストレージが不要な分、コスト削減が可能

とあり、金銭的なメリットもあるようです。

2.2 AlwaysOn フェールオーバー クラスター インスタンス (FCI)

今までもありましたが、下記4つの新しい機能があるとのことでした。

  • SMB接続（共有フォルダー）へのデータベース配置が可能に
  • tempdbデータベースをローカルディスクへ配置可能
  • 複数サイトフェールオーバークラスタリングのサポート
  • 柔軟なフェールオーバーポリシー

特に2点目のtempdbデータベースをローカルディスクへ配置可能というのは2012年当時はきっと爆発的にパフォーマンスを向上させたのではないでしょうか。tempdbはSSDにするのがベストプラクティスでかつ、共有フォルダーだとファイバチャネルで接続していない限り、ストレージアクセスがどうしてもボトルネックになってしまうので。

2.3 Windows Server Core へのインストールがサポート

Widnows ServerをインストールしたときにWindows Server Coreを利用する方はどれぐらいいるのでしょうか。GUIこそWindowsだと思っているのですが…ただインストールできる対象が増えたのは一部の人はうれしかったのでしょうか。個人的にはあまり使わなさそうです。

2.4 包含データベース（CDB：Contained Database）

照合順序で悩まされた経験がないアプリケーションをずっと見ていたため、照合順序の問題があることを初めて知りました。エラーになっていたケースの方々は便利そうですね。

3.1 列ストア インデックスによる飛躍的な性能向上

この機能もとても素晴らしい機能のようです。ただ自習書の内容だけだといまいちわかりづらかったので、下記ページも参考にするとより理解が深まるので時間があったら見ていきたいと思います。SQL Server 2014や2016の新しいバージョンがでる際にも機能追加されているので利用するシーンが出てきたら検討してみたいです。

列ストア インデックス: 概要 - SQL Server | Microsoft Docs

3.2 BI 系の T-SQL 分析関数（Analytic Functions）のサポート

新しい関数が増えたのは便利そうです。機会があれば利用してみたいぐらいの機能でした。

3.3 SQL Server Data Tools による開発生産性向上

SQL Server Data Tools (SSDT)が登場したのはこのバージョンでした！？
いろいろ開発機能が増えたみたいですね。

3.4 Distributed Replay 機能による容易なストレス テストの実施

SQL Server Profilerで実行されているクエリを取得し、流し込むことは以前からできていたのですが、 流し込む際に複数のクライアントから実行できるようになったのがこの機能のようです。
いまの現場ではストレステストはJMeterやLoadRunnerなどのツールから行うので、「分散再生コントローラー」および「分散再生クライアント」はインストール不要と学習できました。

3.5 拡張イベント (XEvents)での GUI サポート

SQL Server Profilerではなく、拡張イベントを推し始めたのが、SQL Server 2012からと聞きました。
GUIで操作できるようになったのはとても便利そうです。
パフォーマンスも向上しているとのことで、今後は拡張イベントを利用したいと思います。

3.6 Transact-SQL の強化

Transact-SQLが増えたとのことです。
シーケンスやページングは使う機会があるのではないでしょうか。

3.7 新しいTransact-SQL関数のサポート

いろいろなTransact-SQL関数が増えたようです。
月末日の取得ができるEOMONTHなどは月次処理などで利用できそうです。

3.8 データベース リストア（復元）時のUI向上

グラフィカルに復旧地点を選べるのは、誤ってテーブルを削除した場合などに便利ですね。
これは使っていきたい。

3.9 起動オプションを設定するための新しいUI

SQL Serverの起動オプションの変更は頻度が少なく気にしたことなかったのですが、設定しやすいようにUIが変わったとのことです。

3.10 Visual Studio 2010 シェルの採用によるUI/操作性の向上

Management Studioも変わっているとのこと。

3.11 DQS(Data Quality Services)による容易なデータ品質の向上

名寄せなどを行う機能が追加されているとのことでした。
これを利用するには「Data Quality Services」と「Data Quality Client」、「Integration Services」をインストールしておく必要があるとのことで、インストールパラメータシートの読み方の勉強になりました。

3.12 FileTableによるWindowsファイルのサポート

そもそもこんな機能があることは初めて知りました。
特定業務や機能ではこの機能を重宝するのでしょうか。

3.13 Power Viewによる容易なデータ分析レポートの作成

Power ViewというBIツールが提供されたそうで、いまではPower BIに吸収されていそうです。

3.14 PowePivot for Excelの進化（バージョンアップ）

SQL Server 2008 R2を主に運用していますが、この機能を使っている人は私の周りにはいませんでした。 これもPower BIに吸収されていそうです。

3.15 Analysis Servicesでのテーブル モデル のサポート（xVelocity）

Analysis Servicesは興味があるものの、私の周りでは利用事例がなくどれぐらいすごいのかわかりません…

3.16 その他の新機能

他にもたくさんの機能があるようです。私が気になったのは下記でこれらはしっかり見ていきたいと思いました。

  • メモリ マネージャーの変更（列ストア インデックスなど、ラージ オブジェクトの処理を効率化）
  • 新しいパフォーマンス カウンターや DMO（動的管理オブジェクト）の追加
  • Integration Services のユーザー インターフェース変更（操作性の向上）

また、Local DBはツール入れたりすると勝手に動き出すことがあるので勉強しておきたい点です。

■終わりに

AlwaysOn以外にも新しい機能があったことを知れたので改めて勉強になりました。 また細かい機能やGUIも結構変化しているのは驚きです。SQL Server 2014や2016も勉強します。