2 deleted 4 characters in body
source | link

turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache

...

Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.

I wouldn't call removing 79% of the AdHoc plans a slight reduction.

In which cases are non-parameterized, non-trivial, Adhoc query plans reused?

When the exact same query, with the same session settings is run after the plan is in the cache by a client connected to the same database.

So,

Why are there multiple cached query plans for identical statements?

Typically it's sessions with different settings that affect query behavior. Any text differences in the query, including whitespace, can cause this. The user's default schema can cause this, as object name resolution differs. Also, two identical queries are submitted at nearly the same time may get independently optimized and cached.

turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache

...

Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.

I wouldn't call removing 79% of the AdHoc plans a slight reduction.

In which cases are non-parameterized, non-trivial, Adhoc query plans reused?

When the exact same query, with the same session settings is run after the plan is in the cache by a client connected to the same database.

So,

Why are there multiple cached query plans for identical statements?

Typically it's sessions with different settings that affect query behavior. Any text differences in the query, including whitespace, can cause this. The user's default schema can cause this, as object name resolution differs. Also, two identical queries are submitted at nearly the same time may get independently optimized and cached.

turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache

...

Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.

I wouldn't call removing 79% of the AdHoc plans a slight reduction.

In which cases are non-parameterized, non-trivial, Adhoc query plans reused?

When the exact same query, with the same session settings is run after the plan is in the cache by a client connected to the same database.

So,

Why are there multiple cached query plans for identical statements?

Typically it's sessions with different settings that affect query behavior. Any text differences in the query, including whitespace, can cause this. The user's default schema can cause this, as object name resolution differs. Also, two identical queries submitted at nearly the same time may get independently optimized and cached.

1
source | link

turning on optimize for ad hoc workloads will result in slight reduction in the size of the query plans in the cache

...

Out of the 158'997 adhoc queries in the plan cache, 126'087 have only been executed once.

I wouldn't call removing 79% of the AdHoc plans a slight reduction.

In which cases are non-parameterized, non-trivial, Adhoc query plans reused?

When the exact same query, with the same session settings is run after the plan is in the cache by a client connected to the same database.

So,

Why are there multiple cached query plans for identical statements?

Typically it's sessions with different settings that affect query behavior. Any text differences in the query, including whitespace, can cause this. The user's default schema can cause this, as object name resolution differs. Also, two identical queries are submitted at nearly the same time may get independently optimized and cached.