THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

Automating dm_exec_query_stats and dm_db_index_usage_stats analysis

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/
Published Monday, June 22, 2009 1:25 AM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Zen said:

Joe,

Great tool!

If you could open the source that would be great!

I tried in one of our production server (BIN collation, SQL Server 2005 with SP3, 32bit on Windows Server 2003 32bit).  I got error here:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

See the end of this message for details on invoking

just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************

System.InvalidOperationException: Invalid attempt to call Read when reader is closed.

  at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

  at System.Data.SqlClient.SqlDataReader.Read()

  at SQLBase.FormSQLBase.PopDistStatHdr(Int32 n, DataTable& dt, String& sMessage) in C:\JoeChang\Project\test_load\SQLBase\SQLExecStats\SQLBase\DataStats.cs:line 351

  at SQLBase.FormSQLBase.Histogram() in C:\JoeChang\Project\test_load\SQLBase\SQLExecStats\SQLBase\DataStats.cs:line 249

  at SQLBase.FormSQLBase.GetDistStats() in C:\JoeChang\Project\test_load\SQLBase\SQLExecStats\SQLBase\DataStats.cs:line 231

  at SQLBase.FormSQLBase.ConnectAuto() in C:\JoeChang\Project\test_load\SQLBase\SQLExecStats\SQLBase\FormCommon.cs:line 400

  at SQLBase.FormSQLBase.buttonConn_Click(Object sender, EventArgs e) in C:\JoeChang\Project\test_load\SQLBase\SQLExecStats\SQLBase\FormSQLBase.cs:line 130

  at System.Windows.Forms.Control.OnClick(EventArgs e)

  at System.Windows.Forms.Button.OnClick(EventArgs e)

  at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

  at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

  at System.Windows.Forms.Control.WndProc(Message& m)

  at System.Windows.Forms.ButtonBase.WndProc(Message& m)

  at System.Windows.Forms.Button.WndProc(Message& m)

  at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

  at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

  at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************

mscorlib

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll

----------------------------------------

SQLExecStats

   Assembly Version: 1.0.0.2

   Win32 Version: 1.0.0.2

   CodeBase: file:///C:/execstats/ExecStats/SQLExecStats.exe

----------------------------------------

System.Windows.Forms

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll

----------------------------------------

System

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll

----------------------------------------

System.Drawing

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll

----------------------------------------

System.Data

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll

----------------------------------------

System.Configuration

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll

----------------------------------------

System.Xml

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll

----------------------------------------

Accessibility

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll

----------------------------------------

System.Transactions

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll

----------------------------------------

System.EnterpriseServices

   Assembly Version: 2.0.0.0

   Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)

   CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll

----------------------------------------

************** JIT Debugging **************

To enable just-in-time (JIT) debugging, the .config file for this

application or computer (machine.config) must have the

jitDebugging value set in the system.windows.forms section.

The application must also be compiled with debugging

enabled.

For example:

<configuration>

   <system.windows.forms jitDebugging="true" />

</configuration>

When JIT debugging is enabled, any unhandled exception

will be sent to the JIT debugger registered on the computer

rather than be handled by this dialog box.

June 24, 2009 3:55 PM
 

Joe Chang said:

Last year, I made my tool for automating execution plan analysis available on www.qdpma.com The original

July 19, 2013 1:35 PM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement