2 added 1019 characters in body
source | link

I'll try to answer with an analogy. Think of SQL Server as an automobile.

Consider the 'Optimize for Ad Hoc Workloads' setting as a sophisticated automatic gear box. When it notices off-road uphill, it sets to a different gear than straight highway. The passengers enjoy a smooth journey, no matter what terrain the automobile goes through.

But how does that explain the different behaviour of the same query in similar servers, when the setting isn't there?

In that case, the gear boxes are still automatic but not so sophisticated. They notice and differentiate between terrains, so (in automobile A) the first time it sees a specific terrain, say off-road uphill, it sets a specific gear. Problem is, they can miss certain details. The next time it sees a similar terrain (say off-road downhill, or highway just a bit uphill), it still uses the same gear as the first time. And the passengers complain because the gear is not the best.
The second automobile (B) started on a different route, first on the highway, then off-road, so the gear box made slightly different decisions the first time it met similar (to those from A) terrains. Luckily things went smoothly and passengers haven't complained. This could change of course, if the terrain after next turn, needs a different gear than the one already used for a similar one.

Terminology explained:

              automobile : SQL Server
"adhoc workload" setting : automatic gear box
                 terrain : query
         terrain details : parameters, table statistics, workload
                    gear : query plan
              passengers : users, developers, DBAs

Some more notes:

  • The analogy is not perfect of course. There are many details on how plans are saved and reused (or not) with the "adhoc workloads" setting (on or off).

  • The setting is not a magic button to solve all problems. While it is helpful in many cases, there are reasons why SQL Server has it set to OFF by default. I'm sure there are many cases where it won't make any difference at all or even degrades performance.

  • For the specific problem, I can't say I'm sure that the above description was the cause. It's just one possible explanation that seems plausible (because changing this setting fixed it). There might have been other reasons for the different behaviour (eg. some other setting you missed between production and test server) or other services/programs running in the two environments. It's practically impossible to have 2 identical setups.

  • If you could provide the query and (similar) plans, there are other users in the site that can show more light into the issue.

I'll try to answer with an analogy. Think of SQL Server as an automobile.

Consider the 'Optimize for Ad Hoc Workloads' setting as a sophisticated automatic gear box. When it notices off-road uphill, it sets to a different gear than straight highway. The passengers enjoy a smooth journey, no matter what terrain the automobile goes through.

But how does that explain the different behaviour of the same query in similar servers, when the setting isn't there?

In that case, the gear boxes are still automatic but not so sophisticated. They notice and differentiate between terrains, so (in automobile A) the first time it sees a specific terrain, say off-road uphill, it sets a specific gear. Problem is, they can miss certain details. The next time it sees a similar terrain (say off-road downhill, or highway just a bit uphill), it still uses the same gear as the first time. And the passengers complain because the gear is not the best.
The second automobile (B) started on a different route, first on the highway, then off-road, so the gear box made slightly different decisions the first time it met similar (to those from A) terrains. Luckily things went smoothly and passengers haven't complained. This could change of course, if the terrain after next turn, needs a different gear than the one already used for a similar one.

Terminology explained:

              automobile : SQL Server
"adhoc workload" setting : automatic gear box
                 terrain : query
         terrain details : parameters, table statistics, workload
                    gear : query plan
              passengers : users, developers, DBAs

I'll try to answer with an analogy. Think of SQL Server as an automobile.

Consider the 'Optimize for Ad Hoc Workloads' setting as a sophisticated automatic gear box. When it notices off-road uphill, it sets to a different gear than straight highway. The passengers enjoy a smooth journey, no matter what terrain the automobile goes through.

But how does that explain the different behaviour of the same query in similar servers, when the setting isn't there?

In that case, the gear boxes are still automatic but not so sophisticated. They notice and differentiate between terrains, so (in automobile A) the first time it sees a specific terrain, say off-road uphill, it sets a specific gear. Problem is, they can miss certain details. The next time it sees a similar terrain (say off-road downhill, or highway just a bit uphill), it still uses the same gear as the first time. And the passengers complain because the gear is not the best.
The second automobile (B) started on a different route, first on the highway, then off-road, so the gear box made slightly different decisions the first time it met similar (to those from A) terrains. Luckily things went smoothly and passengers haven't complained. This could change of course, if the terrain after next turn, needs a different gear than the one already used for a similar one.

Terminology explained:

              automobile : SQL Server
"adhoc workload" setting : automatic gear box
                 terrain : query
         terrain details : parameters, table statistics, workload
                    gear : query plan
              passengers : users, developers, DBAs

Some more notes:

  • The analogy is not perfect of course. There are many details on how plans are saved and reused (or not) with the "adhoc workloads" setting (on or off).

  • The setting is not a magic button to solve all problems. While it is helpful in many cases, there are reasons why SQL Server has it set to OFF by default. I'm sure there are many cases where it won't make any difference at all or even degrades performance.

  • For the specific problem, I can't say I'm sure that the above description was the cause. It's just one possible explanation that seems plausible (because changing this setting fixed it). There might have been other reasons for the different behaviour (eg. some other setting you missed between production and test server) or other services/programs running in the two environments. It's practically impossible to have 2 identical setups.

  • If you could provide the query and (similar) plans, there are other users in the site that can show more light into the issue.

1
source | link

I'll try to answer with an analogy. Think of SQL Server as an automobile.

Consider the 'Optimize for Ad Hoc Workloads' setting as a sophisticated automatic gear box. When it notices off-road uphill, it sets to a different gear than straight highway. The passengers enjoy a smooth journey, no matter what terrain the automobile goes through.

But how does that explain the different behaviour of the same query in similar servers, when the setting isn't there?

In that case, the gear boxes are still automatic but not so sophisticated. They notice and differentiate between terrains, so (in automobile A) the first time it sees a specific terrain, say off-road uphill, it sets a specific gear. Problem is, they can miss certain details. The next time it sees a similar terrain (say off-road downhill, or highway just a bit uphill), it still uses the same gear as the first time. And the passengers complain because the gear is not the best.
The second automobile (B) started on a different route, first on the highway, then off-road, so the gear box made slightly different decisions the first time it met similar (to those from A) terrains. Luckily things went smoothly and passengers haven't complained. This could change of course, if the terrain after next turn, needs a different gear than the one already used for a similar one.

Terminology explained:

              automobile : SQL Server
"adhoc workload" setting : automatic gear box
                 terrain : query
         terrain details : parameters, table statistics, workload
                    gear : query plan
              passengers : users, developers, DBAs