tag:blogger.com,1999:blog-4752849182353473259.post6175714024721293287..comments2024-03-28T22:56:33.554-07:00Comments on James' SQL Footprint: Ad hoc query optimization in SQL ServerAnonymoushttp://www.blogger.com/profile/12598141235217155685noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-4752849182353473259.post-86330178263972252672013-09-16T04:29:17.794-07:002013-09-16T04:29:17.794-07:00excellent article indeed!! thanks
ad queryexcellent article indeed!! thanks<br /><br /><br /><br /><a href="http://www.corporate-directory.net/blog/active-directory/ad-query-tools-network-administration" rel="nofollow">ad query</a>Anonymoushttps://www.blogger.com/profile/04178972811403632475noreply@blogger.comtag:blogger.com,1999:blog-4752849182353473259.post-13077002382042028862012-06-28T16:04:42.757-07:002012-06-28T16:04:42.757-07:00Hi Claude, you are correct. 'optimize for ad h...Hi Claude, you are correct. 'optimize for ad hoc workloads' can relieve the memory pressure, but adhoc queries need to be complied 2 times before their full compiled plan are cached. <br /><br />As to "Forced Parameterization", SQL Book Online says:<br /><br />"Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Therefore, the query optimizer might choose suboptimal plans for queries. In particular, the query optimizer is less likely to match the query to an indexed view or an index on a computed column. "<br /><br />"Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance."Anonymoushttps://www.blogger.com/profile/12598141235217155685noreply@blogger.comtag:blogger.com,1999:blog-4752849182353473259.post-68615963405161774592012-06-27T22:54:52.735-07:002012-06-27T22:54:52.735-07:00Thank you for this excellent article. I have read ...Thank you for this excellent article. I have read some other stuff basically recommending to turn on adhoc optimization regardless. However, after running your query against my SP2010 environment, only 20MB (of 5000MB) was actually in use by single use query plans. From reading your article I get the sense that there might in fact be a slight negative impact to performance from having this optimization turned on in such cases. What is your view?Claudehttps://www.blogger.com/profile/10074857253830228919noreply@blogger.com