Automating dm_exec_query_stats and dm_db_index_usage_stats analysis

jchang — Mon, 22 Jun 2009 04:25:00 GMT

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 along 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 tool.

A few people have tried it on production systems. There are still bugs, and feature requests, but hopefully I can turn this into something useful over time.

lets use this url for the latest build rather than changing the url for each build

http://www.qdpma.com/tools/ExecStats.zip

The above build is now 20090628. The (poor excuse for) documention is also online http://www.qdpma.com/SQLExecStats.html

Feedback is welcome, in particular, send me the output files so I can try to figure out if this is collecting the right information. Support will be based on availability.

Note: SQL Server 2008 sp1 fix now correctly reports parallel execution plan total_worker_time (CPU). It is not fixed on SQL Server 2005 sp3, latest cu?

Limitations:

European Localization The XML plan is stored as a string, with numbers in US-en format. My program extracts plan cost and estimated row numbers from the XML plan. The previous version did not specify any localization, but I think the default is the host environment. Hence the code double.Format(value) is interpreted as whatever the local environment is instead of US-en. The builds since 20090623 specifies the US-en format in most places.

If some one with European number formats could run this program and send me the output, I would appreciate it.

Bug fixes, features etc

2009-06-22: The password now should display *, I don't do security work, and it shows. Some formatting was changed to right align, comma thousands separator, Very large numbers in Excel cannot be changed, 4 bytes integers are represented as numbers, larger numbers are represented as text.
I will do more formatting changes later

2009-06-28

The error described by Zen occurs with case sensitive collations. Apparently one SQL query I issued from the executable was not a case correct with the columns I declared on the receiving side, and this is sufficient to cause the data table load to fail.

The current build is really an intermediate build. I am adding the ability to collect table and index information for multiple databases and not everything is consistent yet.

Be careful on trying to collect for too many databases, and too many execution plans, there are checks for over size yet.

2009-07-09

I am still in the middle of changing the tool to have the option of collecting table and index info from all databases. So some of the UI is not entirely consistent. However, all bug fixes are in the new version.

Previously there were SQL queries that were not case correct with client-side table definitions, and when the default collation is case sensitive, an error occurred. This should be corrected.

Also, previously I issued DBCC SHOW_STATISTICS([schema.table],[stat])

which can generate an error if there is a hyphen '-' in the table or schema. I changed this to

('schema.table',[stat])
I am not sure if this is the most safe format, should it be: ('schema.table','stat')?
anyways, go to the main url, http://www.qdpma.com/

look for SQL Exec Stats Build 2009-07-09

http://www.qdpma.com/tools/ExecStats_20090709.zip

Search results matching tags 'Performance', 'indexes', and 'Query Tuning'

Automating dm_exec_query_stats and dm_db_index_usage_stats analysis