Home » Microsoft » 70-462 v.2 » Which three Transact-SQL segments should you use to develop the solution?
DRAG DROP
You administer a Microsoft SQL Server instance.
An application executes a large volume of dynamic queries.
You need to reduce the amount of memory used for cached query plans.
Which three Transact-SQL segments should you use to develop the solution? (To answer, move the appropriate Transact-SQL segments from the list Transact-SQL segments to the answer area and arrange them in the correct order.) Select and Place:
Select and Place:
Correct Answer:
Explanation/Reference:
Explanation:
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 this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
To do it with SP_Configure:
— if you haven’t looked at advanced options before in SP_CONFIGURE
SP_CONFIGURE ‘Show Advanced Options’, 1
GO
— To make that online setting take effect
RECONFIGURE
GO
— Change Optimize for Ad Hoc Workload Setting to 1 – or enabled
SP_CONFIGURE ‘optimize for ad hoc workloads’, 1
GO
RECONFIGURE
GO
Note: Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. To affect already cached query plans immediately, the plan cache needs to be cleared. DBCC FREESYSTEMCACHE releases all unused cache entries from all caches.
References:
https://blogs.technet.microsoft.com/josebda/2009/03/19/sql-server-2008-optimize-for-ad-hoc-workloads/
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freesystemcache-transact-sql?view=sql-server-2017