SQL Server チューニングのポイント

インデックス不足解消

ロックの最小化

暗黙の型変換の抑制

  • 条件に指定するカラムに対し、違う型の値で条件を指定すると「暗黙の型変換(CONVERT_IMPLICIT )」が発生する
  • Index Scan が発生することでパフォーマンスが落ちる
  • Index Scan を発生させないためには、暗黙の型変換を発生させないように、正しい型で条件を設定する必要がある
    • 例:nvarchar の列に対して、正しい型で条件を指定
      • ×:WHERE NationalIDNumber = 295847284
      • ○:WHERE NationalIDNumber = N'295847284'

コンパイル回数の低減

SQL Server Management Studio

動的管理ビュー

  • SQL Server のチューニングには、dm_* で始まる動的管理ビューを多用する

主要テーブル

  • sys.dm_db_missing_index_group_stats ... 欠落インデックス グループに関する情報を取得
  • sys.dm_db_missing_index_groups ... sys.dm_db_missing_index_group_stats と sys.dm_db_missing_index_details を JOIN するために必要なテーブル
  • sys.dm_db_missing_index_details ... 欠落したインデックスについての詳細情報を取得

インデックスの調査

インデックス不足数の調査

SELECT DB_NAME(database_id) as [データベース名],
       count(*) as [期待しているインデックスが存在しない数]
FROM sys.dm_db_missing_index_details 
GROUP BY DB_NAME(database_id)

インデックスを作成した場合のパフォーマンス改善率

USE [データベース名]
SELECT gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率],
       gs.last_user_seek AS [最後にシークした時間],
       id.statement AS [テーブル名] ,
       id.equality_columns AS [等値述語に使用できる列],
       id.inequality_columns AS [不等値述語に使用できる列] ,
       id.included_columns AS [包括列として必要な列],
       gs.unique_compiles AS [コンパイルおよび再コンパイルの数],
       gs.user_seeks AS [クエリによって発生したシーク数]
 FROM  sys.dm_db_missing_index_group_stats AS gs
       INNER JOIN sys.dm_db_missing_index_groups AS ig
			ON gs.group_handle = ig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS id
			ON ig.index_handle = id.index_handle
 WHERE id.[database_id] =DB_ID() Order By gs.last_user_seek ASC

インデックス統計情報の参照

SELECT [テーブル名] = t.name,
       [統計名] = s.name, 
       [統計の自動作成] = s.auto_created, 
       [NORECOMPUTEオプション] = s.no_recompute,
       [フィルター定義] = s.has_filter,
       [フィルター定義] = s.filter_definition,
       [行数] = sp.rows,
       [フィルター化されていない行数] = sp.unfiltered_rows,
       [ステップ数] = sp.steps,
       [サンプリング行数] = sp.rows_sampled,
       [統計情報最終更新日] = sp.last_updated
FROM sys.tables AS t 
INNER JOIN sys.stats AS s  
ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp 
ORDER BY t.name OPTION (RECOMPILE);

コネクション数の調査

  • 接続元ホスト毎のコネクション数を確認
    select hostname,count(*) from master..sysprocesses where hostname!='' group by hostname
  • (接続元ホスト, プログラム名, ステータス)毎のコネクション数を確認
    select hostname,proguram_name,status,count(*) from master..sysprocesses where hostname!='' group by hostname,proguram_name,status
  • 生データの確認
    select * from master..sysprocesses where hostname!='';

クエリの調査

ステートメントのプロファイル情報確認

  • SET STATISTICS PROFILE ON
    • SQL文の前にこれを付けて実行すると、ステートメントのプロファイル情報を表示する

クエリパフォーマンスの調査

SELECT [平均実行時間(msec)] = total_elapsed_time / execution_count / 1000.00,
       [平均CPU時間(msec)] = total_worker_time / execution_count / 1000.00,
       [平均物理I/O数] = total_physical_reads / execution_count,
       [平均論理I/O数] = (total_logical_reads + total_logical_writes)/ execution_count,
       [SQL文] = SUBSTRING(est.text, (statement_start_offset / 2) + 1, 
       ((CASE statement_end_offset
         WHEN -1 THEN DATALENGTH(est.text)
         ELSE statement_end_offset
         END - statement_start_offset) / 2) + 1)
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est

実行プランの調査

パフォーマンス低下の防止策

トランザクションログの自動拡張の増分調整

データベースの状態調査

問題

クエリパフォーマンスの低下

パフォーマンス改善の施策

統計情報の更新

  • テーブル単位の更新
    UPDATE STATISTICS テーブル名;
  • データベース全体
    EXEC sp_updatestats;

実行プランの再生成

  • インスタンス単位
    DBCC FREEPROCCACHE;

インデックス再構築

  • インデックス指定
    ALTER INDEX <インデックス名> ON <テーブル名> REBUILD; 
  • テーブルに関連する全てのインデックス
    ALTER INDEX ALL ON <テーブル名> REBUILD;

参考URL

都内で働くSEの技術的なひとりごと

大変勉強になります。

SQL Serverトラブルシューティング

その他

タイムアウト


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2019-04-05 (金) 13:35:05 (192d)