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

Reading Time: 42 minutes

Speak 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

Other Languages

Subscribe to a blog by email

Blog Views

Categories

Azure Banking Admin Course

SQL Server Security Course

Microsoft MVP Data Platform

My Certifications

FREE check-up of your BD!

Posts Archive

Recent Posts

SQL Server - Useful DBA Queries You Always Have to Look for on the Internet

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