During exam preparation I was asked to answer a question that there are a lots of execution plans generated during querying a database and used only once. How can I reduce that and I got following answer
Server Configuration Option (optimize for ad hoc workloads).
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When option is enabled only regularly used queries are cached that are used more than once. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
Let's understand how it works. Run the following text to enable advance option
Instead of show 5 different execution plan. It will comes up with only one plan and user count is 5. It means when this option is enabled same plan will execute again and for rarely used queries that are only used for one time will not be cached.
This option is very good for memory utilization. Microsoft mentioned that only advanced options should be changed only by an experienced database administrator or certified SQL Server technician.