Stored procedure execution plan and impact analysis

  • 30 September 2020
  • 0 replies
  • 155 views

Userlevel 5
Badge +9

Goal:

As applications become bigger there are also the risks of having no overview of what a stored procedure technically does. One stored procedure could have 6 sub-stored procedures and impact 12 tables with 7 triggers.

There are two situations where this can become a problem:

  • During troubleshooting: finding where a certain stored procedure modifies a specific table can become cumbersome.
  • Impact analysis of a change: scanning all the sub processes and triggers involved can take a lot of time. 

The goal of this script is to give a visual representation of the execution plan of a stored procedure. And to make a better impact analysis also give you some info on the processes and triggers involved and the amount of rows and characters involved. 

The script scans every involved stored procedure/trigger and removes any comment lines. For now it ignores drop table statements (since these should be temp tables only), temp tables and it also ignores tsf_sendmessage since this stored procedure is only informational. Since there is no way to know which parts are actually executed everything is shown and it is up to the developer to analyze the results.

What is supported:

  • Updates
  • Deletes
  • Inserts
  • exec statements
  • deadlock loop detecting (trigger   referencing itself),
  • table alias detection

What is (not yet) supported:

  • views
  • before triggers
  • execute statements
  • merge statements
  • multiple database/server executions
  • truncate statements

Release info:

This is version 1 of the script, since there are a lot syntaxes to take care of there is still some work to do to improve this script. It now works about 90% of the time.

I am working on a version 2 of the script that will also incorporate views, before triggers and merge statements. If you have ideas on what kind of information you would like to see in the results, then let me know in the comments and I can add it to version 2 of the script.

Solution:

To use the script enter the name of the stored procedure in the variable @procedureName.

declare @procedureName nvarchar(100)

set @procedureName = 'enter_sp_name_here'

 

----------------------------------------

---- find the script attached ----

----------------------------------------
 

Result:

Here is an example of results shown for a stored procedure (db and procedure created for this example).

Scanning the stored procedure: Change_Role

 

The first table shows the amount of unique triggers/stored procedures and the number of rows and characters (original and without any comment lines). This information can be used for risk analysis for a change to a process.

 

The second table shows detailed information about all the individual stored procedures and triggers. Here you can also gain information where a deadend loop was detected (a trigger referencing itself) and the times a certain procedure was scanned.

 

The last table shows a visual representation of the execution plan of the stored procedure. The first column is the main process (the process you are scanning). Level 1 shows all the processes within the main stored procedure. In this example the stored procedure has two distinct executions: an update on table A and a stored procedure change_sub_role. From there on it branches from the individual stored procedures/dml statements. The first update (row 2) has an update trigger on the table (row 3) and within that trigger is an insert on tableB (row 4).  You also see a loop created by the delete trigger on table B (row 10).

 

 


0 replies

Be the first to reply!

Reply