SQL Serverの最大メモリの設定は?SQL Serverの環境設定についてまとめる

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

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

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

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

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

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

最新の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

この記事では、さまざまなバージョンのSQL Serverで使用できるさまざまなビルドまたは更新プログラムの一覧を示します。

learn.microsoft.com

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

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

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

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

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

これ結構知らない人が多く、パフォーマンスモニターで物理メモリーの使用量がMAXで張り付いているんだけど大丈夫かと聞かれることが多いです。

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

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

結局、確保しすぎたインスタンスのサービスを再起動するはめになりました。。。。

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

OSやほかのアプリのメモリもあるので最適な設定値はいろいろ試してみないとわからないと思いますので本番運用開始までにいろいろ試してみましょう。

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

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

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

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

【SQL Server】照合順序の設定はインストール時に決まるので注意

デフォルトでインストールした場合の既定の設定値は【Japanese_CI_AS】です。この設定を変更する場合は、最悪インストールからやり直しになってしまうので必ずチェックしておきましょう。

kajiblo.com

2021.02.28

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

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

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

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

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

【SQL Server】復旧モデルの一括ログって何?

実はこの復旧モデルは,「単純」「完全」「一括ログ」の3種類があります。この一括ログの使い方がよくわからない? なので今回復旧モデルについていろいろ調べてみました。

kajiblo.com

2021.02.21

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

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

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

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

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

【SQL Server】トランザクション分離レベルを設定する

SQL Serverにはトランザクション分離レベルというものがあり、結構システムのタイムアウトやデッドロックなどはこの設定を変更することで発生しなくなったりすることもあるので一度確認しておく必要があると思います。おすすめはREAD_COMMITTED_SNAPSHOTです。

kajiblo.com

2021.02.13

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

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

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

【SQL Server】バックアップの圧縮オプションを検証

SQL ServerのDBバックアップを圧縮オプションは、圧縮率も高く、バックアップ時間も短縮されます。気になるCPUの負荷もそれほど高くなく、バックアップ中にSELECT文を実行しても結果が返ってくるまでの時間は変わりませんでした。

kajiblo.com

2020.10.11

まとめ

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