SQL Server - Consultas úteis do dia a dia do DBA que você sempre tem que ficar procurando na Internet

Post Views 16,110 views

Neste artigo

Reading time 42 minutes

Hey guys!
Nesse artigo eu gostaria de compartihar com vocês, vários scripts úteis do dia a dia do DBA que você sempre tem que ficar procurando na Internet quando precisa fazer uma determinada consulta. A minha ideia aqui é facilitar a sua vida e ter uma artigo com vários scripts, de diversas finalidades, para você favoritar no seu navegador e ter sempre as informaçõs que deseja aqui 🙂

Sem mais enrolação, vamos ao que interessa!

Informações gerais

Visualizar scripts

Visão geral dos databases e configurações

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;

Consultas que estão em execução atualmente

Para saber mais sobre essa rotina, veja o meu artigo SQL Server – Query para retornar as consultas em execução (sp_WhoIsActive sem consumir TempDB) ou o SQL Server – Utilizando a SP WhoIsActive para identificar Locks, Blocks, Queries Lentas, Queries em Execução e muito mais.

Versão SQL Server 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))

Versão SQL Server 2008 e anteriores

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))

Verifica os principais parâmetros da instância

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]

Identifica o uso da transaction log de cada database na instância (equivalente ao 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

Identifica e tenta corrigir usuários órfãos

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]

Identifica quando o serviço do SQL Server foi iniciado

Utilizando a DMV sys.dm_os_sys_info conseguimos identificar a data em que o serviço do SQL Server foi iniciado. Leitura recomendada para complementar essa informação: SQL Server – Como descobrir há quanto tempo a instância está online ou quando a instância foi iniciada

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

1	SELECT sqlserver_start_time FROM sys.dm_os_sys_info

Identifica operações de DDL e DCL realizadas na instância

Utilizando o Default Trace, podemos identificar operações DDL (ALTER, CREATE, DROP) e DCL (GRANT, DENY, REVOKE) realizadas na instância. Para entender melhor esse recurso, recomendo a leitura do artigo Monitoring DDL and DCL operations using SQL Server's 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

Identifica quando backups foram gerados ou restaurados

Utilizando o default trace, conseguimos identificar a ocorrência de comandos de BACKUP e RESTORE na instância. Para melhor entendimento desse script, sugiro a leitura do artigo Using the standard SQL Server 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

Identifica eventos de auto growth

Utilizando o default trace, conseguimos identificar a ocorrência de eventos de autogrowth na instância, isto é, quando o SQL Server alocou dinamicamente mais espaço nos arquivos à medida que isso foi necessário para alocar novos dados. Para melhor entendimento desse script, sugiro a leitura do artigo Monitorando os eventos de crescimento automático de espaço (Autogrowth) em databases no 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

Identifica a execução de comandos DBCC

Utilizando o default trace, conseguimos identificar a ocorrência de comandos DBCC executados na instância, como CHECKDB. Para melhor entendimento desse script, sugiro a leitura do artigo Using the standard SQL Server 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

Tabelas e índices

Nessa sessão vou compartilhar alguns scripts relacionados a consultas em tabelas e índices, como identificar tabelas HEAP, índices fragmentados, etc..
Leitura recomendada: Série de Performance Tuning

Visualizar scripts

Identificar tabelas e índices sem compressão

Utilizando a consulta abaixo, vamos identificar as tabelas e índices no modo RowStore que não estão utilizando compressão de dados. Vale lembrar que até a versão 2016, esse recurso era exclusivo da versão Enterprise (além da Trial e Developer), então se a sua versão for inferir ao 2016 e na edição Standard, esse script não será útil para você. Para entender melhor o que é a compressão de dados, sugiro que leia o artigo 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

Identificar fragmentação dos índices

Para identificar o nível de fragmentação dos índices e avaliar se é necessário realizar um REORGANIZE ou REBUILD, utilize o script abaixo. Para entender melhor sobre a estrutura dos índices e o que é fragmentação, leia o artigo Entendendo o funcionamento dos índices no 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

Identificar tabelas HEAP (sem índice clustered)

Utilizando a consulta abaixo, você poderá identificar as tabelas que não possuem índice clustered criado, o que quase sempre, pode representar um possível problema de performance nas consultas, uma vez que os dados não estarão ordenados e a utilização de apenas índices Non-Clustered podem acabar gerando muitos eventos de Key Lookup.

Para entender melhor sobre a estrutura dos índices e o que é um índice clustered, leia o artigo Entendendo o funcionamento dos índices no 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

Utilização dos índices

Com a query abaixo, você poderá identificar se os índices criados estão sendo utilizados da forma correta. Também é útil para identificar índices que podem ser bons candidatos para serem excluídos, pois estão apenas ocupando espaço e ainda te ajuda a identificar tabelas que são muito acessadas e as que não são acessadas há bastante tempo.

Para entender melhor sobre performance tuning e entender o que é operação de Seek, Scan, etc, leia o artigo SQL Server – Introdução ao estudo de Performance Tuning.

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

Identificando possíveis bons candidatos para índice Clustered

Com a query abaixo, vou ajudar vocês a identificar possíveis melhores candidatos a índice clustered do que os atuais. A métrica para identificar isso é através do número de leituras seek entre os índices nonclustered e o clustered. Lembre-se que isso é apenas para dar uma ideia do melhor candidato para o índice clustered. Você não deve confiar apenas nessa consulta, faça as suas análises!

Para entender melhor sobre a estrutura dos índices e o que é um índice clustered, leia o artigo Entendendo o funcionamento dos índices no SQL Server. Para entender melhor sobre performance tuning e entender o que é operação de Seek, Scan, etc, leia o artigo SQL Server – Introdução ao estudo de Performance Tuning.

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

Sugestões de Missing Index

Com a consulta abaixo, você poderá visualizar as sugestões de índices do SQL Server baseado nas estatísticas de Missing Index. Muito cuidado com essas sugestões, pois nem sempre elas são a melhor opção para a criação de um índice. Analise as sugestões antes de criar no banco.

Para entender melhor sobre performance tuning e entender o que é operação de Seek, Scan, etc, leia o artigo SQL Server – Introdução ao estudo de Performance Tuning.

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 );

Estatísticas há mais de 7 dias sem atualizar

Com a consulta abaixo, faremos algumas consultas nas views relacionada às estatísticas de colunas e índices e poderemos visualizar as estatísticas que estão há mais de 7 dias sem atualizações. Estatística desatualizada pode causar muitos problemas de performance, mas também não é necessário atualizar a estatística se não houveram atualizações de dados.

Para entender melhor como a estatística influencia na execução das suas consultas, leia o artigo SQL Server – Introdução ao estudo de Performance Tuning.

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

Gerar script dos índices das tabelas

