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

Stored Procedures - Estimated Plan

ExecStats get a list of the stored procedures and functions in each selected database. In the exec query stats modes, if the SQL belongs to a stored procedure or function, a reference is indicated. In the Stored Procedure - Estimated Plan mode, ExecStats will attempt to generate the estimated execution plan for each procedure.

Prc

If a stored procedure defines a temp table with the CREATE TABLE statement, the estimated execution plan is aware of the temp table definition. If a table is created with SELECT INTO #t, then it is necessary to create the table first. The Exec Stats tool has a provision for creating temp tables prior to generating the estimated execution plan. However, if more that one procedure uses a temp table created with SELECT INTO of the same name, then there could be a conflict.

SQL Source - Estimated Plan & Actual Plan

SQL -Source has two variations, one generates estimated plans, the other executes the specified SQL and attempts to detect the actual execution time along with actual execution plan. There is a setting for SQLSource in the configuration file.

<setting name = "SQLSource" serializeAs = "String " >
    <value> SELECT ID, REPLACE(REPLACE(Query,'- ',''),' ','_') AS Query, sqltext
    FROM tpch10c..tpchq WHERE ID
&lt; =22 ORDER BY ID </value>

</setting>

This should be an SQL query that returns 3 columns: ID, Query and sqltext. Query is the noun name or label for the query and sqltext is the sql to be executed.

It is necessary that this query be executed with a wrapper SELECT COUNT(*) FROM ( SQL ).

The SQL Source option is an alternative to the Procedures option in cases where it is desired to exclude certain procedures, or when there are too many procedures, to split procedures into multiple sets.

 

Trace File and SQL plan files

ExecStats should be able to open one or more sqlplan files. It should tabulate the indexes used. However this mode has not been tested in a while?

Trace File TBD?
At some point, I looked into parsing any of the several options in Trace that included the plan, in particular because one option was for the actual plan. At this point, the proper option is to parse plans captured from Extended events, but I have not gotten around to learning Extended events.

 

Prev        Next