SE の雑記

先日 db tech showcase 2014 の SQL Server パフォーマンス問題対処 Deep Dive で Performance Statistics Script  (Perf Stats) が紹介されていたので、情報をまとめてみたいと思います。

情報としては、
SQL Server 2005 Performance Statistics Script
SQL Server ? Download the Perfstats script
SQL Server 2005/2008/2008R2/2012 Performance Statistics collection scripts
What is SQL Nexus?
SQL パフォーマンス チューニング 中上級編 vol. 3 SQLDiag / SQL Nexus ツールの利用 (前編)
SQL パフォーマンス チューニング 中上級編 vol. 3 SQLDiag / SQL Nexus ツールの利用 (後編)
が参考になります。

Perf Stats は SQL Nexus に含まれている情報取得のためのスクリプトになります。
本投稿を書いている時点の最新版である [4.0.0.64] では [DataCollectors] というフォルダに SQL Server 2005 ～ SQL Server 2014 に対応したスクリプトが格納されています。

フォルダーには以下のファイルが格納されています。

• SQLDiagPerfStats_Detailed_Trace2012.XML   
• SQLDiagPerfStats_Trace2012.XML
• SQLDiagReplay2012.XML
• SQL_2014_Perf_Stats.sql
• SQL_2014_Perf_Stats_Snapshot.sql   
• StartSQLDiagDetailed_Trace2014.cmd   
• StartSQLDiagForReplay2014.cmd   
• StartSQLDiagTrace2014.cmd   

XML と cmd は SQLDiag を実行する際の定義となり、PerfStats の SQL としては、

• SQL_2014_Perf_Stats.sql
• SQL_2014_Perf_Stats_Snapshot.sql   

の 2 本となります。

Perf Stats.sql

デフォルトの設定では 10 秒間隔で以下のストアドプロシージャが実行されます。
定期的に取得すると効果的である [実行中のセッション] [実行中の要求] の情報が取得されています。

• sp_perf_stats11 (10 秒間隔で実行される)
• sys.dm_exec_sessions (★)
• sys.dm_exec_requests
• sys.dm_os_tasks
• sys.dm_exec_connections
• sys.dm_tran_active_transactions
• sys.dm_tran_session_transactions
• sys.dm_tran_active_transactions
• sys.dm_os_waiting_tasks
• sys.dm_exec_query_stats
• sys.dm_exec_plan_attributes
• sys.dm_exec_sql_text
• sys.dm_exec_requests
• sys.dm_os_workers
• sys.dm_os_threads
• sys.dm_exec_session
• sp_perf_stats_infrequent11 (60 秒間隔で実行される)
• sys.dm_exec_requests (★)
• sys.dm_os_wait_stats (★)
• sys.dm_os_spinlock_stats (★)
• sys.dm_os_performance_counters (★)
• sys.dm_os_sys_info (★)
• sys.dm_os_ring_buffers
• sys.dm_os_latch_stats (★)

(★) : 情報取得時のベーステーブル

Perf_Stats_Snapshot.sql

こちらは定期的に情報の取得を行うのではなく実行時の情報を取得します。
スナップショットですので、1 回取得すればよい設定等の情報が取得されています。

• sys.dm_exec_query_stats (★)
• sys.dm_exec_cached_plans
• sys.dm_exec_plan_attributes
• sys.dm_exec_sql_text
• sys.dm_db_missing_index_groups (★)
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_details
• master.dbo.sysdatabases (★)
• sys.resource_governor_configuration (★)
• sys.resource_governor_resource_pools (★)
• sys.resource_governor_workload_groups (★)
• sys.dm_database_encryption_keys (★)
• sys.dm_os_loaded_modules (★)
• sys.dm_server_audit_status (★)
• sys.dm_exec_procedure_stats (★)
• sys.dm_exec_trigger_stats (★)
• sys.availability_groups (★)
• sys.availability_replicas
• sys.dm_hadr_availability_replica_states
• sys.dm_hadr_cluster (★)
• sys.dm_hadr_cluster_members (★)
• sys.dm_hadr_cluster_networks (★)
• sys.availability_replicas (★)
• sys.stats (★)
• sys.dm_os_threads (★)
• sys.dm_os_tasks
• sys.dm_exec_requests

(★) : 情報取得時のベーステーブル

こちらの SQL でもクエリ情報は取得されていますが、キャッシュされている情報をベースとして、以下の観点の情報が取得されています。

私も診断系の作業を実施することがあり、診断用のクエリをいくつか持っているのですが、実行中の情報については取得情報の再考が必要だなと感じました。