SQL Server - Useful DBA queries you always have to look for on the Internet

Reading Time: 42 minutes

Hey guys!
In this article I would like to share with you a number of useful DBA day-to-day scripts that you always have to look for on the Internet when you need to make a particular query. My idea here is to make your life easier and have a multi-purpose article for many different purposes for you to bookmark in your browser and always have the information you want here 🙂

No more tangling, let's get down to business!

General information

View scripts

Databases and Settings Overview

SELECT
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    state_desc,
    (
        SELECT
            COUNT(1)
        FROM
            sys.master_files
        WHERE
            DB_NAME(database_id) = DB.name
            AND type_desc = 'rows'
    ) AS DataFiles,
    (
        SELECT
            SUM(( size * 8 ) / 1024)
        FROM
            sys.master_files
        WHERE
            DB_NAME(database_id) = DB.name
            AND type_desc = 'rows'
    ) AS [Data MB],
    (
        SELECT
            COUNT(1)
        FROM
            sys.master_files
        WHERE
            DB_NAME(database_id) = DB.name
            AND type_desc = 'log'
    ) AS LogFiles,
    (
        SELECT
            SUM(( size * 8 ) / 1024)
        FROM
            sys.master_files
        WHERE
            DB_NAME(database_id) = DB.name
            AND type_desc = 'log'
    ) AS [Log MB],
    recovery_model_desc AS [Recovery model],
    CASE [compatibility_level]
        WHEN 60 THEN '60 (SQL Server 6.0)'
        WHEN 65 THEN '65 (SQL Server 6.5)'
        WHEN 70 THEN '70 (SQL Server 7.0)'
        WHEN 80 THEN '80 (SQL Server 2000)'
        WHEN 90 THEN '90 (SQL Server 2005)'
        WHEN 100 THEN '100 (SQL Server 2008)'
        WHEN 110 THEN '110 (SQL Server 2012)'
        WHEN 120 THEN '120 (SQL Server 2014)'
        WHEN 130 THEN '130 (SQL Server 2016)'
        WHEN 140 THEN '140 (SQL Server 2017)'
        WHEN 150 THEN '150 (SQL Server 2019)'
    END AS [compatibility level],
    CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
    -- last backup
    ISNULL(
    (
        SELECT TOP 1
            CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(), backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(SECOND, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)'
        FROM
            msdb..backupset BK
        WHERE
            BK.database_name = DB.name
        ORDER BY
            backup_set_id DESC
    ),    '-'
          ) AS [Last backup],
    CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
    page_verify_option_desc AS [page verify option],
    CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
    CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
    CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
    DB.delayed_durability_desc,
    DB.is_parameterization_forced,
    DB.user_access_desc,
    DB.snapshot_isolation_state_desc,
    DB.is_read_only,
    DB.is_trustworthy_on,
    DB.is_encrypted,
    DB.is_query_store_on,
    DB.is_cdc_enabled,
    DB.is_remote_data_archive_enabled,
    DB.is_subscribed,
    DB.is_merge_published
FROM
    sys.databases DB
ORDER BY
    6 DESC;

SELECT

CONVERT(VARCHAR(25), DB.name) AS dbName,

state_desc,

(

SELECT

COUNT(1)

FROM

sys.master_files

WHERE

DB_NAME(database_id) = DB.name

AND type_desc = 'rows'

) AS DataFiles,

(

SELECT

SUM(( size * 8 ) / 1024)

FROM

sys.master_files

WHERE

DB_NAME(database_id) = DB.name

AND type_desc = 'rows'

) AS [Data MB],

(

SELECT

COUNT(1)

FROM

sys.master_files

WHERE

DB_NAME(database_id) = DB.name

AND type_desc = 'log'

) AS LogFiles,

(

SELECT

SUM(( size * 8 ) / 1024)

FROM

sys.master_files

WHERE

DB_NAME(database_id) = DB.name

AND type_desc = 'log'

) AS [Log MB],

recovery_model_desc AS [Recovery model],

CASE [compatibility_level]

WHEN 60 THEN '60 (SQL Server 6.0)'

WHEN 65 THEN '65 (SQL Server 6.5)'

WHEN 70 THEN '70 (SQL Server 7.0)'

WHEN 80 THEN '80 (SQL Server 2000)'

WHEN 90 THEN '90 (SQL Server 2005)'

WHEN 100 THEN '100 (SQL Server 2008)'

WHEN 110 THEN '110 (SQL Server 2012)'

WHEN 120 THEN '120 (SQL Server 2014)'

WHEN 130 THEN '130 (SQL Server 2016)'

WHEN 140 THEN '140 (SQL Server 2017)'

WHEN 150 THEN '150 (SQL Server 2019)'

END AS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],

-- last backup

ISNULL(

(

SELECT TOP 1

CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(), backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(SECOND, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)'

FROM

msdb..backupset BK

WHERE

BK.database_name = DB.name

ORDER BY

backup_set_id DESC

), '-'

) AS [Last backup],

CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],

page_verify_option_desc AS [page verify option],

CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],

CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],

CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],

DB.delayed_durability_desc,

DB.is_parameterization_forced,

DB.user_access_desc,

DB.snapshot_isolation_state_desc,

DB.is_read_only,

DB.is_trustworthy_on,

DB.is_encrypted,

DB.is_query_store_on,

DB.is_cdc_enabled,

DB.is_remote_data_archive_enabled,

DB.is_subscribed,

DB.is_merge_published

FROM

sys.databases DB

ORDER BY

6 DESC;

Queries that are currently running

To learn more about this routine, see my article. SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB) or SQL Server - Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more.

SQL Server version 2012 +

SELECT
    RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' + 
    RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' + 
    RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' + 
    RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' + 
    RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3) 
    AS Duration,
    A.session_id AS session_id,
    B.command,
    TRY_CAST('<?query --' + CHAR(10) + (
        SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
                                                                          WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
                                                                          ELSE B.statement_end_offset
                                                                      END
                                                                     ) - B.statement_start_offset
                                                                    ) / 2 + 1
                     )
    ) + CHAR(10) + '--?>' AS XML) AS sql_text,
    TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
    A.login_name,
    '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE 
        WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
        WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
        ELSE ''
    END), '') AS wait_info,
    FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,
    FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,
    FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current,
    FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,
    FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,
    FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,
    FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,
    NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
    COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
    'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
    (CASE 
        WHEN B.[deadlock_priority] <= -5 THEN 'Low'
        WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
        WHEN B.[deadlock_priority] >= 5 THEN 'High'
    END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
    B.row_count,
    COALESCE(A.open_transaction_count, 0) AS open_tran_count,
    (CASE B.transaction_isolation_level
        WHEN 0 THEN 'Unspecified' 
        WHEN 1 THEN 'ReadUncommitted' 
        WHEN 2 THEN 'ReadCommitted' 
        WHEN 3 THEN 'Repeatable' 
        WHEN 4 THEN 'Serializable' 
        WHEN 5 THEN 'Snapshot'
    END) AS transaction_isolation_level,
    A.[status],
    NULLIF(B.percent_complete, 0) AS percent_complete,
    A.[host_name],
    COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
    (CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) +  ')' ELSE A.[program_name] END) AS [program_name],
    H.[name] AS resource_governor_group,
    COALESCE(B.start_time, A.last_request_end_time) AS start_time,
    A.login_time,
    COALESCE(B.request_id, 0) AS request_id,
    W.query_plan
FROM
    sys.dm_exec_sessions AS A WITH (NOLOCK)
    LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
    JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
    LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)
    LEFT JOIN (
        SELECT
            session_id, 
            wait_type,
            wait_duration_ms,
            resource_description,
            ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
        FROM 
            sys.dm_os_waiting_tasks
    ) E ON A.session_id = E.session_id AND E.Ranking = 1
    LEFT JOIN (
        SELECT
            session_id,
            request_id,
            SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
            SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
        FROM
            sys.dm_db_task_space_usage
        GROUP BY
            session_id,
            request_id
    ) F ON B.session_id = F.session_id AND B.request_id = F.request_id
    LEFT JOIN (
        SELECT 
            blocking_session_id,
            COUNT(*) AS blocked_session_count
        FROM 
            sys.dm_exec_requests
        WHERE 
            blocking_session_id != 0
        GROUP BY
            blocking_session_id
    ) G ON A.session_id = G.blocking_session_id
    OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
    OUTER APPLY sys.dm_exec_query_plan(B.plan_handle) AS W
    LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id
WHERE
    A.session_id > 50
    AND A.session_id <> @@SPID
    AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))

100

101

102

103

104

105

SELECT

RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +

RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +

RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +

RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +

RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)

AS Duration,

A.session_id AS session_id,

B.command,

TRY_CAST('<?query --' + CHAR(10) + (

SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE

WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)

ELSE B.statement_end_offset

END

) - B.statement_start_offset

) / 2 + 1

)

) + CHAR(10) + '--?>' AS XML) AS sql_text,

TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,

A.login_name,

'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE

WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)

WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'

ELSE ''

END), '') AS wait_info,

FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU,

FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations,

FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current,

FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads,

FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes,

FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads,

FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory,

NULLIF(B.blocking_session_id, 0) AS blocking_session_id,

COALESCE(G.blocked_session_count, 0) AS blocked_session_count,

'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,

(CASE

WHEN B.[deadlock_priority] <= -5 THEN 'Low'

WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'

WHEN B.[deadlock_priority] >= 5 THEN 'High'

END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],

B.row_count,

COALESCE(A.open_transaction_count, 0) AS open_tran_count,

(CASE B.transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END) AS transaction_isolation_level,

A.[status],

NULLIF(B.percent_complete, 0) AS percent_complete,

A.[host_name],

COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],

(CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name],

H.[name] AS resource_governor_group,

COALESCE(B.start_time, A.last_request_end_time) AS start_time,

A.login_time,

COALESCE(B.request_id, 0) AS request_id,

W.query_plan

FROM

sys.dm_exec_sessions AS A WITH (NOLOCK)

LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id

JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id

LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)

