Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a query that is executed using sp_executesql

code is as follows

DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3);
SET @ppCityID = CAST(@cityID AS NVARCHAR(3));

exec sp_executesql N'Insert Into #FeaturedAdIDs Select 
    from FeaturedAds fa 
            INNER JOIN #FilteredFeaturedAdIDs ffa 
            ON fa.AdID = ffa.AdID 
                    and fa.SubCategoryID = ffa.SubCategoryID 
                    and fa.RecordStatusKey = @pRsk 
    Where fa.CityID = @pCityID'
    , N'@pRsk INT, @pCityID INT'
    , @pRsk = @rsk
    , @pCityID = @ppCityID;

This piece of code is inside a stored proc.

When I look at the sys.dm_exec_cached_plans it shows that every time I execute this query, with OR without the same parameters, it keeps increasing the plan_generation_num and the exec_count stays at 1.

How can I stop recompiles of this statement?

share|improve this question
I'm pretty sure you can't because of the temporary tables in the query. – Jon Seigel Nov 11 '13 at 16:23

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.