How can I optimize this stored procedure

PRO 2013-03-02 02:16:38

Dave Ballantyne 2013-03-02 05:26:13

The answer to that is not exactly simple and hard to summarise in an allowable time frame.

The stored procedure doesn't exactly have any big performance issues, but you are generating dynamic SQL that may well have a non-optimal operation. This leads to plan-cache bloat and plans that cannot be re-used.

There are also some nested stored procedure calls that may be running slow, but this queryanalysis give no visibility to those.

Are you able to post the dynamic sql (and associated plans) that are being generated ? Use profiler with a Stored Procedure statement trace (not in a production environment though) to get those.

GregGonzalez 2013-03-02 11:31:46
Note that PE PRO will automatically capture all dynamic SQL if you use it to get the actual plan.