LEFT JOIN (

SELECT

session_id,

wait_type,

wait_duration_ms,

resource_description,

ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking

FROM

sys.dm_os_waiting_tasks

) E ON A.session_id = E.session_id AND E.Ranking = 1

LEFT JOIN (

SELECT

session_id,

request_id,

SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,

SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current

FROM

sys.dm_db_task_space_usage

GROUP BY

session_id,

request_id

) F ON B.session_id = F.session_id AND B.request_id = F.request_id

LEFT JOIN (

SELECT

blocking_session_id,

COUNT(*) AS blocked_session_count

FROM

sys.dm_exec_requests

WHERE

blocking_session_id != 0

GROUP BY

blocking_session_id

) G ON A.session_id = G.blocking_session_id

OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X

OUTER APPLY sys.dm_exec_query_plan(B.plan_handle) AS W

LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id

WHERE

A.session_id > 50

AND A.session_id <> @@SPID

AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))

SQL Server version 2008 and earlier

SELECT
    RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' + 
    RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' + 
    RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' + 
    RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' + 
    RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3) 
    AS Duration,
    A.session_id AS session_id,
    B.command,
    CAST('<?query --' + CHAR(10) + (
        SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
                                                                          WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
                                                                          ELSE B.statement_end_offset
                                                                      END
                                                                     ) - B.statement_start_offset
                                                                    ) / 2 + 1
                     )
    ) + CHAR(10) + '--?>' AS XML) AS sql_text,
    CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
    A.login_name,
    '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE 
        WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
        WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
        ELSE ''
    END), '') AS wait_info,
    COALESCE(B.cpu_time, 0) AS CPU,
    COALESCE(F.tempdb_allocations, 0) AS tempdb_allocations,
    COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0) AS tempdb_current,
    COALESCE(B.logical_reads, 0) AS reads,
    COALESCE(B.writes, 0) AS writes,
    COALESCE(B.reads, 0) AS physical_reads,
    COALESCE(B.granted_query_memory, 0) AS used_memory,
    NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
    COALESCE(G.blocked_session_count, 0) AS blocked_session_count,
    'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,
    (CASE 
        WHEN B.[deadlock_priority] <= -5 THEN 'Low'
        WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'
        WHEN B.[deadlock_priority] >= 5 THEN 'High'
    END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],
    B.row_count,
    B.open_transaction_count,
    (CASE B.transaction_isolation_level
        WHEN 0 THEN 'Unspecified' 
        WHEN 1 THEN 'ReadUncommitted' 
        WHEN 2 THEN 'ReadCommitted' 
        WHEN 3 THEN 'Repeatable' 
        WHEN 4 THEN 'Serializable' 
        WHEN 5 THEN 'Snapshot'
    END) AS transaction_isolation_level,
    A.[status],
    NULLIF(B.percent_complete, 0) AS percent_complete,
    A.[host_name],
    COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],
    A.[program_name],
    H.[name] AS resource_governor_group,
    COALESCE(B.start_time, A.last_request_end_time) AS start_time,
    A.login_time,
    COALESCE(B.request_id, 0) AS request_id,
    W.query_plan
FROM
    sys.dm_exec_sessions AS A WITH (NOLOCK)
    LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
    JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id
    LEFT JOIN (
        SELECT
            session_id, 
            wait_type,
            wait_duration_ms,
            resource_description,
            ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
        FROM 
            sys.dm_os_waiting_tasks
    ) E ON A.session_id = E.session_id AND E.Ranking = 1
    LEFT JOIN (
        SELECT
            session_id,
            request_id,
            SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,
            SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current
        FROM
            sys.dm_db_task_space_usage
        GROUP BY
            session_id,
            request_id
    ) F ON B.session_id = F.session_id AND B.request_id = F.request_id
    LEFT JOIN (
        SELECT 
            blocking_session_id,
            COUNT(*) AS blocked_session_count
        FROM 
            sys.dm_exec_requests
        WHERE 
            blocking_session_id != 0
        GROUP BY
            blocking_session_id
    ) G ON A.session_id = G.blocking_session_id
    OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X
    OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS W
    LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id
WHERE
    A.session_id > 50
    AND A.session_id <> @@SPID
    AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND B.open_transaction_count > 0))

100

101

102

103

104

SELECT

RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' +

RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' +

RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' +

RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' +

RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3)

AS Duration,

A.session_id AS session_id,

B.command,

CAST('<?query --' + CHAR(10) + (

SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE

WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)

ELSE B.statement_end_offset

END

) - B.statement_start_offset

) / 2 + 1

)

) + CHAR(10) + '--?>' AS XML) AS sql_text,

CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,

A.login_name,

'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE

WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)

WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'

ELSE ''

END), '') AS wait_info,

COALESCE(B.cpu_time, 0) AS CPU,

COALESCE(F.tempdb_allocations, 0) AS tempdb_allocations,

COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0) AS tempdb_current,

COALESCE(B.logical_reads, 0) AS reads,

COALESCE(B.writes, 0) AS writes,

COALESCE(B.reads, 0) AS physical_reads,

COALESCE(B.granted_query_memory, 0) AS used_memory,

NULLIF(B.blocking_session_id, 0) AS blocking_session_id,

COALESCE(G.blocked_session_count, 0) AS blocked_session_count,

'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command,

(CASE

WHEN B.[deadlock_priority] <= -5 THEN 'Low'

WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal'

WHEN B.[deadlock_priority] >= 5 THEN 'High'

END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority],

B.row_count,

B.open_transaction_count,

(CASE B.transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END) AS transaction_isolation_level,

A.[status],

NULLIF(B.percent_complete, 0) AS percent_complete,

A.[host_name],

COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name],

A.[program_name],

H.[name] AS resource_governor_group,

COALESCE(B.start_time, A.last_request_end_time) AS start_time,

A.login_time,

COALESCE(B.request_id, 0) AS request_id,

W.query_plan

FROM

sys.dm_exec_sessions AS A WITH (NOLOCK)

LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id

JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id

LEFT JOIN (

SELECT

session_id,

wait_type,

wait_duration_ms,

resource_description,

ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking

FROM

sys.dm_os_waiting_tasks

) E ON A.session_id = E.session_id AND E.Ranking = 1

LEFT JOIN (

SELECT

session_id,

request_id,

SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations,

SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current

FROM

sys.dm_db_task_space_usage

GROUP BY

session_id,

request_id

) F ON B.session_id = F.session_id AND B.request_id = F.request_id

LEFT JOIN (

SELECT

blocking_session_id,

COUNT(*) AS blocked_session_count

FROM

sys.dm_exec_requests

WHERE

blocking_session_id != 0

GROUP BY

blocking_session_id

) G ON A.session_id = G.blocking_session_id

OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X

OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS W

LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id

WHERE

A.session_id > 50

AND A.session_id <> @@SPID

AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND B.open_transaction_count > 0))

Check the main instance parameters

SELECT 
    [name],
    [value],
    [description]
FROM 
    sys.configurations
WHERE 
    [name] IN ( 'max degree of parallelism', 'cost threshold for parallelism', 'min server memory (MB)',
                'max server memory (MB)', 'clr enabled', 'xp_cmdshell', 'Ole Automation Procedures',
                'user connections', 'fill factor (%)', 'cross db ownership chaining', 'remote access',
                'default trace enabled', 'external scripts enabled', 'Database Mail XPs', 'Ad Hoc Distributed Queries',
                'SMO and DMO XPs', 'clr strict security', 'remote admin connections'
              )
ORDER BY 
    [name]

SELECT

[name],

[value],

[description]

FROM

sys.configurations

WHERE

[name] IN ( 'max degree of parallelism', 'cost threshold for parallelism', 'min server memory (MB)',

'max server memory (MB)', 'clr enabled', 'xp_cmdshell', 'Ole Automation Procedures',

'user connections', 'fill factor (%)', 'cross db ownership chaining', 'remote access',

'default trace enabled', 'external scripts enabled', 'Database Mail XPs', 'Ad Hoc Distributed Queries',

'SMO and DMO XPs', 'clr strict security', 'remote admin connections'

)

ORDER BY

[name]

Identifies the transaction log usage of each database in the instance (equivalent to DBCC SQLPERF (LOGSPACE))

SELECT
    RTRIM(A.instance_name) AS [Database Name],
    A.cntr_value / 1024.0 AS [Log Size (MB)],
    CAST(B.cntr_value * 100.0 / A.cntr_value AS DEC(18, 5)) AS [Log Space Used (%)]
FROM
    sys.dm_os_performance_counters A
    JOIN sys.dm_os_performance_counters B ON A.instance_name = B.instance_name
WHERE
    A.[object_name] LIKE '%Databases%'
    AND B.[object_name] LIKE '%Databases%'
    AND A.counter_name = 'Log File(s) Size (KB)'
    AND B.counter_name = 'Log File(s) Used Size (KB)'
    AND A.instance_name NOT IN ( '_Total', 'mssqlsystemresource' )
    AND A.cntr_value > 0

SELECT

RTRIM(A.instance_name) AS [Database Name],

A.cntr_value / 1024.0 AS [Log Size (MB)],

CAST(B.cntr_value * 100.0 / A.cntr_value AS DEC(18, 5)) AS [Log Space Used (%)]

FROM

sys.dm_os_performance_counters A

JOIN sys.dm_os_performance_counters B ON A.instance_name = B.instance_name

WHERE

A.[object_name] LIKE '%Databases%'

AND B.[object_name] LIKE '%Databases%'

AND A.counter_name = 'Log File(s) Size (KB)'

AND B.counter_name = 'Log File(s) Used Size (KB)'

AND A.instance_name NOT IN ( '_Total', 'mssqlsystemresource' )

AND A.cntr_value > 0

Identify and try to fix orphaned users

SELECT
    A.[name],
    A.[sid],
    (CASE 
        WHEN C.principal_id IS NULL THEN NULL -- Não tem o que fazer.. Login correspondente não existe
        ELSE 'ALTER USER [' + A.[name] + '] WITH LOGIN = [' + C.[name] + ']' -- Tenta corrigir o usuário órfão
    END) AS command