/*****************************************************************************
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 e Deadlock

Nessa sessão, vamos aprender como identificar blocks, locks, deadlocks, o que é cada tipo de bloqueio desses e como podemos evitar que isso ocorra na instância.
Visualizar scripts

Identificar locks (sessões que estão travando recursos, mas não tem nenhuma sessão sendo travada ainda)

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

Identificar blocks (sessões que estão travando outras)

Para o melhor entendimento dos termos de block e lock, e também do script disponibilizado, sugiro a leitura do artigo SQL Server – Como identificar locks, blocks e sessões bloqueadoras.

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

Identificar eventos de deadlock

Utilizando o Extended Events que já vem habilitado por padrão no SQL Server, o System_Health, podemos identificar eventos de Deadlock que ocorreram recentemente na instância. Para entender melhor o uso desse script, recomendo a leitura do artigo SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas.

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

Permissões

Nesta sessão, vou ajudá-los a identificar as permissões dos usuários em objetos, database roles, server roles e permissões a nível de instância no ambiente.

Como leitura complementar, recomendo os artigos:

Visualizar 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

Permissões a nível de database

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

Permissões a nível de instância

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]

Espaço em disco

Visualizar scripts

Tamanho das tabelas

Com o script abaixo, poderemos visualizar o tamanho alocado em disco por cada tabela do banco de dados. Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases

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

Tamanho dos índices

Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases

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

Tamanho dos arquivos de dados e log (datafiles e logfiles)

Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases

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 = (
		CASE WHEN (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END) = 0 THEN 0
		ELSE ((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)
	END)

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 / 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 = (

CASE WHEN (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END) = 0 THEN 0

ELSE ((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)

END)

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

Tamanho dos discos

Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases

SELECT DISTINCT
    VS.volume_mount_point [Montagem] ,
    VS.logical_volume_name AS [Volume] ,
    CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Total (GB)] ,
    CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço Disponível (GB)] ,
    CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço Disponível ( % )] ,
    CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço em uso ( % )]
FROM
    sys.master_files AS MF
    CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS
WHERE
    CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100;

SELECT DISTINCT

VS.volume_mount_point [Montagem] ,

VS.logical_volume_name AS [Volume] ,

CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Total (GB)] ,

CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço Disponível (GB)] ,

CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço Disponível ( % )] ,

CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço em uso ( % )]

FROM

sys.master_files AS MF

CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS

WHERE

CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100;

Performance

Para um melhor entendimento dos scripts dessa sessão, recomendo a leitura da série de posts Performance Tuning.
Visualizar scripts

Eventos de wait

;WITH [Waits]
AS ( SELECT
         [wait_type],
         [wait_time_ms] / 1000.0 AS [WaitS],
         ( [wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
         [signal_wait_time_ms] / 1000.0 AS [SignalS],
         [waiting_tasks_count] AS [WaitCount],
         100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS [Percentage],
         ROW_NUMBER() OVER ( ORDER BY
                                 [wait_time_ms] DESC
                           ) AS [RowNum]
     FROM
         sys.dm_os_wait_stats
     WHERE
         [wait_type] NOT IN (
                                -- These wait types are almost 100% never a problem and so they are
                                -- filtered out to avoid them skewing the results. Click on the URL
                                -- for more information.
                                N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
                                N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
                                N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
                                N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
                                N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
                                N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
                                N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
                                N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
                                N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
                                N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
                                N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER

                                -- Maybe comment these four out if you have mirroring issues
                                N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
                                N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
                                N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
                                N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD

                                N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
                                N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
                                N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
                                N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
                                N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
                                N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX

                                -- Maybe comment these six out if you have AG issues
                                N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
                                N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
                                N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
                                N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
                                N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
                                N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE

                                N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
                                N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
                                N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
                                N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
                                N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
                                N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
                                N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
                                N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
                                N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
                                N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
                                N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
                                N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
                                N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
                                N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
                                N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
                                N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
                                N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
                                N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
                                N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
                                N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
                                N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
                                N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
                                N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
                                N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
                                N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
                                N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
                                N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
                                N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
                                N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
                                N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
                                N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
                                N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
                                N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
                                N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
                                N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
                                N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
                                N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
                                N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
                                N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
                                N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
                                N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
                                N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
                                N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
                                N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
                                N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
                                N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
                            )
         AND [waiting_tasks_count] > 0 )
SELECT
    MAX([W1].[wait_type]) AS [WaitType],
    CAST(MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_S],
    CAST(MAX([W1].[ResourceS]) AS DECIMAL(16, 2)) AS [Resource_S],
    CAST(MAX([W1].[SignalS]) AS DECIMAL(16, 2)) AS [Signal_S],
    MAX([W1].[WaitCount]) AS [WaitCount],
    CAST(MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage],
    CAST(( MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgWait_S],
    CAST(( MAX([W1].[ResourceS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgRes_S],
    CAST(( MAX([W1].[SignalS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgSig_S],
    CAST('https://www.sqlskills.com/help/waits/' + MAX([W1].[wait_type]) AS XML) AS [Help/Info URL]
FROM
    [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY
    [W1].[RowNum]
HAVING
    SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95; -- percentage threshold
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

;WITH [Waits]

AS ( SELECT

[wait_type],

[wait_time_ms] / 1000.0 AS [WaitS],

( [wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],

[signal_wait_time_ms] / 1000.0 AS [SignalS],

[waiting_tasks_count] AS [WaitCount],

100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS [Percentage],

ROW_NUMBER() OVER ( ORDER BY

[wait_time_ms] DESC

) AS [RowNum]

FROM

sys.dm_os_wait_stats

WHERE

[wait_type] NOT IN (

-- These wait types are almost 100% never a problem and so they are

-- filtered out to avoid them skewing the results. Click on the URL

-- for more information.

N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER

N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR

N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP

N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH

N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER

N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE

N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT

N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT

N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT

N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE

N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER

-- Maybe comment these four out if you have mirroring issues

N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT

N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE

N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE

N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD

N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL

N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE

N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC

N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT

N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT

N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX

-- Maybe comment these six out if you have AG issues

N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL

N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION

N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT

N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE

N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK

N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE

N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP

N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP

N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE

N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT

N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE

N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER

N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE

N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST

N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC

N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK

N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE

N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED

N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT

N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP

N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE

N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP

N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE

N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK

N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH

N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE

N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK

N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH

N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP

N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP

N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY

N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY

N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED

N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP

N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK

N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK

N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP

N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT

N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP

N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH

N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP

N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES

N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS

N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR

N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN

N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY

N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT

N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG

N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE

N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN

N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT

N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT

)

AND [waiting_tasks_count] > 0 )

SELECT

MAX([W1].[wait_type]) AS [WaitType],

CAST(MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_S],

CAST(MAX([W1].[ResourceS]) AS DECIMAL(16, 2)) AS [Resource_S],

CAST(MAX([W1].[SignalS]) AS DECIMAL(16, 2)) AS [Signal_S],

MAX([W1].[WaitCount]) AS [WaitCount],

CAST(MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage],

CAST(( MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgWait_S],

CAST(( MAX([W1].[ResourceS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgRes_S],

CAST(( MAX([W1].[SignalS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgSig_S],

CAST('https://www.sqlskills.com/help/waits/' + MAX([W1].[wait_type]) AS XML) AS [Help/Info URL]

FROM

[Waits] AS [W1]

INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]

GROUP BY

[W1].[RowNum]

HAVING

SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95; -- percentage threshold

Consulta a configuração de energia do servidor

Se você acha que a configuração de energia do seu servidor não faz diferença na performance do seu SQL Server, dê uma lida neste artigo aqui.

DECLARE
    @value VARCHAR(64),
    @key VARCHAR(512) = 'SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes'
 
EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = @key,
    @value_name = 'ActivePowerScheme',
    @value = @value OUTPUT;
 
SELECT (CASE 
    WHEN @value = '381b4222-f694-41f0-9685-ff5bb260df2e' THEN '(Balanced)'
    WHEN @value = '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' THEN '(High performance)'
    WHEN @value = 'a1841308-3541-4fab-bc81-f71556f20b4a' THEN '(Power saver)'
END)

DECLARE

@value VARCHAR(64),

@key VARCHAR(512) = 'SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes'

EXEC master..xp_regread

@rootkey = 'HKEY_LOCAL_MACHINE',

@key = @key,

@value_name = 'ActivePowerScheme',

@value = @value OUTPUT;

SELECT (CASE

WHEN @value = '381b4222-f694-41f0-9685-ff5bb260df2e' THEN '(Balanced)'

WHEN @value = '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' THEN '(High performance)'

WHEN @value = 'a1841308-3541-4fab-bc81-f71556f20b4a' THEN '(Power saver)'

END)

Histórico do consumo de CPU (últimos 256 minutos)

DECLARE @ts_now BIGINT =
        (
            SELECT
                cpu_ticks / ( cpu_ticks / ms_ticks )
            FROM
                sys.dm_os_sys_info WITH ( NOLOCK )
        );

SELECT TOP ( 256 )
    SQLProcessUtilization AS [SQL Server Process CPU Utilization],
    SystemIdle AS [System Idle Process],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
    DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE()) AS [Event Time]
FROM
(
    SELECT
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
        [timestamp]
    FROM
    (
        SELECT
            [timestamp],
            CONVERT(XML, record) AS [record]
        FROM
            sys.dm_os_ring_buffers WITH ( NOLOCK )
        WHERE
            ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE N'%<SystemHealth>%'
    ) AS x
) AS y
ORDER BY
    record_id DESC
OPTION ( RECOMPILE );

DECLARE @ts_now BIGINT =

(

SELECT

cpu_ticks / ( cpu_ticks / ms_ticks )

FROM

sys.dm_os_sys_info WITH ( NOLOCK )

);

SELECT TOP ( 256 )

SQLProcessUtilization AS [SQL Server Process CPU Utilization],

SystemIdle AS [System Idle Process],

100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE()) AS [Event Time]

FROM

(

SELECT

record.value('(./Record/@id)[1]', 'int') AS record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],

[timestamp]

FROM

(

SELECT

[timestamp],

CONVERT(XML, record) AS [record]

FROM

sys.dm_os_ring_buffers WITH ( NOLOCK )

WHERE

ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

AND record LIKE N'%<SystemHealth>%'

) AS x

) AS y

ORDER BY

record_id DESC

OPTION ( RECOMPILE );

Monitora o uso de memória na instância

-- https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/04/using-sys-dm_os_ring_buffers-to-diagnose-memory-issues-in-sql-server/
WITH RingBuffer
AS ( SELECT
         CAST(dorb.record AS XML) AS xRecord,
         dorb.timestamp
     FROM
         sys.dm_os_ring_buffers AS dorb
     WHERE
         dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' )
SELECT
    xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
    xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
    xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
    DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime,
    xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
    xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
    xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
    xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
    xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
    xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
    xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
    xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
    xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
    xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
    xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
    xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS AvailableVirtualAddressSpace,
    xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint') AS AvailableExtendedVirtualAddressSpace
FROM
    RingBuffer AS rb
    CROSS APPLY rb.xRecord.nodes('Record') record(xr)
    CROSS JOIN sys.dm_os_sys_info AS dosi
ORDER BY
    RmDateTime DESC;

-- https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/04/using-sys-dm_os_ring_buffers-to-diagnose-memory-issues-in-sql-server/

WITH RingBuffer

AS ( SELECT

CAST(dorb.record AS XML) AS xRecord,

dorb.timestamp

FROM

sys.dm_os_ring_buffers AS dorb

WHERE

dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' )

SELECT

xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,

xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,

xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,

DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime,

xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,

xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,

xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,

xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,

xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,

xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,

xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,

xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,

xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,

xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,

xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,

xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS AvailableVirtualAddressSpace,

xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint') AS AvailableExtendedVirtualAddressSpace

FROM

RingBuffer AS rb

CROSS APPLY rb.xRecord.nodes('Record') record(xr)

CROSS JOIN sys.dm_os_sys_info AS dosi

ORDER BY

RmDateTime DESC;

Verifica desempenho de I/O dos discos

SELECT
    DB_NAME(fs.database_id) AS [Database Name],
    mf.physical_name,
    io_stall_read_ms,
    num_of_reads,
    CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],
    io_stall_write_ms,
    num_of_writes,
    CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],
    io_stall_read_ms + io_stall_write_ms AS [io_stalls],
    num_of_reads + num_of_writes AS [total_io],
    CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC;

SELECT

DB_NAME(fs.database_id) AS [Database Name],

mf.physical_name,

io_stall_read_ms,

num_of_reads,

CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],

io_stall_write_ms,

num_of_writes,

CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],

io_stall_read_ms + io_stall_write_ms AS [io_stalls],

num_of_reads + num_of_writes AS [total_io],

CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]

FROM

sys.dm_io_virtual_file_stats(NULL, NULL) AS fs

INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]

ORDER BY

avg_io_stall_ms DESC;

Verifica a fila de disco (requisições pendentes de I/O)

SELECT
    DB_NAME(mf.database_id) AS [Database],
    mf.physical_name,
    r.io_pending,
    r.io_pending_ms_ticks,
    r.io_type,
    fs.num_of_reads,
    fs.num_of_writes,
    GETDATE()
FROM
    sys.dm_io_pending_io_requests AS r
    INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle
    INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
ORDER BY
    r.io_pending,
    r.io_pending_ms_ticks DESC

SELECT

DB_NAME(mf.database_id) AS [Database],

mf.physical_name,

r.io_pending,

r.io_pending_ms_ticks,

r.io_type,

fs.num_of_reads,

fs.num_of_writes,

GETDATE()

FROM

sys.dm_io_pending_io_requests AS r

INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle

INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id

ORDER BY

r.io_pending,

r.io_pending_ms_ticks DESC

Quantidade de páginas e memória alocada por database

SELECT
    CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id)END AS database_name,
    COUNT(*) AS cached_pages_count,
    COUNT(*) * .0078125 AS cached_megabytes /* Each page is 8kb, which is .0078125 of an MB */
