Re: Database Engine Tuning Advisor suggestion to replace syntax.
- From: Mike <mssql@xxxxxxxxxxxxx>
- Date: Tue, 2 Sep 2008 13:17:00 -0700
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:
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" <mssql@xxxxxxxxxxxxx> wrote in message
news:50B8B307-B78B-457D-B9AC-13BFD535086D@xxxxxxxxxxxxxxxx
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-executesql-and-tsql-variables.aspx
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" <RussellFields@xxxxxxxxxx> wrote in message
news:O0PAgIXCJHA.1224@xxxxxxxxxxxxxxxxxxxxxxx
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" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9B09349D5FDDYazorman@xxxxxxxxxxxx
Mike (mssql@xxxxxxxxxxxxx) 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
Russell seemed to assumed that query 2 is a stored procedure. I may be
missing something, but I cannot see any SP.
And as a loose query batch, it's a poor choice. If you change the
parameter values, it will be a new query text, and there will be no
cache it. On top of that, since SQL Server does not know the parameter
values, it will not "sniff" the parameters on the first invocation but
make some standard assumption.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
- References:
- Prev by Date: Re: Database Engine Tuning Advisor suggestion to replace syntax.
- Next by Date: Re: SSMS 2008: Ctrl-Shift-M
- Previous by thread: Re: Database Engine Tuning Advisor suggestion to replace syntax.
- Next by thread: RE: Database Engine Tuning Advisor suggestion to replace syntax.
- Index(es):
Relevant Pages
|
Loading
