Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 379

The optimizer goes through the statistical data to get a determination of values of local
variables used that will generate a query plan instead of following the specific parameter
values that were passed in the initial SELECT query.
Using the sys.dm_exec_cached_plans DMV, the information on the
compile plan stubs can be obtained; the cacheobjtypes is the key column.
Without the hint, the original query generates a cached query plan, which can be reused for
the number of consecutive executions. By using the RECOMPILE hint, we have overridden the
default optimizer behavior by forcing the query to compile each time when it is executed; that
removes the requirement of storing query plans in cache.
It is essential to highlight the importance of table/query hints and understand how query
plans can help to accomplish optimized performance. SQL Server 2008 (and higher)
acknowledges that the TABLE HINT clause is introduced to allow the table-level hints as
query hints in the OPTION clause of the queries. This feature is beneficial to provide the
flexibility for a set of hints using a plan guide feature where the mere choice is to apply a
query hint.
The plan guides feature is intended to lock down the supportive plans for all repeated queries
against the databases, as opposed to a few queries alone. The inner-working for plan guides
is: if the server handles a well-defined query and a repeated set of parameterized queries are
executed then plan freezing will allow the DBA to pick a schedule when the system is running
on expected levels. This will ensure that any subsequent compiles or recompiles do not pick a
different (inefficient) plan to mix any unpredictability into the data platform performance.
Designing sustainable locking methods to
improve concurrency
Every time a query is executed, the standard SQL defines four types of actions to maintain
the concurrent transactions. They are dirty reads, non-repeatable reads, phantom reads, and
lost updates. By default, SQL Server is efficient enough to use default locking mechanism
behavior to control the task completion for simultaneous transactions. The locking behavior
controls access to database resources and imposes a certain level of transactional isolation
levels. The different SQL Server isolation levels are: READ COMMITTED , READ UNCOMMITTED ,
REPEATABLE READ , SERIALIZABLE , and SNAPSHOT —which is a SQL Server-specific
isolation level implementation. In this recipe, we will go through the required steps in
designing sustainable locking methods to improve concurrency on the data platform. By
default, SQL Server allows using table hints for locking in SELECT , MERGE , UPDATE , INSERT ,
and DELETE statements to override the isolation level currently set at the session level.
The default determination of locking is SHARED , UPDATE , and EXCLUSIVE on a resource
depending upon the type of statement executed.