SQL Server - Database Engine Tuning Advisor suggestion to replace syntax.

Asked By mssq

28-Aug-08 12:20 PM

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

28-Aug-08 02:27 PM

Mike,

Yes, they both optimize for reuse, but the first choice is dynamic SQL and
the second choice is executable TSQL.

This has an impact on security.  For example, if this code is running in a
stored procedure, the user needs EXECUTE rights to the stored procedure.
However, Query 1 requires the user to have SELECT rights on the Customers
table, but Query2 does not need these extra rights, since the stored
procedure permission has the needed rights to do the SELECT for the user.
(And a best practice is (IMHO) to create stored procedures for all such
accesses.

Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the
procedure is created.

Generally speaking, it is better to avoid dynamic SQL, but there are time
when it is the only choice.  See:
http://www.sommarskog.se/dynamic_sql.html

RLF

28-Aug-08 04:48 PM

Russell,

Thanks for the input. I understand what your says and that is part of the
reason I posted this question. This seems more of  security issue than
performance.

Mike.

28-Aug-08 06:19 PM

Mike (mssql@nospam.nospam) writes:

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@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

28-Aug-08 08:56 PM

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

29-Aug-08 03:53 AM

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
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

29-Aug-08 01:09 PM

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

29-Aug-08 06:42 PM

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

30-Aug-08 08:22 AM

Thanks, Erland - RLF

02-Sep-08 12:06 PM

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.

02-Sep-08 01:08 PM

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

02-Sep-08 04:17 PM

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.

04-Sep-08 04:59 PM

Can Microsoft comment on this please?

Mike.

23-Sep-08 01:31 PM

It appears that Microsoft doesn’t want to answer this. I can’t see that we
have an answer that everyone is happy with. So much for the MSDN subscription
Managed Newsgroups.

23-Sep-08 03:25 PM

In order for Microsoft personnel to recognize you as a valid MSDN subscriber
that is eligible for managed newsgroup support, you must use the same e-mail
address that is registered with your subscriber account.  My guess is that
it isn't mssql@nospam.nospam.  Did you register this as a valid no-spam
alias through the managed newsgroup portal, and connect it to your real
e-mail address that is associated with your MSDN subscription?  Can you be
sure that someone before you (whose subscription might have expired) didn't
use the same address?

Note from http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx:

If you post with any other e-mail address, we cannot guarantee a response
from the community or a Microsoft Support Engineer within two business days.

01-Oct-08 04:33 PM

My alias keeps reverting back to one setup on an expired subscription only
when I post the first message in a thread. I check the profile and it was
correct, then I checked the profile information by clicking my name in this
thread and it changed again.

What a system.

I guess I should post another message and hopefully Microsoft will pick it up.

Mike.

19-Apr-10 06:12 AM

When a sql statement is submitted to sql server, it is first parsed by sql parser which verifies the syntax and converts the query into relational algebric expressions. Then the query optmizer constructs the execution plan based on several rules and cost of executing the query. Once the execution plan is generated, action switches to the storage engine where query is actually executed, according to the plan.



There are two distinct types of execution plan. The plan that outputs from optimizer is known as estimated execution plan and the other plan represents the output from actual query execution.It is expensive for the Server to generate execution plans so SQL Server will keep and reuse plans wherever possible .As they are created, plans are stored in a section of memory called the plan cache.



The optimizer compares this estimated plan to actual execution plans that already exists in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it's already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries.



http://www.mindfiresolutions.com/How-to-reuse-query-execution-plan-116.php