Get all foreign keys that refer a specific table and column

SELECT
        OBJECT_NAME(constraint_object_id) as FKName,
        OBJECT_NAME(parent_object_id) as TableName,
        COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
        OBJECT_NAME(referenced_object_id) AS ReferencedTableName,      
        COL_NAME(referenced_object_id, referenced_column_id) as ReferencedColumnName   
FROM
        sys.foreign_key_columns FKC
WHERE
        OBJECT_NAME(referenced_object_id) = '<ReferencedTableName>'
        AND COL_NAME(referenced_object_id, referenced_column_id) = '<ReferencedColumnName>'
ORDER BY 1

Traversing hierarchies with nodes having multiple parents

Given a table called Relations with columns IdNode, IdParentNode that describes relations between nodes, the following query uses a recursive CTE to return all ancestors (at all levels) for all child nodes.

;WITH cteTree AS
(
    SELECT  IdNode, IdParentNode, 1 as TreeLevel
    FROM Relations

    UNION ALL

    SELECT  cteTree.IdNode, R.IdParentNode, TreeLevel + 1
    FROM
                cteTree
                INNER JOIN Relations R ON cteTree.IdParentNode = R.IdNode
)
SELECT IdNode, IdParentNode, TreeLevel
FROM cteTree
OPTION(MAXRECURSION 10)

The OPTION(MAXRECURSION 200) means that it looks only 10 levels deep. You can use OPTION(MAXRECURSION 0) if you want to traverse all the levels, though you have to make sure you don't have a circular reference.

How to get database backup history

This script will allow you to see the last performed backups and their type (full, differential or log):

SELECT
        database_name
        , [backup_start_date]
        , CASE [type]
                WHEN N'D' THEN N'Full Backup'
                WHEN N'I' THEN N'Diff Backup'
                WHEN N'L' THEN N'Log Backup'
                ELSE N'Unknown'
          END AS N'backup_type'
        , [position]
        , [name]
        , [description]
        , [first_lsn]
        , [last_lsn]
        , [backup_finish_date]
FROM
        msdb.dbo.[backupset] AS S
        INNER JOIN msdb.dbo.[backupmediafamily] AS M ON S.[media_set_id] = M.[media_set_id]
WHERE [database_name] = N'<database_name>'
ORDER BY [backup_start_date];
GO

When database consistency check was performed last time?

This script will show the last time DBCC CHECKDB was completed without errors, in dbi_dbccLaskKnownGood:

DBCC TRACEON (3604);
DBCC DBINFO (N'DatabaseName');

How to check consistency of your backups - CHECKSUM

Backups should always use WITH CHECKSUM:

BACKUP DATABASE [DatabaseName]
TO DISK = N'D:\Backups\DatabaseName.bak'
WITH INIT, CHECKSUM;
GO

Check in backup header (column IsDamaged) , if it contains a corrupted database (i.e. the database was already corrupted when the backup was performed):

RESTORE HEADERONLY
FROM DISK = N'D:\Backups\DatabaseName.bak';
GO

You can test the backup integrity:

RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\DatabaseName.bak'
WITH CHECKSUM;
GO

Checking backup consistency is not an alternative to regular database consistency checking via DBCC CHECKDB.

Performance troubleshooting using Waits

WITH [Waits]
AS
(SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL')
)
SELECT TOP 10
        W1.[wait_type] AS [WaitType],
        CAST (W1.[WaitS] AS DECIMAL(14, 2)) AS [Wait (seconds)],
        CAST (W1.[ResourceS] AS DECIMAL(14, 2)) AS [Resource wait (seconds)],
        CAST (W1.[SignalS] AS DECIMAL(14, 2)) AS [Signal wait (seconds)],
        W1.[WaitCount],
        CAST (W1.[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
        CAST ((W1.[WaitS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait (seconds)],
        CAST ((W1.[ResourceS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgResource (seconds)],
        CAST ((W1.[SignalS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSignal (seconds)]
FROM
        [Waits] AS W1
        INNER JOIN [Waits] AS W2 ON W2.[RowNum] <= W1.[RowNum]
GROUP BY W1.[RowNum], W1.[wait_type], W1.[WaitS],
        W1.[ResourceS], W1.[SignalS], W1.[WaitCount], W1.[Percentage];
GO

Find out waiting tasks - sys.dm_os_waiting_tasks

sys.dm_os_waiting_tasks dmv shows all threads that are currently suspended:

SELECT
        DB_NAME(ER.database_id) DatabaseName,
        OT.[session_id],
        OT.[exec_context_id],
        OT.[wait_duration_ms],
        OT.[wait_type],
        OT.[blocking_session_id],
        OT.[resource_description],
        ES.[program_name],
        ST.[text],
        ES.[cpu_time],
        ES.[memory_usage],
        QP.[query_plan]
FROM
        sys.dm_os_waiting_tasks OT
        INNER JOIN sys.dm_exec_sessions ES ON OT.[session_id] = ES.[session_id]
        INNER JOIN sys.dm_exec_requests ER ON ES.[session_id] = ER.[session_id]
        OUTER APPLY sys.dm_exec_sql_text (ER.[sql_handle]) ST
        OUTER APPLY sys.dm_exec_query_plan (ER.[plan_handle]) QP
WHERE ES.[is_user_process] = 1
ORDER BY OT.[session_id], OT.[exec_context_id];
GO

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)

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
IS S U IX SIX X
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.

Pages

Subscribe to SQLhint RSS