T-SQL

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.

Counting all the rows in a database

A quick count of all rows in all tables in a database can be done using sysindexes table, a system table containing one row for each index and table in the database.

The following query shows the rows count for each table in database. List of tables can be found by interrogating sysobjects system table (it contains one row for each object created within a database: constraint, default, log, rule, stored procedure, and so on) . The 'rows' column in the system table 'sysindexes' holds the number of committed rows in the table.

SELECT SCHEMA_NAME(O.schema_id) as schema_name, O.name as table_name, I.[rows] as no_rows
FROM
    sys.objects O
    INNER JOIN sysindexes I ON O.object_id = I.id
WHERE
        I.indid < 2
        AND O.is_ms_shipped = 0
ORDER BY no_rows DESC

I added indid < 2 condition for restricting the query to looking at the clustered index or heap info, and ignore subsequent indices. Note that only the results of committed transactions are held in the rows column (uncommitted transactions are not).

Use stored procedures instead of embedded T-SQL

Use stored procedures whenever a client application needs to send Transact-SQL to SQL Server. Stored procedures offer many benefits:

  • Reduces network traffic and latency, boosting application performance.
  • Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  • Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don’t remove any result sets columns). This saves developer time.
  • Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access.

How to disable and enable a trigger

Business rules in table triggers often expect your application to update the table one row at a time. Also, some triggers generate an error when the code in the trigger assigns to a local variable the value returned by selecting a column from the inserted virtual table. The assignment fails if you're updating multiple rows because the inserted table contains more than one row, so the subquery returns more than a single value.

Multirow updates need special handling in such a scenario. Developers often wind up dropping a trigger before multirow updates, then creating them later to resume single-row updates. However, I recommend disabling such a trigger instead:

ALTER TABLE MyTable DISABLE TRIGGER MyTrigger

You can re-enable the trigger once you finish your multirow data updates:

ALTER TABLE MyTable ENABLE TRIGGER MyTrigger

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

Concatenate data from a table column

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

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

Pages

Subscribe to RSS - T-SQL