List all stored procedures that are using CLR functions/procedures

The following takes each CLR function/procedures and returns all stored procedures/function that are using it:

SELECT DISTINCT
        O.name AS ClrObjectName,
        OBJECT_NAME(SM.object_id) AS IsUsedInObject,
        AM.assembly_class + '.' + AM.assembly_method AS AssemblyMethod
FROM   
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
        INNER JOIN sys.sql_modules SM ON SM.definition LIKE '%' + O.name + '%'
ORDER BY 1, 2

List of all CLR functions and procedures associated with a .NET assembly

The following script returns one row for each function, procedure or trigger that is defined by a common language runtime (CLR) assembly:

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

More details on Books online.

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)

Pages

Subscribe to SQLhint RSS