Monday, September 23, 2013

Great Page Explaining Stored Procedure Speed Issues

Had an issue with an SQL Server stored procedure running far slower than the same code as a stand alone query.
It was running a totally different execution plan due to the parameters.
This page explains how all that happens. The key to my issue was in the section ‘The Default Settings’

Pouring through the execution plans for procs can be an eye opener. I've found myself vastly improving performances with seemingly minor changes, such as reordering the join elements, adding indexes if missing on join columns, and in one case, using 'Forceseek' to stop the server from using a particular index and instead use a different one.

Just be aware that a new index that makes your current proc zoom, could adversely affect another proc using that table.

No comments:

Post a Comment