FROM
    sys.dm_os_buffer_descriptors
GROUP BY
    DB_NAME(database_id),
    database_id
ORDER BY
    cached_pages_count DESC;

SELECT

CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id)END AS database_name,

COUNT(*) AS cached_pages_count,

COUNT(*) * .0078125 AS cached_megabytes /* Each page is 8kb, which is .0078125 of an MB */

FROM

sys.dm_os_buffer_descriptors

GROUP BY

DB_NAME(database_id),

database_id

ORDER BY

cached_pages_count DESC;

Backup e Restore

Visualizar scripts

Histórico dos backups realizados na instância

SELECT
    B.[database_name],
    (CASE B.[type]
        WHEN 'D' THEN 'Full Backup'
        WHEN 'I' THEN 'Differential Backup'
        WHEN 'L' THEN 'TLog Backup'
        WHEN 'F' THEN 'File or filegroup'
        WHEN 'G' THEN 'Differential file'
        WHEN 'P' THEN 'Partial'
        WHEN 'Q' THEN 'Differential Partial'
    END) AS BackupType,
    B.recovery_model AS RecoveryModel,
    B.backup_start_date,
    B.backup_finish_date,
    CAST(DATEDIFF(SECOND,B.backup_start_date, B.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' AS TotalTimeTaken,
    B.expiration_date,
    B.[user_name],
    B.machine_name,
    B.is_password_protected,
    B.collation_name,
    B.is_copy_only,
    CONVERT(NUMERIC(20, 2), B.backup_size / 1048576) AS BackupSizeMB,
    A.logical_device_name,
    A.physical_device_name,
    B.[name] AS backupset_name,
    B.[description],
    B.has_backup_checksums,
    B.is_damaged,
    B.has_incomplete_metadata
FROM
    sys.databases X
    JOIN msdb.dbo.backupset B ON X.[name] = B.[database_name]
    JOIN msdb.dbo.backupmediafamily A ON A.media_set_id = B.media_set_id
WHERE
    B.backup_start_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))

SELECT

B.[database_name],

(CASE B.[type]

WHEN 'D' THEN 'Full Backup'

WHEN 'I' THEN 'Differential Backup'

WHEN 'L' THEN 'TLog Backup'

WHEN 'F' THEN 'File or filegroup'

WHEN 'G' THEN 'Differential file'

WHEN 'P' THEN 'Partial'

WHEN 'Q' THEN 'Differential Partial'

END) AS BackupType,

B.recovery_model AS RecoveryModel,

B.backup_start_date,

B.backup_finish_date,

CAST(DATEDIFF(SECOND,B.backup_start_date, B.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' AS TotalTimeTaken,

B.expiration_date,

B.[user_name],

B.machine_name,

B.is_password_protected,

B.collation_name,

B.is_copy_only,

CONVERT(NUMERIC(20, 2), B.backup_size / 1048576) AS BackupSizeMB,

A.logical_device_name,

A.physical_device_name,

B.[name] AS backupset_name,

B.[description],

B.has_backup_checksums,

B.is_damaged,

B.has_incomplete_metadata

FROM

sys.databases X

JOIN msdb.dbo.backupset B ON X.[name] = B.[database_name]

JOIN msdb.dbo.backupmediafamily A ON A.media_set_id = B.media_set_id

WHERE

B.backup_start_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))

Histórico dos Restores realizados na instância

SELECT
    A.[restore_history_id],
    A.[restore_date],
    A.[destination_database_name],
    C.physical_device_name,
    A.[user_name],
    A.[backup_set_id],
    CASE A.[restore_type]
        WHEN 'D' THEN 'Database'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        WHEN 'F' THEN 'File'
        WHEN 'G' THEN 'Filegroup'
        WHEN 'V' THEN 'Verifyonlyl'
    END AS RestoreType,
    A.[replace],
    A.[recovery],
    A.[restart],
    A.[stop_at],
    A.[device_count],
    A.[stop_at_mark_name],
    A.[stop_before]
FROM
    [msdb].[dbo].[restorehistory] A
    JOIN [msdb].[dbo].[backupset] B ON A.backup_set_id = B.backup_set_id
    JOIN msdb.dbo.backupmediafamily C ON B.media_set_id = C.media_set_id
WHERE
    A.restore_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))

SELECT

A.[restore_history_id],

A.[restore_date],

A.[destination_database_name],

C.physical_device_name,

A.[user_name],

A.[backup_set_id],

CASE A.[restore_type]

WHEN 'D' THEN 'Database'

WHEN 'I' THEN 'Differential'

WHEN 'L' THEN 'Log'

WHEN 'F' THEN 'File'

WHEN 'G' THEN 'Filegroup'

WHEN 'V' THEN 'Verifyonlyl'

END AS RestoreType,

A.[replace],

A.[recovery],

A.[restart],

A.[stop_at],

A.[device_count],

A.[stop_at_mark_name],

A.[stop_before]

FROM

[msdb].[dbo].[restorehistory] A

JOIN [msdb].[dbo].[backupset] B ON A.backup_set_id = B.backup_set_id

JOIN msdb.dbo.backupmediafamily C ON B.media_set_id = C.media_set_id

WHERE

A.restore_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))

Visualizar databases há mais de 7 dias sem fazer backup

SELECT
    A.[name] AS [database_name],
    A.recovery_model_desc,
    (SELECT SUM(CAST(size / 128 / 1024.0 AS NUMERIC(18, 2))) FROM sys.master_files WHERE A.[name] = [name]) AS size_GB,
    MAX(B.backup_start_date) AS last_backup_date
FROM
    sys.databases A
    LEFT JOIN msdb.dbo.backupset B ON A.[name] = B.[database_name]
WHERE
    (B.backup_set_id IS NULL OR DATEDIFF(DAY, B.backup_start_date, GETDATE()) > 7)
    AND A.[name] NOT IN ('tempdb', 'model')
GROUP BY
    A.[name],
    A.recovery_model_desc

SELECT

A.[name] AS [database_name],

A.recovery_model_desc,

(SELECT SUM(CAST(size / 128 / 1024.0 AS NUMERIC(18, 2))) FROM sys.master_files WHERE A.[name] = [name]) AS size_GB,

MAX(B.backup_start_date) AS last_backup_date

FROM

sys.databases A

LEFT JOIN msdb.dbo.backupset B ON A.[name] = B.[database_name]

WHERE

(B.backup_set_id IS NULL OR DATEDIFF(DAY, B.backup_start_date, GETDATE()) > 7)

AND A.[name] NOT IN ('tempdb', 'model')

GROUP BY

A.[name],

A.recovery_model_desc

Visualizar backups/restore em andamento e estimar o tempo restante

SELECT
    R.session_id,
    R.command AS Ds_Operacao,
    B.name AS Nm_Banco,
    R.start_time AS Dt_Inicio,
    CONVERT(VARCHAR(20), DATEADD(MS, R.estimated_completion_time, GETDATE()), 20) AS Dt_Previsao_Fim,
    CONVERT(NUMERIC(6, 2), R.percent_complete) AS Vl_Percentual_Concluido,
    CONVERT(NUMERIC(6, 2), R.total_elapsed_time / 1000.0 / 60.0) AS Qt_Minutos_Execucao,
    CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0) AS Qt_Minutos_Restantes,
    CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS Qt_Horas_Restantes,
    CONVERT(VARCHAR(MAX), ( SELECT
                                SUBSTRING(text, R.statement_start_offset / 2, CASE WHEN R.statement_end_offset = -1 THEN 1000 ELSE ( R.statement_end_offset - R.statement_start_offset ) / 2 END)
                            FROM
                                sys.dm_exec_sql_text(sql_handle)
                            )) AS Ds_Comando
FROM
    sys.dm_exec_requests	R	WITH(NOLOCK)
    JOIN sys.databases		B	WITH(NOLOCK)	 ON R.database_id = B.database_id
WHERE
    R.command IN ( 
        'BACKUP DATABASE', 
        'RESTORE DATABASE', 
        'ALTER INDEX REORGANIZE', 
        'AUTO_SHRINK option with ALTER DATABASE', 
        'CREATE INDEX',
        'DBCC CHECKDB',
        'DBCC CHECKFILEGROUP',
        'DBCC CHECKTABLE',
        'DBCC INDEXDEFRAG',
        'DBCC SHRINKDATABASE',
        'DBCC SHRINKFILE',
        'KILL',
        'UPDATE STATISTICS',
        'DBCC'
    )
    AND R.estimated_completion_time > 0

SELECT

R.session_id,

R.command AS Ds_Operacao,

B.name AS Nm_Banco,

R.start_time AS Dt_Inicio,

CONVERT(VARCHAR(20), DATEADD(MS, R.estimated_completion_time, GETDATE()), 20) AS Dt_Previsao_Fim,

CONVERT(NUMERIC(6, 2), R.percent_complete) AS Vl_Percentual_Concluido,

CONVERT(NUMERIC(6, 2), R.total_elapsed_time / 1000.0 / 60.0) AS Qt_Minutos_Execucao,

CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0) AS Qt_Minutos_Restantes,

CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS Qt_Horas_Restantes,

CONVERT(VARCHAR(MAX), ( SELECT

SUBSTRING(text, R.statement_start_offset / 2, CASE WHEN R.statement_end_offset = -1 THEN 1000 ELSE ( R.statement_end_offset - R.statement_start_offset ) / 2 END)

FROM

sys.dm_exec_sql_text(sql_handle)

)) AS Ds_Comando

FROM

sys.dm_exec_requests R WITH(NOLOCK)

JOIN sys.databases B WITH(NOLOCK) ON R.database_id = B.database_id

WHERE

R.command IN (

'BACKUP DATABASE',

'RESTORE DATABASE',

'ALTER INDEX REORGANIZE',

'AUTO_SHRINK option with ALTER DATABASE',

'CREATE INDEX',

'DBCC CHECKDB',

'DBCC CHECKFILEGROUP',

'DBCC CHECKTABLE',

'DBCC INDEXDEFRAG',

'DBCC SHRINKDATABASE',

'DBCC SHRINKFILE',

'KILL',

'UPDATE STATISTICS',

'DBCC'

)

AND R.estimated_completion_time > 0

Plan cache

Para um melhor entendimento dos scripts dessa sessão, recomendo a leitura da série de posts Performance Tuning.
Visualizar scripts

Visualizar plano das consultas em execução

SELECT
    B.start_time,
    A.session_id,
    B.command,
    A.login_name,
    A.[host_name],
    A.[program_name],
    B.logical_reads,
    B.cpu_time,
    B.writes,
    B.blocking_session_id,
    C.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
    OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS C
WHERE
    A.session_id > 50
    AND A.session_id <> @@SPID
    AND (A.[status] <> 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))
ORDER BY
    B.start_time

SELECT

B.start_time,

A.session_id,

B.command,

A.login_name,

A.[host_name],

A.[program_name],

B.logical_reads,

B.cpu_time,

B.writes,

B.blocking_session_id,

C.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

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

WHERE

A.session_id > 50

AND A.session_id <> @@SPID

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

ORDER BY

B.start_time

Visualizar planos em cache

SELECT
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.text AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
ORDER BY
    ExecutionCount DESC

SELECT

cp.objtype AS ObjectType,

OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

st.text AS QueryText,

qp.query_plan AS QueryPlan

FROM

sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

ORDER BY

ExecutionCount DESC

Identificar consultas pesadas utilizando a plancache