FROM
    sys.database_principals A WITH(NOLOCK)
    LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    LEFT JOIN sys.server_principals C WITH(NOLOCK) ON (A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI OR A.[sid] = C.[sid]) AND C.is_fixed_role = 0 AND C.[type_desc] = 'SQL_LOGIN'
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND A.[type_desc] = 'SQL_USER'
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.[name]

SELECT

A.[name],

A.[sid],

(CASE

WHEN C.principal_id IS NULL THEN NULL -- Não tem o que fazer.. Login correspondente não existe

ELSE 'ALTER USER [' + A.[name] + '] WITH LOGIN = [' + C.[name] + ']' -- Tenta corrigir o usuário órfão

END) AS command

FROM

sys.database_principals A WITH(NOLOCK)

LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]

LEFT JOIN sys.server_principals C WITH(NOLOCK) ON (A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI OR A.[sid] = C.[sid]) AND C.is_fixed_role = 0 AND C.[type_desc] = 'SQL_LOGIN'

WHERE

A.principal_id > 4

AND B.[sid] IS NULL

AND A.is_fixed_role = 0

AND A.[type_desc] = 'SQL_USER'

AND A.authentication_type <> 0 -- NONE

ORDER BY

A.[name]

Identifies when the SQL Server service started

Using the sys.dm_os_sys_info DMV we can identify the date the SQL Server service was started. Recommended reading to supplement this information: SQL Server - How to find out how long the instance has been online or when the instance was started

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

1	SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Identifies DDL and DCL operations performed on the instance.

Using Default Trace, we can identify DDL (ALTER, CREATE, DROP) and DCL (GRANT, DENY, REVOKE) operations performed on the instance. To better understand this feature, I recommend reading the article. Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable.

DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1)

SELECT
    A.HostName,
    A.ApplicationName,
    A.NTUserName,
    A.NTDomainName,
    A.LoginName,
    A.SPID,
    A.EventClass,
    B.name,
    A.EventSubClass,
    A.TextData,
    A.StartTime,
    A.DatabaseName,
    A.ObjectID,
    A.ObjectName,
    A.TargetLoginName,
    A.TargetUserName
FROM
    [fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A
    JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id
WHERE
    A.EventClass IN ( 164, 46, 47, 108, 110, 152 ) 
    AND A.StartTime >= GETDATE()-7
    AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' )
    AND A.LoginName NOT LIKE '%SQLTELEMETRY$%'
    AND A.DatabaseName <> 'tempdb'
    AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL )
    AND A.ObjectName <> 'telemetry_xevents'
    AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy')
ORDER BY
    StartTime DESC

DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1)

SELECT

A.HostName,

A.ApplicationName,

A.NTUserName,

A.NTDomainName,

A.LoginName,

A.SPID,

A.EventClass,

B.name,

A.EventSubClass,

A.TextData,

A.StartTime,

A.DatabaseName,

A.ObjectID,

A.ObjectName,

A.TargetLoginName,

A.TargetUserName

FROM

[fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A

JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id

WHERE

A.EventClass IN ( 164, 46, 47, 108, 110, 152 )

AND A.StartTime >= GETDATE()-7

AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' )

AND A.LoginName NOT LIKE '%SQLTELEMETRY$%'

AND A.DatabaseName <> 'tempdb'

AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL )

AND A.ObjectName <> 'telemetry_xevents'

AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy')

ORDER BY

StartTime DESC

Identifies when backups were generated or restored

Using the default trace, we can identify the occurrence of BACKUP and RESTORE commands in the instance. For a better understanding of this script, I suggest reading the article. Using SQL Server Standard Trace to Audit Events (fn_trace_gettable).

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    TextData,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 115 )
ORDER BY
    StartTime DESC

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT

TextData,

Duration,

StartTime,

EndTime,

SPID,

ApplicationName,

LoginName

FROM

sys.fn_trace_gettable(@path, DEFAULT)

WHERE

EventClass IN ( 115 )

ORDER BY

StartTime DESC

Identify auto growth events

Using the default trace, we were able to identify the occurrence of autogrowth events in the instance, that is, when SQL Server dynamically allocated more file space as it was needed to allocate new data. For a better understanding of this script, I suggest reading the article. Monitoring Autogrowth Events in Databases in SQL Server.

DECLARE 
    @Ds_Arquivo_Trace VARCHAR(500) = (SELECT [path] FROM sys.traces WHERE is_default = 1)
    
DECLARE
    @Index INT = PATINDEX('%\%', REVERSE(@Ds_Arquivo_Trace))
 
DECLARE
    @Nm_Arquivo_Trace VARCHAR(500) = LEFT(@Ds_Arquivo_Trace, LEN(@Ds_Arquivo_Trace) - @Index) + '\log.trc'
 
 
SELECT
    A.DatabaseName,
    A.[Filename],
    ( A.Duration / 1000 ) AS 'Duration_ms',
    A.StartTime,
    A.EndTime,
    ( A.IntegerData * 8.0 / 1024 ) AS 'GrowthSize_MB',
    A.ApplicationName,
    A.HostName,
    A.LoginName
FROM
    ::fn_trace_gettable(@Nm_Arquivo_Trace, DEFAULT) A
WHERE
    A.EventClass >= 92
    AND A.EventClass <= 95
    AND A.ServerName = @@servername 
ORDER BY
    A.StartTime DESC

DECLARE

@Ds_Arquivo_Trace VARCHAR(500) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

DECLARE

@Index INT = PATINDEX('%\%', REVERSE(@Ds_Arquivo_Trace))

DECLARE

@Nm_Arquivo_Trace VARCHAR(500) = LEFT(@Ds_Arquivo_Trace, LEN(@Ds_Arquivo_Trace) - @Index) + '\log.trc'

SELECT

A.DatabaseName,

A.[Filename],

( A.Duration / 1000 ) AS 'Duration_ms',

A.StartTime,

A.EndTime,

( A.IntegerData * 8.0 / 1024 ) AS 'GrowthSize_MB',

A.ApplicationName,

A.HostName,

A.LoginName

FROM

::fn_trace_gettable(@Nm_Arquivo_Trace, DEFAULT) A

WHERE

A.EventClass >= 92

AND A.EventClass <= 95

AND A.ServerName = @@servername

ORDER BY

A.StartTime DESC

Identify DBCC Command Execution

Using the default trace, we can identify the occurrence of DBCC commands executed in the instance, such as CHECKDB. For a better understanding of this script, I suggest reading the article. Using SQL Server Standard Trace to Audit Events (fn_trace_gettable).

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT
    TextData,
    Duration,
    StartTime,
    EndTime,
    SPID,
    ApplicationName,
    LoginName
FROM
    sys.fn_trace_gettable(@path, DEFAULT)
WHERE
    EventClass IN ( 116 )
ORDER BY
    StartTime DESC

DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1)

SELECT

TextData,

Duration,

StartTime,

EndTime,

SPID,

ApplicationName,

LoginName

FROM

sys.fn_trace_gettable(@path, DEFAULT)

WHERE

EventClass IN ( 116 )

ORDER BY

StartTime DESC

Tables and Indexes

In this session I will share some scripts related to queries on tables and indexes, such as identifying HEAP tables, fragmented indexes, etc.
Recommended reading: Performance Tuning Series

View scripts

Identify uncompressed tables and indexes

Using the query below, we will identify the tables and indexes in RowStore mode that are not using data compression. Keep in mind that until 2016, this feature was unique to the Enterprise version (in addition to Trial and Developer), so if your version is inferred from 2016 and Standard edition, this script will not be useful for you. To better understand what data compression is, I suggest you read the article Compressing all tables in a database in SQL Server.

SELECT DISTINCT 
    C.[name] AS [Schema],
    A.[name] AS Tabela,
    NULL AS Indice,
    'ALTER TABLE [' + C.[name] + '].[' + A.[name] + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS Comando
FROM 
    sys.tables                   A
    INNER JOIN sys.partitions    B   ON A.[object_id] = B.[object_id]
    INNER JOIN sys.schemas       C   ON A.[schema_id] = C.[schema_id]
WHERE 
    B.data_compression_desc = 'NONE'
    AND B.index_id = 0 -- HEAP
    AND A.[type] = 'U'
    
UNION
 
SELECT DISTINCT 
    C.[name] AS [Schema],
    B.[name] AS Tabela,
    A.[name] AS Indice,
    'ALTER INDEX [' + A.[name] + '] ON [' + C.[name] + '].[' + B.[name] + '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE)'
FROM 
    sys.indexes                  A
    INNER JOIN sys.tables        B   ON A.[object_id] = B.[object_id]
    INNER JOIN sys.schemas       C   ON B.[schema_id] = C.[schema_id]
    INNER JOIN sys.partitions    D   ON A.[object_id] = D.[object_id] AND A.index_id = D.index_id
WHERE
    D.data_compression_desc =  'NONE'
    AND D.index_id <> 0
    AND A.[type] IN (1, 2) -- CLUSTERED e NONCLUSTERED (Rowstore)
    AND B.[type] = 'U'
ORDER BY
    Tabela,
    Indice

SELECT DISTINCT

C.[name] AS [Schema],

A.[name] AS Tabela,

NULL AS Indice,

'ALTER TABLE [' + C.[name] + '].[' + A.[name] + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS Comando

FROM

sys.tables A

INNER JOIN sys.partitions B ON A.[object_id] = B.[object_id]

INNER JOIN sys.schemas C ON A.[schema_id] = C.[schema_id]

WHERE

B.data_compression_desc = 'NONE'

AND B.index_id = 0 -- HEAP

AND A.[type] = 'U'

UNION

SELECT DISTINCT

C.[name] AS [Schema],

B.[name] AS Tabela,

A.[name] AS Indice,

'ALTER INDEX [' + A.[name] + '] ON [' + C.[name] + '].[' + B.[name] + '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE)'

FROM

sys.indexes A

INNER JOIN sys.tables B ON A.[object_id] = B.[object_id]

INNER JOIN sys.schemas C ON B.[schema_id] = C.[schema_id]

INNER JOIN sys.partitions D ON A.[object_id] = D.[object_id] AND A.index_id = D.index_id

