SQL Server 2005 Database Engine Tuning Advisor informs me that I should
replace query 1 below with the syntax in query 2 below. I’m just not seeing
the reason since both the “SELECT” statements optimization plans are stored
for reuse. Am I missing something?

Mike.

Query 1
EXEC sp_executesql N'SELECT FirstName, LastName, Company, StreetAddress,
City, State, ZIP5, ZIP4
FROM Customers
WHERE City = @P1
AND State = @P2',
N'@P1 char(32), @P2 varchar(32)', 'Poedunk', 'Iowa'

Russell, Erland, Mike,

Wow, I've been reading this thread three times now and I'm still confused. Seems like something is
messing with my head and twist some things in the opposite direction...

Going back to Mike's original post:

Mike,
Are you saying that DTE suggest instead of sp_executesql version use static SQL with variables
instead? Just so I understand. There are important differences between the two.

For the sp_executesql alternative, the parameter can be sniffed and used to determine things like
selectivity and also plan can be re-used. This can be a good thing or a bad thing.

For the TSQL variable alternative, the optimizer has no knowledge of the contents of the variables
so selectivity can not be determined based on those values. This can be a good thing or a bad thing.

Which one is best? I don't know and most probably DTA doesn't know either. If you want to read more
about the technicalities and differences between the two alternatives, check out this blog I just
wrote:
http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/29/execution-plan-re-use-sp...

Russell,

I don't see a contradiction between that BOL quote and Erland's post. Erland's remark was about the
TSQL variable alternative, not the sp_executesql alternative. Perhaps that confused you?

Tibor, Quite right. I was confused and added to the confustion. Sorry
about that. - RLF

"Tibor Karaszi" hotmail.nomail.com> wrote in
message news:%%23JGNaxaCJHA.4724@TK2MSFTNGP05.phx.gbl...

Russell Fields (RussellFields@NoMail.com) writes:

Which is not fully correct. The plan is reused, if the next guy has the
same default schema. But best practice is to use two-part notation with
sp_executesql.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks, Erland - RLF

"Erland Sommarskog" wrote in message
news:Xns9B0A7496817BYazorman@127.0.0.1...

Tibor,

DTE is suggesting that I replace the logic in query 1 with that in Query 2.
Sorry for the delay, I been out of town.

Mike.

"Tibor Karaszi" wrote:

Mike,

Then in my opinion DTE is making some very bold assumptions about your situation. Check my blog post
for elaboration...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Mike" wrote in message
news:50B8B307-B78B-457D-B9AC-13BFD535086D@microsoft.com...

Tibor,

I was under the same impression when I first ran it with a small sample set
of trace data. I ran it again over the long weekend with a much larger set of
tract data and all, yes "ALL", of the recommendations are suggesting that I
replace all of my dynamic SQL running via sp_executesql with the same type
query in my query 2 example.

Mike.

"Tibor Karaszi" wrote:

Can Microsoft comment on this please?

Mike.

"Mike" wrote: