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

Visualizações: 15.569 views

Neste artigo

Tempo de Leitura: 42 minutos

Fala pessoal!
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 Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server.

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 Utilizando o trace padrão do SQL Server para auditar eventos (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 Utilizando o trace padrão do SQL Server para auditar eventos (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 Comprimindo todas as tabelas de um database no 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

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

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

Informações gerais

Visão geral dos databases e configurações

Consultas que estão em execução atualmente

Verifica os principais parâmetros da instância

Identifica o uso da transaction log de cada database na instância (equivalente ao DBCC SQLPERF(LOGSPACE))

Identifica e tenta corrigir usuários órfãos

Identifica quando o serviço do SQL Server foi iniciado

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

Identifica quando backups foram gerados ou restaurados

Identifica eventos de auto growth

Identifica a execução de comandos DBCC

Tabelas e índices

Identificar tabelas e índices sem compressão

Identificar fragmentação dos índices

Identificar tabelas HEAP (sem índice clustered)

Utilização dos índices

Identificando possíveis bons candidatos para índice Clustered

Sugestões de Missing Index

Estatísticas há mais de 7 dias sem atualizar

Gerar script dos índices das tabelas

Lock, Block e Deadlock

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

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

Identificar eventos de deadlock

Permissões

Database roles

Permissões a nível de database

Server roles

Permissões a nível de instância