HomeQuery OptimizerBenchmarksServer Systems ProcessorsStorage Scripts ExecStats
SQL ExecStats, Copyright © 2006-2018 Elemental Inc. All rights reserved.  (old version: ExecStats)

ExecStats documentation
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

Natural

Many SQL Server performance methodologies are built around the system DMVs introduced in version 2005. This might be in the form of a collection of scripts used by the DBA directly, or one of the many third party tools.

The core DMV is dm_exec_query_stats. A quick word on this:
the procedure cache in modern systems with very large memory can be tens of GBs. The formula for plan cache size limit in less ancient versions of SQL Server is is 75% of the first 4GB, 10% of the memory from 4 to 64GB and 5% of the visible memory above 64GB. (Plan Caching in SQL Server 2008). This works out to 9GB for 64GB memory, and just over 31GB for 512GB memory.

We might think that a properly architected database system making extensive use of stored procedures should not have unusually large plan cache. But many real-world systems are not well-architected, either in having too much dynamic SQL, or in the case of Entity Frameworks, a bloated paramterized SQL plan cache.

Considering that procedures and function can have very many statements, there can be more than one million rows in dm_exec_query_stats. As such, some care should be given to how dm_exec_query_stats is queried.

Two of the columns in this are sql_handle, which groups a collection of SQL statements issued together, including stored procedures and functions. (Note, from version 2008 dm_exec_procedure_stats provides aggregate execution statistics for procedures and from 2016 dm_exec_function_stats for functions.) Another column is query_hash, which is a hash, after replacing literals.

Based on knowledge of the information important in SQL Server performance, we can form a plan on collecting this data. A good starting is the DMV dm_exec_query_stats.
From there, the query could use the CROSS APPLY with the sql and plan handles dm_exec_sql_text and dm_exec_query_plan and dm_exec_text_query_plan to get the SQL text and XML sqlplan.
By converting the query_plan column to data type XML, the plan can be viewed graphically with a double-click in SSMS. Manually saving each plan to individual files is a cumbersome process.

In a parallel execution plan the setting for the cost threshold for parallelism and max degree of parallelism, along with the actual hardware parameters: total number of cores, cores per socket and hyper-threading are considered. Certain execution plan operations are also dependent on system memory (I am not sure if they are actually dependent on maximum server memory).

The above are factors that the SQL Server query optimizer uses to determine the execution plan. Without knowledge of all of the above information, one cannot claim to be able to offer educated advice on performance. Now would be a good time to evaluate a SQL Server performance tool on the information that it collects.

Collecting Performance Information

ExecStats first queries only dm_exec_query_stats, and then makes separate calls for one SQL text and plan at a time to avoid sending a single excessively expensive query to SQL Server.

It is very possible that very many of the entries in dm_exec_query_stats are scalar statments have minuscule total CPU (worker time) consumption. These probably do not contribute to any SQL Server performance problems, and can be filtered out to focus on the queries that have actual table access.

At the end of the execution plan are compile parameter values.

The element not shown above are the parameter runtime values in the actual plan.

Another useful DMV is dm_db_index_usage_stats. This has information on how often each index has been used in seek, scan, lookup and write operations. If the usage statistics has been active sufficiently, we can identify indexes that have not been used and could be considered for deletion. However, there may be indexes that were used only infrequently. There may also be indexes with the similar keys that suggest consolidation may be possible. What is missing is the linkage to the SQL for each index. This information is in the query plans.

The final information of interest are data distribution statistics. The traditional method is to use DBCC SHOW_STATISTICS. In more recent versions we can use the DMF dm_db_stats_properties. This DMV has most of the statistics header information such as last update, rows sampled, and modification counter.

In version 2016, there is also dm_db_stats_histogram for the histogram portion of statistics. From this, we can look for skewed data distribution. At this time, ExecStats does not example histogram information. What would be simpler is the density vector information. There is no DMV for this in current versions of SQL Server.

 

Additional DMVs

dm_exec_function_stats (SQL Server version 2016)
Returns aggregate performance statistics for cached functions. The view returns one row for each cached function plan, and the lifetime of the row is as long as the function remains cached. When a function is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats. Returns information about scalar functions, including in-memory functions and CLR scalar functions. Does not return information about table valued functions.

In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out.

dm_exec_procedure_stats (2008)
Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out.

dm_exec_trigger_stats (2008)
Returns aggregate performance statistics for cached triggers.

dm_exec_query_statistics_xml (2016)
Returns query execution plan for in-flight requests. Use this DMV to retrieve showplan XML with transient statistics.


dm_exec_external_work
Returns information about the workload per worker, on each compute node. Query sys.dm_exec_external_work to identify the work spun up to communicate with the external data source (e.g. Hadoop or external SQL Server).

dm_exec_cursors (2008)
Returns information about the cursors that are open in various databases.

dm_exec_xml_handles (2008)
Returns information about active handles that have been opened by sp_xml_preparedocument .