SQL Exec Stats, Copyright 2006-2013 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

License

The license to use this software is free. Elemental Inc. (www.QDPMA.com) assumes no liability for use.
Download  SQL Exec Stats

update in progress 2013-Sep

About

ExecStats started out with a focus on execution plan analysis with cross-referencing index usage to the SQL. Formerly separate programs for performance monitoring and server system architecture discovery via WMI have now been integrated into ExecStats (2012-Oct).

The Distributed Management Views and functions (DMV and DMF) have greatly simplified the process of collecting performance data sufficient for basic assessment and SQL query and index tuning. Still, even the modest effort in collecting a set of DMV/DMFs is more than the push-button effort that people will be inclined to do on a regular basis. Query tuning also requires the additional effort to examine the execution plans.

Index tuning involves both indexes that should be added or expanded, and indexes that should be removed or consolidated. The DMVs provide some recommendations on missing indexes, but this is not always reliable, so a live expert is still essential. Identifying indexes not used is simple. The other part is identifying infrequently used indexes that could also be dropped or consolidated. This would require finding the execution plans that reference the infrequently used indexes to assess the implications or impact.

Much of this can be automated with the SQL Exec Stats so that regular performance data collection is nearly a zero effort routine, including correlation of the execution statistics in the DMV with information from the execution plans, and a cross reference of execution plans that reference each index. Finally, if performance expertise is not available onsite, the set of information is compact enough to be sent by email or ftp for full assessment.

 

Requirements

  1. Either SQL Server 2005 any build, service pack 2 or later prefered
      (statement level query plan).
  2. or SQL Server 2008 any build, service pack 1 preferred
      (worker time for parallel execution plans).
  3. .NET Framework 3.5, Builds 2010-10-29 and later
  4. .NET Framework 2.0, Builds 2010-09-01 and earlier

Supported versions are SQL Server 2005 (service pack 2 or later preferred), and SQL Server 2008 (service pack 1 or later preferred). SQL Server 2008 SP1 now correctly reports worker time for parallels execution plans. Previous 2005 and 2008 versions typically reported zero, negating the ability to weight by CPU correctly when parallel plans are present.

References in early (2008?) versions to SMO were removed. (In the past?) the SMO dlls were 32-bit only. (There are now 32 and 64-ibt SMO dlls. New features may employ SMO dlls.) It is highly desired to run ExecStats in a 64-bit environment as a large virtual address space is necessary to handle very large amount of plan contents.
A new data structure requires .NET Framework 3.0. The previous requirements were .NET Framework 2.0. (Can't remember what I was thinking here)
A component from Gembox (www.gemboxsoftware.com) is employed to generate Excel files.
The orginal compression using the sample code for deflate and gzip have been disabled. I am looking to see if I can implement DotNetZip and 7z-lzma. (The C# version of lzma has poor performance. When I can learn to program the call to the C version of lzma, compression capabiliy will be enabled.)

 

Objectives: What does SQL Exec Stats do?

  1. Automate collection of key DMVs for query tuning.
  2. Collates query exec stats with execution plan details.
  3. Cross reference index usage by execution plan, what plans reference infrequently used indexes.
  4. Saves XML plan for top queries.
  5. Save information directly to Microsoft Excel 2003 file format and in binary.

Additional options include:
a) generate estimated execution plans for all stored procedures in a database,
b) execute a list of SQL statements with actual execution plan, or just get the estimated plan.

DMV and DMFs include:

  1. dm_exec_query_stats
  2. dm_exec_query_plan or dm_exec_text_query_plan
  3. dm_exec_sql_text
  4. dm_db_index_usage_stats
  5. dm_io_virtual_file_stats

The collation involves parsing the XML plan so that plan information can be displayed along with execution statistics, and to build a cross reference between index usage statistics and the execution plans that reference each index.

The performance data is saved as a compressed binary file and as a Microsoft Excel 2003 file. The top XML plans are saved in the Sqlplan XML format that can be opened from SSMS. Another idea is to save all the Sqlplans in a trace file for easier browsing, but I have not implemented this yet.

Performance Monitoring

Previously, I had a separate tool (SqlSystem) for monitoring performance. Why would I go to the effort of this when there are soo many other performance monitoring software out there. Well because system architecture is very important, along with understanding how to interpret the counters. Most other tools have no concept of a real server, which is not a desktop with 1 controller and 1 disk.

As of 2012, performance monitoring is now integrated into ExecStats. At some point, I may also integrate Trace analysis and SQL Clone, as both existing standalone programs are now very old.

Comments

The DMV dm_exec_query_stats does not a replace for SQL Server Profiler and Trace. the Profiler/Trace can capture every statement or batch. The DMV will only maintain the overall summary. The DMV may miss SQL and RPC evicted from the procedure cache or will lose history for recompiles. Still, dm_exec_query_stats is easy to use, the data is already available on an active system, and is sufficiently useful in many situations.

Sure, SQL Server 2008 has the Management Data Warehouse along with several built-in reports. My tools does some similar functions, some different, but the main difference is portability of data for remote tuning.

 

Purpose: Why do we need or want this?
  1. Simplifies performance data collection (sufficiently complete)
  2. Encourages regular collection of performance data, i.e. keep a history of system health
  3. Automates several elements of performance analysis (reduces time and effort)
  4. Enable remote tuning assistance, key information is compact enough to be sent by email

The intent is that later versions of this utility will have the ability to compare execution and index usage statistics on a day-to-day basis. Another possibility is to have some of the capability of Database Tuning Assistant, with the ability to identify situations where rewriting SQL is the proper resolution, not just index or partition tuning.

 

Introduction

Everyone should know by now how really useful the DMVs dm_exec_query_stats, and dm_db_index_usage_stats and the associated DMFs for obtaining the SQL and XML plan: dm_exec_sql_text, dm_exec_query_plan, and dm_exec_text_query_plan. Of course it has been explained that dm_exec_query_stats is not a replacement for SQL Server Profiler and SQL Trace. The DMV is only reliable if execution plans are not frequently evicted from the procedure cache and if SQL is not frequently recompiled.

Still the DMV dm_exec_query_stats is popular because the results are available with a simple query. The complexity of setting up a trace, making sure it is not adversely impacting server performance, making sure space is available, and most of all, the effort of parsing the trace are all avoided.

So what do we do next? From the top query statistics we can start looking at the SQL and the execution plans. One minor annoyance is that if we use CROSS APPLY to dm_exec_query_plan, each XML plan has to be saved individually. This can be annoying if one has to remote into to a PC in the server room and this connection is slow.

There is much information in the XML plan that would be helpful if we could see it in a grid with the execution statistics. From the index usage statistics, we can eliminate unused indexes. There might also be indexes that are infrequently used. Sometimes it is obvious that certain indexes can be consolidated. Other times, it is necessary to examine the execution plan to determine if another index is sufficient. The XML plan analysis cross-references in which plans each index is used. All of this is tedious work which can be automated. So this is the purpose of the SQLExecStats tools.

Keep in mind the missing index information is not an optimized list. DTA would do a proper analysis, while the missing indexes generator may apply more indexes than necessary. It would be nice to have a magic index tuning wizard, but nothing today replaces a good expert. Also, an automated tuning tool cannot question the developer as to what was meant by a specific query.

 

Configuration:

The file SQLStatsExec.exe.config contains the configuration values.
SqlServer is the preferred SQL Server Instance.
Database is for the preferred database for which detailed table and index information is extracted. Execution statistics are instance-wide. Use this field when there is only a single database of interest.
Databases is a comma separated string listing the databases of interest. Use this value when there are multiple databases of interest.

XML config

 

Next