THE SQL Server Blog Spot on the Web

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

Joe Chang

Automating Query Exec, Index Usage and Execution Plans, Update 2

I have made updates to my tool for automating SQL Server query exec stats, index usage, execution plan analysis. So I would like people to give it a try and send feedback. If there is an error, please report the file and line number (the top level is sufficient).

download at (check for updates)

documentations is now at

As before, the objectives are to:

1. Simplfy the process of collecting key query tuning information, which also means one can keep a running record of query stats and execution plans over time (daily or weekly).

2. Automatically examine the XML execution plans for information that to be displayed with the query exec stats, and build a cross-reference of between indexes and execution plan references.

3. Store the information in a compact self contained file, ie, not a massive performance data warehouse. Hopefully the DBA makes an effort to learn how to do basic query tuning. Still a reasonably complete set of information (query stats and plans tbut not database data) can be sent for someone else to examine, including an (horribly) expensive consultant who may or may not have any meaningful expertise. (Not to be construed as a solicitation for services)

I will update the documentation as soon as I can. Below is the main screen

Click the button to the right of the databases box to bring up all databases.

This will collect table and index info on multiple databases. The SQLExecStats programs also builds a cross-reference of index usage by execution plan. If the instance has many databases with a large number of table, and indexes, I suggest deselecting the inactive databases. Also, if in multi-db mode, consider deselecting the Stats Hdr check box

One of things I want is to see space information for each database. This view show how much file space is allocated for data and logs separately, and how much is use, and not used in each. Note sp_spaceused shows space for data and logs combined

Below is the virtual file IO. I have also calculated the Average bytes per rd and write, and the average ms/Rd and Write. Of course, this is a point in time calculation, which would include database backups. Later I will do differential file IO

I will add more later

Published Monday, July 13, 2009 8:18 PM 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



Uri Dimant said:


I ran your script and it hangs for 50 database on my server and also throws the error 'value 33.0 is already presents'

July 14, 2009 1:06 AM

jchang said:

if you could provide a file & line number, that would help.

I use number sets to identify datafiles (dbid and fileid), tables (dbid,objectid) and indexes (dbid, objectid, indexid).

I use dbid.0 for the database totals. It is also necessary to map database name to id, I am wondering if two names differ only in non-US collation, I might not be handling this correctly.

My test system as 37 databases, 2300 tables, 3900 indexes, 6300 stats. Memory usage is around 90MB, except during file save which rises to 230MB. I think SAP has 30,000 tables? which would definitely be more than the current program can handle.

July 14, 2009 5:49 AM

Uri Dimant said:

Hi Joe

I'm able to reproduce the error. Do you have an email I would send the exact error?

July 14, 2009 8:39 AM

Joe said:

there is an email link on the right, just below This Blog

July 14, 2009 9:01 AM

Ranga Narasimhan said:


This tool is cool...I like the save xml plan feature very much and is sorted by resource consumption..

just one feedback....It would be great if the column names in the tabs are expanded by default.


July 14, 2009 4:20 PM

Joe said:

look at the excel spreadsheet for the exec stats, I use slanted column headers for some, the font is not as clear as horizontal.

I am trying to figure out if the DataGridView lets me do the same,

I want lots of columns visible, but it would be nice to be able to read the header

July 14, 2009 5:30 PM

Joe said:

apparently my program can encounter problems with certain string value names, like the hypen for database name

if one database is named Test and another Test-2

if you have a table name: Table.1.1, my DBCC SHOW_STATISTICS call fails, and some index names seem to cause problems,

I will look into this and provide fixes

July 15, 2009 11:38 AM

Kim Cantrell said:

This seems like a very neat tool and I hope to better understand it after looking into it. I did load and run it but there are so many stats generated it takes time to parse thru it.


July 15, 2009 3:28 PM

AaronBertrand said:

Joe, whenever you are building scripts that use table names, database names etc., just always use QUOTENAME() to be sure any characters that present identifier incompatibilities are taken care of...

July 18, 2009 11:53 AM

Joe Chang said:

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

July 19, 2013 1:35 PM

Leave a Comment


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


Privacy Statement