SQL Exec Stats, Copyright © 2006-2012 Elemental Inc. All rights reserved.

Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, Exec Stats, Stored Procs, Dist Stats, Appendix

SQL Exec Stats Readme, www.qdpma.com, jchang6@yahoo.com

Build 2012-09-07

New Features

This is an intermediate build.
Many of the try catch blocks have been disabled to force an error
The basic data structures are being changed,
so backward compatability cannot be maintained.
The current version cannot read files from previous ExecStats.
It is possible to use an older version to generate the sqlplan files.
Then use the current version to parse the plan files.

 

Modes:

Originally, I intended to provide many modes, but after extensive field use, I have been focused on 2 modes:
1) Execution stat froms sys.dm_exec_query_stats grouped by sql_handle and statement_start,
2) Estimated execution plans for all stored procedures in selected databases.

Other modes that I intend to support (but not currently getting tested with new features) are:
a) Estimated or Actual plans with SQL from a query specified in the config file
b) Execution plans from a trace file.

  1. Get sys.dm_exec_query_stats and other dmvs only, without execution plans.
  2. Get execution plans from sys.dm_exec_query_stats by batch (plan handle, temporarily disabled).
  3. Get execution plans from sys.dm_exec_query_stats by statement.
  4. Get execution plans from sys.dm_exec_procedure_stats (temporarily disabled).
  5. Get execution plans from plan_cache.
  6. Generate execution plans for all stored procedures in a database.
  7. Read from previous Exec Stats binary data file.
  8. Read from existing of SQL plan files (*.sqlplan).

(2010-11-23) The dm_exec_procedure_stats option is disabled while work on new features is in progress. The dm_exec_query_stats option grouped by plan handle is disabled pending review of functionality.

 

Generate Plans for All Stored Procedure

Check the radio button SP fn (functions not currently implemented)
Sub-procedure plan info is removed, so only actual SQL in a given procedure is displayed.
Function SQL cannot be removed(?) This will only get stored procedures from the last database check,
so this is really a single database option for now.

 

Parse existing Sqlplan files

Right click the Connect Button until the text is SQL Plans
click again, select the first file in a directory.
An index usage list is generated as well.

 

Multi-Database

Click the '...' button to the right of the databases drop box to list all databases
Not all UI is consistent with multi-database functionality.
This will be corrected over time.

 

To Do
  1. Handle very large table\index sets, and very large plan sets
  2. Database, table, index names with a hyphen may cause problems, I am looking into this. Someone suggested QUOTENAME.
  3. Implement DBCC SHOWFILESTATS for file level space usage - Done.
  4. How to best display the Index-Execution plan/stored procedure cross-reference list..
  5. Batch & Stored Proc mode - new grid view for each statement within a batch?.
  6. Additional views of interest.
  7. hash table for Sql handle, get SQL text only once per handle, instead of for each substring.
  8. Read xmlplan from trace file?
  9. .

Collecting too much data can crash the program. Currently there is nothing that checks for oversize data, other than overly frequent calls to the garbage collector. I suggest not collecting on too many databases together, with attention to tables with a very large number of indexes and statistics. Later versions will check for this..

 

Views to be implemented

Activity Monitor in SQL Server 2008 merges dm_exec_query_stats with dm_exec_requests to include currently running queries. I might do the same as time permits.

  1. sys.dm_exec_connections -- most_recent_sql_handle.
  2. sys.dm_exec_requests -- sql_handle and plan_handle.
  3. sys.dm_exec_sessions -- no handles?.
  4. sys.dm_exec_procedure_stats -- Done
  5. sys.dm_exec_trigger_stats -- is this necessary?
  6. .

 

Bug fixes in 2009:
  1. Force US English interpretation of numbers parsed in the XML showplan..
  2. SQL queries and application code should now be case correct. Previously a case sensitive collation would cause errors in the distribution statistics.
  3. Changed the DBCC SHOW_STATISTICS from ([schema.table],[stat]) to ('schema.table',[stat]) I am not sure if this is the most safe format, should it be: ('schema.table','stat') now changed to ('[schema].[table]',[stat])
  4. StmtCursor added.

 

Additional Notes

The index views sys.dm_db_index_operational_stats and sys.dm_db_index_physical_stats are of interest, but can be resource consuming to run. These might be more appropriate for a general index maintenance tool. So there are no immediate plans to include the index views in ExecStats.
sys.dm_db_file_space_usage is for tempdb only

sys.dm_db_index_operational_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | 0 | NULL | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT } )

Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT } )

 

The view sys.dm_os_buffer_descriptors provides detailed information on memory allocation by database, file, numa node, and type. Some useful queries based on this view are below.
On high-end systems with very large amounts of memory, and very many databases, this query can be expensive to run. I may include an option for this later.

-- should implement?
SELECT * FROM sys.dm_os_nodes -- 14 col
SELECT * FROM sys.dm_os_schedulers

-- by numa_node
SELECT *
FROM (
  SELECT database_id, file_id, numa_node, row_count
  FROM sys.dm_os_buffer_descriptors WITH(NOLOCK )
 ) p
PIVOT (COUNT(row_count) FOR numa_node IN ([0],[1],[2], [3],[4],[5],[6], [7])) AS pvt
ORDER BY database_id, file_id

 

-- by page_type
SELECT DB_NAME(database_id), *
FROM ( -- p
  SELECT database_id, file_id, page_type, row_count
  FROM sys.dm_os_buffer_descriptors WITH(NOLOCK)
) p
PIVOT ( COUNT(row_count) FOR page_type IN ([DATA_PAGE],[INDEX_PAGE],[TEXT_MIX_PAGE]) ) AS pvt
ORDER BY database_id, file_id
/* GAM_PAGE, IAM_PAGE, SGAM_PAGE, PFS_PAGE, TEXT_TREE_PAGE, DIFF_MAP_PAGE, BOOT_PAGE*/