Enable Optimize for Ad Hoc Workloads option

The TSQL code enables the Optimize for Ad Hoc Workloads option by changing the setting to 1.

Procedure 

1. Open the SQL Server Management Studio and connect to the database.

2. Click New and select Query with Current Connection.

3. Paste the following TSQL code into the new query window:

   sp_configure 'show advanced options', 1

   go

   reconfigure

   go

   sp_configure 'optimize for ad hoc workloads', 1

   go

   reconfigure

   go

4. Click Execute.

   The query results panel displays a message indicating that the option was configured successfully, as in the following message:

   Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

   Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.