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
http://www.qdpma.com/tools/ExecStats.zip (check http://www.qdpma.com/ for updates)
documentations is now at
http://www.qdpma.com/SQLExecStats.html
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