WHERE

D.data_compression_desc = 'NONE'

AND D.index_id <> 0

AND A.[type] IN (1, 2) -- CLUSTERED e NONCLUSTERED (Rowstore)

AND B.[type] = 'U'

ORDER BY

Tabela,

Indice

Identify Index Fragmentation

To identify the level of fragmentation of the indexes and to assess whether a REORGANIZE or REBUILD is required, use the script below. To better understand the structure of indexes and what fragmentation is, read the article. Understanding Index Functioning in SQL Server.

SELECT
    C.[name] AS TableName,
    B.[name] AS IndexName,
    A.index_type_desc AS IndexType,
    A.avg_fragmentation_in_percent,
    'ALTER INDEX [' + B.[name] + '] ON [' + D.[name] + '].[' + C.[name] + '] REBUILD' AS CmdRebuild
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')	A
    JOIN sys.indexes B ON B.[object_id] = A.[object_id] AND B.index_id = A.index_id
    JOIN sys.objects C ON B.[object_id] = C.[object_id]
    JOIN sys.schemas D ON D.[schema_id] = C.[schema_id]
WHERE
    A.avg_fragmentation_in_percent > 30
    AND OBJECT_NAME(B.[object_id]) NOT LIKE '[_]%'
    AND A.index_type_desc != 'HEAP'
ORDER BY
    A.avg_fragmentation_in_percent DESC

SELECT

C.[name] AS TableName,

B.[name] AS IndexName,

A.index_type_desc AS IndexType,

A.avg_fragmentation_in_percent,

'ALTER INDEX [' + B.[name] + '] ON [' + D.[name] + '].[' + C.[name] + '] REBUILD' AS CmdRebuild

FROM

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') A

JOIN sys.indexes B ON B.[object_id] = A.[object_id] AND B.index_id = A.index_id

JOIN sys.objects C ON B.[object_id] = C.[object_id]

JOIN sys.schemas D ON D.[schema_id] = C.[schema_id]

WHERE

A.avg_fragmentation_in_percent > 30

AND OBJECT_NAME(B.[object_id]) NOT LIKE '[_]%'

AND A.index_type_desc != 'HEAP'

ORDER BY

A.avg_fragmentation_in_percent DESC

Identify HEAP tables (without clustered index)

Using the query below, you will be able to identify tables that do not have a clustered index created, which can almost always represent a potential query performance issue, as data will not be sorted and the use of only non-clustered indexes can end up generating many Key Lookup events.

To better understand the structure of indexes and what a clustered index is, read the article. Understanding Index Functioning in SQL Server.

SELECT
    B.[name] + '.' + A.[name] AS table_name
FROM
    sys.tables A
    JOIN sys.schemas B ON A.[schema_id] = B.[schema_id]
    JOIN sys.indexes C ON A.[object_id] = C.[object_id]
WHERE
    C.[type] = 0 -- = Heap 
ORDER BY
    table_name

SELECT

B.[name] + '.' + A.[name] AS table_name

FROM

sys.tables A

JOIN sys.schemas B ON A.[schema_id] = B.[schema_id]

JOIN sys.indexes C ON A.[object_id] = C.[object_id]

WHERE

C.[type] = 0 -- = Heap

ORDER BY

table_name

Using Indexes

With the query below, you will be able to identify if the indexes created are being used correctly. It is also useful for identifying indexes that may be good candidates for deletion, as they are just taking up space, and it also helps you identify tables that are widely accessed and not accessed for a long time.

To better understand performance tuning and understand what is Seek operation, Scan, etc. read the article SQL Server - Introduction to Performance Tuning Study.

