Inspecting a Stored Procedure

From SQLBWiki

1 Trees Pane
- 1.1 Filtering
2 Objects Pane
3 Queries Pane
4 Variables Pane
5 Defects Pane

[edit] Trees Pane

Analysing a stored procedure produces a parse tree, which shows 'interesting parts' of the stored procedure call tree. The left pane shows some selection (filters) of nodes from the full syntax tree. This allows you to quickly grasp what a stored procedure does. for example, this a typical syntax tree:

It shows the main statements contained in a procedure tree. The full syntax tree is the basis for all filtered trees and is a tree-style representation of the entire program, where nodes are syntactical elements of the Transact-SQL language. for instance an "if statement" like if @@ will be represented by the following tree:

The principle of SQLBrowser is to graft each stored proc body under each sub-proc execution node. For instance, since the stored proc sp_help calls the stored proc sp_objectsegment, the syntax tree will show the body of sp_objectsegment grafted under the execution node.

Doing so allows to see the proc effects in depth. Note that to avoid huge trees, only the first instance of a proc is grafted, i.e. you won't see a proc grafted twice.

[edit] Filtering

Clicking on an icon filters the tree on nodes of interest:

S shows all selects which are result sets, i.e. things that go back to the client. Selects that are assignment to local variables are not shown. Using this filter, you can quickly see what result sets go back to the client.
U shows all updates in the broadest sense of the word, i.e. all update, delete, select into or insert statements. This allows you to quickly see what side effects this proc causes. (A side effect in software jargon is a modification to the environment)
P shows all sub-procs calls. this tree shows you all the sub-procs used by this proc
T shows all transactions statements, i.e. begin tran, commit tran, save tran and rollback tran found in this proc tree. This allows you to see the transactional behaviour of a proc.
A this filter shows all of the above. It gives a broad vision of what this proc does. It is the filter shown by default.
F this filter shows the full syntax tree. It is of no great interest but shows the totality of the syntax.

[edit] Objects Pane

This tab shows all objects (i.e. procs and tables) that are used throughout the proc execution. For instance, given a table, you can find immediately all accesses to this table. Tables indices are also shown in this tree.

[edit] Queries Pane

This tab takes all SQL queries (insert/select/update/delete) and presents them in a readable way. This is particularly convenient when you deal with very complex queries, where the "where clause" has been amended many times and is not easy to read. Here, the presentation of the "where clause" is ordered:

  1. SARGS are shown first
  2. SARGS are sorted by table (all SARGS on one table are shown close to each other)
  3. JOINS are shown second
  4. JOINS are sorted by table (all JOINS on two tables are shown close to each other)

for instance, the query:

is broken down like this:

You can Right-Click on a query to display a query's graph:

[edit] Variables Pane

This tab shows all events that happens to the stored procedure variables. This tab is convenient when you want to track a parameter down to where it is used in deep sub procs. When variables are passed to a subproc, even if their name changes, the event tree of the variable in the subproc is grafted to the event tree for this variable: