You are developing a database application by using Microsoft SQL Server 2012. An application that usesa
database begins to run slowly. You discover that a large amount of memory is consumed by single-use
dynamic queries. You need to reduce procedure cacheusage from these statements without creating any
additional indexes. What should you do?
A.
Add a HASH hint to the query.
B.
Add a LOOP hint to the query.
C.
Add a FORCESEEK hint to the query.
D.
Add an INCLUDE clause to the index.
E.
Add a FORCESCAN hint to the Attach query.
F.
Add a columnstore index to cover the query.
G.
Enable the optimize for ad hoc workloads option.
H.
Cover the unique clustered index with a columnstore index.
I.
Include a SET FORCEPLAN ON statement before you run the query.
J.
Include a SET STATISTICS PROFILE ON statement before you run the query.
K.
Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L.
Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the
query.
M.
Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N.
Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/cc645587.aspx
G.
http://stackoverflow.com/questions/32251623/how-to-reduce-procedure-cacheusage-from-these-statements-without-creating-any-ad?noredirect=1#comment52385183_32251623