 | |
#81
 
| |||
| |||
|
Re: Database Engine Tuning Advisor suggestion to replace syntax. -
09-02-2008
, 11:06 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 wrote: http://sqlblog.com/blogs/tibor_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#82
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#83
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#84
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#85
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#86
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#87
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#88
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#89
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
#90
| |||
| |||
|
|
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_karas...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 (AT) NoMail (DOT) com> wrote in message news:O0PAgIXCJHA.1224 (AT) TK2MSFTNGP02 (DOT) phx.gbl... 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 (AT) sommarskog (DOT) se> wrote in message news:Xns9B09349D5FDDYazorman (AT) 127 (DOT) 0.0.1... Mike (mssql (AT) nospam (DOT) nospam) 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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.