SQLServer
パフォーマンス悪化時に疑うべきこと
- 直近に変更したSQL文があれば、それを確認する
- すぐに以前のSQL文に戻せるよう、前のSQL文をコメントアウトしたり、きっちりと変更管理を行っておくとよい。
- SQL実行回数、接続ユーザー数が増えていないか確認する
- SQLの発行回数が増え、さばき切れていない可能性があります。対策として、接続ユーザー数を制限することも検討できます。
- ページングが発生しているか調べる
- データベースプログラムのメモリ使用量が急激に増加し、ページング現象（ページイン/アウト）が発生してるかどうかを確認する。 目盛の増設で対応できそうなら、できるだけ早くサーバーメンテナンスを行う。
- DBのインデックス設計の見直し、統計情報の確認
- インデックスを使っていないなど、クエリプランに問題があることも考えられます。ある特定のSQL文に対して大きな負荷がかかっている場合には、これで対応できるケースがあります。
- ロックがかかっていないか調べる
- 内部でデッドロックが発生しているなど、ロックがかかっていることが影響している可能性があります。トランザクション制御の流れを見直してみましょう。
SQL Serverのパフォーマンス改善についてのPattern＆Practiceについて、下記ページにまとめられています。
http://msdn.microsoft.com/ja-jp/library/cc419190.aspx?ppud=4
スキーマ変更に伴うパフォーマンス
テーブルに列を追加、列の定義を変更するときのパフォーマンスに関する記事
http://blogs.msdn.com/b/sqljapan/archive/2007/06/28/3579883.aspx
http://blogs.msdn.com/b/sqljapan/archive/2007/08/20/4471435.aspx
SQLServerの通信ポート
通信で使用するポート番号
| 1433 | TCP | SQLServerTCP/IP 接続用 （任意のポート番号に変更可能） |
| 1434 | UDP | SQLServerTCP/IP 接続用 （任意のポート番号に変更可能） |
| 2433 | TCP | SQLServerTCP/IP 接続用 |
| 2393 | TCP | Analysis Service（OLAP Service 7.0 互換用） |
| 2394 | TCP | Analysis Service（OLAP Service 7.0 互換用） |
| 2725 | TCP | Analysis Service |
SQLServer Management Studio
SQLServerサービスに他のマシンから接続するための手順と動作確認
※Express版でなくても、インスタンス名を指定してサーバープログラムをインストールした場合は参考にすること。
Serverサービスへの接続確認
SQLServerをインストールしたマシンでSQLServer ManagementStudioを起動。
localhost[\インスタンス名]に、Windowsユーザー（全権限を持ったユーザー）でログインする。
テーブルの内容が閲覧できることを確認。
SQLServer認証での接続チェック
SQLServerをインストールしたマシンでSQLServer ManagementStudioを起動。
localhost[\インスタンス名]に、SQLServer認証用のユーザーでログインする。
saや、事前に作ったSQLserver認証用のユーザでログインでき、正しく権限設定がなされていることを確認。
バックアップデータベースから復旧したとき、SQLServer認証用のユーザーで正しくログインできないときは、サーバーレベルでログインを作り直すこと。
TCP/IPでの接続チェック
SQLServerの構成マネージャで、TCP/IPでの接続を有効にする。
そのうえで、次のいずれかを選択する。
- 構成マネージャで、SQLServerのサービスポートを、TCP1434で固定する。
（TCP1433ではない）（下記画像を参照） - SQLServerのサービスポートは動的なままで、SQLServerをインストールしたマシンのSQLServer Browserサービスを起動する。
SQLServerをインストールしたマシンでSQLServer ManagementStudioを起動。
（まだ他のサーバーからのログインは試みないこと）
IPアドレス[\インスタンス名]のように、IPアドレスを指定してログインする。
（例：192.168.1.2\sqlexpress ）
※[サーバのIPアドレスorマシン名]\[インスタンス名],1434、というように、末尾にサービスポートの番号を指定するやり方もある。ポート番号を省略する場合は、TCP1434で固定するか、SQLServerBrowserサービスにポート番号を探してもらう必要がある。
Windowsファイアーウォールの設定
SQLServerをインストールしたマシンの、windowsファイアウォールの設定を変える。セキュリティが強化されたWindowsファイアウォールを起動し、次のいずれかを設定する。
- SQLServerのサービスポートを固定している場合は、そのTCPポートの受信を許可する。
（TCP1433ではない点に注意） - SQLServerのサービスポートが動的な場合は、UDPの1434ポートの受信を許可する。
※やっていないが、効果があるかもしれないこと
SQLServerサービスのプログラム自体を、通信許可するよう、ファイアーウォールに登録する。
プログラムのパスは、次の通り。
C:\Program Files\Microsoft SQLServer\MSSQL12＜バージョン番号＞.＜インスタンス名＞\MSSQL\Binn\sqlsevr.exe
設定を有効にするために、OS自体を再起動した方がよいこともあるかも。
「サーバーへ接続」ダイアログで記憶したログインを削除
- C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\[バージョン番号]\UserSettings.xml をテキストエディタで開く
- 該当のログインが記憶されている＜Element＞タグを削除する
ディスクの設定
NTFSのファイルアロケーションユニットサイズを、64KBに設定する。
SQLServerのデータファイルは、1ページが8KB、8ページで1エクステント。
このエクステントの単位でデータを読み込むので、NTFSのファイルアロケーションユニットサイズを64KBに設定すると、アクセス効率が向上する。
トランザクションログの切り捨て
SQLServerのトランザクションログは、何もしなければどんどん拡大していきます。
かといって、復旧モデルを「単純」にしてしまうと、自動的にログが削除されるため、不測の事態が発生したときのデータの復旧が困難になります（具体的には、バックアップを取った時点以降のデータは復旧できません）
トランザクションログを切り捨てるためには、トランザクションログをバックアップする必要があります。
トランザクションログのバックアップを定期的に実施することで、トランザクションログを効率よく切り捨てることができます。
こうすることで、不測の事態からのデータ復旧も可能になり、またログファイルが際限なく肥大化することも防ぐことができます。
差分バックアップとリストア
完全バックアップからの復旧に対しては「WITH NORECOVERY」を指定し、その後の差分バックアップからの復旧に対しては「WITH RECOVERY」を指定する。
参考：
https://qiita.com/fuk101/items/e93a63a8ada3523a03ce
インデックス
クラスタ化インデックスの活用
下記に該当する場合は、クラスタ化インデックスを活用するとパフォーマンス向上が見込める。
- 普遍的、一意であるデータ（IDENTITYなど）
- 範囲検索（between、＞、＞＝、＜、＜＝）で利用する
- order byやgroup byで利用する
- 外部キーからの参照項目である
- 以上の処理が、頻繁に実行される
逆に、キーの内容が頻繁に更新される場合は、パフォーマンスの劣化を招く原因にもなる。
なお注意点として、クラスタ化インデックスの作成には、実際のテーブルサイズの1.5倍のディスク空き容量が必要である。
これは、データをハードディスク上に決まったルールに並べ替える作業が発生するためである。
カバリングインデックスの活用
カバリングインデックスをうまく活用すると、インデックス読むだけで目的のデータを取得できる。
（ヒープ領域へのアクセスがなくなるため高速な読み出しが可能）
キーとしては必要ないが、カバリングインデックスにより値をインデックスに格納したい場合は、INCLUDE句を利用するとよい(付加列とも呼びます)。
カバリングインデックスでは、インデックスの中間ページとリーフレベルにも値が格納される（それにより、キーによるソートが可能になる）。そのため、サイズの大きい列を指定すると、インデックスのサイズが肥大化してしまう。
一方、INCLUDE句を用いると、インデックスのリーフレベルにのみ値が格納されるため、インデックスの容量を抑え、結果として検索によるIO速度も高速化できる。
インデックスのフラグメンテーション
以下のSQLで確認できる。
select *
from sys.dm_db_index_physical_stats
(DB_ID(N'[データベース名]'),
OBJECT_ID(N'[データベース名].dbo.[テーブル名]')
, NULL
, NULL
, 'DETAILED');
実行結果のavg_fragmentation_in_percent列が、フラグメンテーションの比率が％で表される。
http://msdn.microsoft.com/ja-jp/library/ms189858.aspx
目安として、この列の値が5〜30%以下ならインデックスのデフラグを実施する。
(ALTER INDEX REORGANIZE)
それ以上ならインデックスの再作成または再構築を行うとよい。
再作成：DROP INDEX→CREATE INDEX
再構築：ALTER INDEX REBUILD WITH (ONLINE = ON)
FILL FACTOR
マスタや過去データのように、更新がほとんど行われないようなデータの場合は、FILL FACTOR をデフォルトの 0 ないし 100 にしておくほうが、インデックスの空き領域が発生せず、パフォーマンス上優れる。
SQLServer Management Studioではオフラインでインデックスを作成できない
SQLServer Management StudioのGUIでインデックスを作成、削除したときは、オフラインでそれらの処理が行われる。
インデックスの作成、削除処理をオンラインで実行したい場合は、WITH (ONLINE=OFF) を指定したSQL文を構築し、実行しなければならない。
メンテナンス
全テーブルの行数を取得する方法
SQLServer2005以上で、データベース内の全テーブルの行数を取得する方法です。
-- テーブルの行数取得
select object_name(object_id) as テーブル名 ,rows from sys.partitions order by object_name(object_id)
SQLServer エラーメッセージ一覧
SQLServerで発生したエラーのIDとその内容（メッセージ）についての対応は、以下のSQL文でその一覧を取得することができます。
select *
from sys.messages
【参考】
http://msdn.microsoft.com/ja-jp/library/ms187382.aspx
テーブル定義の取得スクリプト関連
下記URL参照
http://d.hatena.ne.jp/odashinsuke/20100427/1272365375
IDENTITYが設定された列に任意の値を入れる方法
SQLServerでIDENTITYが設定された列に、強引に任意の値を入れなければならない事態がありました。
調べてみたところ、これを実現する機能がありました。
SET IDENTITY_INSERT
という命令で、IDENTITYが設定された列に強引に任意の値を入れるか入れないかのON/OFF設定ができます。
「SET IDENTITY_INSERT」で検索すると、関連する情報がたくさん出てきます。
知っておくと、DBメンテナンスでいざという時に役に立ちます。
SQLServerでのページング機能（Offset Fetch）
SQLServerでページング機能を実装したいときに、データの何番目〜何番目までを取得する、というSQL文を書きたい場合があります。
SQLServer2012から、これを簡潔にかける構文があることを、今日知りました。
OrderBy句に対して、さらにOffsetとFetchの句を組み合わせることで、簡単に書くことができます。
詳しくは、https://technet.microsoft.com/ja-jp/library/ms188385(v=sql.110).aspx
昔はちょっと技巧的なやり方をしていましたが、これならとてもシンプルです。
ただ、大量データに対して行う場合は注意が必要です。
OrderBy句を指定する必要があるので、テーブルの全件ソートが発生します。
SQLServer内部では、テーブルの内容を一度すべてソートした後、OffsetとFetchで指定した範囲のデータを取り出します。
インデックスを適切に設定しておかないと、応答が返ってこないということになりかねないので、設計には十分注意が必要ですね。