|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Interesting QuestionFrom another group:
"I'm reading some articles that objects should always be referred to in exactly the same case, i.e., using dbo.TABLE won't re-use the plan for dbo.Table. I don't thing that is the case for SQL, but I realized that I'm not sure. Any thoughts or 'nitty-gritty' information? Suggestions about how to demonstrate? -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous It is mostly the case with cache reuse, esp. with auto-parameterization. In
gist of the matter is that, cache is case-sensitive and requires every character to be exactly the same for the plan reuse. This is because during plan reuse, the query text is hashed by SQL Server and the computed hash value is compared with the existing hash in the cache. Obviously hash values could be different if the case of the characters in the query text is altered. Therefore, adding a space in the query text or changing the case could prevent the plan from being reused. I have read this info somewhere, perhaps in the private groups, but cannot remember the source. Hope someone else can provide an official url. -- Anith
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
scroll down to Plan Caching Ad-hoc queries. An ad-hoc query is a batch that contains one SELECT, INSERT, UPDATE, or DELETE statement. SQL Server requires exact text match for two ad-hoc queries. The text match is both case- and space-sensitive. For example, the following two queries do not share the same query plan. (All T-SQL code snippets appearing in this white paper are posed on SQL Server 2005's AdventureWorks database.) SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductId Denis the SQL Menace http://sqlservercode.blogspot.com/ Anith Sen wrote: Show quote > It is mostly the case with cache reuse, esp. with auto-parameterization. In > gist of the matter is that, cache is case-sensitive and requires every > character to be exactly the same for the plan reuse. > > This is because during plan reuse, the query text is hashed by SQL Server > and the computed hash value is compared with the existing hash in the cache. > Obviously hash values could be different if the case of the characters in > the query text is altered. Therefore, adding a space in the query text or > changing the case could prevent the plan from being reused. > > I have read this info somewhere, perhaps in the private groups, but cannot > remember the source. Hope someone else can provide an official url. > > -- > Anith I see Kalen beat me to it, oh well..disregard my previous message
Denis the SQL Menace http://sqlservercode.blogspot.com/ SQL Menace wrote: Show quote > http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx > > scroll down to Plan Caching > > Ad-hoc queries. An ad-hoc query is a batch that contains one SELECT, > INSERT, UPDATE, or DELETE statement. SQL Server requires exact text > match for two ad-hoc queries. The text match is both case- and > space-sensitive. For example, the following two queries do not share > the same query plan. (All T-SQL code snippets appearing in this white > paper are posed on SQL Server 2005's AdventureWorks database.) > > SELECT ProductID > FROM Sales.SalesOrderDetail > GROUP BY ProductID > HAVING AVG(OrderQty) > 5 > ORDER BY ProductID > SELECT ProductID > > FROM Sales.SalesOrderDetail > GROUP BY ProductID > HAVING AVG(OrderQty) > 5 > ORDER BY ProductId > > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > Anith Sen wrote: > > It is mostly the case with cache reuse, esp. with auto-parameterization. In > > gist of the matter is that, cache is case-sensitive and requires every > > character to be exactly the same for the plan reuse. > > > > This is because during plan reuse, the query text is hashed by SQL Server > > and the computed hash value is compared with the existing hash in the cache. > > Obviously hash values could be different if the case of the characters in > > the query text is altered. Therefore, adding a space in the query text or > > changing the case could prevent the plan from being reused. > > > > I have read this info somewhere, perhaps in the private groups, but cannot > > remember the source. Hope someone else can provide an official url. > > > > -- > > Anith True. The mention of auto-parameterized queries in my post was not correct,
should be ad hoc queries. -- Anith > This is because during plan reuse, the query text is hashed by SQL Server Yes, this is covered in the white paper "Batch Compilation, Recompilation, > and the computed hash value is compared with the existing hash in the > cache. Obviously hash values could be different if the case of the > characters in the query text is altered. Therefore, adding a space in the > query text or changing the case could prevent the plan from being reused. > I have read this info somewhere, perhaps in the private groups, but cannot > remember the source. and Plan Caching Issues in SQL Server 2005" http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx. See the Plan Caching section Regards, Gail -- Show quoteGail Erickson [MS] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights Download the latest version of Books Online from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx "Anith Sen" <an***@bizdatasolutions.com> wrote in message news:e0c53qmtGHA.4460@TK2MSFTNGP04.phx.gbl... > It is mostly the case with cache reuse, esp. with auto-parameterization. > In gist of the matter is that, cache is case-sensitive and requires every > character to be exactly the same for the plan reuse. > > This is because during plan reuse, the query text is hashed by SQL Server > and the computed hash value is compared with the existing hash in the > cache. Obviously hash values could be different if the case of the > characters in the query text is altered. Therefore, adding a space in the > query text or changing the case could prevent the plan from being reused. > > I have read this info somewhere, perhaps in the private groups, but cannot > remember the source. Hope someone else can provide an official url. > > -- > Anith > Hi Arnie
This is not completely true, but only true for 'adhoc' query plan reuse for which there must be an exact, character for character match between queries in order for plans to be reused. Autoparameterization builds an internal form of the query, and can recognize queries with different case and different spacing as variations of the same query. This whitepaper can give you a lot more info. It written specifically for SQL 2005, but many of the details (e.g. the difference between adhoc plans and autoparameterized plans) are applicable to SQL 2000. Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx -- HTH Kalen Delaney, SQL Server MVP "Arnie Rowland" <ar***@1568.com> wrote in message From another group:news:uGPG0YmtGHA.3808@TK2MSFTNGP06.phx.gbl... "I'm reading some articles that objects should always be referred to in exactly the same case, i.e., using dbo.TABLE won't re-use the plan for dbo.Table. I don't thing that is the case for SQL, but I realized that I'm not sure. Any thoughts or 'nitty-gritty' information? Suggestions about how to demonstrate? -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Thanks Kalen,
That matches up with my 'fuzzy' recollection -probably something I've heard you say at some PASS meeting. I didn't think that case was an issue for sproc and parameter names -since that is all I use. But in those situations where 'ad hoc' queries are being used out of laziness or unknowingness, it is good to be able to offer one more piece of information arguing against generalized ad hoc query usage. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:%23ypXsvmtGHA.5076@TK2MSFTNGP04.phx.gbl... > Hi Arnie > > This is not completely true, but only true for 'adhoc' query plan reuse > for which there must be an exact, character for character match between > queries in order for plans to be reused. > > Autoparameterization builds an internal form of the query, and can > recognize queries with different case and different spacing as variations > of the same query. > > This whitepaper can give you a lot more info. It written specifically for > SQL 2005, but many of the details (e.g. the difference between adhoc plans > and autoparameterized plans) are applicable to SQL 2000. > > Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server > 2005 > http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx > > -- > HTH > Kalen Delaney, SQL Server MVP > > > "Arnie Rowland" <ar***@1568.com> wrote in message > news:uGPG0YmtGHA.3808@TK2MSFTNGP06.phx.gbl... > From another group: > > "I'm reading some articles that objects should always be referred to in > exactly the same case, i.e., using dbo.TABLE won't re-use the plan for > dbo.Table. > > I don't thing that is the case for SQL, but I realized that I'm not sure. > > Any thoughts or 'nitty-gritty' information? > > Suggestions about how to demonstrate? > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > Absolutely. Reuse of adhoc plans is so unreliable that it is not ever
something you should plan on. It's good when it happens, but if you know what your queries are ahead of time, you should work to make them prepared or to turn them into stored procedures. -- Show quoteHTH Kalen Delaney, SQL Server MVP "Arnie Rowland" <ar***@1568.com> wrote in message news:%23CMCU6mtGHA.2020@TK2MSFTNGP03.phx.gbl... > Thanks Kalen, > > That matches up with my 'fuzzy' recollection -probably something I've > heard you say at some PASS meeting. > > I didn't think that case was an issue for sproc and parameter > names -since that is all I use. > > But in those situations where 'ad hoc' queries are being used out of > laziness or unknowingness, it is good to be able to offer one more piece > of information arguing against generalized ad hoc query usage. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:%23ypXsvmtGHA.5076@TK2MSFTNGP04.phx.gbl... >> Hi Arnie >> >> This is not completely true, but only true for 'adhoc' query plan reuse >> for which there must be an exact, character for character match between >> queries in order for plans to be reused. >> >> Autoparameterization builds an internal form of the query, and can >> recognize queries with different case and different spacing as variations >> of the same query. >> >> This whitepaper can give you a lot more info. It written specifically for >> SQL 2005, but many of the details (e.g. the difference between adhoc >> plans and autoparameterized plans) are applicable to SQL 2000. >> >> Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server >> 2005 >> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> >> >> "Arnie Rowland" <ar***@1568.com> wrote in message >> news:uGPG0YmtGHA.3808@TK2MSFTNGP06.phx.gbl... >> From another group: >> >> "I'm reading some articles that objects should always be referred to in >> exactly the same case, i.e., using dbo.TABLE won't re-use the plan for >> dbo.Table. >> >> I don't thing that is the case for SQL, but I realized that I'm not sure. >> >> Any thoughts or 'nitty-gritty' information? >> >> Suggestions about how to demonstrate? >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> > >  |
|||||||||||||||||||||||
Other interesting topics