Looping in SQL code almost always causes performance problems. You can use a cursor or a while loop in SQL code. Both offer similarly bad performance. Not all code can be re-written in a set based manner, but it does make sense to revisit your looping code from time to time to see if it can be re-written.

There are rare times when a cursor will outperform set based code. Not all procedures returned by the query need (or should) be re-written. But it is a worth while endeavor to re-examine these procedures from time to time.

How to detect this problem:

Select  Object_Name(Object_ID) As ProcedureName
From    sys.sql_modules S
Where   (Definition Like '%cursor%' or Definition Like '%while%')
        And ObjectProperty(Object_ID, N'IsMSShipped') = 0
Order By Object_Name(Object_ID)

How to correct it: Examine each block of code that has a loop, and design an alternative method for accomplishing the same results. This is not always possible.

Level of severity: moderate

Level of difficulty: High