[SQL Database] アプリケーション作成における推奨事項について (Microsoft Azure SQL Database)

  皆さん、こんにちは。 SQL Server/Microsoft Azure SQL Database サポートチーム です。 今回は、Microsoft Azure SQL Database (以下 MASD) を使用したシステムを構築するうえで、推奨されるアプリケーションの実装について紹介します。   MASD は、クラウド上に配置されたデータベースをサービス (Software as a Service : SaaS) として提供しており、高可用性を実現するため、複数レプリカによる冗長性を備えています。また、高可用性を維持していくために、Reconfiguration (リコンフィグレーション) が内部的に行われています。 ※ Reconfiguration (リコンフィグレーション) の詳細については、以下の ブログを参照ください。   [SQL Database] Reconfiguration (リコンフィグレーション) は悪ではない。 http://blogs.msdn.com/b/jpsql/archive/2014/10/22/sql-database-reconfiguration.aspx   そのため、MASD を使用したシステムを構築する場合は、アプリケーション側でも、 アプリケーションからデータベースまでの経路間におけるネットワーク遅延、及び、Reconfiguration (リコンフィグレーション) の発生に伴う、既存セッションの切断、ロールの変更 (セカンダリからプライマリへの昇格など) が完了するまでの期間 (数秒から数十秒程度) における、新規接続の一時的な遅延を考慮した実装を検討する必要があります。 上記の事項を考慮した実装として、アプリケーション側で以下を実装することを推奨しています。   推奨実装 (アプリケーション側) 1)…

0

DO’s&DONT’s #18: やった方がいいこと - .NET Framework アプリケーションでパラメータクエリを実行する場合にはパラメータのデータ型やサイズを明示的に指定する

    高橋 理香 SQL Developer Support Escalation Engineer     SQL Server では、パラメータ付きのストアドプロシージャを作成して実行したり、sp_executesql を使用してパラメータ クエリを実行することができます。このパラメータ クエリを実行する際には、パラメータのデータ型やパラメータ値が必要となります。また、文字列型やバイナリ型の場合には、サイズの指定も必要です。 一方で .NET Framework Data Provider for SQL Server (以降 SqlClient) を使用するアプリケーションからストアドプロシージャやパラメータ クエリを実行する場合、SqlParameter クラスを使用してパラメータのデータ型等を設定できますが省略することもできます。これは、SqlClient が SqlParameter の Value プロパティに代入された値を基にデータ型やサイズの推論を行っているからです。 しかしながら、パラメータのデータ型やサイズを明示的に指定しなかった場合には、意図しないデータ型やサイズとなることで、クエリのパフォーマンス劣化が発生したり、エラーによって実行できないなどの問題が発生する可能性もあります。そのため、可能な限りパラメータのデータ型やサイズは明示的に記述しておいた方が、アプリケーションの堅牢性の面からもよいでしょう。   推奨事項 パラメータのデータ型は SQL Server 側のデータ型に合わせて明示的に指定する。 文字列やバイナリのデータ型の場合、サイズも明示的に指定する。 varchar(max) 等の max を使用したデータ型についてはサイズに -1 を指定する。   なぜデータ型を明示的に指定した方がいいのか? 以前に以下のブログで紹介しているように、データ型が一致していない場合にはデータ型変換を行ってから比較を行うことになります。つまり、クエリ パフォーマンスが悪化し、ひいては、アプリケーションのパフォーマンス劣化につながります。 DO’s&DONT’s #2:…

0

DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK)

神谷 雅紀 SQL Server Escalation Engineer   インデックスの再構築 (ALTER INDEX REBUILD または DBCC DBREINDEX) を行う目的が、ページ密度を向上させて、データベースファイル内の使用領域サイズを小さくすることであれば、インデックス再構築後にデータベース圧縮を行っても、その目的は損なわれません。しかし、インデックスを再構築する目的が、論理断片化を解消し、検索パフォーマンスを向上させることであれば、インデックス再構築後にデータベース圧縮を行うと、その目的は達せられなくなります。 ページ密度 (Page Density) : ページ内でデータが占める割合。100% の場合、それ以上そのページにはデータが入らない、空き領域がない状態。ただし、行サイズとページサイズの関係上、100% になることはほとんどない。(例 : 1 行 100 バイトの場合、1 ページには、8060 バイトのデータが入るため、ギッシリ詰め込んでも 8060%100=60 と 60 バイトの空きは必ずできてしまう。) 論理断片化 (Logical Fragmentation / Logical Scan Fragmentation) 率 : ページの物理的な順番と論理的なリンクが異なる割合。ページ番号はファイルの先頭から順に 0, 1, 2 … と振られているが、論理的なページのリンクが 81 の次に 48 など、ページ番号順になっていない割合。   なぜ? インデックスを再構築すると、インデックスは再作成され、各インデックスページは…

