SQL Exec Stats, Copyright © 2006-2012 Elemental Inc. All rights reserved.
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.
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 call the C version of lzma, compression capabiliy will be enabled.)
DMV and DMFs include:
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 can be saved in the Sqlplan XML format that can be opened from SSMS.
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?
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.
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.
The file SQLStatsExec.exe.config contains the configuration values. Disregard the entry for Target. Source is the preferred SQL Server Instance. Database is for the preferred database. Detailed table and index information is extracted for that database. Execution statistics are instance-wide.