SELECT
    D.[name] + '.' + C.[name] AS ObjectName,
    A.[name] AS IndexName,
    (CASE WHEN A.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + A.[type_desc] AS IndexType,
    MAX(B.last_user_seek) AS last_user_seek,
    MAX(COALESCE(B.last_user_seek, B.last_user_scan)) AS last_read,
    SUM(B.user_seeks) AS User_Seeks,
    SUM(B.user_scans) AS User_Scans,
    SUM(B.user_seeks) + SUM(B.user_scans) AS User_Reads,
    SUM(B.user_lookups) AS User_Lookups,
    SUM(B.user_updates) AS User_Updates,
    SUM(E.[rows]) AS [row_count],
    CAST(ROUND(((SUM(F.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(F.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(F.total_pages) - SUM(F.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
    sys.indexes A
    LEFT JOIN sys.dm_db_index_usage_stats B ON A.[object_id] = B.[object_id] AND A.index_id = B.index_id AND B.database_id = DB_ID()
    JOIN sys.objects C ON A.[object_id] = C.[object_id]
    JOIN sys.schemas D ON C.[schema_id] = D.[schema_id]
    JOIN sys.partitions E ON A.[object_id] = E.[object_id] AND A.index_id = E.index_id
    JOIN sys.allocation_units F ON E.[partition_id] = F.container_id
WHERE
    C.is_ms_shipped = 0
GROUP BY
    D.[name] + '.' + C.[name],
    A.[name],
    (CASE WHEN A.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + A.[type_desc]
ORDER BY
    1, 2

SELECT

D.[name] + '.' + C.[name] AS ObjectName,

A.[name] AS IndexName,

(CASE WHEN A.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + A.[type_desc] AS IndexType,

MAX(B.last_user_seek) AS last_user_seek,

MAX(COALESCE(B.last_user_seek, B.last_user_scan)) AS last_read,

SUM(B.user_seeks) AS User_Seeks,

SUM(B.user_scans) AS User_Scans,

SUM(B.user_seeks) + SUM(B.user_scans) AS User_Reads,

SUM(B.user_lookups) AS User_Lookups,

SUM(B.user_updates) AS User_Updates,

SUM(E.[rows]) AS [row_count],

CAST(ROUND(((SUM(F.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],

CAST(ROUND(((SUM(F.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb],

CAST(ROUND(((SUM(F.total_pages) - SUM(F.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]

FROM

sys.indexes A

LEFT JOIN sys.dm_db_index_usage_stats B ON A.[object_id] = B.[object_id] AND A.index_id = B.index_id AND B.database_id = DB_ID()

JOIN sys.objects C ON A.[object_id] = C.[object_id]

JOIN sys.schemas D ON C.[schema_id] = D.[schema_id]

JOIN sys.partitions E ON A.[object_id] = E.[object_id] AND A.index_id = E.index_id

JOIN sys.allocation_units F ON E.[partition_id] = F.container_id

WHERE

C.is_ms_shipped = 0

GROUP BY

D.[name] + '.' + C.[name],

A.[name],

(CASE WHEN A.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + A.[type_desc]

ORDER BY

1, 2

Identifying Possible Good Clustered Index Candidates

With the query below, I will help you identify possible better clustered index candidates than the current ones. The metric to identify this is through the number of seek reads between nonclustered and clustered indexes. Remember that this is just to give you an idea of the best candidate for the clustered index. You should not only rely on this query, do your analysis!

To better understand the structure of indexes and what a clustered index is, read the article. Understanding Index Functioning in SQL Server. To better understand performance tuning and understand what is Seek operation, Scan, etc. read the article SQL Server - Introduction to Performance Tuning Study.

SELECT
    B.[name] AS table_name,
    idx.[name] AS clustered_index,
    nc.nonclusteredname AS best_non_clustered,
    c.user_seeks AS clustered_user_seeks,
    nc.user_seeks AS nonclustered_user_seeks,
    c.user_lookups AS clustered_user_lookups
FROM
    sys.indexes idx
    JOIN sys.objects B ON idx.[object_id] = B.[object_id]
    LEFT JOIN sys.dm_db_index_usage_stats c ON idx.[object_id] = c.[object_id] AND idx.index_id = c.index_id AND c.database_id = DB_ID()
    JOIN (
           SELECT
                idx.[object_id],
                idx.[name] AS nonclusteredname,
                ius.user_seeks
           FROM
                sys.indexes idx
                JOIN sys.dm_db_index_usage_stats ius ON idx.[object_id] = ius.[object_id] AND idx.index_id = ius.index_id
           WHERE
                idx.[type_desc] = 'nonclustered' 
                AND ius.user_seeks = ( SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats WHERE [object_id] = ius.[object_id] AND [type_desc] = 'nonclustered' AND database_id = DB_ID() )
                AND ius.database_id = DB_ID()
           GROUP BY
                idx.[object_id],
                idx.[name],
                ius.user_seeks
         ) nc ON nc.[object_id] = idx.[object_id]
WHERE
    idx.[type_desc] IN ( 'clustered', 'heap' )
    AND nc.user_seeks > ( c.user_seeks * 1.50 ) -- 150%
    AND nc.user_seeks >= ( c.user_lookups * 0.75 ) -- 75%
ORDER BY
    nc.user_seeks DESC

SELECT

B.[name] AS table_name,

idx.[name] AS clustered_index,

nc.nonclusteredname AS best_non_clustered,

c.user_seeks AS clustered_user_seeks,

nc.user_seeks AS nonclustered_user_seeks,

c.user_lookups AS clustered_user_lookups

FROM

sys.indexes idx

JOIN sys.objects B ON idx.[object_id] = B.[object_id]

LEFT JOIN sys.dm_db_index_usage_stats c ON idx.[object_id] = c.[object_id] AND idx.index_id = c.index_id AND c.database_id = DB_ID()

JOIN (

SELECT

idx.[object_id],

idx.[name] AS nonclusteredname,

ius.user_seeks

FROM

sys.indexes idx

JOIN sys.dm_db_index_usage_stats ius ON idx.[object_id] = ius.[object_id] AND idx.index_id = ius.index_id

WHERE

idx.[type_desc] = 'nonclustered'

AND ius.user_seeks = ( SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats WHERE [object_id] = ius.[object_id] AND [type_desc] = 'nonclustered' AND database_id = DB_ID() )

AND ius.database_id = DB_ID()

GROUP BY

idx.[object_id],

idx.[name],

ius.user_seeks

) nc ON nc.[object_id] = idx.[object_id]

WHERE

idx.[type_desc] IN ( 'clustered', 'heap' )

AND nc.user_seeks > ( c.user_seeks * 1.50 ) -- 150%

AND nc.user_seeks >= ( c.user_lookups * 0.75 ) -- 75%

ORDER BY

nc.user_seeks DESC

Missing Index Suggestions

Using the query below, you can view SQL Server index suggestions based on the Missing Index statistics. Be wary of these suggestions, as they are not always the best option for creating an index. Review suggestions before creating in the bank.

To better understand performance tuning and understand what is Seek operation, Scan, etc. read the article SQL Server - Introduction to Performance Tuning Study.

SELECT
    db.[name] AS [DatabaseName],
    id.[object_id] AS [ObjectID],
    OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName],
    id.[statement] AS [FullyQualifiedObjectName],
    id.[equality_columns] AS [EqualityColumns],
    id.[inequality_columns] AS [InEqualityColumns],
    id.[included_columns] AS [IncludedColumns],
    gs.[unique_compiles] AS [UniqueCompiles],
    gs.[user_seeks] AS [UserSeeks],
    gs.[user_scans] AS [UserScans],
    gs.[last_user_seek] AS [LastUserSeekTime],
    gs.[last_user_scan] AS [LastUserScanTime],
    gs.[avg_total_user_cost] AS [AvgTotalUserCost],
    gs.[avg_user_impact] AS [AvgUserImpact],
    gs.[user_seeks] * gs.[avg_total_user_cost] * ( gs.[avg_user_impact] * 0.01 ) AS [IndexAdvantage],
    gs.[system_seeks] AS [SystemSeeks],
    gs.[system_scans] AS [SystemScans],
    gs.[last_system_seek] AS [LastSystemSeekTime],
    gs.[last_system_scan] AS [LastSystemScanTime],
    gs.[avg_total_system_cost] AS [AvgTotalSystemCost],
    gs.[avg_system_impact] AS [AvgSystemImpact],
    'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [NVARCHAR](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex],
    CAST(CURRENT_TIMESTAMP AS [SMALLDATETIME]) AS [CollectionDate]
FROM
    [sys].[dm_db_missing_index_group_stats] gs WITH ( NOLOCK )
    JOIN [sys].[dm_db_missing_index_groups] ig WITH ( NOLOCK ) ON gs.[group_handle] = ig.[index_group_handle]
    JOIN [sys].[dm_db_missing_index_details] id WITH ( NOLOCK ) ON ig.[index_handle] = id.[index_handle]
    JOIN [sys].[databases] db WITH ( NOLOCK ) ON db.[database_id] = id.[database_id]
WHERE
    db.[database_id] = DB_ID()
    --AND gs.avg_total_user_cost * ( gs.avg_user_impact / 100.0 ) * ( gs.user_seeks + gs.user_scans ) > 10
ORDER BY
    [IndexAdvantage] DESC
OPTION ( RECOMPILE );

SELECT

db.[name] AS [DatabaseName],

id.[object_id] AS [ObjectID],

OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName],

id.[statement] AS [FullyQualifiedObjectName],

id.[equality_columns] AS [EqualityColumns],

id.[inequality_columns] AS [InEqualityColumns],

id.[included_columns] AS [IncludedColumns],

gs.[unique_compiles] AS [UniqueCompiles],

gs.[user_seeks] AS [UserSeeks],

gs.[user_scans] AS [UserScans],

gs.[last_user_seek] AS [LastUserSeekTime],

gs.[last_user_scan] AS [LastUserScanTime],

gs.[avg_total_user_cost] AS [AvgTotalUserCost],

gs.[avg_user_impact] AS [AvgUserImpact],

gs.[user_seeks] * gs.[avg_total_user_cost] * ( gs.[avg_user_impact] * 0.01 ) AS [IndexAdvantage],

gs.[system_seeks] AS [SystemSeeks],

gs.[system_scans] AS [SystemScans],

gs.[last_system_seek] AS [LastSystemSeekTime],

gs.[last_system_scan] AS [LastSystemScanTime],

gs.[avg_total_system_cost] AS [AvgTotalSystemCost],

gs.[avg_system_impact] AS [AvgSystemImpact],

'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [NVARCHAR](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex],

CAST(CURRENT_TIMESTAMP AS [SMALLDATETIME]) AS [CollectionDate]

FROM

[sys].[dm_db_missing_index_group_stats] gs WITH ( NOLOCK )

JOIN [sys].[dm_db_missing_index_groups] ig WITH ( NOLOCK ) ON gs.[group_handle] = ig.[index_group_handle]

JOIN [sys].[dm_db_missing_index_details] id WITH ( NOLOCK ) ON ig.[index_handle] = id.[index_handle]

JOIN [sys].[databases] db WITH ( NOLOCK ) ON db.[database_id] = id.[database_id]

WHERE

db.[database_id] = DB_ID()

--AND gs.avg_total_user_cost * ( gs.avg_user_impact / 100.0 ) * ( gs.user_seeks + gs.user_scans ) > 10

ORDER BY

[IndexAdvantage] DESC

OPTION ( RECOMPILE );

Statistics for more than 7 days without updating

With the query below, we will do some query queries related to column and index statistics, and we will be able to view statistics that have been over 7 days without updates. Outdated statistics can cause many performance issues, but it is not necessary to update statistics if there have been no data updates.

To better understand how statistics influence the execution of your queries, read the article. SQL Server - Introduction to Performance Tuning Study.

SELECT
    D.last_updated AS [LastUpdate],
    B.[name] AS [Table],
    A.[name] AS [Statistic],
    D.modification_counter AS ModificationCounter,
    'UPDATE STATISTICS [' + E.[name] + '].[' + B.[name] + '] [' + A.[name] + '] WITH FULLSCAN' AS UpdateStatisticsCommand
FROM
    sys.stats A
    JOIN sys.objects B ON A.[object_id] = B.[object_id]
    JOIN sys.indexes C ON C.[object_id] = B.[object_id] AND A.[name] = C.[name]
    OUTER APPLY sys.dm_db_stats_properties(A.[object_id], A.stats_id) D
    JOIN sys.schemas E ON B.[schema_id] = E.[schema_id]
WHERE
    D.last_updated < GETDATE() - 7
    AND E.[name] NOT IN ( 'sys', 'dtp' )
    AND B.[name] NOT LIKE '[_]%'
    AND D.modification_counter > 1000
ORDER BY
    D.modification_counter DESC

SELECT

D.last_updated AS [LastUpdate],

B.[name] AS [Table],

A.[name] AS [Statistic],

D.modification_counter AS ModificationCounter,

'UPDATE STATISTICS [' + E.[name] + '].[' + B.[name] + '] [' + A.[name] + '] WITH FULLSCAN' AS UpdateStatisticsCommand

FROM

sys.stats A

JOIN sys.objects B ON A.[object_id] = B.[object_id]

JOIN sys.indexes C ON C.[object_id] = B.[object_id] AND A.[name] = C.[name]

OUTER APPLY sys.dm_db_stats_properties(A.[object_id], A.stats_id) D

JOIN sys.schemas E ON B.[schema_id] = E.[schema_id]

WHERE

D.last_updated < GETDATE() - 7

AND E.[name] NOT IN ( 'sys', 'dtp' )

AND B.[name] NOT LIKE '[_]%'

AND D.modification_counter > 1000

ORDER BY

D.modification_counter DESC

Script table indexes

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
Copyright (c) 2018 SQL Workbooks LLC
Permission is hereby granted, free of charge, to any person 
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without 
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom 
the Software is furnished to do so, subject to the following 
conditions:
The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 
OTHER DEALINGS IN THE SOFTWARE.

https://gist.github.com/LitKnd

*****************************************************************************/


SELECT 
    DB_NAME() AS [database_name],
    sc.[name] + N'.' + t.[name] AS table_name,
    si.index_id,
    si.[name] AS index_name,
    si.[type_desc],
    (SELECT MAX(user_reads) 
        FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
    last_user_update,
    CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'
    ELSE 
        CASE is_primary_key WHEN 1 THEN
            N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
                CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '
            ELSE N'CREATE ' + 
                CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
                CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' + (CASE WHEN si.[type] IN (4, 5) THEN 'COLUMNSTORE ' ELSE '' END) +
                N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '
        END +
        /* key def */ (CASE WHEN si.[type] IN (0, 1, 2) THEN N'(' + key_definition + N')' ELSE '' END) +
        /* includes */ (CASE WHEN si.[type] IN (0, 1, 2) THEN CASE WHEN include_definition IS NOT NULL THEN 
            N' INCLUDE (' + include_definition + N')'
            ELSE N''
        END ELSE '' END) +
        /* filters */ CASE WHEN filter_definition IS NOT NULL THEN 
            N' WHERE ' + filter_definition ELSE N''
        END +
        /* with clause - compression goes here */
        CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL 
            THEN N' WITH (' +
                CASE WHEN row_compression_partition_list IS NOT NULL THEN
                    N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
                ELSE N'' END +
                CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +
                CASE WHEN page_compression_partition_list IS NOT NULL THEN
                    N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
                ELSE N'' END
            + N')'
            ELSE N''
        END +
        /* ON where? filegroup? partition scheme? */
        ' ON ' + CASE WHEN psc.name is null 
            THEN ISNULL(QUOTENAME(fg.name),N'')
            ELSE psc.name + N' (' + partitioning_column.column_name + N')' 
            END
        + N';'
    END AS index_create_statement,
    partition_sums.reserved_in_row_GB,
    partition_sums.reserved_LOB_GB,
    partition_sums.row_count,
    stat.user_seeks,
    stat.user_scans,
    stat.user_lookups,
    user_updates AS queries_that_modified,
    partition_sums.partition_count,
    si.allow_page_locks,
    si.allow_row_locks,
    si.is_hypothetical,
    si.has_filter,
    si.fill_factor,
    si.is_unique,
    ISNULL(pf.name, '/* Not partitioned */') AS partition_function,
    ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
    t.create_date AS table_created_date,
    t.modify_date AS table_modify_date
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON 
    stat.database_id = DB_ID() 
    and si.object_id=stat.object_id 
    and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + QUOTENAME(c.name) +
        CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.key_ordinal > 0
    ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
    SELECT MAX(QUOTENAME(c.name)) AS column_name
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + QUOTENAME(c.name)
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.is_included_column = 1
    ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY ( 
    SELECT 
        COUNT(*) AS partition_count,
        CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
        CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
        SUM(ps.row_count) AS row_count
    FROM sys.partitions AS p
    JOIN sys.dm_db_partition_stats AS ps ON
        p.partition_id=ps.partition_id
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
    ) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
    FROM sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 1
    ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
    FROM sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 2
    ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE 
    si.type IN (0,1,2,4,5) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
    OPTION (RECOMPILE);
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

/*****************************************************************************

MIT License, http://www.opensource.org/licenses/mit-license.php

Contact: [email protected]

Permission is hereby granted, free of charge, to any person

obtaining a copy of this software and associated documentation

files (the "Software"), to deal in the Software without

restriction, including without limitation the rights to use,

copy, modify, merge, publish, distribute, sublicense, and/or

sell copies of the Software, and to permit persons to whom

the Software is furnished to do so, subject to the following

conditions:

The above copyright notice and this permission notice shall be

included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,

EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES

OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND

NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT

HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,

WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING

FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR

OTHER DEALINGS IN THE SOFTWARE.

https://gist.github.com/LitKnd

*****************************************************************************/

SELECT

DB_NAME() AS [database_name],

sc.[name] + N'.' + t.[name] AS table_name,

si.index_id,

si.[name] AS index_name,

si.[type_desc],

(SELECT MAX(user_reads)

FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,

last_user_update,

CASE si.index_id WHEN 0 THEN N'/* No create statement (Heap) */'

ELSE

CASE is_primary_key WHEN 1 THEN

N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +

CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED '

ELSE N'CREATE ' +

CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +

CASE WHEN si.index_id > 1 THEN N'NON' ELSE N'' END + N'CLUSTERED ' + (CASE WHEN si.[type] IN (4, 5) THEN 'COLUMNSTORE ' ELSE '' END) +

N'INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' '

END +

/* key def */ (CASE WHEN si.[type] IN (0, 1, 2) THEN N'(' + key_definition + N')' ELSE '' END) +

/* includes */ (CASE WHEN si.[type] IN (0, 1, 2) THEN CASE WHEN include_definition IS NOT NULL THEN

N' INCLUDE (' + include_definition + N')'

ELSE N''

END ELSE '' END) +

/* filters */ CASE WHEN filter_definition IS NOT NULL THEN

N' WHERE ' + filter_definition ELSE N''

END +

/* with clause - compression goes here */

CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL

THEN N' WITH (' +

CASE WHEN row_compression_partition_list IS NOT NULL THEN

N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END

ELSE N'' END +

CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', ' ELSE N'' END +

CASE WHEN page_compression_partition_list IS NOT NULL THEN

N'DATA_COMPRESSION = PAGE ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END

ELSE N'' END

+ N')'

ELSE N''

END +

/* ON where? filegroup? partition scheme? */

' ON ' + CASE WHEN psc.name is null

THEN ISNULL(QUOTENAME(fg.name),N'')

ELSE psc.name + N' (' + partitioning_column.column_name + N')'

END

+ N';'

END AS index_create_statement,

partition_sums.reserved_in_row_GB,

partition_sums.reserved_LOB_GB,

partition_sums.row_count,

stat.user_seeks,

stat.user_scans,

stat.user_lookups,

user_updates AS queries_that_modified,

partition_sums.partition_count,

si.allow_page_locks,

si.allow_row_locks,

si.is_hypothetical,

si.has_filter,

si.fill_factor,

si.is_unique,

ISNULL(pf.name, '/* Not partitioned */') AS partition_function,

ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,

t.create_date AS table_created_date,

t.modify_date AS table_modify_date

FROM sys.indexes AS si

JOIN sys.tables AS t ON si.object_id=t.object_id

JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id

LEFT JOIN sys.dm_db_index_usage_stats AS stat ON

stat.database_id = DB_ID()

and si.object_id=stat.object_id

and si.index_id=stat.index_id

LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id

LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id

LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id

/* Key list */ OUTER APPLY ( SELECT STUFF (

(SELECT N', ' + QUOTENAME(c.name) +

CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END

FROM sys.index_columns AS ic

JOIN sys.columns AS c ON

ic.column_id=c.column_id

and ic.object_id=c.object_id

WHERE ic.object_id = si.object_id

and ic.index_id=si.index_id

and ic.key_ordinal > 0

ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS keys ( key_definition )

/* Partitioning Ordinal */ OUTER APPLY (

SELECT MAX(QUOTENAME(c.name)) AS column_name

FROM sys.index_columns AS ic

JOIN sys.columns AS c ON

ic.column_id=c.column_id

and ic.object_id=c.object_id

WHERE ic.object_id = si.object_id

and ic.index_id=si.index_id

and ic.partition_ordinal = 1) AS partitioning_column

/* Include list */ OUTER APPLY ( SELECT STUFF (

(SELECT N', ' + QUOTENAME(c.name)

FROM sys.index_columns AS ic

JOIN sys.columns AS c ON

ic.column_id=c.column_id

and ic.object_id=c.object_id

WHERE ic.object_id = si.object_id

and ic.index_id=si.index_id

and ic.is_included_column = 1

ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )

/* Partitions */ OUTER APPLY (

SELECT

COUNT(*) AS partition_count,

CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,

CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,

SUM(ps.row_count) AS row_count

FROM sys.partitions AS p

JOIN sys.dm_db_partition_stats AS ps ON

p.partition_id=ps.partition_id

WHERE p.object_id = si.object_id

and p.index_id=si.index_id

) AS partition_sums

/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (

(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))

FROM sys.partitions AS p

WHERE p.object_id = si.object_id

and p.index_id=si.index_id

and p.data_compression = 1

ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )

/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (

(SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))

FROM sys.partitions AS p

WHERE p.object_id = si.object_id

and p.index_id=si.index_id

and p.data_compression = 2

ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )

WHERE

si.type IN (0,1,2,4,5) /* heap, clustered, nonclustered */

ORDER BY table_name, si.index_id

OPTION (RECOMPILE);

Lock, Block and Deadlock

In this session, we will learn how to identify blocks, locks, deadlocks, what each type of block is, and how we can prevent it from occurring in the instance.
View scripts

Identify locks (sessions that are locking resources but have no sessions being locked yet)

SELECT
    A.request_session_id AS session_id,
    COALESCE(G.start_time, F.last_request_start_time) AS start_time,
    COALESCE(G.open_transaction_count, F.open_transaction_count) AS open_transaction_count,
    A.resource_database_id,
    DB_NAME(A.resource_database_id) AS dbname,
    (CASE WHEN A.resource_type = 'OBJECT' THEN D.[name] ELSE E.[name] END) AS ObjectName,
    (CASE WHEN A.resource_type = 'OBJECT' THEN D.is_ms_shipped ELSE E.is_ms_shipped END) AS is_ms_shipped,
    --B.index_id,
    --C.[name] AS index_name,
    --A.resource_type,
    --A.resource_description,
    --A.resource_associated_entity_id,
    A.request_mode,
    A.request_status,
    F.login_name,
    F.[program_name],
    F.[host_name],
    G.blocking_session_id
FROM
    sys.dm_tran_locks A WITH(NOLOCK)
    LEFT JOIN sys.partitions B WITH(NOLOCK) ON B.hobt_id = A.resource_associated_entity_id
    LEFT JOIN sys.indexes C WITH(NOLOCK) ON C.[object_id] = B.[object_id] AND C.index_id = B.index_id
    LEFT JOIN sys.objects D WITH(NOLOCK) ON A.resource_associated_entity_id = D.[object_id]
    LEFT JOIN sys.objects E WITH(NOLOCK) ON B.[object_id] = E.[object_id]
    LEFT JOIN sys.dm_exec_sessions F WITH(NOLOCK) ON A.request_session_id = F.session_id
    LEFT JOIN sys.dm_exec_requests G WITH(NOLOCK) ON A.request_session_id = G.session_id
WHERE
    A.resource_associated_entity_id > 0
    AND A.resource_database_id = DB_ID()
    AND A.resource_type = 'OBJECT'
    AND (CASE WHEN A.resource_type = 'OBJECT' THEN D.is_ms_shipped ELSE E.is_ms_shipped END) = 0
ORDER BY
    A.request_session_id,
    A.resource_associated_entity_id

SELECT

A.request_session_id AS session_id,

COALESCE(G.start_time, F.last_request_start_time) AS start_time,

COALESCE(G.open_transaction_count, F.open_transaction_count) AS open_transaction_count,

A.resource_database_id,

DB_NAME(A.resource_database_id) AS dbname,

(CASE WHEN A.resource_type = 'OBJECT' THEN D.[name] ELSE E.[name] END) AS ObjectName,

(CASE WHEN A.resource_type = 'OBJECT' THEN D.is_ms_shipped ELSE E.is_ms_shipped END) AS is_ms_shipped,

--B.index_id,

--C.[name] AS index_name,

--A.resource_type,

--A.resource_description,

--A.resource_associated_entity_id,

A.request_mode,

A.request_status,

F.login_name,

F.[program_name],

F.[host_name],

G.blocking_session_id

FROM

sys.dm_tran_locks A WITH(NOLOCK)

LEFT JOIN sys.partitions B WITH(NOLOCK) ON B.hobt_id = A.resource_associated_entity_id

LEFT JOIN sys.indexes C WITH(NOLOCK) ON C.[object_id] = B.[object_id] AND C.index_id = B.index_id

LEFT JOIN sys.objects D WITH(NOLOCK) ON A.resource_associated_entity_id = D.[object_id]

LEFT JOIN sys.objects E WITH(NOLOCK) ON B.[object_id] = E.[object_id]

LEFT JOIN sys.dm_exec_sessions F WITH(NOLOCK) ON A.request_session_id = F.session_id

LEFT JOIN sys.dm_exec_requests G WITH(NOLOCK) ON A.request_session_id = G.session_id

WHERE

A.resource_associated_entity_id > 0

AND A.resource_database_id = DB_ID()

AND A.resource_type = 'OBJECT'

AND (CASE WHEN A.resource_type = 'OBJECT' THEN D.is_ms_shipped ELSE E.is_ms_shipped END) = 0

ORDER BY

A.request_session_id,

A.resource_associated_entity_id

Identify blocks (sessions that are crashing others)

For a better understanding of the block and lock terms, as well as the script available, I suggest reading the article. SQL Server - How to identify locks, blocks, and blocking sessions.

DECLARE @Monitoramento_Locks TABLE
(
    [nested_level] INT,
    [session_id] SMALLINT,
    [wait_info] NVARCHAR(4000),
    [wait_time_ms] BIGINT,
    [blocking_session_id] SMALLINT,
    [blocked_session_count] INT,
    [open_transaction_count] INT,
    [sql_text] XML,
    [sql_command] XML,
    [total_elapsed_time] INT,
    [deadlock_priority] INT,
    [transaction_isolation_level] VARCHAR(50),
    [last_request_start_time] DATETIME,
    [login_name] NVARCHAR(128),
    [nt_user_name] NVARCHAR(128),
    [original_login_name] NVARCHAR(128),
    [host_name] NVARCHAR(128),
    [program_name] NVARCHAR(128)
)

INSERT INTO @Monitoramento_Locks
SELECT
    NULL AS nested_level,
    A.session_id AS session_id,
    '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE 
        WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999)
        WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'
        ELSE ''
    END), '') AS wait_info,
    COALESCE(E.wait_duration_ms, B.wait_time) AS wait_time_ms,
    NULLIF(B.blocking_session_id, 0) AS blocking_session_id,
    COALESCE(F.blocked_session_count, 0) AS blocked_session_count,
    A.open_transaction_count,
    CAST('<?query --' + CHAR(10) + (
    SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE
                                                                        WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)
                                                                        ELSE B.statement_end_offset
                                                                    END
                                                                    ) - B.statement_start_offset
                                                                ) / 2 + 1
                    )
    ) + CHAR(10) + '--?>' AS XML) AS sql_text,
    CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,
    A.total_elapsed_time,
    A.[deadlock_priority],
    (CASE B.transaction_isolation_level
        WHEN 0 THEN 'Unspecified' 
        WHEN 1 THEN 'ReadUncommitted' 
        WHEN 2 THEN 'ReadCommitted' 
        WHEN 3 THEN 'Repeatable' 
        WHEN 4 THEN 'Serializable' 
        WHEN 5 THEN 'Snapshot'
    END) AS transaction_isolation_level,
    A.last_request_start_time,
    A.login_name,
    A.nt_user_name,
    A.original_login_name,
    A.[host_name],
    (CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) +  ')' ELSE A.[program_name] END) AS [program_name]
FROM
    sys.dm_exec_sessions AS A WITH (NOLOCK)
    LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
    LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)
    LEFT JOIN (
        SELECT
            session_id, 
            wait_type,
            wait_duration_ms,
            resource_description,
            ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking
        FROM 
            sys.dm_os_waiting_tasks
    ) E ON A.session_id = E.session_id AND E.Ranking = 1
    LEFT JOIN (
        SELECT
            blocking_session_id,
            COUNT(*) AS blocked_session_count
        FROM
            sys.dm_exec_requests
        WHERE
            blocking_session_id <> 0
        GROUP BY
            blocking_session_id
    ) F ON A.session_id = F.blocking_session_id
    LEFT JOIN sys.sysprocesses AS G WITH(NOLOCK) ON A.session_id = G.spid
    OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], G.[sql_handle])) AS X
WHERE
    A.session_id > 50
    AND A.session_id <> @@SPID
    AND (
        (NULLIF(B.blocking_session_id, 0) IS NOT NULL OR COALESCE(F.blocked_session_count, 0) > 0)
        OR (A.session_id IN (SELECT NULLIF(blocking_session_id, 0) FROM sys.dm_exec_requests))
    )


------------------------------------------------
-- Gera o nível dos locks
------------------------------------------------

UPDATE @Monitoramento_Locks
SET nested_level = 1
WHERE blocking_session_id IS NULL


DECLARE @Contador INT = 2

WHILE(EXISTS(SELECT NULL FROM @Monitoramento_Locks WHERE nested_level IS NULL) AND @Contador < 50)
BEGIN
        

    UPDATE A
    SET 
        A.nested_level = @Contador
    FROM 
        @Monitoramento_Locks A
        JOIN @Monitoramento_Locks B ON A.blocking_session_id = B.session_id
    WHERE 
        A.nested_level IS NULL
        AND B.nested_level = (@Contador - 1)


    SET @Contador += 1


END


UPDATE @Monitoramento_Locks
SET nested_level = @Contador
WHERE nested_level IS NULL


SELECT * 
FROM @Monitoramento_Locks
ORDER BY nested_level, blocked_session_count DESC, blocking_session_id, wait_time_ms DESC

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

DECLARE @Monitoramento_Locks TABLE

(

[nested_level] INT,

[session_id] SMALLINT,

[wait_info] NVARCHAR(4000),

[wait_time_ms] BIGINT,

[blocking_session_id] SMALLINT,

[blocked_session_count] INT,

[open_transaction_count] INT,

[sql_text] XML,

[sql_command] XML,

[total_elapsed_time] INT,

[deadlock_priority] INT,

[transaction_isolation_level] VARCHAR(50),

[last_request_start_time] DATETIME,

[login_name] NVARCHAR(128),

[nt_user_name] NVARCHAR(128),

[original_login_name] NVARCHAR(128),

[host_name] NVARCHAR(128),

[program_name] NVARCHAR(128)

)

INSERT INTO @Monitoramento_Locks

SELECT

NULL AS nested_level,

A.session_id AS session_id,

'(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE

WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']'

ELSE ''

END), '') AS wait_info,

COALESCE(E.wait_duration_ms, B.wait_time) AS wait_time_ms,

NULLIF(B.blocking_session_id, 0) AS blocking_session_id,

COALESCE(F.blocked_session_count, 0) AS blocked_session_count,

A.open_transaction_count,

CAST('<?query --' + CHAR(10) + (

SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE

WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2)

ELSE B.statement_end_offset

END

) - B.statement_start_offset

) / 2 + 1

)

) + CHAR(10) + '--?>' AS XML) AS sql_text,

CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command,

A.total_elapsed_time,

A.[deadlock_priority],

(CASE B.transaction_isolation_level

WHEN 0 THEN 'Unspecified'

WHEN 1 THEN 'ReadUncommitted'

WHEN 2 THEN 'ReadCommitted'

WHEN 3 THEN 'Repeatable'

WHEN 4 THEN 'Serializable'

WHEN 5 THEN 'Snapshot'

END) AS transaction_isolation_level,

A.last_request_start_time,

A.login_name,

A.nt_user_name,

A.original_login_name,

A.[host_name],

(CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name]

FROM

sys.dm_exec_sessions AS A WITH (NOLOCK)

LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id

LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10)

LEFT JOIN (

SELECT

session_id,

wait_type,

wait_duration_ms,

resource_description,

ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking

FROM

sys.dm_os_waiting_tasks

) E ON A.session_id = E.session_id AND E.Ranking = 1

LEFT JOIN (

SELECT

blocking_session_id,

COUNT(*) AS blocked_session_count

FROM

sys.dm_exec_requests

WHERE

blocking_session_id <> 0

GROUP BY

blocking_session_id

) F ON A.session_id = F.blocking_session_id

LEFT JOIN sys.sysprocesses AS G WITH(NOLOCK) ON A.session_id = G.spid

OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], G.[sql_handle])) AS X

WHERE

A.session_id > 50

AND A.session_id <> @@SPID

AND (

(NULLIF(B.blocking_session_id, 0) IS NOT NULL OR COALESCE(F.blocked_session_count, 0) > 0)

OR (A.session_id IN (SELECT NULLIF(blocking_session_id, 0) FROM sys.dm_exec_requests))

)

------------------------------------------------

-- Gera o nível dos locks

------------------------------------------------

UPDATE @Monitoramento_Locks

SET nested_level = 1

WHERE blocking_session_id IS NULL

DECLARE @Contador INT = 2

WHILE(EXISTS(SELECT NULL FROM @Monitoramento_Locks WHERE nested_level IS NULL) AND @Contador < 50)

BEGIN

UPDATE A

SET

A.nested_level = @Contador

FROM

@Monitoramento_Locks A

JOIN @Monitoramento_Locks B ON A.blocking_session_id = B.session_id

WHERE

A.nested_level IS NULL

AND B.nested_level = (@Contador - 1)

SET @Contador += 1

END

UPDATE @Monitoramento_Locks

SET nested_level = @Contador

WHERE nested_level IS NULL

SELECT *

FROM @Monitoramento_Locks

ORDER BY nested_level, blocked_session_count DESC, blocking_session_id, wait_time_ms DESC

Identify Deadlock Events

Using Extended Events, which is already enabled by default in SQL Server, System_Health, we can identify Deadlock events that have recently occurred in the instance. To better understand the use of this script, I recommend reading the article. SQL Server - How to generate a deadlock history for failure analysis in routines.

DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())

SELECT
    DATEADD(HOUR, @TimeZone, xed.value('@timestamp', 'datetime2(3)')) AS CreationDate,
    xed.query('.') AS XEvent
FROM
(
    SELECT 
        CAST(st.[target_data] AS XML) AS TargetData
    FROM 
        sys.dm_xe_session_targets AS st
        INNER JOIN sys.dm_xe_sessions AS s ON s.[address] = st.event_session_address
    WHERE 
        s.[name] = N'system_health'
        AND st.target_name = N'ring_buffer'
) AS [Data]
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY 
    CreationDate DESC

DECLARE @TimeZone INT = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())

SELECT

DATEADD(HOUR, @TimeZone, xed.value('@timestamp', 'datetime2(3)')) AS CreationDate,

xed.query('.') AS XEvent

FROM

(

SELECT

CAST(st.[target_data] AS XML) AS TargetData

FROM

sys.dm_xe_session_targets AS st

INNER JOIN sys.dm_xe_sessions AS s ON s.[address] = st.event_session_address

WHERE

s.[name] = N'system_health'

AND st.target_name = N'ring_buffer'

) AS [Data]

CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)

ORDER BY

CreationDate DESC

In this session, I will help you identify user permissions on objects, database roles, server roles, and instance level permissions in the environment.

As a further reading, I recommend the articles:

View scripts

Database roles

SELECT
    C.[name] AS Ds_Usuario,
    B.[name] AS Ds_Database_Role
FROM 
    sys.database_role_members A
    JOIN sys.database_principals B ON A.role_principal_id = B.principal_id
    JOIN sys.database_principals C ON A.member_principal_id = C.principal_id

SELECT

C.[name] AS Ds_Usuario,

B.[name] AS Ds_Database_Role

FROM

sys.database_role_members A

JOIN sys.database_principals B ON A.role_principal_id = B.principal_id

JOIN sys.database_principals C ON A.member_principal_id = C.principal_id

Database Level Permissions

SELECT
    A.class_desc AS Ds_Tipo_Permissao, 
    A.[permission_name] AS Ds_Permissao,
    A.state_desc AS Ds_Operacao,
    B.[name] AS Ds_Usuario_Permissao,
    C.[name] AS Ds_Login_Permissao,
    D.[name] AS Ds_Objeto
FROM 
    sys.database_permissions A
    JOIN sys.database_principals B ON A.grantee_principal_id = B.principal_id
    LEFT JOIN sys.server_principals C ON B.[sid] = C.[sid]
    LEFT JOIN sys.objects D ON A.major_id = D.[object_id]
WHERE
    A.major_id >= 0

SELECT

A.class_desc AS Ds_Tipo_Permissao,

A.[permission_name] AS Ds_Permissao,

A.state_desc AS Ds_Operacao,

B.[name] AS Ds_Usuario_Permissao,

C.[name] AS Ds_Login_Permissao,

D.[name] AS Ds_Objeto

FROM

sys.database_permissions A

JOIN sys.database_principals B ON A.grantee_principal_id = B.principal_id

LEFT JOIN sys.server_principals C ON B.[sid] = C.[sid]

LEFT JOIN sys.objects D ON A.major_id = D.[object_id]

WHERE

A.major_id >= 0

Server roles

SELECT 
    B.[name] AS Ds_Usuario,
    C.[name] AS Ds_Server_Role
FROM 
    sys.server_role_members A
    JOIN sys.server_principals B ON A.member_principal_id = B.principal_id
    JOIN sys.server_principals C ON A.role_principal_id = C.principal_id

SELECT

B.[name] AS Ds_Usuario,

C.[name] AS Ds_Server_Role

FROM

sys.server_role_members A

JOIN sys.server_principals B ON A.member_principal_id = B.principal_id

JOIN sys.server_principals C ON A.role_principal_id = C.principal_id

Instance Level Permissions

SELECT
    A.class_desc AS Ds_Tipo_Permissao,
    A.state_desc AS Ds_Tipo_Operacao,
    A.[permission_name] AS Ds_Permissao,
    B.[name] AS Ds_Login,
    B.[type_desc] AS Ds_Tipo_Login
FROM 
    sys.server_permissions A
    JOIN sys.server_principals B ON A.grantee_principal_id = B.principal_id
WHERE
    B.[name] NOT LIKE '##%'
ORDER BY
    B.[name],
    A.[permission_name]

SELECT

A.class_desc AS Ds_Tipo_Permissao,

A.state_desc AS Ds_Tipo_Operacao,

A.[permission_name] AS Ds_Permissao,

B.[name] AS Ds_Login,

B.[type_desc] AS Ds_Tipo_Login

FROM

sys.server_permissions A

JOIN sys.server_principals B ON A.grantee_principal_id = B.principal_id

WHERE

B.[name] NOT LIKE '##%'

ORDER BY

B.[name],

A.[permission_name]

Disk Space

View scripts

Tables Size

With the script below, we will be able to view the size allocated on disk by each database table. Further reading: SQL Server - How to identify and monitor total free disk space used by database datafiles

SELECT 
    s.[name] AS [schema],
    t.[name] AS [table_name],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM 
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name], 
    p.[rows]