2

DO’s&DONT’s #17: やっておいた方がいいこと - tempdb データファイル数を CPU 数に一致させる

神谷 雅紀 Escalation Engineer   KB 2154845 で述べられていますが、一般的には、tempdb データファイルの数は、SQL Server が使用可能な CPU の数に一致させた方が高負荷時のパフォーマンス劣化を防ぐことができるとされています。ただし、2011 年の PASS Conference (PASS : Professional Assosiation for SQL Server) におけるセッション Inside Tempdb で発表されたテスト結果を受けて、8 を超える CPU が使用可能な SQL Server では、tempdb データファイル数は 8 を基準とし、状況を見ながら必要に応じて 4 の整数倍の数のファイルを追加するという方法が推奨されています。 ただ、これを必ずやらなければならないかと言えば、tempdb 負荷の低い環境では必ずしもやる必要はなく、そのような環境では、やったとしても害はありませんが、これといった効果もありません。tempdb 負荷が高いのか低いのか分からないのであれば、やっておいた方が無難でしょう。   推奨事項 tempdb データファイルの数は SQL Server に割り当てている CPU の数と一致させる。 ただし、CPU 数が 8 を超える場合には、ファイル数は 8…

1

DO’s&DONT’s #16: やってはいけないこと - ログ配布プライマリデータベースのログバックアップ

神谷 雅紀 Escalation Engineer ログ配布 (Log Shipping) のプライマリデータベースに対して、ログ配布バックアップジョブ以外のトランザクションログバックアップは行ってはいけません。 なぜ? COPY_ONLY オプション (もしくは NO_TRUNCATE オプション) を指定しないトランザクションログバックアップは、トランザクションログをバックアップすると同時に、バックアップしたトランザクションログレコードを切り捨てます。ログ配布のバックアップジョブ以外がトランザクションログバックアップを行った時にトランザクションログが切り捨てられてしまうことで、ログ配布によりバックアップされるトランザクションログの連続性が失われます。その結果、セカンダリサーバーでは、ログ配布により配信されたトランザクションログバックアップをリストアすることができなくなります。 例えば、以下の例では、ログ配布以外によりバックアップ A が作成されています。ログ配布コピージョブによりバックアップ A がセカンダリーサーバーにコピーされない場合、セカンダリーサーバーでは、バックアップ 3 の前にリストアすべきバックアップ A があるため、ログ配布リストアジョブは、バックアップ A がコピーされてくるのを待ちます。バックアップ 3 はリストアされません。   上の状況が発生し、長期間同期していない場合に警告を発するように設定している場合、エラーログやイベントログには以下の警告が記録されるようになります。   エラー: 14421、重大度: 16、状態: 1。 The log shipping secondary database SERVER\INSTANCE.DATANASE has restore threshold of XXX minutes and is out of sync. No restore was…

1

DO’s&DONT’s #15: やってはいけないこと - クラスタ SQL Server をサービスとして操作すること

神谷 雅紀 Escalation Engineer   MSCS または MSFC クラスタリソースとして動作している SQL Server や SQL Server Agent を net コマンドや sc コマンド、コントロールパネルの「サービス」 (services.msc MMC スナップイン) から停止したり、開始したりしてはいけません。   なぜ? クラスタリソースとして動作している SQL Server や SQL Server Agent は、その実体はサービスであっても、クラスタサービスの管理下にあります。例えば、SQL Server クラスタリソースをオンラインまたはオフラインにしようとすると、その要求は、以下の図のように伝播します。SQL Server Agent の場合にはクラスタリソース DLL が SQL Server Agent 用になりますが、要求の伝播の仕方は全く同じです。要求の処理結果は、この経路を逆にたどって要求元に返されます。 では、net stop や sc stop で SQL Server サービスを停止した場合、どのようなことが起こるでしょうか? その要求は、クラスタサービスを経由せず、直接サービスコントロールマネージャに送られます。そのような停止要求があったことは、クラスタリソース…

0

DO’s&DONT’s #14: 絶対にやってはいけないこと - ひとつの CPU に対して affinity mask と affinity I/O mask の両方を ON にする

  神谷 雅紀 Escalation Engineer   SQL Server の sp_configure 構成オプションとして、affinity mask, affinity64 mask および affinity I/O mask, affinity64 I/O mask があります。affinity mask は最初の 32 個の CPU 用、affinity64 mask は 33 個目以降の CPU 用である点を除けば、これら 2 つの機能は同じです。affinity I/O mask と affinity64 I/O mask も同様です。 affinity I/O mask が SQL Server の機能として実装された時点から、Books Online をはじめとする多くの技術文書で、affinity mask と affinity…

0

