Parent, SQL Exec Stats, Copyright 2006-2018 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

update in progress 2015-Feb new parent: Execstats 2015,
As of 2012 Oct, SQL Exec Stats and SQL System have been consolidated into a single program. Note the Start button at the bottom left has been replace by "Exec Stats". The second button from the left "Perf Ctr" starts the System Monitor tool from SQL System. Right click to enable openning a previously saved binary file.
There is a bug for Perf Ctr mode not displaying correctly, but it does enable saving Perf counters to an Excel file.

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. One might think that sa and dbo permissions are required for many system oriented DMV's, but curiously it the statistics data accessed via DBCC that require high privilege levels. Uncheck the SSPI box to set SQL Server user and password if desired.

Main Screen 2015

Server

Set the server and instance. The button the right of the SQL Servers box will browse for available (and visible) servers, but it usually better to set the default server in the configuration file.

Servers

Security
Use a SQL Server user account or SSPI.

Users

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

Network

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 Mode default is also set in the config file with 0 for dm_exec_query_stats. This is the most common mode using the information in sys.dm_exec_query_stats. Past versions had additional modes that are nolonger relavent.

Mode

Other modes, not tested as frequently but still of interested are
Stored Procedures - Estimated Plan
SQL Source - Estimated and Actual

I will also support the SQL Source (Estimated and Actual) and Trace file options as best as I can on request.

In earlier versions, there were separate modes for dm_exec_query_stats based on sorting by
1) individual statement, represented by the sql_handle and statement_start_offset combination,
2) batch level, represented by the sql_handle consolidating all statement_start_offsets,
and 3) query hash which aggregates SQL differing only by literals.

The new dm_exec_query_stats mode consolidates the above into a single combination mode. Rows from dm_exec_query_stats are first identified by query_hash. The first occurrence of each query_hash also sets the sql_handle and offset for that query_hash. Any subsequent matching query_hash are group with the first. The final sort will be grouped with other query_hash that match to the first sql_handle.

The Procedure Stats (Prc Stats) mode using the DMV sys.dm_exec_procedure_stats has been removed. 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

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

Sort Order Options

Sort
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

Top
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

Conn Str

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
Debug 1a

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.

Debug 1b

 

Databases
Use the combobox to select a single database

Multi Db   Multi Db

or the Multi-DB button, which set all databases to selected. Specific databases can then be selected or cleared.

Multi Db

Using the combobox drop list to select a single database after the multi-db button has been used clears all but the selected database. The config file can be used to set a single database or multiple databases delimited with a comma.

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 specified, 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.

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.

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

Step 2

Click the Exec Stats button at the lower left to start data collection. (In previous versions, this was the Start button) 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.

 

Prev        Next