ORDER BY 
    [size_mb] DESC

SELECT

s.[name] AS [schema],

t.[name] AS [table_name],

p.[rows] AS [row_count],

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb],

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]

FROM

sys.tables t

JOIN sys.indexes i ON t.[object_id] = i.[object_id]

JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id

JOIN sys.allocation_units a ON p.[partition_id] = a.container_id

LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

WHERE

t.is_ms_shipped = 0

AND i.[object_id] > 255

GROUP BY

t.[name],

s.[name],

p.[rows]

ORDER BY

[size_mb] DESC

Index Size

Further reading: SQL Server - How to identify and monitor total free disk space used by database datafiles

SELECT 
    s.[name] AS [schema],
    t.[name] AS [table_name],
    i.[name] AS [index_name],
    i.[type_desc],
    p.[rows] AS [row_count],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
    sys.tables t
    JOIN sys.indexes i ON t.[object_id] = i.[object_id]
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE 
    t.is_ms_shipped = 0
    AND i.[object_id] > 255 
GROUP BY
    t.[name], 
    s.[name],
    i.[name],
    i.[type_desc],
    p.[rows]
ORDER BY 
    [size_mb] DESC

SELECT

s.[name] AS [schema],

t.[name] AS [table_name],

i.[name] AS [index_name],

