SQL Server caches the execution plans for queries in an area of memory called the plan cache. 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 "optimize for ad hoc workloads" 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.