Why we get 2 different execution plan using sp_executesql and plain text

Question

Why we get 2 different execution plan using sp_executesql and plain text

devbyalan

SSC Veteran

Points: 269
More actions
September 27, 2019 at 7:02 pm

#3684166

Hello everyone
Here we go with this topic.
I have some confusion about why we get 2 different execution plans when run with sp_executesql vs plain text in SSMS. When we run with sp_executesql we get 68MM reads vs 33k with plain text, the difference is very huge, this is not a normal behavior, but why this is happening?
With sp_executesql:
With t-sql text
Can someone advice me how can i "fix" or understand the problem or behavior?
Is this a normal behavior?
Thanks a lot.
PS: We are using SQL Server 2016 SP1 Enterprise Edition.
Attached 2 execution plans for more info.
I made a post in SentryOne with anonymized plans and decided to make a post also here.
SentryOne post

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 1

SQL Server caches the execution plan of queries, it may recompile a query a different way if it is executed at different times.

If there is any difference in the text between the two queries, e.g. a new line or an extra space, it will not use the plan in the cache but recompile it, maybe with a different execution plan.

You should clear the query cache and try again:

CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

ScottPletcher SSC Guru Points: 101201 More actions · Answer 2

We're seeing only a partial plan with none of the actual stats and no sql query text. I can't offer any real guidance with such limited info.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

devbyalan SSC Veteran Points: 269 More actions · Answer 3

Jonathan AC Roberts wrote:

SQL Server caches the execution plan of queries, it may recompile a query a different way if it is executed at different times.
If there is any difference in the text between the two queries, e.g. a new line or an extra space, it will not use the plan in the cache but recompile it, maybe with a different execution plan.
You should clear the query cache and try again:
CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

Hi,

This is an production enviroment.

devbyalan SSC Veteran Points: 269 More actions · Answer 4

ScottPletcher wrote:

We're seeing only a partial plan with none of the actual stats and no sql query text. I can't offer any real guidance with such limited info.

Sorry, attached anonymized execution plan.

This reply was modified 5 years, 11 months ago by devbyalan.
This reply was modified 5 years, 11 months ago by devbyalan.

Attachments:

You must be logged in to view attached files.