Recently me and my development team have been looking at performance and how to improve it.

One area of improvement we have identified is with our stored procedures, which we plan to rewrite. But how do we identify which are the easiest to rewrite and which are the hardest?

My first thought was maybe to count the number of times each keyword is used and rank them somehow. eg each JOIN gets 5, OUTER APPLY gets 20, each term in WHERE gets 1, and then combine that with the length of the query and how many parameters.

However doing a bit of googling I came across the following blog and sql script. This script analyses the stored procedures in terms of number of lines of code, number of parameters and number of dependencies.

The complexity is divided into SIMPLE, MEDIUM and COMPLEX.

WHEN NumberOfLines * NumberOfDependencies * NumberOfParameters < 5000 THEN ‘Simple’
WHEN NumberOfLines * NumberOfDependencies * NumberOfParameters < 10000 THEN ‘Medium’
ELSE ‘Complex’

This gives a fairly good estimate of which stored procedures are the most complex and which would probably take the longest time to rewrite.