SQL Serverの最大メモリの設定などを含め構築運用時に知っておくべきこと

SQL
スポンサーリンク

私が担当しているシステムのデータベースは、ほとんどがSQL Serverでした。SQL Server2005から使い始めてSQL Server2017まで約10年ほどの付き合いです。

今までSQL Serverでもいろいろなトラブルにも遭遇してきました。


今回は、今まで経験してきたものを踏まえてSQL Serverの構築及び運用時に
最低限知っておいたほがいいことについてまとめてみたいと思います。


SQL Serverに携わっている人にとっては当たり前だと思いますが、
結構重要なのに知らないということが多い内容だと思います。

ポイントは以下になります。

  • 最新のCU及びSPを適用しているか
  • インスタンスの最大メモリを設定しているか
  • 照合順序は正しく設定されているか
  • トランザクション分離レベルは、きちんと検討しているか
  • バックアップの圧縮をするか

構築時に知っておいたほうがいいこと

最新のCU及びSPを適用する

サーバなどにSQL Serverをインストールするときは、大体DVDやイメージファイルなどの媒体でインストールすることがほとんどだと思います。ただ、インストール媒体は、RTMというバージョンが発売された当時のもののことが多いです。

SQL Serverのインストールが完了後に必ず以下のSQLでSQL Serverのバージョンを確認しましょう。

SELECT @@VERSION

-----------------
Microsoft SQL Server 2017 (RTM-CU22) (KB4577467)

SQL Server2016までは※SP(サービスパック)、2017以降はCU(累積的な更新プログラム)がリリースされていることが多いです。
※SQL Server2017以降はSP(サービスバック)の配布は廃止されています。

SQL Serverを構築して本番運用に入ってしまうと、おいそれとこれらの更新プログラムを適用するわけにはいかなくなります。あとから問題が発生してサービスパックを適用しておけばよかったと後悔しないようにできるだけ構築時に適用しておくほうがいいです。

各SQL Serverのリリース情報は以下のサイトから確認することが可能です。

SQL Server の最新の更新プログラム - SQL Server
Microsoft SQL Server のビルド番号を含む最新の累積的な更新プログラムと Service Pack の一覧。

インスタンスを複数持つ場合は必ずメモリの設定を行う

インスタンスを複数持たない場合も設定しておいたほうがいいですが、複数のインスタンスを持つ場合は必ずメモリの最大値の設定を行うようにしましょう。

SQL Serverのメモリ設定はインスタンスごとに設定できます。

ここで重要なのは、SQL Serverは仕様上、設定した最大サーバメモリー(MAX SERVER MOMORY)までメモリを確保しようとします。

さらに一度確保したメモリはSQL Serverのサービスが再起動するまでメモリ領域を解放しません。

インスタンスが複数あった場合で、両方ともに最大メモリの設定を行わず、無制限に確保してしまうと片方のインスタンスにだけメモリが確保されてしまいもう片方のインスタンスがメモリを確保できなくなるケースがよくあります。この場合は、最悪メモリが確保できずインスタンスの起動すらできなくなることがあります。

私の場合は、インスタンスは起動したのですが、片方のインスタンスにメモリが集中しすぎてシステムのパフォーマンスが滅茶苦茶悪くなってしまったことがあります。

この時に初めてきずきサーバーの再起動をすることになってしまいました。

最大メモリの設定の目安は、サーバーの物理メモリと各インスタンスごとに設定した最大メモリ値の合計が超えないように設定する必要があります。

OSやほかのアプリのメモリもあるので最適な設定値はいろいろ試してみないとわからないと思います。

  • SQL Serverは、各インスタンスに設定された最大メモリまでメモリを確保しようとする
  • 一度確保したメモリはSQL Serverのサービスが停止するまでメモリ領域を解放しない

照合順序は必ず確認しておく

これも知らずに適当に設定していると後で困ることになります。

照合順序はデータの文字の大小関係を比較する場合の基準となる設定です。この設定によって検索結果が今までと変わってしまうというパターンがよくあります。

照合順序を誤って設定して後から修正する場合は最悪再インストールが必要になります。
照合順序の設定内容について以下の記事を参考にしてもらえればと思います。

復旧モデルを決定しておく

これは構築時というよりは、サーバーなどのディスク容量割り当て時点で決めておく必要があります。
SQL Serverのトランザクションログの運用は以下の3通りの復旧モデルがあります。

復旧モデル内容
完全データベースが破損した直前まで戻したり、指定した時間に戻すことも可能なモデル
・トランザクションログのバックアップ運用が必須
単純・データベースに反映した履歴は自動的に切り捨てられる。
・前回DBバックアップを取得した時点にしか戻せない。
一括ログ復旧完全復旧モデルの内一括処理系のトランザクションログを書き込まないモデル
・完全復旧モデルよりはトランザクションログの容量が少ない。
・時間指定での復旧などはできない。

システムに障害が発生したときに、どの地点までのデータを保証するかによって設定します。


特に完全復旧モデルを採用する場合は、バックアップ時間やトランザクションログを保存しておくディスク容量などで問題になることが多いです。ここでディスク容量不足などの問題になると正直どうしようもなくなります。

詳細はこの記事を参考にしていただければと思います。

トランザクション分離レベルの設定を確認する

SQL Serverでは、複数のトランザクションが同時に実行された時に、データを他のトランザクションからどの程度分離するかを定義するトランザクション分離レベルという設定があります。

これは、システム要件によって設定は異なりますが、オンラインでの検索処理とトランザクションのバッチ処理が共存するシステムは、検討しておいたほうがいいです。

状況によってはread committed snapshotの設定を入れるべきです。

トランザクション分離レベルの設定等は、以下の記事を参考にしてください。

バックアップの圧縮オプション

これは、データベースのバックアップと一緒にバックアップファイルも圧縮するという設定です。
SQL Server 2008から追加された機能でバックアップファイルが少なくなるだけではなく、バックアップの取得時間も早くなります。

詳細は以下の記事を参考にしていただければと思います。

まとめ

SQL Server構築時にはぜひ以下の項目をきちんと検討しているか確認してもらったらと思います。

  • 最新のCU及びSPを適用しているか
  • インスタンスの最大メモリを設定しているか
  • 照合順序は正しく設定されているか
  • トランザクション分離レベルは、きちんと検討しているか
  • バックアップの圧縮をするか

コメント

タイトルとURLをコピーしました