A developer was asking me about why his procedure wasn't running, but the query was ok.
When I run the query, it was fast.But running the procedure, it was executing forever.
What I did was to add at the end of the query:
OPTION(RECOMPILE)
It doesn't help.
The procedure was like this (the slow one):
CREATE procedure [dbo].[pr_ProcedureName]
@CodOnda int
AS
BEGIN
set nocount on
...
Then I did this:
CREATE procedure [dbo].[pr_ProcedureName]
@CodOnda int
AS
BEGIN
set nocount on
declare @localCodOnda int
set @localCodOnda = @CodOnda
...
And BAM! Local variables. The procedure was faster than ever. I know this is something about parameter sniffing. But why the recompile didn't help?
I really would like to have the old query plan, but I don't have.
And the problem wasn't about locking or blocking. I was tracking with sp_WhoIsActive and some queries that I found in some answers here (thanks to Aaron Bertrand and Brent Ozar for them). I am using SQL Server 2008 R2.
Best Answer
Using
OPTION (RECOMPILE)
is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits.Determining the specific reasons that
OPTION (RECOMPILE)
did not help in your particular case would require details you are unable to provide.Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before
OPTION (OPTIMIZE FOR UNKNOWN)
and trace flag 4136 became available.For more details please see:
Parameter Sniffing, Embedding and the
RECOMPILE
Options