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
mssq wrote:
Database Engine Tuning Advisor suggestion to replace syntax.
28-Aug-08
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
Previous Posts In This Thread:
On Thursday, August 28, 2008 12:20 PM
mssq wrote:
Database Engine Tuning Advisor suggestion to replace syntax.
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
On Thursday, August 28, 2008 2:27 PM
Russell Fields wrote:
Mike,Yes, they both optimize for reuse, but the first choice is dynamic SQL
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
"Mike" wrote in message
On Thursday, August 28, 2008 4:48 PM
mssq wrote:
Russell,Thanks for the input.
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.
"Russell Fields" wrote:
On Thursday, August 28, 2008 6:19 PM
Erland Sommarskog wrote:
Re: Database Engine Tuning Advisor suggestion to replace syntax.
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.DeleteThis@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
On Thursday, August 28, 2008 8:56 PM
Russell Fields wrote:
Erland,Actually, what I said was "IF this code is running in a stored
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
On Friday, August 29, 2008 3:53 AM
Tibor Karaszi wrote:
Russell, Erland, Mike,Wow, I've been reading this thread three times now and
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-pl...re-use-
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" wrote in message
On Friday, August 29, 2008 1:09 PM
Russell Fields wrote:
Tibor, Quite right. I was confused and added to the confustion.
Tibor, Quite right. I was confused and added to the confustion. Sorry
about that. - RLF
On Friday, August 29, 2008 6:42 PM
Erland Sommarskog wrote:
Re: Database Engine Tuning Advisor suggestion to replace syntax.
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.DeleteThis@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
On Saturday, August 30, 2008 8:22 AM
Russell Fields wrote:
Re: Database Engine Tuning Advisor suggestion to replace syntax.
Thanks, Erland - RLF
On Tuesday, September 02, 2008 12:06 PM
mssq wrote:
Tibor,DTE is suggesting that I replace the logic in query 1 with that in Query
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:
On Tuesday, September 02, 2008 1:08 PM
Tibor Karaszi wrote:
Mike,Then in my opinion DTE is making some very bold assumptions about your
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
On Tuesday, September 02, 2008 4:17 PM
mssq wrote:
Tibor,I was under the same impression when I first ran it with a small sample
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:
On Thursday, September 04, 2008 4:59 PM
mssq wrote:
RE: Database Engine Tuning Advisor suggestion to replace syntax.
Can Microsoft comment on this please?
Mike.
"Mike" wrote:
On Tuesday, September 23, 2008 1:31 PM
mssq wrote:
It appears that Microsoft doesn???t want to answer this.
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.
"Mike" wrote:
On Tuesday, September 23, 2008 3:25 PM
Aaron Bertrand [SQL Server MVP] wrote:
In order for Microsoft personnel to recognize you as a valid MSDN
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.DeleteThis@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.
On Wednesday, October 01, 2008 4:33 PM
realto wrote:
My alias keeps reverting back to one setup on an expired subscription only
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.
"Aaron Bertrand [SQL Server MVP]" wrote:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Book Review: C# 4.0 In a Nutshell [O'Reilly]
http://www.eggheadcafe.com/tutorials/aspnet/6dc05c04-c7f9-40cc-a2da-88...2e6d891 >> Stay informed about: Database Engine Tuning Advisor suggestion to replace syntax. 
FAQ
Search
Profile
Private Messages
Log in
