Optimize for Ad Hoc Workloads - SQL Server Configuration Parameter

Play Optimize for Ad Hoc Workloads - SQL Server Configuration Parameter
Sign in to queue

Description

SQL Server configuration parameter "optimized for ad hoc workloads" can be very useful when you execute lots of single use ad hoc statements or dynamic SQL statements by reducing memory usage of plan cache. If the majority of ad hoc statements or dynamic SQL statements execute more than once, then "optimize for ad hoc workloads" configuration parameter can increase the CPU usage due to additional optimization cost and hence it is not recommended in such scenarios.

When the plan is stable, that is, when the query hash and query plan hash is the same for all executions, it is recommended that you parameterize these statements to reduce plan cache pollution.

The below video demonstrates this with hands-on example.SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the Video.

Tag:

SQL Server

Embed

Download

Download this episode

The Discussion

Add Your 2 Cents