Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 74

NOTE Having read what LPIM was introduced to i x, it's also worth noting that
a side-effect of using locked pages is that they require slightly less overhead to
manage (because they can't be moved). This can translate into a real performance
benei t on large scale, high-throughput SQL Servers. So it's dei nitely worth
testing on your most performance sensitive servers to see if it helps.
If LPIM is working, you'll see the following message in the SQL Server Error Log:
Using Locked Pages in the Memory Manager.
You can read about Microsoft support for this feature and how to enable it at
http://support.microsoft.com/kb/2659143 .
Optimize for Ad-Hoc Workloads
Every time an execution plan (see Chapter 5) is generated, it is stored in the plan cache in the hope
that it can be reused — this is one of the efi cient ways that SQL Server manages its workload.
If an execution plan is never reused, then it's just taking up resources unnecessarily; and the use of
unparameterized ad-hoc T-SQL is the most likely cause.
When you execute code in SQL Server, it generates a hash value of your code and uses that to
determine plan reuse. If you execute a stored procedure, a hash value is generated from the stored
procedure name, and the plan will be reused on each subsequent procedure call regardless of the
parameter values used.
If you run the same code outside of a stored procedure ( ad-hoc T-SQL ), the hash is taken on the
whole statement, including any literal values. When you then change the literal values for another
execution, the hash is different, so SQL Server doesn't i nd a match and generates a new execution
plan instead of reusing the previous one.
This situation can lead to a scenario called plan cache bloat , whereby potentially thousands of
ad-hoc plans are generated and cached with a usecount of 1 even though the code is fundamentally
the same.
The ideal solution is to use stored procedures or functions, or to parameterize all your ad-hoc
T-SQL; but this can be very challenging, and often unachievable due to complexity and company
politics, so Microsoft introduced the Optimize for Ad-hoc Workloads server-level option in SQL
Server 2008 to help.
When this option is enabled, SQL Server will cache only a plan stub the i rst time a piece of ad-hoc
T-SQL is executed, rather than the full plan. If SQL Server subsequently tries to reuse that plan, it
will be generated again but this time cached in full. This avoids the scenario of thousands of
single-use plans taking up valuable space in cache.