DO’s&DONT’s #13: 絶対にやってはいけないこと - ORDER BY が指定されていないクエリの結果が一定の順番に並んでいると仮定すること

  神谷 雅紀 Escalation Engineer   多くの場所で議論されている場面を見かけるため、ここで明確にしておきたいと思います。   ORDER BY のない SELECT の実行結果内の行の並び順は一切保証されない SELECT の実行結果の並び順が保証されるのは、最も外側 (最も上位) の SELECT に ORDER BY が指定されている場合だけです。 SELECT 内のサブクエリ内に ORDER BY が指定されていたとしても、また、ビューの中で ORDER BY が指定されていたとしても、それらを参照する最も外側の SELECT に ORDER BY が指定されていなければ、SELECT の実行結果内の行の並び順は不定です。 これは、クラスタ化インデックスがあるかどうか、非クラスタ化インデックスがあるかどうか、並列クエリであるのかどうか、どのような順番で行が INSERT されたかなど、いかなる要因にも左右されません。どのような場合であっても、SELECT の実行結果の並び順が保証されるのは、最も外側の SELECT に ORDER BY が指定されている場合だけです。 もし ORDER BY を指定していない SELECT の結果が期待した順番に並んでいたとしても、それに依存してアプリケーションやシステムを設計、実装、構築しないで下さい。期待する並び順があるのであれば、必ずそれを ORDER BY として指定することで、並び順を SQL…

0

DO’s&DONT’s #12: やった方がいいこと - max server memory を設定する

  神谷 雅紀 Escalation Engineer   サーバースローダウンなどの事象 SQL Server の一時的な無応答、処理の遅延 (サーバースローダウン)、クエリタイムアウト、ログインタイムアウト、ネットワークエラー、既存のクライアント接続の切断、MSFC フェールオーバーの発生、MSFC での SQL Server クラスタリソース再起動、ミラーリングフェールオーバー、ミラーリングパートナータイムアウト、CPU 高負荷 (使用率 100%) といった事象が発生することがあります。これらの原因のひとつがページング (paging) です。ページングにより SQL Server プロセスのワーキングセット (working set) が小さくなることで、SQL Server 上で行われるすべての処理が遅くなり、また、SQL Server が動作するサーバー全体のパフォーマンスが悪化します。 このような事象が発生した場合、定常監視項目として Memory:Pages/sec や Process:Working Set, SQLServer: Buffer Manager\Total pages パフォーマンスカウンタを採取している場合には、それらのカウンタを確認してみて下さい。問題の事象の発生と同時に Memory:Pages/sec の値が上昇し、sqlservr プロセスの Process:Working Set の値が大きく減少してるにも関わらず、SQLServer: Buffer Manager\Total pages がそれと同等の減少をしていない場合、その事象はページングが原因であると考えて間違いありません。   対応方法 ページングによるスローダウンを防ぐために、まず最初にすべきことは、max…

0

DO’s&DONT’s #11: やってはいけないこと - トランザクションログファイルの拡張サイズを極端に小さくする

  神谷 雅紀 Escalation Engineer   トランザクションログファイルを自動拡張にすることは問題ありませんが、拡張サイズを 1MB など極端に小さな値に設定してはいけません。   なぜ? トランザクションログは、仮想ログファイル (Virtual Log File / VLF) と呼ばれる論理的なファイルに分割されて使用されます。VLF は、トランザクションログファイル作成時および拡張時に作成されますが、トランザクションログファイル拡張時の拡張サイズが小さいと、小さな VLF が多数作成されることになります。VLF の数が多くなると次のようなことが起こります。 データベースの起動に時間がかかる。 トランザクションログのバックアップ、復元 (restore) に時間がかかる。 データベースミラーリングにおいて、ミラーサーバー側へのトランザクションの配信に時間がかかる。 トランザクションレプリケーションにおいて、サブスクライバへのトランザクションの配信に時間がかかる。 DBCC CHECKDB の実行に時間がかかる。 データベーススナップショットの作成に時間がかかる。 上にリストした操作は、トランザクションログをスキャンするため、VLF の数が多くなると、処理時間が長くなります。 トランザクションログの物理アーキテクチャの詳細については、Books Online トランザクションログの物理アーキテクチャを参照して下さい。   対処 既に VLF の数が多くなっている可能性がある場合 VLF の数を少なくするために、一旦トランザクションログファイルのサイズを小さくします。その後、元のファイルサイズまたはそれ以上のサイズを明示的に指定してファイルサイズを大きくします。 1. ファイルを圧縮してファイルサイズを小さくする。 ※ 「ファイル圧縮」の「圧縮アクション」は、「未使用領域を解放する」は TRUNCATEONLY, 「データを同じファイルグループのファイルに移行してファイルを空にする」は ENPTYFILE オプション付きの DBCC SHRINKFILE です。…

0