SQL Server caches the execution plans for queries in an area of memory called the Newtablink |
---|
alias | plan cache. url | http://technet.microsoft.com/en-us/library/cc293624.aspx |
When a server executes a large number of single-use queries, which can be a common scenario for certain applications, the space used caching these various execution plans can grow quite large, possibly impacting server performance for very little benefit.The server configuration Newtablink |
---|
alias | "optimize for ad hoc workloads"url | http://technet.microsoft.com/en-us/library/cc645587.aspx |
changes changes the behavior of the instance such that the first time a statement is run, a very small version of the plan called a stub is cached, and it is only once the query is executed a second time that the full execution plan is stored. While there are special cases where this option may have a negative impact, for most environments turning this option on will have a positive or neutral effect on the server.
Need more help? Search the Idera Customer Support Portal.