SQL Exec Stats, Copyright © 2006-2012 Elemental Inc. All rights reserved.

Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, Exec Stats, Stored Procs, Dist Stats, Appendix

Main Screen

Below is the main screen. The default SQL Servers and Databases are set in the config file. Default security is SSPI, so the client must have proper permissions, typically sa and dbo in order to access required system views. Uncheck the SSPI box to set SQL Server user and password if desired. After the SQL Server is set, the button to the right of Databases can bring up a list of databases.

The default mode collects table and index information on one database when a database has been specified in to config file database setting, on selected databases if the databases setting is speficied, and all databases if both are blank (this is the intent) or the manually selected databases the Databases drop box to the right of the Server box.

The Mode default is also set in the config file with 1 for query stats with sql handle and offset grouping,

Server and database selection
ExecStats can collect table, index and statistics for multiple databases. Use the combobox to select a single database or the Multi-DB button to select all databases. Additional databases can then be selected or cleared.

Security
Use a SQL Server user account or SSPI.

Connection Options
Options include: a specific network library, connection timeout, packet size and port number.

Note that the second box above is Connection Timeout. There is a separate setting for commnand timeout in the config file only.

Modes of Operation
The past versions, there was a combobox for modes. Now there is drop-down box listing the modes. As mentioned, the modes tested with recent builds are
1) dm_exec_query_stats Stmt - sqlhandle+offset
and
5) Stored Procedures - Estimated Plan
The numbers above correspond to the config file Mode setting. I will also support the SQL Source (Estimated and Actual) and Trace file options as best as I can on request.

Batch and Statement (Stmt) mode use sys.dm_exec_query_stats. In early versions, Batch mode grouped all query stat lines by sql_handle (comprised of one or more SQL statements). The current implementation shows the individual sql_handle and statement_start_offset combination (a single SQL statement), pending feedback on the desired functionality. The default mode is Statement mode, which groups by sql_handle and statement_start_offset combination. There could be multiple rows in dm_exec_query_stats for a single SQL statement, but only a single execution plan will be collected.

There is currently not a raw mode that processes every row in dm_exec_query_stats. I would interested in hearing opinions on whether this would be useful.

The Procedure Stats (Prc Stats) mode uses the DMV sys.dm_exec_procedure_stats. There is a entry in the Mode group for Trigger Stats, sys.dm_exec_trigger_stats, but this currently not implemented pending feedback.

The Plan Cache mode uses sys.dm_exec_cached_plans with no sorting. In principle, it is possible to sort by refcounts or usecounts, but neither has been implemented.

The Stored Procedure (Strd Prc) mode attempts to generate execution plans for all stored procedures in one database. It is necessary select only a single database for this mode to work correctly. The file temp.xml in the txt sub-directory contains a list of temp tables to create prior to generating execution plans. The last line drops all temp tables.

The Trace File (Trc File) mode reads from a single trace file, parsing any Showplan XML, Showplan XML Statistics Profile, or Showplan XML For Query Compile events.

Statistics Options

Statistics options are: 1) no statistics, 2) indexes only and 3) index and column statistics.

Sort Order Options

Sort options are CPU, duration and execution count. Sorting by logical reads is not enabled as I do not want to encourage this use.

Additional Options

The "Top" setting limits the number of query plans. I may change this later to limit based on the size of the query plans. Parsing 150MB of query plans from a 32-bit OS seems to be possible. A 64-bit platform should be able to support very large cumulative plan size. The "Exec Plans" is now the only tested option. In early versions, I allowed for only getting the execution stats.

Connection String

In other tools, I made a provision for adjusting the connection string, but here it is for display only.

Debug (and information) Messages

On startup, the Debug Messages group Textbox at bottom will now display the host operating system and .NET Framework version, example

Microsoft Windows NT 5.1.2600 Service Pack 3, Win32NT, 5.1.2600.196608, Service Pack 3
Environment: 2.0.50727.3603, Major: 2, Minor: 0, Build: 50727, Size of IntPtr is 4

The Size of IntPtr should indicate whether the current process is running as full 64-bit or 32-bit, even if the underlying OS is 64-bit. The next line are the WMI Logical Memory Configuration, which works on 32-bit OS, but apparently not on 64-bit.

Step 1
Click the Databases dropdown box to the right of the Databases box to select a single database or the Multi-DB button to select all databases.

Single-Db


Multi-Db

Select the database(s) for which detailed table and index information should be collected.

Step 2
Click the Start button at the lower left to start data collection. The SQL Exec Stats utility will collect all information and save the results in Excel and binary. Later versions may allow manual selection of which components to execute.