• Articles
• admin

最近、SQL serverをデータベースとして実行している大規模なお客様のために、sql serverのパフォーマンスの問題をトラブルシューティングする作業に取り組んできました。 この記事では、トラブルシュートのパフォーマンスの問題に対処するためのいくつかの手順を説明することで、プロセスから学んだことを要約します。

SQL Serverの構成を確認してください

データベースサーバーがCPUコアの数やメモリの量など、十分なリソースで構成されていることを確認してください。

サーバー構成を確認するには、”システム情報”ビューを開くことができます:

SQL Serverのメモリ設定を確認する,

1. SQL Server Management Studioを起動します。
2. データベースインスタンスを右クリックし、”プロパティ”を選択します。
3. “サーバープロパティ”ポップアップウィンドウの”メモリ”テーブルをクリックします。
4. メモリの設定を確認します。

スナップショットモードがオンになっていることを確認

データベースのスナップショットモードがオンになっている SQL Serverがロックされないようにするには、snapshot modeフラグをオンにする必要があります。 次のクエリを実行して、フラグがオンになっているかどうかを確認します:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

クエリが’1’を返した場合、スナップショットモードは既にオンになっています。 それ以外の場合は、次のクエリを実行してオンにします。

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

関連: 要件の定義と実装ベースライン

データベースインデックスのチェック

データベースインデックスをチェックして、欠落しているインデックスがないこと 次のクエリを実行して、データベース内のすべてのインデックスをすべて一覧表示します（クレジット：tsql-SQL Server DBのすべてのインデックス&インデックス列の):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

クエリ結果をタブ区切りの列を含むテキストファイルに保存して、後でスプレッドシートアプリケーションにインポートできるようにします。

断片化の回避

データベースインデックスが断片化されていないことを確認します。 データベース内のインデックスは、データベースクエリを高速化するのに役立ちます。 しかし、断片化すると、クエリの実行が本当に遅くなる可能性があります。 データベースのメンテナンスの一環として、索引は定期的に再編成または再構築する必要があります。

次のクエリを実行して、データベース内のすべてのインデックスの断片化率を確認します(credit:How to Check Index Fragmentation on Indexes in A Database):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

インデックスが断片化されているデータベースのクエリ結果の例を次に示します:

ページ数が1000を超えるインデックスの場合、”avg_framentation_in_percent”は10%以下に保つ必要があります。 小さなインデックスではあまり重要ではありません。 経験則として、断片化の割合が5%から30%のインデックスを再編成し、断片化の割合が30%を超えるインデックスを再構築する必要があります。

単一のインデックスを再編成するには:

ALTER INDEX REORGANIZE

単一のインデックスを再構築するには:

ALTER INDEX REBUILD WITH (ONLINE = ON)

sql Server Management Studioを使用して、再構築または再編成するインデックスを右クリックして、インデックスを再編成または再構築することもできます。

データベースに多数のデフラグされたインデックスがある場合は、次のクエリを実行してすべてを再構築できます（クレジット：SQL SERVER–2008-2005–データベースのすべての表のすべてのインデックスを再構築-FillFactorを使用したインデックスの再構築）。 クエリは、大規模なデータベースのためにしばらくの間実行することができますのでご注意ください。 データベースがアイドル状態またはオフラインの間にクエリを実行することをお勧めします。

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

データベースインデックスの再編成を定期的に実行するように、SQL Server studioでメンテナンスジョブを設定することを検討してください。 SQL Serverでのインデックスの再構築は、おそらくオフラインまたはシステムがアイドル状態のときに行う必要がありますが、インデック ここでは、このトピックに関する良い記事です：再構築または再編成：SQL Serverインデックスのメンテナンス。

SQL Serverでインデックス再編成計画を設定するには、”管理”、”保守計画”を右クリックし、”新しい保守計画”または”保守計画ウィザード”を選択します。 指示に従って計画を作成します。

関連:Jama Connectのリリース管理オプション

不足しているインデックスレポートの実行

SQL Serverには、データベースアクティビティを分析し、クエリのパフォーマンスに役立つ追加のインデックスを推奨するツールが用意されています。 このレポートは、特定のクエリが遅い理由についていくつかのアイデアを与える可能性があります。

レポートを生成するには、データベースインスタンスがしばらく使用された後に次のクエリを実行します(credit: ちょうど盲目的にそれらの”行方不明の”索引を作成してはいけない!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

このクエリからの出力を理解するには、”不足しているインデックスの検索”の記事をお読みください。

データベースセッションの監視

アクティビティモニタを使用してデータベースセッションを監視します。 Microsoft SQL Server Management Studioには、データベースセッションの監視に使用できるアクティビティモニタが付属しています。

他のセッションによって長時間ブロックされているセッションを探します。 セッションを右クリックし、”詳細”を選択して、プロセスに関連付けられているクエリの詳細を表示します。

Windowsリソースモニタの使用

Windowsリソースモニタを使用して、SQL ServerプロセスのメモリとCPU使用率を監視し、システムに十分なメモリがあり、CPUが飽和していないことを確認することができます。 Sql Server2008では、リソースモニターのSQL Serverプロセスに表示されているメモリの数値が正しくないという既知の問題があることに注意してください。

低速クエリの識別

パフォーマンスがリソースに関連していないと判断した場合、次のステップは、パフォーマンスが悪

次のクエリは、300ミリ秒以上実行される上位100の低速クエリを返します(credit:Long Running Queries):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

ここでは、いくつかのサンプル結果のスクリーンショットです:

遅いクエリのリストを収集したら、それらをデータベースで直接実行し、実行計画を見てそれらのクエリを理解

その他のツール

SQL ServerおよびJavaアプリケーションのトラブルシューティングに使用できるその他のツールのリストを次に示します:

• SQL Server Profiler:遅いクエリを識別するために使用できます。 SQL Server Management Studio
• VisualVMから起動できます: Javaリソースの使用状況を監視し、スレッドダンプを作成するために使用できます
• Java Mission Control/Flight recording:システムイベントの監視と記録
• JProfiler:遅いサービスまたはデー
• New Relic:データベースのパフォーマンスを監視し、低速クエリを記録できます

チェックリスト

SQL Serverの問題をトラブルシューティングするために収集できる情報:

1. データベースサーバー構成(CPUコア数、物理メモリ、ディスク領域、Windows Serverバージョン、SQL Serverバージョン、SQL Serverメモリ設定)
2. アプリケーションサーバー構成(CPUコア数、物理メモリ、ディスク領域、Javaコアメモリ設定)
3. データベースインデックス統計
4. データベース断片化統計
5. SQL serverインデックスレポートが欠落しています
6. サーバーが低速の場合のデータベースサーバーのメモリとCPUの統計
7. システムが低速になる操作
8. SQL Serverの低速クエリレポート
9. データベーステーブルの統計
10. 該当する場合、低速操作中に取得されたJavaアプリケーションからのスレッドダンプのカップル

結論

SQL serverのパフォーマ しかし、適切なツールと忍耐力で、これらの問題の原因を理解できるはずです。 この記事の情報がそれに役立つことを願っています。