i.[type_desc],

p.[rows] AS [row_count],

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb],

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]

FROM

sys.tables t

JOIN sys.indexes i ON t.[object_id] = i.[object_id]

JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id

JOIN sys.allocation_units a ON p.[partition_id] = a.container_id

LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]

WHERE

t.is_ms_shipped = 0

AND i.[object_id] > 255

GROUP BY

t.[name],

s.[name],

i.[name],

i.[type_desc],

p.[rows]

ORDER BY

[size_mb] DESC

Data and log file size (datafiles and logfiles)

Further reading: SQL Server - How to identify and monitor total free disk space used by database datafiles

IF (OBJECT_ID('tempdb..#Datafile_Size ') IS NOT NULL) DROP TABLE #Datafile_Size
SELECT
    B.database_id AS database_id,
    B.[name] AS [database_name],
    A.state_desc,
    A.[type_desc],
    A.[file_id],
    A.[name],
    A.physical_name,
    CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,
    CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,
    CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,
    CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,
    CAST(
        (CASE
        WHEN A.growth <= 0 THEN A.size / 128 / 1024.0
            WHEN A.max_size <= 0 THEN C.total_bytes / 1073741824.0
            WHEN A.max_size / 128 / 1024.0 > C.total_bytes / 1073741824.0 THEN C.total_bytes / 1073741824.0
            ELSE A.max_size / 128 / 1024.0 
        END) AS NUMERIC(18, 2)) AS max_real_size_GB,
    CAST(NULL AS NUMERIC(18, 2)) AS free_space_GB,
    (CASE WHEN A.is_percent_growth = 1 THEN A.growth ELSE CAST(A.growth / 128 AS NUMERIC(18, 2)) END) AS growth_MB,
    A.is_percent_growth,
    (CASE WHEN A.growth <= 0 THEN 0 ELSE 1 END) AS is_autogrowth_enabled,
    CAST(NULL AS NUMERIC(18, 2)) AS percent_used,
    CAST(NULL AS INT) AS growth_times
