Home > Performence Tuning > Performance boost up by Optimize for ad hoc workloads

Performance boost up by Optimize for ad hoc workloads


Performance boost up by Optimize for ad hoc workloads

I have faced a serious issue where the sales calculation is taking more than 7 hours whereas as per IBM it should be completed within 3 hours. The client is using Varicent sales application ( IBM software)to make the sales report. We are using 64 GB RAM and 8 core processor but still sql server faced the memory pressure. I am start to investigate the memory pressure and observed the cache memory issue. As per the application tendency it is using the lots ad hoc query. I am resolve the issue after enable the optimize for ad hoc workloads and time is comes down to 2 hours 30 minute. I have mentioned the details of optimize for ad hoc workloads option so we can determine how is it help us to improve the performance.

In SQL Server 2008 and later, we can use optimize for ad hoc workloads, which is a server-level setting, i.e. it will affect every single database on the server (as opposed to forced parameterization, which is database-specific).

With this setting enabled, SQL Server does not cache the plan of an ad hoc query the first time it is encountered. Instead, a plan-stub is cached that just indicates that the query has been seen before. Only if the query is seen a second time is the plan cached. This won’t reduce the number of compiles for ad hoc queries, but it will make it less likely that the plan cache will grow as much, since the initial stub takes up very little memory. As such,
it reduces the chances that other plans which could be reusable will be discarded due to memory pressure.

To enable the optimize for ad hoc workloads setting, use sp_configure, as shown

EXEC sp_configure ‘show advanced options’,1
RECONFIGURE
EXEC sp_configure ‘optimize for ad hoc workloads’,1
RECONFIGURE

Advertisements
Categories: Performence Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: