Home » Microsoft » 70-462 v.2 » Which three Transact-SQL statements should you use?
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 statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Select and Place:
Correct Answer:
Explanation/Reference:
Explanation:
Box 1: EXEC sp_configure ‘show advanced options’, 1; RECONFIGURE
Box 2: sp_CONFIGURE ‘optimize for ad hoc workloads’,1
Box 3: RECONFIGURE
SQL SERVER – 2008 – Optimize for Ad hoc Workloads – Advance Performance Optimization
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason a large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
Let us now enable the option of optimizing ad hoc workload. This feature is available in all the versions of SQL Server 2008. sp_CONFIGURE ‘show advanced options’,1
RECONFIGURE
GO sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO
SQL Server 2008 has feature of optimizing ad hoc workloads.
References: https://blog.sqlauthority.com/2009/03/21/sql-server-2008-optimize-for-ad-hoc-workloads-advance-performance-optimization/