SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Performance Statistics Scripts を使用して SQL Server の情報を取得

leave a comment

先日 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 に対応したスクリプトが格納されています。
image

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

  • 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 でもクエリ情報は取得されていますが、キャッシュされている情報をベースとして、以下の観点の情報が取得されています。

top 10 CPU consuming procedures
  • sys.dm_exec_procedure_stats
    • ORDER BY total_worker_time DESC
top 10 CPU consuming triggers
  • sys.dm_exec_trigger_stats
    • ORDER BY total_worker_time DESC
top 10 CPU by query_hash
  • sys.dm_exec_query_stats
    • ORDER BY sum(total_worker_time) DESC
top 10 logical reads by query_hash
  • sys.dm_exec_query_stats
    • ORDER BY sum(total_logical_reads) DESC
top 10 elapsed time by query_hash
  • sys.dm_exec_query_stats (★)
  • sys.dm_exec_sql_text
    • group by query_hash
    • ORDER BY sum(total_elapsed_time) DESC
top 10 CPU by query_plan_hash and query_hash
  • sys.dm_exec_query_stats (★)
  • sys.dm_exec_sql_text
    • group by query_plan_hash, query_hash
    • ORDER BY sum(total_worker_time) DESC
top 10 logical reads by query_plan_hash and query_hash
  • sys.dm_exec_query_stats (★)
  • sys.dm_exec_sql_text
    • group by query_plan_hash, query_hash
    • ORDER BY sum(total_logical_reads) DESC
top 10 elapsed time  by query_plan_hash and query_hash
  • sys.dm_exec_query_stats (★)
  • sys.dm_exec_sql_text
    • group by query_plan_hash, query_hash
    • ORDER BY sum(total_elapsed_time) DESC

 

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

Written by masayuki.ozawa

6月 22nd, 2014 at 2:46 pm

Posted in SQL Server

Tagged with

Leave a Reply

*