You work as a database administrator at ABC.com. ABC.com has a SQL Server 2012 database
infrastructure that contains a database named SalesDB.
The SalesDB database is used by an in-house application named ABCApp3. ABC.com users
report that ABCApp3 is functioning sluggishly.
You discover that application consumes considerable memory when it runs single-use dynamic
queries against the SalesDB database. You suspect that these queries are making excessive use
of procedure cache.
How would you reduce procedure cache if you cannot create new indexes on the SalesDB
database?
A.
You should replace the queries with recursive stored procedures.
B.
You should add make use of the INCLUDE clause in the index.
C.
You should make use of the READPAST hint in the queries.
D.
You should make use of the READCOMMITTED hint in the queries.
E.
You should make use of the optimize for ad hoc workloads option.
Explanation:
D
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option