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.