Re: Database Engine Tuning Advisor suggestion to replace syntax.

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




.

Relevant Pages

  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... query in my query 2 example. ... Tibor Karaszi, SQL Server MVP ... Actually, what I said was "IF this code is running in a stored procedure", not "BECAUSE this ... statement in the second execution with the execution plan generated for the first execution. ...
    (microsoft.public.sqlserver.tools)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Very slow query
    ... with lots if information in Books Online, Books, Courses, the Net etc. ... While testing the query in QA, use the "Show Estimated Query Plan" feature. ... This is what I suggest you also pick up using Profiler. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • RE: sql performance
    ... You should look at the query execution plan, ... found in books online and in "Inside SQL Server 2000" by Kalen Delaney ISBN ... Overall performance tuning information can be found in the "SQL Server 2000 ...
    (microsoft.public.sqlserver.programming)