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:
>> 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:
>>
>>> 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 Karaszi, SQL Server MVP
>>>
http://www.karaszi.com/sqlserver/default.asp
>>>
http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Russell Fields"
NoMail.com> wrote in message
>>> news:O0PAgIXCJHA.1224@TK2MSFTNGP02.phx.gbl...>>>> Erland,
>>>>
>>>> Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this
>>>> code
>>>> is running in a
>>>> stored procedure". (Then I recommended that it should be in a stored procedure.)
>>>>
>>>> However, I appreciate your comments on the reuse. What you said was what I first planned to
>>>> say,
>>>> but then I read the Books Online which implies that the batch is indeed reused, so I held back
>>>> from saying that.
>>>>
>>>>
http://msdn.microsoft.com/en-us/library/ms188001.aspx "Being able to substitute parameters in
>>>> sp_executesql offers the following ... the query optimizer will probably match the Transact-SQL
>>>> statement in the second execution with the execution plan generated for the first execution.
>>>> Therefore, SQL Server does not have to compile the second statement."
>>>>
>>>> If that is true, Mike's code would benefit from reuse, but only if he fully specified the table
>>>> name. (Because the Books Online also comment "If object names in the statement string are not
>>>> fully qualified, the execution plan is not reused.")
>>>>
>>>> So, are the Books Online comments incorrect? Or did I just misunderstand them?
>>>>
>>>> RLF
>>>>
>>>>
>>>> "Erland Sommarskog"
wrote in message
>>>> news:Xns9B09349D5FDDYazorman@127.0.0.1...>>>>> Mike (mssql@nospam.nospam) writes:
>>>>>> 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'
>>>>>>
>>>>>> Query 2
>>>>>> DECLARE @P1 varchar(32)
>>>>>> DECLARE @P2 varchar(32)
>>>>>> SET @P1 = 'Poedunk'
>>>>>> SET @P2 = 'Iowa'
>>>>>> SELECT FirstName, LastName, Company, StreetAddress, City, State, ZIP5,
>>>>>> ZIP4
>>>>>> FROM Customers
>>>>>> WHERE City = @P1
>>>>>> AND State = @P2
>>>>
>>>>
>>>
>>>
>
>
>
