Understanding SQL Server Lock Modes

Shared S - Allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. It is used for READ-ONLY operations.
- The lock is released as soon as the read operation completes (except the case when isolation level is repeatable reads (or higher) or hints are used.
- It doesn’t prevent other read-only queries from accessing the data simultaneously because the integrity of the data isn’t compromised by the concurrent reads.
Update U - Indicates that the data is read for modification (associated with an UPDATE statement).
- Only one transaction can acquire an update lock at a time.
- Instead of acquiring an exclusive right while reading the data, the data to be modified is read and an (U) lock is acquired on the data. When data is modified, the (U) lock is converted to an exclusive lock for modification. If no modification is required, then the (U) lock is released.
Exclusive X - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent IS IX IU SIX SIU UIX - The intent lock shows the future intention of the lock manager to acquire locks on a specific unit of data for a particular transaction.
- If the intent locks were not used, then a transaction trying to acquire a lock at a higher level would have to scan through the lower levels to detect the presence of lower level locks.
Schema modification Sch-M Used when a DDL or DML operation is performed.
Schema stability Sch-S Used when queries are being compiled. Other transactinal locks are not blocked (S, U, X), but a Sch-M lock cannot be acquired.
Bulk Update Used when you are bulk copying data into a table. Allow multiple threads to copy data concurrently into the same table while preventing access to that table by any other process.
Key-Range Lock index rows in case of serializable transactions. No rows whose key falls withing the range of the locked index keys can be inserted, updated or deleted.
RangeS-S - Shared range, shared resource lock; serializable range scan.
RangeS-U - Shared range, update resource lock; serializable update scan.
RangeI-N - Insert range, null resource lock; used to test ranges before inserting a new key into an index.
RangeX-X - Exclusive range, exclusive resource lock; used when updating a key in a range.

Temporary tables vs table variables in SQL Server 2012

Table variables Temporary tables (local or global)
Scope Duration of batch execution. Local temporary tables: current session, so it can be accessed only by the session that created it.
Global temporary tables: as long there is a session referencing it.
Support for SELECT INTO No Yes
Support for usage of user defined types Yes No. As alternative you can re-create the UDT in tempdb database.
Support for ALTER TABLE No. Change definition cannot be changed after table variable declaration. Yes
Supported in triggers definition Yes Yes
Supported in functions definition Yes No
TRUNCATE TABLE support Not Allowed Yes
Collation Use the collation of the current DB Use the collation of TempDB database.
Support for constraints Yes, except foreign keys. Also, named constraints are not supported. Yes, except foreign keys (they are allowed but not enforced). Also, named constraints are supported.
Can participate in a transaction? No Yes
Column statistics No column-level statistics. This is not an issue when the table actually contains only a small quantity of data ( less than 100 rows), otherwise cardinality estimate skews may appear. Yes
  • Both make use of tempdb system database.
  • There is only one tempdb database per SQL Server instance => high usage of temporary object can make this database to becode the central point of contention.
  • By default, there is only one data file allocated to tempdb database => can cause latch contention in case of heavy creating and deletion of small temporary objects. As a general practice, number of data files for tempdb database is:
    MIN (8, # processor logical cores).

View state of plan cache - verifying for Plan Cache Pollution

        CP.[objtype] as [Cache Type]
        , COUNT (*) as [# Plans]
        , SUM (CASE WHEN CP.[usecounts] = 1  THEN 1 ELSE 0 END) as [# Plans - UseCount1]        
        , SUM (CAST(size_in_bytes as DECIMAL)) / 1024 / 1024 as [Space_MB)]
        , SUM (CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END ) /1024/1024  as [Space_MB_UseCount_1]
        , AVG (CP.[usecounts]) as [Avg Use Counts]
FROM [sys].[dm_exec_cached_plans] CP
GROUP BY CP.[objtype]
ORDER BY [Space_MB_UseCount_1] DESC;

Performance statistics for stored procedures

By looking at the results returned by dm_exec_procedure_stats DMV, we can gather info about how a stored procedure is performing:

        DB_NAME(database_id), p.name as SPName, execution_count
        , total_physical_reads, total_physical_reads / execution_count as avg_physical_reads
        , total_logical_reads, total_logical_reads / execution_count as avg_logical_reads
        , total_elapsed_time, total_elapsed_time / execution_count as avg_elapsed_time
        , QP.query_plan
        sys.procedures P
        JOIN sys.dm_exec_procedure_stats S ON S.object_id = P.object_id
        CROSS APPLY [sys].[dm_exec_query_plan] (S.[plan_handle]) QP
        S.database_id = DB_ID()
        AND P.is_ms_shipped = 0

How to delete or invalidate a query plan

Reboot / Restart
When SQL Server is rebooted, or the service is stopped and restarted, any DMV data is lost.

Flushed Cache
The hard way, delete all query plans from cache:

-- Never execute this on your production server

or you can target specific plans:

Memory pressures
Keeping a large number of cached plans can quickly fill the available memory, especially in systems that create lots of SQL queries that differ subtly (for example, having different environment settings), resulting in separate cached plans for each one.
SQL Server manages the size of the procedure cache by maintaining the age of the execution plans in the cache. If a stored procedure is not re-executed for a long time, the age field of the execution plan can come down to 0, and the plan can be removed from the cache when SQL Server observes a pressure on its memory.

Recompile all queries related to a specific table or view:

EXEC sp_recompile <tablename>

Delete all query plans associated with a stored procedure.

-- Manually mark a stored procedure for recompilation, so it will be recompiled next time it is executed.
EXEC sp_recompile <procname>

Or you can enforce a query recompilation each time, then add a query hint to the end of the query:


Add the WITH RECOMPILE option when a stored proc is created:


When statistics are updated
More details about when statistics change.

Simple parametrization
Setting configuration option optimize for ad hoc workloads to 1, will store only query_hash into the cache and and on second execution (if it happens), the plan itself will be placed in cache.

Forced parametrization
Parametrize all queries like in case of simple parametrization


List of cached query plans

dm_exec_cached_plans returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

      CP.[objtype] -- ad hoc query, stored procedure, prepared statement, etc.
    , CP.[cacheobjtype]
    , CP.[size_in_bytes]
    , CP.[refcounts]
    , CP.[usecounts]
    , ST.[text]
    , QP.query_plan
    sys.dm_exec_cached_plans as CP
    CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) as ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
    1 = 1
    AND ST.[text] LIKE '%<string to find>%'
    --AND CP.[objtype] NOT IN (N'Adhoc', N'Prepared')

When statistics really change?

Stats change:

  • When a table with no rows gets a row
  • When a table has fewer than 500 rows and is increased by 500 or more rows
  • Through SQL Server's auto-updating mechanism based on database option AUTO_UPDATE_STATISTICS (which by default is enabled and it performs synchronously), statistics get automatically changed when 500 + 20% of the table rows change. E.g. for a table with 100k rows, stats will get invalidated when 20500 rows are updated. Read more about this.
  • In addition to statistics on indexes, SQL Server can build statistics on columns with no indexes. See AUTO_CREATE_STATISTICS option.
  • If trace flag 2371 is turned on, then updates are dynamic Read more about this.
  • When you rebuild an index (equivalent to FULLSCAN).
  • Or you can decide to manually update the statistics.

Get statistics info about an index

DBCC SHOW_STATISTICS returns info about 3 components:
- header
- density vector
- histogram = is a statistical construct that shows how often data falls into varying categories. The histogram stored by SQL Server consists of a sampling of data distribution for a column or an index key of up to 200 rows.

DBCC SHOW_STATISTICS('<tablename>', '<indexname>');

You can limit the statistics information displayed by using WITH HISTOGRAM clause:

DBCC SHOW_STATISTICS('<tablename>', '<indexname>')

Performance statistics for cached query plans

        , ST.[text]
        , QS.[execution_count]        
        , QS.query_hash
        , QS.query_plan_hash
        , QP.query_plan
        , QS.*
        [sys].[dm_exec_query_stats] as QS
        CROSS APPLY [sys].[dm_exec_sql_text] (QS.[sql_handle]) AS ST
        CROSS APPLY [sys].[dm_exec_query_plan] (QS.[plan_handle]) AS QP
        ST.[text] LIKE '%<part of your query here>%'
        QS.[execution_count] DESC;


Subscribe to RSS - sql-server-2012