March 30, 2016 at 6:28 am
Hi,
I have following scenario:-
1. Stored Procedure - [dbo].[usp_ListLearners_WithExec]
ALTER PROCEDURE [dbo].[usp_ListLearners_WithExec]
@Learner_ID INT = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @select nvarchar(4000), @where nvarchar(4000), @orderby nvarchar(4000), @query nvarchar(4000) = N'',
@ParameterDefinition nvarchar(4000) = N'@Learner_ID INT';
select @select = N'', @where = '', @orderby = ' ORDER BY L.Learning_StartDT DESC '
select @select = @select +
N'SELECT
L.Learner_ID,
L.Book_ID,
L.Article_ID,
L.Learning_StartDT,
L.Learning_EndDT
FROM Learner.Learnings as L
INNER JOIN Learner.Learners as LR on L.Learner_ID = LR.LearnerID
WHERE 1 = 1 '
if @Learner_ID is not null
select @where = @where + N' and LR.LearnerID = ' + cast(@Learner_ID as nvarchar)
select @query = @query + @select + @where+ @orderby
exec(@query)
--exec sp_executesql @query, @ParameterDefinition, @Learner_ID = @Learner_ID
END
2. I ran this procedure as follows
exec [usp_ListLearners_WithExec] 1
3. Attached is list of cached plans CP1.png and shows 3 entires after procedure execution
a). Proc type compiled plan
b). Prepared type compiled plan (Due to Force Paramterization On)
c). Adhoc type compiled plan for executed select statement and usecounts of all of them is 1.
Query :
When I again execute query
usecount of Proc type compiled plan increases to 2 as it uses saved plan. But usecounts of adhoc type also increases to 2. Why did sql server hits compiled plans of proc type and adhoc type as same time and increases value to 2? Please help.
(see CP2.png)
Regards
VG
March 30, 2016 at 6:35 am
It's the plan 'shell' for the SELECT, it's not a complete plan. It's kinda a pointer to the parameterised form of the plan.
http://daleburnett.com/2011/08/forced-parameterization/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2016 at 7:14 am
Perfect Article 🙂
Thanks Gail:-)
Regards
VG
March 30, 2016 at 9:01 am
it increased by 1 because you ran it twice with the same parameter.
Looking at the embedding of the value in the sql string, if you change the parameter, you will get a new plan for each unique parameter.
Rather do this
if @Learner_ID is not null
select @where = @where + N' and LR.LearnerID = @Learner_ID '
select @query = @query + @select + @where+ @orderby
--exec(@query)
exec sp_executesql @query, @ParameterDefinition, @Learner_ID = @Learner_ID
Or maybe even better, because you only have one optional parameter,
ALTER PROCEDURE [dbo].[usp_ListLearners_WithExec]
@Learner_ID INT = NULL
AS
BEGIN
IF @Learner_ID is not null
exec usp_ListLearnersByNotNullValue @Learner_ID
ELSE
EXEC usp_ListLearnersALL
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply