THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Joe Chang

Automating SQL Execution Plan analysis

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

The original version could parse execution plans from sys.dm_exec_query_stats or dm_exec_cached_plans and generate a cross-reference of which execution plans employed each index. The DMV sys.dm_db_index_usage_stats shows how often each index is used, but not where, that is, which particular stored procedure or

 

My latest version can now also 1) use the DMV sys.dm_exec_procedure_stats, 2) it can also get the full list of stored procedures in a database and generate the plans with SET SHOWPLAN_XML, and it can parse a set of previously generated sqlplans.

http://www.qdpma.com/ExecStats/SQLExecStats.html

http://www.qdpma.com/tools/ExecStats_20100321.zip (see Seth's comment)

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

(build time 2010-03-22 10:02 EST) 

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

Better yet, go to www.qdpma.com to check for the most recent version.

 

A quick review: SQL Server 2005 made it easy to get execution statistics with DMVs and DMFs. Among the collection of Execution Related Dynamic Management Views and Functions, sys.dm_exec_query_stats maintains aggregate performance statistics for cached query plans. By cross applying to the DMFs sys.dm_exec_sql_text and sys.dm_exec_query_plan, we can get the SQL and the XML execution plan.

 

Technically, the SQL Server engine can parse the contents of the XML plan, but I have never thought that this was a good idea, especially when working with very large execution plans or a very large number of execution plans.

 

Parsing XML execution plans from a C# program, on the other hand is not problem at all. It takes only a few minutes to grind through 1GB of XML plans.

If anyone thinks this is an abnormally large, check what your Plan Cache: Cache Pages and Cache Object Counts are. I have seen over 1M pages, and 50,000 objects (of course only a few hundred of the 50K were actually reused).

 

Anyways, please give my tool a try, and send feedback,

I am trying to make this a generally useful tool

 

For reference, the previous blogs:

http://sqlblog.com/blogs/joe_chang/archive/2009/07/13/automating-query-exec-index-usage-and-execution-plans-update-2.aspx

http://sqlblog.com/blogs/joe_chang/archive/2009/06/22/automating-dm-exec-query-stats-and-dm-db-index-usage-stats-analysis.aspx

Published Monday, March 22, 2010 1:34 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

 

Seth said:

Hi

I tried this but kept getting a divide by zeros error.

this is the print out from JIT:

See the end of this message for details on invoking

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

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

System.DivideByZeroException: Attempted to divide by zero.

  at SQLBase.FormBase.FormatDataGridExecStats(SQLStats& st, DataGridView& dgv) in C:\JoeChang\Project\SQLExecStats2\SQLBase\ExecStats.cs:line 905

 

March 22, 2010 5:09 AM
 

jchang said:

 My bad, I still had query_hash and query_plan_hash for 2005, fixed now

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

905 is the step that calculates the average cost per execute, so some how it got to that point, but had zero exec stat rows

are you on SQL Server 2005, it could be not every thing works for 2005, as I have been adding new features for 2008, and its hard to keep track of what feature was added when

Anyways, i put in a trap for the divide, but there was probably an earlier error messsage.

There was probably an earlier error (0 rows in the execution stats), but I need the program to run though to save the txt file, please give it another try, and look in the txt file towards the end, or send it to me

March 22, 2010 8:49 AM
 

uberVU - social comments said:

This post was mentioned on Twitter by sqlmanager: SQL Blog. Automating #SQL Execution Plan analysis: Last year, I made my tool for automating execution plan analysi... http://bit.ly/c22D8j

March 22, 2010 7:20 PM
 

Feodor Georgiev said:

Hello, Joe! Thank you for the article. I have not tried your tool yet, however I am referencing it on my blog and wanted to let you know. http://feodorgeorgiev.com/blog/2010/03/getting-to-know-your-performance-tuning-best-friend/

I will check out the tool and will comment on it later on.

March 26, 2010 11:37 AM
 

sandson said:

Hello,

I just fall on your blog in a last googling attempt before building my own similar tool to answer the "Who is using my indexes ?" question. So it seems promising but I have an issue: (the message in french [yes I'm French] says "The format of the input string is incorrect")

System.FormatException: Le format de la chaîne d'entrée est incorrect.

  à System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

  à System.Number.ParseSingle(String value, NumberStyles options, NumberFormatInfo numfmt)

  à System.Single.Parse(String s, NumberStyles style, NumberFormatInfo info)

  à SQLBase.FormBase.ChildAttributeMIG(Int32 n, StringBuilder& sb, XmlNode& xNodeMIG, SQLStats& st) dans C:\JoeChang\Project\SQLExecStats2\SQLBase\ParseXML.cs:ligne 611

I'm loading an SQL 2008 database but using the Compatibility mode 90 not 100 (in case this matters). The server is running SQL 2008 Enterprise 64 bits as the db engine and Windows 2008 Standard x64. Your program is run from another computer which runs Windows 2003 Standard 32 bits.

Thanks anyway for saving me nights of coding... :)

August 31, 2010 8:19 AM
 

jchang said:

i will look into this, send me an email too

Apparently when parsing the Missing Indexes Impact string to a number, I did not include the number format information, so see new build 2010-08-31.

Why is it you europeans just can't do it the American way, its the only way?, right? unless its food, i will elect the french or italian on this, and a few other things too, never mind.

August 31, 2010 1:29 PM
 

sandson said:

I'm wondering the same when dealing with date formats in America :)

Why have decided to introduce the month before the day and then the year, there is no logic behind that ! :)

Anyway, I just test the new build, but seems that some others number formats missing.

Why not adding this at startup:

System.Threading.Thread.CurrentThread.CurrentCulture = System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("fr");

where the parameter "fr" could be setup in app.config file, thus you don't have to deal with formats (this is what I do in my projects).

here is the call stack (cut at GetSqlPlan as the rest is the same as previous stack)

System.FormatException: Le format de la chaîne d'entrée est incorrect.

  à System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

  à System.Number.ParseSingle(String value, NumberStyles options, NumberFormatInfo numfmt)

  à System.Single.Parse(String s, NumberStyles style, NumberFormatInfo info)

  à SQLBase.FormBase.ChildAttributeMIG(Int32 n, StringBuilder& sb, XmlNode& xNodeMIG, SQLStats& st) dans C:\JoeChang\Project\SQLExecStats2\SQLBase\ParseXML.cs:ligne 611

Thank you,

September 1, 2010 3:30 AM
 

jchang said:

OK, I think you are still using the 20100322 build, which is why you keep showing the MIG call as line 611, when the new code has this at line 630 

please try both builds:

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

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

A .NET program runs with the culture/number format of the local environment. However, the SQL Server xmlplans still use US-En format, so I have the use NumberFormatInfo nfi = CultureInfo.GetCultureInfo("en-US").NumberFormat;

I am not sure why applying this to the Missing Index string does not work, either w/o or with formatting code.

The new 9-1 build (actually I like yyyymmdd because my files appear in chronological order instead of mm/dd or dd/mm, so why doesn't the rest the of world follow my lead?)

now has a try-catch block around the MI impact parse.

Do you know how to get the Missing Index info from the DMV?

Can you send me the full string?

September 1, 2010 8:26 AM
 

vini senza solfiti said:

Interesting blog. Actually google made searching of information easy on any topic. Well keep it up and post more interesting blogs.

November 29, 2010 5:18 AM

Leave a Comment

(required) 
(optional)
(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
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement