 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / SQL Server Tools / October 2008Database 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 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 > 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 [quoted text clipped - 23 lines] > WHERE City = @P1 > AND State = @P2 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. > Mike, > [quoted text clipped - 45 lines] > > WHERE City = @P1 > > AND State = @P2 > 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 [quoted text clipped - 21 lines] > 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.  SignatureErland 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 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 >> 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 [quoted text clipped - 30 lines] > values, it will not "sniff" the parameters on the first invocation but > make some standard assumption. 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? SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Erland, > [quoted text clipped - 53 lines] >> values, it will not "sniff" the parameters on the first invocation but >> make some standard assumption. Tibor, Quite right. I was confused and added to the confustion. Sorry about that. - RLF > Russell, Erland, Mike, > [quoted text clipped - 90 lines] >>> values, it will not "sniff" the parameters on the first invocation but >>> make some standard assumption. 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. > Russell, Erland, Mike, > [quoted text clipped - 80 lines] > >> values, it will not "sniff" the parameters on the first invocation but > >> make some standard assumption. Mike, Then in my opinion DTE is making some very bold assumptions about your situation. Check my blog post for elaboration... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi > Tibor, > [quoted text clipped - 94 lines] >> >> values, it will not "sniff" the parameters on the first invocation but >> >> make some standard assumption. 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. > Mike, > [quoted text clipped - 99 lines] > >> >> values, it will not "sniff" the parameters on the first invocation but > >> >> make some standard assumption. > 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.") 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. SignatureErland 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 Thanks, Erland - RLF >> 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 [quoted text clipped - 4 lines] > same default schema. But best practice is to use two-part notation with > sp_executesql. Can Microsoft comment on this please? Mike. > 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 [quoted text clipped - 20 lines] > WHERE City = @P1 > AND State = @P2 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. > Can Microsoft comment on this please? > [quoted text clipped - 24 lines] > > WHERE City = @P1 > > AND State = @P2 > 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. 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. 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. > > 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 [quoted text clipped - 13 lines] > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.