Stored Procedure Performance – What You Can Do

There are lots of things you can do to address stored procedure performance issues in Sql Server (this is based on version 2014 which is what I’m currently working in). In fact, there are so many techniques that they seemed worth listing in a blog. Of course, correct indexing is fundamental; I’m assuming you’ve looked at that already. What is listed below would be more appropriate for problems such as execution plans being compiled with specific parameters and not working well when other parameters are passed in. There’s no detail here though: please bear in mind that some of the commands listed are well-known to be buggy. You’ll need to Google for more information on issues and trade-offs.

Forcing recompilation in Stored Procedure Definition
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

::=
[ ENCRYPTION ]
[ RECOMPILE]
[ EXECUTE AS Clause ]

Forcing recompilation at statement-level
SELECT * FROM sys.all_objects ao WHERE ao.name = @i_Parameter OPTION (RECOMPILE)

Providing parameter value for compilation of statement
SELECT * FROM sys.all_objects ao WHERE ao.name = @i_Parameter OPTION (OPTIMIZE FOR (@i_ObjectName = 'all_objects'))

Forcing parameter value to be ignored in compilation
SELECT * FROM sys.all_objects ao WHERE ao.name = @i_Parameter OPTION (OPTIMIZE FOR UNKNOWN)
(NB:if a local variable assigned to the parameter value is used in the query it's equivalent to the above

Forcing recompilation at execution
EXEC {Procedure Name} WITH RECOMPILE

Forcing recompilation at next execution
sp_recompile

Hinting a plan where you cannot edit the source code
Plan Guides

This article gives you several methods of fixing the problem and explains the benefits and pitfalls that come with each:
High Performance Procedures

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s