How to clear wait statistics

You may need to clear the aggregated wait statistics (available via sys.dm_os_wait_statistics DMV) in order to measure the effect of a workload change against previous wait statistics.

This can be done at any time by running the below statement:

DBCC SQLPERF (N'sys.dm_os_wait_statistics', CLEAR)

Concatenate data from a table column

       @result VARCHAR(MAX)
     , @delimiter VARCHAR(1) = ','

SELECT @result = COALESCE(@result + @delimiter, '') + ColumnName
FROM TableName
SELECT @result

How to get database owner

SELECT suser_sname(owner_sid)
FROM sys.databases
WHERE name = '<dbname>';

How to get current recovery model for a database

Option 1:

     name as [Database Name],
     recovery_model_desc as [Recovery Model]
FROM sys.databases

Option 2:

SELECT DATABASEPROPERTYEX(N'<db name>', N'Recovery') as [Recovery Model];

Deadlock victim selection

The deadlock victim is chosen based on the following criteria:

  • The DEADLOCK_PRIORITY of the two sessions is compared and the lowest priority session is selected as the victim.
  • If both sessions have the same DEADLOCK_PRIORITY value, the transaction that is the least expensive to rollback (based on the log records that have been generated) is selected as the victim.

SQL Server - lock compatibility matrix

If a resource is already locked when a transaction requests a lock on it, the new lock can only be acquired if it is compatible with the existing lock on resource.

Requested lock mode Existing lock mode
Intent Shared - IS Yes Yes Yes Yes Yes No
Shared - S Yes Yes Yes No No No
Update - U Yes Yes No No No No
Intent Exclusive - IX Yes No No Yes No No
Shared with Intent Exclusive - SIX Yes No No No No No
Exclusive - X No No No No No No

A full compatibility matrix is available in SQL Server Books Online.

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.

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;


Subscribe to RSS - sql-server-2008