INTO
    #Datafile_Size 
FROM
    sys.master_files        A   WITH(NOLOCK)
    JOIN sys.databases      B   WITH(NOLOCK)    ON  A.database_id = B.database_id
    CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.[file_id]) C

    
UPDATE A
SET
    A.free_space_GB = (
    (CASE 
        WHEN max_size_GB <= 0 THEN A.disk_free_size_GB
        WHEN max_real_size_GB > disk_free_size_GB THEN A.disk_free_size_GB 
        ELSE max_real_size_GB - size_GB
    END)),
    A.percent_used = (size_GB / (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END)) * 100
FROM 
    #Datafile_Size A
    

UPDATE A
SET
    A.growth_times = 
    (CASE 
        WHEN A.growth_MB <= 0 THEN 0 
        WHEN A.is_percent_growth = 0 THEN (A.max_real_size_GB - A.size_GB) / (A.growth_MB / 1024.0) 
        ELSE NULL 
    END)
FROM 
    #Datafile_Size A


SELECT * 
FROM #Datafile_Size

IF (OBJECT_ID('tempdb..#Datafile_Size ') IS NOT NULL) DROP TABLE #Datafile_Size

SELECT

B.database_id AS database_id,

B.[name] AS [database_name],

A.state_desc,

A.[type_desc],

A.[file_id],

A.[name],

A.physical_name,

CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,

CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,

CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,

CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,

CAST(

(CASE

WHEN A.growth <= 0 THEN A.size / 128 / 1024.0

WHEN A.max_size <= 0 THEN C.total_bytes</

General information

Databases and Settings Overview

Queries that are currently running

Check the main instance parameters

Identifies the transaction log usage of each database in the instance (equivalent to DBCC SQLPERF (LOGSPACE))

Identify and try to fix orphaned users

Identifies when the SQL Server service started

Identifies DDL and DCL operations performed on the instance.

Identifies when backups were generated or restored

Identify auto growth events

Identify DBCC Command Execution

Tables and Indexes

Identify uncompressed tables and indexes

Identify Index Fragmentation

Identify HEAP tables (without clustered index)

Using Indexes

Identifying Possible Good Clustered Index Candidates

Missing Index Suggestions

Statistics for more than 7 days without updating

Script table indexes

Lock, Block and Deadlock

Identify locks (sessions that are locking resources but have no sessions being locked yet)

Identify blocks (sessions that are crashing others)

Identify Deadlock Events

All rights reserved.

Database roles

Database Level Permissions

Server roles

Instance Level Permissions

Disk Space

Tables Size

Index Size

Data and log file size (datafiles and logfiles)