SELECT TOP(100)
    DB_NAME(C.[dbid]) as [database],
    B.[text],
    (SELECT CAST(SUBSTRING(B.[text], (A.statement_start_offset/2)+1,   
        (((CASE A.statement_end_offset  
            WHEN -1 THEN DATALENGTH(B.[text]) 
            ELSE A.statement_end_offset  
        END) - A.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''), TYPE) AS [TSQL],
    C.query_plan,

    A.last_execution_time,
    A.execution_count,
 
    A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
    A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
    A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
    ((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
 
    A.total_worker_time / 1000 AS total_worker_time_ms,
    A.last_worker_time / 1000 AS last_worker_time_ms,
    A.min_worker_time / 1000 AS min_worker_time_ms,
    A.max_worker_time / 1000 AS max_worker_time_ms,
    ((A.total_worker_time / a.execution_count) / 1000) AS avg_worker_time_ms,
   
    A.total_physical_reads,
    A.last_physical_reads,
    A.min_physical_reads,
    A.max_physical_reads,
   
    A.total_logical_reads,
    A.last_logical_reads,
    A.min_logical_reads,
    A.max_logical_reads,
   
    A.total_logical_writes,
    A.last_logical_writes,
    A.min_logical_writes,
    A.max_logical_writes
FROM
    sys.dm_exec_query_stats A
    CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) B
    OUTER APPLY sys.dm_exec_query_plan (A.plan_handle) AS C
ORDER BY
    A.total_elapsed_time DESC

SELECT TOP(100)

DB_NAME(C.[dbid]) as [database],

B.[text],

(SELECT CAST(SUBSTRING(B.[text], (A.statement_start_offset/2)+1,

(((CASE A.statement_end_offset

WHEN -1 THEN DATALENGTH(B.[text])

ELSE A.statement_end_offset

END) - A.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''), TYPE) AS [TSQL],

C.query_plan,

A.last_execution_time,

A.execution_count,

A.total_elapsed_time / 1000 AS total_elapsed_time_ms,

A.last_elapsed_time / 1000 AS last_elapsed_time_ms,

A.min_elapsed_time / 1000 AS min_elapsed_time_ms,

A.max_elapsed_time / 1000 AS max_elapsed_time_ms,

((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,

A.total_worker_time / 1000 AS total_worker_time_ms,

A.last_worker_time / 1000 AS last_worker_time_ms,

A.min_worker_time / 1000 AS min_worker_time_ms,

A.max_worker_time / 1000 AS max_worker_time_ms,

((A.total_worker_time / a.execution_count) / 1000) AS avg_worker_time_ms,

A.total_physical_reads,

A.last_physical_reads,

A.min_physical_reads,

A.max_physical_reads,

A.total_logical_reads,

A.last_logical_reads,

A.min_logical_reads,

A.max_logical_reads,

A.total_logical_writes,

A.last_logical_writes,

A.min_logical_writes,

A.max_logical_writes

FROM

sys.dm_exec_query_stats A

CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) B

OUTER APPLY sys.dm_exec_query_plan (A.plan_handle) AS C

ORDER BY

A.total_elapsed_time DESC

Identificar as Stored Procedures pesadas utilizando a plancache

SELECT TOP(100)
    B.[name] AS rotina,
    A.cached_time,
    A.last_execution_time,
    A.execution_count,

    A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
    A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
    A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
    ((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,

    A.total_worker_time / 1000 AS total_worker_time_ms,
    A.last_worker_time / 1000 AS last_worker_time_ms,
    A.min_worker_time / 1000 AS min_worker_time_ms,
    A.max_worker_time / 1000 AS max_worker_time_ms,
    ((A.total_worker_time / A.execution_count) / 1000) AS avg_worker_time_ms,
    
    A.total_physical_reads,
    A.last_physical_reads,
    A.min_physical_reads,
    A.max_physical_reads,
    
    A.total_logical_reads,
    A.last_logical_reads,
    A.min_logical_reads,
    A.max_logical_reads,
    
    A.total_logical_writes,
    A.last_logical_writes,
    A.min_logical_writes,
    A.max_logical_writes
FROM
    sys.dm_exec_procedure_stats                 A
    JOIN sys.objects                            B    ON  A.[object_id] = B.[object_id]
ORDER BY
    A.execution_count DESC

SELECT TOP(100)

B.[name] AS rotina,

A.cached_time,

A.last_execution_time,

A.execution_count,

A.total_elapsed_time / 1000 AS total_elapsed_time_ms,

A.last_elapsed_time / 1000 AS last_elapsed_time_ms,

A.min_elapsed_time / 1000 AS min_elapsed_time_ms,

A.max_elapsed_time / 1000 AS max_elapsed_time_ms,

((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,

A.total_worker_time / 1000 AS total_worker_time_ms,

A.last_worker_time / 1000 AS last_worker_time_ms,

A.min_worker_time / 1000 AS min_worker_time_ms,

A.max_worker_time / 1000 AS max_worker_time_ms,

((A.total_worker_time / A.execution_count) / 1000) AS avg_worker_time_ms,

A.total_physical_reads,

A.last_physical_reads,

A.min_physical_reads,

A.max_physical_reads,

A.total_logical_reads,

A.last_logical_reads,

A.min_logical_reads,

A.max_logical_reads,

A.total_logical_writes,

A.last_logical_writes,

A.min_logical_writes,

A.max_logical_writes

FROM

sys.dm_exec_procedure_stats A

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

ORDER BY

A.execution_count DESC

Identificar eventos de Key Lookup através da plancache

/********************************************************************************************* 
Find Key Lookups in Cached Plans v1.00 (2010-07-27) 
(C) 2010, Kendal Van Dyke

Feedback: mailto:kendal.vandyke@gmail.com

License: 
   This query is free to download and use for personal, educational, and internal 
   corporate purposes, provided that this header is preserved. Redistribution or sale 
   of this query, in whole or in part, is prohibited without the author's express 
   written consent. 
   
Note: 
   Exercise caution when running this in production!

   The function sys.dm_exec_query_plan() is resource intensive and can put strain 
   on a server when used to retrieve all cached query plans.

   Consider using TOP in the initial select statement (insert into @plans) 
   to limit the impact of running this query or run during non-peak hours 
*********************************************************************************************/
DECLARE @plans TABLE
(
    query_text NVARCHAR(MAX),
    o_name sysname,
    execution_plan XML,
    last_execution_time DATETIME,
    execution_count BIGINT,
    total_worker_time BIGINT,
    total_physical_reads BIGINT,
    total_logical_reads BIGINT
);

DECLARE @lookups TABLE
(
    table_name sysname,
    index_name sysname,
    index_cols NVARCHAR(MAX)
);

WITH query_stats
AS ( 
    SELECT
         [sql_handle],
         [plan_handle],
         MAX(last_execution_time) AS last_execution_time,
         SUM(execution_count) AS execution_count,
         SUM(total_worker_time) AS total_worker_time,
         SUM(total_physical_reads) AS total_physical_reads,
         SUM(total_logical_reads) AS total_logical_reads
     FROM
         sys.dm_exec_query_stats
     GROUP BY
         [sql_handle],
         [plan_handle] 
)
INSERT INTO @plans
(
    query_text,
    o_name,
    execution_plan,
    last_execution_time,
    execution_count,
    total_worker_time,
    total_physical_reads,
    total_logical_reads
)
SELECT /*TOP 50*/
    sql_text.[text],
    CASE
        WHEN sql_text.objectid IS NOT NULL THEN ISNULL(OBJECT_NAME(sql_text.objectid, sql_text.[dbid]), 'Unresolved')
        ELSE CAST('Ad-hoc\Prepared' AS sysname)
    END,
    query_plan.query_plan,
    query_stats.last_execution_time,
    query_stats.execution_count,
    query_stats.total_worker_time,
    query_stats.total_physical_reads,
    query_stats.total_logical_reads
FROM
    query_stats
    CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle) AS [sql_text]
    CROSS APPLY sys.dm_exec_query_plan(query_stats.plan_handle) AS [query_plan]
WHERE
    query_plan.query_plan IS NOT NULL;

;WITH XMLNAMESPACES
 (
     DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
 )
, lookups
AS ( SELECT
         DB_ID(REPLACE(REPLACE(keylookups.keylookup.value('(Object/@Database)[1]', 'sysname'), '[', ''), ']', '')) AS [database_id],
         OBJECT_ID(keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') + '.' + keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') + '.' + keylookups.keylookup.value('(Object/@Table)[1]', 'sysname')) AS [object_id],
         keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') AS [database],
         keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') AS [schema],
         keylookups.keylookup.value('(Object/@Table)[1]', 'sysname') AS [table],
         keylookups.keylookup.value('(Object/@Index)[1]', 'sysname') AS [index],
         REPLACE(keylookups.keylookup.query(' 
for $column in DefinedValues/DefinedValue/ColumnReference 
return string($column/@Column) 
').value('.', 'varchar(max)'), ' ', ', ') AS [columns],
         plans.query_text,
         plans.o_name,
         plans.execution_plan,
         plans.last_execution_time,
         plans.execution_count,
         plans.total_worker_time,
         plans.total_physical_reads,
         plans.total_logical_reads
     FROM
         @plans AS [plans]
         CROSS APPLY execution_plan.nodes('//RelOp/IndexScan[@Lookup="1"]') AS keylookups(keylookup) )
SELECT
    lookups.[database],
    lookups.[schema],
    lookups.[table],
    lookups.[index],
    lookups.[columns],
    index_stats.user_lookups,
    index_stats.last_user_lookup,
    lookups.execution_count,
    lookups.total_worker_time,
    lookups.total_physical_reads,
    lookups.total_logical_reads,
    lookups.last_execution_time,
    lookups.o_name AS [object_name],
    lookups.query_text,
    lookups.execution_plan
FROM
    lookups
    INNER JOIN sys.dm_db_index_usage_stats AS [index_stats] ON lookups.database_id = index_stats.database_id
                                                               AND lookups.[object_id] = index_stats.[object_id]
WHERE
    index_stats.user_lookups > 0
    AND lookups.[database] NOT IN ( '[master]', '[model]', '[msdb]', '[tempdb]' )
ORDER BY
    index_stats.user_lookups DESC,
    lookups.total_physical_reads DESC,
    lookups.total_logical_reads 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

138

139

140

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

Find Key Lookups in Cached Plans v1.00 (2010-07-27)

Feedback: mailto:[email protected]

License:

This query is free to download and use for personal, educational, and internal

corporate purposes, provided that this header is preserved. Redistribution or sale

of this query, in whole or in part, is prohibited without the author's express

written consent.

Note:

Exercise caution when running this in production!

The function sys.dm_exec_query_plan() is resource intensive and can put strain

on a server when used to retrieve all cached query plans.

Consider using TOP in the initial select statement (insert into @plans)

to limit the impact of running this query or run during non-peak hours

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

DECLARE @plans TABLE

(

query_text NVARCHAR(MAX),

o_name sysname,

execution_plan XML,

last_execution_time DATETIME,

execution_count BIGINT,

total_worker_time BIGINT,

total_physical_reads BIGINT,

total_logical_reads BIGINT

);

DECLARE @lookups TABLE

(

table_name sysname,

index_name sysname,

index_cols NVARCHAR(MAX)

);

WITH query_stats

AS (

SELECT

[sql_handle],

[plan_handle],

MAX(last_execution_time) AS last_execution_time,

SUM(execution_count) AS execution_count,

SUM(total_worker_time) AS total_worker_time,

SUM(total_physical_reads) AS total_physical_reads,

SUM(total_logical_reads) AS total_logical_reads

FROM

sys.dm_exec_query_stats

GROUP BY

[sql_handle],

[plan_handle]

)

INSERT INTO @plans

(

query_text,

o_name,

execution_plan,

last_execution_time,

execution_count,

total_worker_time,

total_physical_reads,

total_logical_reads

)

SELECT /*TOP 50*/

sql_text.[text],

CASE

WHEN sql_text.objectid IS NOT NULL THEN ISNULL(OBJECT_NAME(sql_text.objectid, sql_text.[dbid]), 'Unresolved')

ELSE CAST('Ad-hoc\Prepared' AS sysname)

END,

query_plan.query_plan,

query_stats.last_execution_time,

query_stats.execution_count,

query_stats.total_worker_time,

query_stats.total_physical_reads,

query_stats.total_logical_reads

FROM

query_stats

CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle) AS [sql_text]

CROSS APPLY sys.dm_exec_query_plan(query_stats.plan_handle) AS [query_plan]

WHERE

query_plan.query_plan IS NOT NULL;

;WITH XMLNAMESPACES

(

DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'

)

, lookups

AS ( SELECT

DB_ID(REPLACE(REPLACE(keylookups.keylookup.value('(Object/@Database)[1]', 'sysname'), '[', ''), ']', '')) AS [database_id],

OBJECT_ID(keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') + '.' + keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') + '.' + keylookups.keylookup.value('(Object/@Table)[1]', 'sysname')) AS [object_id],

keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') AS [database],

keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') AS [schema],

keylookups.keylookup.value('(Object/@Table)[1]', 'sysname') AS [table],

keylookups.keylookup.value('(Object/@Index)[1]', 'sysname') AS [index],

REPLACE(keylookups.keylookup.query('

for $column in DefinedValues/DefinedValue/ColumnReference

return string($column/@Column)

').value('.', 'varchar(max)'), ' ', ', ') AS [columns],

plans.query_text,

plans.o_name,

plans.execution_plan,

plans.last_execution_time,

plans.execution_count,

plans.total_worker_time,

plans.total_physical_reads,

plans.total_logical_reads

FROM

@plans AS [plans]

CROSS APPLY execution_plan.nodes('//RelOp/IndexScan[@Lookup="1"]') AS keylookups(keylookup) )

SELECT

lookups.[database],

lookups.[schema],

lookups.[table],

lookups.[index],

lookups.[columns],

index_stats.user_lookups,

index_stats.last_user_lookup,

lookups.execution_count,

lookups.total_worker_time,

lookups.total_physical_reads,

lookups.total_logical_reads,

lookups.last_execution_time,

lookups.o_name AS [object_name],

lookups.query_text,

lookups.execution_plan

FROM

lookups

INNER JOIN sys.dm_db_index_usage_stats AS [index_stats] ON lookups.database_id = index_stats.database_id

AND lookups.[object_id] = index_stats.[object_id]

WHERE

index_stats.user_lookups > 0

AND lookups.[database] NOT IN ( '[master]', '[model]', '[msdb]', '[tempdb]' )

ORDER BY

index_stats.user_lookups DESC,

lookups.total_physical_reads DESC,

lookups.total_logical_reads DESC

Identificar conversão implícita através da plancache

SELECT TOP ( 100 )
    DB_NAME(B.[dbid]) AS [Database],
    B.[text] AS [Consulta],
    A.total_worker_time AS [Total Worker Time],
    A.total_worker_time / A.execution_count AS [Avg Worker Time],
    A.max_worker_time AS [Max Worker Time],
    A.total_elapsed_time / A.execution_count AS [Avg Elapsed Time],
    A.max_elapsed_time AS [Max Elapsed Time],
    A.total_logical_reads / A.execution_count AS [Avg Logical Reads],
    A.max_logical_reads AS [Max Logical Reads],
    A.execution_count AS [Execution Count],
    A.creation_time AS [Creation Time],
    C.query_plan AS [Query Plan]
FROM
    sys.dm_exec_query_stats AS A WITH ( NOLOCK )
    CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) AS B
    CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C
WHERE
    CAST(C.query_plan AS NVARCHAR(MAX)) LIKE ( '%PlanAffectingConvert%ConvertIssue%CONVERT_IMPLICIT%' )
    AND B.[dbid] = DB_ID()
    AND B.[text] NOT LIKE '%sys.dm_exec_sql_text%' -- Não pegar a própria consulta
ORDER BY
    A.total_worker_time DESC

SELECT TOP ( 100 )

DB_NAME(B.[dbid]) AS [Database],

B.[text] AS [Consulta],

A.total_worker_time AS [Total Worker Time],

A.total_worker_time / A.execution_count AS [Avg Worker Time],

A.max_worker_time AS [Max Worker Time],

A.total_elapsed_time / A.execution_count AS [Avg Elapsed Time],

A.max_elapsed_time AS [Max Elapsed Time],

A.total_logical_reads / A.execution_count AS [Avg Logical Reads],

A.max_logical_reads AS [Max Logical Reads],

A.execution_count AS [Execution Count],

A.creation_time AS [Creation Time],

C.query_plan AS [Query Plan]

FROM

sys.dm_exec_query_stats AS A WITH ( NOLOCK )

CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) AS B

CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C

WHERE

CAST(C.query_plan AS NVARCHAR(MAX)) LIKE ( '%PlanAffectingConvert%ConvertIssue%CONVERT_IMPLICIT%' )

AND B.[dbid] = DB_ID()

AND B.[text] NOT LIKE '%sys.dm_exec_sql_text%' -- Não pegar a própria consulta

ORDER BY

A.total_worker_time DESC

Planos em cache com alertas de Missing Index

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    query_plan,
    n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
    n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
    DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '')) AS database_id,
    OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
    n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,
    (
        SELECT DISTINCT
            c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
        FROM
            n.nodes('//ColumnGroup') AS t(cg)
            CROSS APPLY cg.nodes('Column') AS r(c)
        WHERE
            cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
        FOR XML PATH('')
    ) AS equality_columns,
    (
        SELECT DISTINCT
            c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
        FROM
            n.nodes('//ColumnGroup') AS t(cg)
            CROSS APPLY cg.nodes('Column') AS r(c)
        WHERE
            cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
        FOR XML PATH('')
    ) AS inequality_columns,
    (
        SELECT DISTINCT
            c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
        FROM
            n.nodes('//ColumnGroup') AS t(cg)
            CROSS APPLY cg.nodes('Column') AS r(c)
        WHERE
            cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
        FOR XML PATH('')
    ) AS include_columns
INTO
    #MissingIndexInfo
FROM
(
    SELECT
        query_plan
    FROM
    (
        SELECT DISTINCT
            plan_handle
        FROM
            sys.dm_exec_query_stats WITH ( NOLOCK )
    ) AS qs
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
    WHERE
        tp.query_plan.exist('//MissingIndex') = 1
) AS tab(query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE
    n.exist('QueryPlan/MissingIndexes') = 1;

-- Trim trailing comma from lists
UPDATE
    #MissingIndexInfo
SET
    equality_columns = LEFT(equality_columns, LEN(equality_columns) - 1),
    inequality_columns = LEFT(inequality_columns, LEN(inequality_columns) - 1),
    include_columns = LEFT(include_columns, LEN(include_columns) - 1);

SELECT
    *
FROM
    #MissingIndexInfo;

WITH XMLNAMESPACES

(

DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'

)

SELECT

query_plan,

n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,

n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,

DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '')) AS database_id,

OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,

n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,

(

SELECT DISTINCT

c.value('(@Name)[1]', 'VARCHAR(128)') + ', '

FROM

n.nodes('//ColumnGroup') AS t(cg)

CROSS APPLY cg.nodes('Column') AS r(c)

WHERE

cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'

FOR XML PATH('')

) AS equality_columns,

(

SELECT DISTINCT

c.value('(@Name)[1]', 'VARCHAR(128)') + ', '

FROM

n.nodes('//ColumnGroup') AS t(cg)

CROSS APPLY cg.nodes('Column') AS r(c)

WHERE

cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'

FOR XML PATH('')

) AS inequality_columns,

(

SELECT DISTINCT

c.value('(@Name)[1]', 'VARCHAR(128)') + ', '

FROM

n.nodes('//ColumnGroup') AS t(cg)

CROSS APPLY cg.nodes('Column') AS r(c)

WHERE

cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'

FOR XML PATH('')

) AS include_columns

INTO

#MissingIndexInfo

FROM

(

SELECT

query_plan

FROM

(

SELECT DISTINCT

plan_handle

FROM

sys.dm_exec_query_stats WITH ( NOLOCK )

) AS qs

OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp

WHERE

tp.query_plan.exist('//MissingIndex') = 1

) AS tab(query_plan)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)

WHERE

n.exist('QueryPlan/MissingIndexes') = 1;

-- Trim trailing comma from lists

UPDATE

#MissingIndexInfo

SET

equality_columns = LEFT(equality_columns, LEN(equality_columns) - 1),

inequality_columns = LEFT(inequality_columns, LEN(inequality_columns) - 1),

include_columns = LEFT(include_columns, LEN(include_columns) - 1);

SELECT

FROM

#MissingIndexInfo;

Consultas em cache utilizando paralelismo

SELECT TOP(20)
    st.[text] AS [SqlText],
    cp.cacheobjtype,
    cp.objtype,
    DB_NAME(st.[dbid]) AS [DatabaseName],
    cp.usecounts,
    qp.query_plan
FROM
    sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE
    cp.cacheobjtype = 'Compiled Plan'
    AND qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY
    cp.usecounts DESC;

SELECT TOP(20)

st.[text] AS [SqlText],

cp.cacheobjtype,

cp.objtype,

DB_NAME(st.[dbid]) AS [DatabaseName],

cp.usecounts,

qp.query_plan

FROM

sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE

cp.cacheobjtype = 'Compiled Plan'

AND qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0

ORDER BY

cp.usecounts DESC;

SQL Server Agent e Database Mail

Visualizar scripts

Listar o histórico dos e-mails enviados pelo Database Mail

Leitura complementar: SQL Server – Como ativar e configurar o Database mail para enviar e monitorar e-mails pelo banco de dados (sp_send_dbmail)

SELECT
    a.send_request_date AS DataEnvio,
    a.sent_date AS DataEntrega,
    (CASE    
        WHEN a.sent_status = 0 THEN '0 - Aguardando envio'
        WHEN a.sent_status = 1 THEN '1 - Enviado'
        WHEN a.sent_status = 2 THEN '2 - Falhou'
        WHEN a.sent_status = 3 THEN '3 - Tentando novamente'
    END) AS Situacao,
    a.from_address AS Remetente,
    A.recipients AS Destinatario,
    a.subject AS Assunto,
    a.reply_to AS ResponderPara,
    a.body AS Mensagem,
    a.body_format AS Formato,
    a.importance AS Importancia,
    a.file_attachments AS Anexos,
    a.send_request_user AS Usuario,
    B.description AS Erro,
    B.log_date AS DataFalha
FROM 
    msdb.dbo.sysmail_mailitems                  A    WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysmail_event_log        B    WITH(NOLOCK)    ON A.mailitem_id = B.mailitem_id

SELECT

a.send_request_date AS DataEnvio,

a.sent_date AS DataEntrega,

(CASE

WHEN a.sent_status = 0 THEN '0 - Aguardando envio'

WHEN a.sent_status = 1 THEN '1 - Enviado'

WHEN a.sent_status = 2 THEN '2 - Falhou'

WHEN a.sent_status = 3 THEN '3 - Tentando novamente'

END) AS Situacao,

a.from_address AS Remetente,

A.recipients AS Destinatario,

a.subject AS Assunto,

a.reply_to AS ResponderPara,

a.body AS Mensagem,

a.body_format AS Formato,

a.importance AS Importancia,

a.file_attachments AS Anexos,

a.send_request_user AS Usuario,

B.description AS Erro,

B.log_date AS DataFalha

FROM

msdb.dbo.sysmail_mailitems A WITH(NOLOCK)

LEFT JOIN msdb.dbo.sysmail_event_log B WITH(NOLOCK) ON A.mailitem_id = B.mailitem_id

Listar os jobs da instância e seus agendamentos

Leitura complementar: Como listar os Jobs (schedules, commands, steps) via Query no SQL Server

SELECT
    [sJOB].[name] AS [JobName] ,
    CASE [sJOB].[enabled]
      WHEN 1 THEN 'Yes'
      WHEN 0 THEN 'No'
    END AS [IsEnabled] ,
    [sJOB].[date_created] AS [JobCreatedOn] ,
    [sJOB].[date_modified] AS [JobLastModifiedOn] ,
    [sJSTP].[step_id] AS [StepNo] ,
    [sJSTP].[step_name] AS [StepName] ,
    [sDBP].[name] AS [JobOwner] ,
    [sCAT].[name] AS [JobCategory] ,
    [sJOB].[description] AS [JobDescription] ,
    CASE [sJSTP].[subsystem]
      WHEN 'ActiveScripting' THEN 'ActiveX Script'
      WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
      WHEN 'PowerShell' THEN 'PowerShell'
      WHEN 'Distribution' THEN 'Replication Distributor'
      WHEN 'Merge' THEN 'Replication Merge'
      WHEN 'QueueReader' THEN 'Replication Queue Reader'
      WHEN 'Snapshot' THEN 'Replication Snapshot'
      WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
      WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
      WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
      WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
      WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
      ELSE sJSTP.subsystem
    END AS [StepType] ,
    [sPROX].[name] AS [RunAs] ,
    [sJSTP].[database_name] AS [Database] ,
    REPLACE(REPLACE(REPLACE([sJSTP].[command], CHAR(10) + CHAR(13), ' '), CHAR(13), ' '), CHAR(10), ' ') AS [ExecutableCommand] ,
    CASE [sJSTP].[on_success_action]
      WHEN 1 THEN 'Quit the job reporting success'
      WHEN 2 THEN 'Quit the job reporting failure'
      WHEN 3 THEN 'Go to the next step'
      WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name]
    END AS [OnSuccessAction] ,
    [sJSTP].[retry_attempts] AS [RetryAttempts] ,
    [sJSTP].[retry_interval] AS [RetryInterval (Minutes)] ,
    CASE [sJSTP].[on_fail_action]
      WHEN 1 THEN 'Quit the job reporting success'
      WHEN 2 THEN 'Quit the job reporting failure'
      WHEN 3 THEN 'Go to the next step'
      WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name]
    END AS [OnFailureAction],
    CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled],
    [sSCH].[name] AS [JobScheduleName],
    CASE 
        WHEN [sSCH].[freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [sSCH].[freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [sSCH].[freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [sSCH].[freq_type] = 1 THEN 'One Time'
    END [ScheduleType], 
    CASE [sSCH].[freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
  END [Occurrence], 
  CASE [sSCH].[freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on '
                + CASE WHEN [sSCH].[freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                + CASE WHEN [sSCH].[freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                + CASE WHEN [sSCH].[freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                + CASE WHEN [sSCH].[freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                + CASE WHEN [sSCH].[freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                + CASE WHEN [sSCH].[freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                + CASE WHEN [sSCH].[freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([sSCH].[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                 + CASE [sSCH].[freq_relative_interval]
                    WHEN 1 THEN 'First'
                    WHEN 2 THEN 'Second'
                    WHEN 4 THEN 'Third'
                    WHEN 8 THEN 'Fourth'
                    WHEN 16 THEN 'Last'
                   END
                 + ' ' 
                 + CASE [sSCH].[freq_interval]
                    WHEN 1 THEN 'Sunday'
                    WHEN 2 THEN 'Monday'
                    WHEN 3 THEN 'Tuesday'
                    WHEN 4 THEN 'Wednesday'
                    WHEN 5 THEN 'Thursday'
                    WHEN 6 THEN 'Friday'
                    WHEN 7 THEN 'Saturday'
                    WHEN 8 THEN 'Day'
                    WHEN 9 THEN 'Weekday'
                    WHEN 10 THEN 'Weekend day'
                   END
                 + ' of every ' + CAST([sSCH].[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
    END AS [Recurrence], 
    CASE [sSCH].[freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' + CAST([sSCH].[freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' + CAST([sSCH].[freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' + CAST([sSCH].[freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
    END [Frequency], 
    STUFF(STUFF(CAST([sSCH].[active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageStartDate], 
    STUFF(STUFF(CAST([sSCH].[active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageEndDate], 
    [sSCH].[date_created] AS [ScheduleCreatedOn], 
    [sSCH].[date_modified] AS [ScheduleLastModifiedOn],
    CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
    END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id] AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id] AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY
    [JobName] ,
    [StepNo]

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

SELECT

[sJOB].[name] AS [JobName] ,

CASE [sJOB].[enabled]

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END AS [IsEnabled] ,

[sJOB].[date_created] AS [JobCreatedOn] ,

[sJOB].[date_modified] AS [JobLastModifiedOn] ,

[sJSTP].[step_id] AS [StepNo] ,

[sJSTP].[step_name] AS [StepName] ,

[sDBP].[name] AS [JobOwner] ,

[sCAT].[name] AS [JobCategory] ,

[sJOB].[description] AS [JobDescription] ,

CASE [sJSTP].[subsystem]

WHEN 'ActiveScripting' THEN 'ActiveX Script'

WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

WHEN 'PowerShell' THEN 'PowerShell'

WHEN 'Distribution' THEN 'Replication Distributor'

WHEN 'Merge' THEN 'Replication Merge'

WHEN 'QueueReader' THEN 'Replication Queue Reader'

WHEN 'Snapshot' THEN 'Replication Snapshot'

WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

ELSE sJSTP.subsystem

END AS [StepType] ,

[sPROX].[name] AS [RunAs] ,

[sJSTP].[database_name] AS [Database] ,

REPLACE(REPLACE(REPLACE([sJSTP].[command], CHAR(10) + CHAR(13), ' '), CHAR(13), ' '), CHAR(10), ' ') AS [ExecutableCommand] ,

CASE [sJSTP].[on_success_action]

WHEN 1 THEN 'Quit the job reporting success'

WHEN 2 THEN 'Quit the job reporting failure'

WHEN 3 THEN 'Go to the next step'

WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name]

END AS [OnSuccessAction] ,

[sJSTP].[retry_attempts] AS [RetryAttempts] ,

[sJSTP].[retry_interval] AS [RetryInterval (Minutes)] ,

CASE [sJSTP].[on_fail_action]

WHEN 1 THEN 'Quit the job reporting success'

WHEN 2 THEN 'Quit the job reporting failure'

WHEN 3 THEN 'Go to the next step'

WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name]

END AS [OnFailureAction],

CASE

WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'

ELSE 'Yes'

END AS [IsScheduled],

[sSCH].[name] AS [JobScheduleName],

CASE

WHEN [sSCH].[freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'

WHEN [sSCH].[freq_type] = 128 THEN 'Start whenever the CPUs become idle'

WHEN [sSCH].[freq_type] IN (4,8,16,32) THEN 'Recurring'

WHEN [sSCH].[freq_type] = 1 THEN 'One Time'

END [ScheduleType],

CASE [sSCH].[freq_type]

WHEN 1 THEN 'One Time'

WHEN 4 THEN 'Daily'

WHEN 8 THEN 'Weekly'

WHEN 16 THEN 'Monthly'

WHEN 32 THEN 'Monthly - Relative to Frequency Interval'

WHEN 64 THEN 'Start automatically when SQL Server Agent starts'

WHEN 128 THEN 'Start whenever the CPUs become idle'

END [Occurrence],

CASE [sSCH].[freq_type]

WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'

WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on '

+ CASE WHEN [sSCH].[freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END

+ CASE WHEN [sSCH].[freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END

+ CASE WHEN [sSCH].[freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END

+ CASE WHEN [sSCH].[freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END

+ CASE WHEN [sSCH].[freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END

+ CASE WHEN [sSCH].[freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END

+ CASE WHEN [sSCH].[freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END

WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([sSCH].[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

WHEN 32 THEN 'Occurs on '

+ CASE [sSCH].[freq_relative_interval]

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 4 THEN 'Third'

WHEN 8 THEN 'Fourth'

WHEN 16 THEN 'Last'

END

+ ' '

+ CASE [sSCH].[freq_interval]

WHEN 1 THEN 'Sunday'

WHEN 2 THEN 'Monday'

WHEN 3 THEN 'Tuesday'

WHEN 4 THEN 'Wednesday'

WHEN 5 THEN 'Thursday'

WHEN 6 THEN 'Friday'

WHEN 7 THEN 'Saturday'

WHEN 8 THEN 'Day'

WHEN 9 THEN 'Weekday'

WHEN 10 THEN 'Weekend day'

END

+ ' of every ' + CAST([sSCH].[freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'

END AS [Recurrence],

CASE [sSCH].[freq_subday_type]

WHEN 1 THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

WHEN 2 THEN 'Occurs every ' + CAST([sSCH].[freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

WHEN 4 THEN 'Occurs every ' + CAST([sSCH].[freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

WHEN 8 THEN 'Occurs every ' + CAST([sSCH].[freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_start_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')+ ' & ' + STUFF(STUFF(RIGHT('000000' + CAST([sSCH].[active_end_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')

END [Frequency],

STUFF(STUFF(CAST([sSCH].[active_start_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageStartDate],

STUFF(STUFF(CAST([sSCH].[active_end_date] AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-') AS [ScheduleUsageEndDate],

[sSCH].[date_created] AS [ScheduleCreatedOn],

[sSCH].[date_modified] AS [ScheduleLastModifiedOn],

CASE [sJOB].[delete_level]

WHEN 0 THEN 'Never'

WHEN 1 THEN 'On Success'

WHEN 2 THEN 'On Failure'

WHEN 3 THEN 'On Completion'

END AS [JobDeletionCriterion]

FROM

[msdb].[dbo].[sysjobsteps] AS [sJSTP]

INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]

LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id] AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]

LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id] AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]

LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]

LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]

LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]

LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]

LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]

ORDER BY

[JobName] ,

[StepNo]

Listar os jobs em execução

Leitura complementar: Como identificar os jobs em execução via Query no SQL Server

SELECT
    F.session_id,
    A.job_id,
    C.name AS job_name,
    F.login_name,
    F.[host_name],
    F.[program_name],
    A.start_execution_date,
    CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,
    ISNULL(A.last_executed_step_id, 0) + 1 AS current_executed_step_id,
    D.step_name,
    H.[text]
FROM
    msdb.dbo.sysjobactivity                     A   WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysjobhistory            B   WITH(NOLOCK)    ON A.job_history_id = B.instance_id
    JOIN msdb.dbo.sysjobs                       C   WITH(NOLOCK)    ON A.job_id = C.job_id
    JOIN msdb.dbo.sysjobsteps                   D   WITH(NOLOCK)    ON A.job_id = D.job_id AND ISNULL(A.last_executed_step_id, 0) + 1 = D.step_id
    JOIN (
        SELECT CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AS job_id, MAX(login_time) login_time
        FROM sys.dm_exec_sessions WITH(NOLOCK)
        WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep (Job % : Step %)'
        GROUP BY CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER)
    )                                           E                   ON C.job_id = E.job_id
    LEFT JOIN sys.dm_exec_sessions              F   WITH(NOLOCK)    ON E.job_id = (CASE WHEN BINARY_CHECKSUM(SUBSTRING(F.[program_name], 30, 34)) > 0 THEN CAST(TRY_CONVERT( BINARY(16), SUBSTRING(F.[program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) ELSE NULL END) AND E.login_time = F.login_time
    LEFT JOIN sys.dm_exec_connections           G   WITH(NOLOCK)    ON F.session_id = G.session_id
    OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) H
WHERE
    A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions	WITH(NOLOCK) ORDER BY agent_start_date DESC ) 
    AND A.start_execution_date IS NOT NULL 
    AND A.stop_execution_date IS NULL

SELECT

F.session_id,

A.job_id,

C.name AS job_name,

F.login_name,

F.[host_name],

F.[program_name],

A.start_execution_date,

CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,

ISNULL(A.last_executed_step_id, 0) + 1 AS current_executed_step_id,

D.step_name,

H.[text]

FROM

msdb.dbo.sysjobactivity A WITH(NOLOCK)

LEFT JOIN msdb.dbo.sysjobhistory B WITH(NOLOCK) ON A.job_history_id = B.instance_id

JOIN msdb.dbo.sysjobs C WITH(NOLOCK) ON A.job_id = C.job_id

JOIN msdb.dbo.sysjobsteps D WITH(NOLOCK) ON A.job_id = D.job_id AND ISNULL(A.last_executed_step_id, 0) + 1 = D.step_id

JOIN (

SELECT CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) AS job_id, MAX(login_time) login_time

FROM sys.dm_exec_sessions WITH(NOLOCK)

WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep (Job % : Step %)'

GROUP BY CAST(CONVERT( BINARY(16), SUBSTRING([program_name], 30, 34), 1) AS UNIQUEIDENTIFIER)

) E ON C.job_id = E.job_id

LEFT JOIN sys.dm_exec_sessions F WITH(NOLOCK) ON E.job_id = (CASE WHEN BINARY_CHECKSUM(SUBSTRING(F.[program_name], 30, 34)) > 0 THEN CAST(TRY_CONVERT( BINARY(16), SUBSTRING(F.[program_name], 30, 34), 1) AS UNIQUEIDENTIFIER) ELSE NULL END) AND E.login_time = F.login_time

LEFT JOIN sys.dm_exec_connections G WITH(NOLOCK) ON F.session_id = G.session_id

OUTER APPLY sys.dm_exec_sql_text(most_recent_sql_handle) H

WHERE

A.session_id = ( SELECT TOP 1 session_id FROM msdb.dbo.syssessions WITH(NOLOCK) ORDER BY agent_start_date DESC )

AND A.start_execution_date IS NOT NULL

AND A.stop_execution_date IS NULL

Histórico de execução dos Jobs

SELECT 
    A.job_id,
    A.[name],
    msdb.dbo.agent_datetime(B.run_date, B.run_time) AS execution_date,
    A.[enabled],
    B.step_id,
    B.step_name,
    B.[message],
    (CASE B.run_status
        WHEN 0 THEN '0 - Failed'
        WHEN 1 THEN '1 - Succeeded'
        WHEN 2 THEN '2 - Retry'
        WHEN 3 THEN '3 - Canceled'
        WHEN 4 THEN '4 - In Progress'
    END) AS run_status,
    B.run_duration
FROM
    msdb.dbo.sysjobs A
    JOIN msdb.dbo.sysjobhistory B ON B.job_id = A.job_id

SELECT

A.job_id,

A.[name],

msdb.dbo.agent_datetime(B.run_date, B.run_time) AS execution_date,

A.[enabled],

B.step_id,

B.step_name,

B.[message],

(CASE B.run_status

WHEN 0 THEN '0 - Failed'

WHEN 1 THEN '1 - Succeeded'

WHEN 2 THEN '2 - Retry'

WHEN 3 THEN '3 - Canceled'

WHEN 4 THEN '4 - In Progress'

END) AS run_status,

B.run_duration

FROM

msdb.dbo.sysjobs A

JOIN msdb.dbo.sysjobhistory B ON B.job_id = A.job_id

Histórico da atividade da execução dos jobs

Nessa visão, podemos identificar quando um job foi iniciado e quando será a próxima execução. A vantagem dessa view sobre a sysjobhistory, é que se o SQL Agent for reiniciado durante a execução de um job, não é gerado registro na sysjobhistory, mas pela sysjobactivity podemos identificar que um job estava em execução e não terminou.

SELECT 
    A.job_id,
    A.[name],
    B.session_id,
    B.run_requested_date,
    B.run_requested_source,
    B.queued_date,
    B.start_execution_date,
    B.last_executed_step_id,
    B.last_executed_step_date,
    B.stop_execution_date,
    B.job_history_id,
    B.next_scheduled_run_date	
FROM
    msdb.dbo.sysjobs A
    JOIN msdb.dbo.sysjobactivity B ON B.job_id = A.job_id

SELECT

A.job_id,

A.[name],

B.session_id,

B.run_requested_date,

B.run_requested_source,

B.queued_date,

B.start_execution_date,

B.last_executed_step_id,

B.last_executed_step_date,

B.stop_execution_date,

B.job_history_id,

B.next_scheduled_run_date

FROM

msdb.dbo.sysjobs A

JOIN msdb.dbo.sysjobactivity B ON B.job_id = A.job_id

Procurando uma string em um job (ou step) no SQL Server

Leitura complementar: Procurando uma string em um job (ou step) no SQL Server

DECLARE @String VARCHAR(100) = 'Clientes'

SELECT
    [sJOB].[name] AS [JobName] ,
    CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END AS [IsEnabled] ,
    [sJOB].[date_created] AS [JobCreatedOn] ,
    [sJOB].[date_modified] AS [JobLastModifiedOn] ,
    [sJSTP].[step_id] AS [StepNo] ,
    [sJSTP].[step_name] AS [StepName] ,
    [sDBP].[name] AS [JobOwner] ,
    [sCAT].[name] AS [JobCategory] ,
    [sJOB].[description] AS [JobDescription] ,
    CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
    END AS [StepType] ,
    [sPROX].[name] AS [RunAs] ,
    [sJSTP].[database_name] AS [Database] ,
    [sJSTP].[command] AS [ExecutableCommand] ,
    CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3))) + ' ' + [sOSSTP].[step_name]
    END AS [OnSuccessAction] ,
    [sJSTP].[retry_attempts] AS [RetryAttempts] ,
    [sJSTP].[retry_interval] AS [RetryInterval (Minutes)] ,
    CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3))) + ' ' + [sOFSTP].[step_name]
    END AS [OnFailureAction],
    CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
        END AS [IsScheduled],
    [sSCH].[name] AS [JobScheduleName],
    CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
        END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB] ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP] ON [sJSTP].[job_id] = [sOSSTP].[job_id] AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP] ON [sJSTP].[job_id] = [sOFSTP].[job_id] AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX] ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT] ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP] ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH] ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
WHERE
    [sJSTP].[command] LIKE '%' + @String + '%'
    OR [sJOB].[name] LIKE '%' + @String + '%'
ORDER BY
    [JobName] ,
    [StepNo]

DECLARE @String VARCHAR(100) = 'Clientes'

SELECT

[sJOB].[name] AS [JobName] ,

CASE [sJOB].[enabled]

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

END AS [IsEnabled] ,

[sJOB].[date_created] AS [JobCreatedOn] ,

[sJOB].[date_modified] AS [JobLastModifiedOn] ,

[sJSTP].[step_id] AS [StepNo] ,

[sJSTP].[step_name] AS [StepName] ,

[sDBP].[name] AS [JobOwner] ,

[sCAT].[name] AS [JobCategory] ,

[sJOB].[description] AS [JobDescription] ,

CASE [sJSTP].[subsystem]

WHEN 'ActiveScripting' THEN 'ActiveX Script'

WHEN 'CmdExec' THEN 'Operating system (CmdExec)'

WHEN 'PowerShell' THEN 'PowerShell'

WHEN 'Distribution' THEN 'Replication Distributor'

WHEN 'Merge' THEN 'Replication Merge'

WHEN 'QueueReader' THEN 'Replication Queue Reader'

WHEN 'Snapshot' THEN 'Replication Snapshot'

WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'

WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'

WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'

WHEN 'SSIS' THEN 'SQL Server Integration Services Package'

WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'

ELSE sJSTP.subsystem

END AS [StepType] ,

[sPROX].[name] AS [RunAs] ,

[sJSTP].[database_name] AS [Database] ,

[sJSTP].[command] AS [ExecutableCommand] ,