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 scriptsVisã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;
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))
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))
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]
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
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]
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
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
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
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
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
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
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
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
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
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
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
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 );
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
Gerar script dos índices das tabelas
/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: [email protected]
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
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
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
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
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:
- Verificando as permissões de um usuário no SQL Server
- SQL Server – Como transferir logins entre instâncias gerando backup de usuários, logins e permissões
- SQL Server – Como copiar/replicar as permissões de um usuário.
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
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
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
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]
Espaço em disco
Visualizar scriptsTamanho das tabelas
Com o script abaixo, poderemos visualizar o tamanho alocado em disco por cada tabela do banco de dados. Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases
SELECT
s.[name] AS [schema],
t.[name] AS [table_name],
p.[rows] AS [row_count],
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb],
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
sys.tables t
JOIN sys.indexes i ON t.[object_id] = i.[object_id]
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE
t.is_ms_shipped = 0
AND i.[object_id] > 255
GROUP BY
t.[name],
s.[name],
p.[rows]
ORDER BY
[size_mb] DESC
Tamanho dos índices
Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases
SELECT
s.[name] AS [schema],
t.[name] AS [table_name],
i.[name] AS [index_name],
i.[type_desc],
p.[rows] AS [row_count],
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb],
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb],
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb]
FROM
sys.tables t
JOIN sys.indexes i ON t.[object_id] = i.[object_id]
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE
t.is_ms_shipped = 0
AND i.[object_id] > 255
GROUP BY
t.[name],
s.[name],
i.[name],
i.[type_desc],
p.[rows]
ORDER BY
[size_mb] DESC
Tamanho dos arquivos de dados e log (datafiles e logfiles)
Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases
IF (OBJECT_ID('tempdb..#Datafile_Size ') IS NOT NULL) DROP TABLE #Datafile_Size
SELECT
B.database_id AS database_id,
B.[name] AS [database_name],
A.state_desc,
A.[type_desc],
A.[file_id],
A.[name],
A.physical_name,
CAST(C.total_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_total_size_GB,
CAST(C.available_bytes / 1073741824.0 AS NUMERIC(18, 2)) AS disk_free_size_GB,
CAST(A.size / 128 / 1024.0 AS NUMERIC(18, 2)) AS size_GB,
CAST(A.max_size / 128 / 1024.0 AS NUMERIC(18, 2)) AS max_size_GB,
CAST(
(CASE
WHEN A.growth <= 0 THEN A.size / 128 / 1024.0
WHEN A.max_size <= 0 THEN C.total_bytes / 1073741824.0
WHEN A.max_size / 128 / 1024.0 > C.total_bytes / 1073741824.0 THEN C.total_bytes / 1073741824.0
ELSE A.max_size / 128 / 1024.0
END) AS NUMERIC(18, 2)) AS max_real_size_GB,
CAST(NULL AS NUMERIC(18, 2)) AS free_space_GB,
(CASE WHEN A.is_percent_growth = 1 THEN A.growth ELSE CAST(A.growth / 128 AS NUMERIC(18, 2)) END) AS growth_MB,
A.is_percent_growth,
(CASE WHEN A.growth <= 0 THEN 0 ELSE 1 END) AS is_autogrowth_enabled,
CAST(NULL AS NUMERIC(18, 2)) AS percent_used,
CAST(NULL AS INT) AS growth_times
INTO
#Datafile_Size
FROM
sys.master_files A WITH(NOLOCK)
JOIN sys.databases B WITH(NOLOCK) ON A.database_id = B.database_id
CROSS APPLY sys.dm_os_volume_stats(A.database_id, A.[file_id]) C
UPDATE A
SET
A.free_space_GB = (
(CASE
WHEN max_size_GB <= 0 THEN A.disk_free_size_GB
WHEN max_real_size_GB > disk_free_size_GB THEN A.disk_free_size_GB
ELSE max_real_size_GB - size_GB
END)),
A.percent_used = (
CASE WHEN (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END) = 0 THEN 0
ELSE ((size_GB / (CASE WHEN max_real_size_GB > disk_total_size_GB THEN A.disk_total_size_GB ELSE max_real_size_GB END)) * 100)
END)
FROM
#Datafile_Size A
UPDATE A
SET
A.growth_times =
(CASE
WHEN A.growth_MB <= 0 THEN 0
WHEN A.is_percent_growth = 0 THEN (A.max_real_size_GB - A.size_GB) / (A.growth_MB / 1024.0)
ELSE NULL
END)
FROM
#Datafile_Size A
SELECT *
FROM #Datafile_Size
Tamanho dos discos
Leitura complementar: SQL Server – Como identificar e monitorar o espaço em disco total, livre e utilizado pelos datafiles dos databases
SELECT DISTINCT
VS.volume_mount_point [Montagem] ,
VS.logical_volume_name AS [Volume] ,
CAST(CAST(VS.total_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Total (GB)] ,
CAST(CAST(VS.available_bytes AS DECIMAL(19, 2)) / 1024 / 1024 / 1024 AS DECIMAL(10, 2)) AS [Espaço Disponível (GB)] ,
CAST(( CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço Disponível ( % )] ,
CAST(( 100 - CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 ) AS DECIMAL(10, 2)) AS [Espaço em uso ( % )]
FROM
sys.master_files AS MF
CROSS APPLY [sys].[dm_os_volume_stats](MF.database_id, MF.file_id) AS VS
WHERE
CAST(VS.available_bytes AS DECIMAL(19, 2)) / CAST(VS.total_bytes AS DECIMAL(19, 2)) * 100 < 100;
Performance
Para um melhor entendimento dos scripts dessa sessão, recomendo a leitura da série de posts Performance Tuning.
Visualizar scripts
Eventos de wait
;WITH [Waits]
AS ( SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
( [wait_time_ms] - [signal_wait_time_ms] ) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS [Percentage],
ROW_NUMBER() OVER ( ORDER BY
[wait_time_ms] DESC
) AS [RowNum]
FROM
sys.dm_os_wait_stats
WHERE
[wait_type] NOT IN (
-- These wait types are almost 100% never a problem and so they are
-- filtered out to avoid them skewing the results. Click on the URL
-- for more information.
N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER
-- Maybe comment these four out if you have mirroring issues
N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD
N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX
-- Maybe comment these six out if you have AG issues
N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE
N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
)
AND [waiting_tasks_count] > 0 )
SELECT
MAX([W1].[wait_type]) AS [WaitType],
CAST(MAX([W1].[WaitS]) AS DECIMAL(16, 2)) AS [Wait_S],
CAST(MAX([W1].[ResourceS]) AS DECIMAL(16, 2)) AS [Resource_S],
CAST(MAX([W1].[SignalS]) AS DECIMAL(16, 2)) AS [Signal_S],
MAX([W1].[WaitCount]) AS [WaitCount],
CAST(MAX([W1].[Percentage]) AS DECIMAL(5, 2)) AS [Percentage],
CAST(( MAX([W1].[WaitS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgWait_S],
CAST(( MAX([W1].[ResourceS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgRes_S],
CAST(( MAX([W1].[SignalS]) / MAX([W1].[WaitCount])) AS DECIMAL(16, 4)) AS [AvgSig_S],
CAST('https://www.sqlskills.com/help/waits/' + MAX([W1].[wait_type]) AS XML) AS [Help/Info URL]
FROM
[Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY
[W1].[RowNum]
HAVING
SUM([W2].[Percentage]) - MAX([W1].[Percentage]) < 95; -- percentage threshold
GO
Consulta a configuração de energia do servidor
Se você acha que a configuração de energia do seu servidor não faz diferença na performance do seu SQL Server, dê uma lida neste artigo aqui.
DECLARE
@value VARCHAR(64),
@key VARCHAR(512) = 'SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes'
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = @key,
@value_name = 'ActivePowerScheme',
@value = @value OUTPUT;
SELECT (CASE
WHEN @value = '381b4222-f694-41f0-9685-ff5bb260df2e' THEN '(Balanced)'
WHEN @value = '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c' THEN '(High performance)'
WHEN @value = 'a1841308-3541-4fab-bc81-f71556f20b4a' THEN '(Power saver)'
END)
Histórico do consumo de CPU (últimos 256 minutos)
DECLARE @ts_now BIGINT =
(
SELECT
cpu_ticks / ( cpu_ticks / ms_ticks )
FROM
sys.dm_os_sys_info WITH ( NOLOCK )
);
SELECT TOP ( 256 )
SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE()) AS [Event Time]
FROM
(
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[timestamp]
FROM
(
SELECT
[timestamp],
CONVERT(XML, record) AS [record]
FROM
sys.dm_os_ring_buffers WITH ( NOLOCK )
WHERE
ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE N'%<SystemHealth>%'
) AS x
) AS y
ORDER BY
record_id DESC
OPTION ( RECOMPILE );
Monitora o uso de memória na instância
-- https://blogs.msdn.microsoft.com/mvpawardprogram/2012/06/04/using-sys-dm_os_ring_buffers-to-diagnose-memory-issues-in-sql-server/
WITH RingBuffer
AS ( SELECT
CAST(dorb.record AS XML) AS xRecord,
dorb.timestamp
FROM
sys.dm_os_ring_buffers AS dorb
WHERE
dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' )
SELECT
xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
xr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') AS IndicatorsProcess,
xr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'tinyint') AS IndicatorsSystem,
DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime,
xr.value('(MemoryNode/TargetMemory)[1]', 'bigint') AS TargetMemory,
xr.value('(MemoryNode/ReserveMemory)[1]', 'bigint') AS ReserveMemory,
xr.value('(MemoryNode/CommittedMemory)[1]', 'bigint') AS CommitedMemory,
xr.value('(MemoryNode/SharedMemory)[1]', 'bigint') AS SharedMemory,
xr.value('(MemoryNode/PagesMemory)[1]', 'bigint') AS PagesMemory,
xr.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization,
xr.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS TotalPhysicalMemory,
xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS AvailablePhysicalMemory,
xr.value('(MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile,
xr.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile,
xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS TotalVirtualAddressSpace,
xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS AvailableVirtualAddressSpace,
xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint') AS AvailableExtendedVirtualAddressSpace
FROM
RingBuffer AS rb
CROSS APPLY rb.xRecord.nodes('Record') record(xr)
CROSS JOIN sys.dm_os_sys_info AS dosi
ORDER BY
RmDateTime DESC;
Verifica desempenho de I/O dos discos
SELECT
DB_NAME(fs.database_id) AS [Database Name],
mf.physical_name,
io_stall_read_ms,
num_of_reads,
CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms],
io_stall_write_ms,
num_of_writes,
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls],
num_of_reads + num_of_writes AS [total_io],
CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
ORDER BY
avg_io_stall_ms DESC;
Verifica a fila de disco (requisições pendentes de I/O)
SELECT
DB_NAME(mf.database_id) AS [Database],
mf.physical_name,
r.io_pending,
r.io_pending_ms_ticks,
r.io_type,
fs.num_of_reads,
fs.num_of_writes,
GETDATE()
FROM
sys.dm_io_pending_io_requests AS r
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
ORDER BY
r.io_pending,
r.io_pending_ms_ticks DESCQuantidade de páginas e memória alocada por database
SELECT
CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id)END AS database_name,
COUNT(*) AS cached_pages_count,
COUNT(*) * .0078125 AS cached_megabytes /* Each page is 8kb, which is .0078125 of an MB */
FROM
sys.dm_os_buffer_descriptors
GROUP BY
DB_NAME(database_id),
database_id
ORDER BY
cached_pages_count DESC;
Backup e Restore
Visualizar scriptsHistórico dos backups realizados na instância
SELECT
B.[database_name],
(CASE B.[type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'TLog Backup'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END) AS BackupType,
B.recovery_model AS RecoveryModel,
B.backup_start_date,
B.backup_finish_date,
CAST(DATEDIFF(SECOND,B.backup_start_date, B.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' AS TotalTimeTaken,
B.expiration_date,
B.[user_name],
B.machine_name,
B.is_password_protected,
B.collation_name,
B.is_copy_only,
CONVERT(NUMERIC(20, 2), B.backup_size / 1048576) AS BackupSizeMB,
A.logical_device_name,
A.physical_device_name,
B.[name] AS backupset_name,
B.[description],
B.has_backup_checksums,
B.is_damaged,
B.has_incomplete_metadata
FROM
sys.databases X
JOIN msdb.dbo.backupset B ON X.[name] = B.[database_name]
JOIN msdb.dbo.backupmediafamily A ON A.media_set_id = B.media_set_id
WHERE
B.backup_start_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))
Histórico dos Restores realizados na instância
SELECT
A.[restore_history_id],
A.[restore_date],
A.[destination_database_name],
C.physical_device_name,
A.[user_name],
A.[backup_set_id],
CASE A.[restore_type]
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File'
WHEN 'G' THEN 'Filegroup'
WHEN 'V' THEN 'Verifyonlyl'
END AS RestoreType,
A.[replace],
A.[recovery],
A.[restart],
A.[stop_at],
A.[device_count],
A.[stop_at_mark_name],
A.[stop_before]
FROM
[msdb].[dbo].[restorehistory] A
JOIN [msdb].[dbo].[backupset] B ON A.backup_set_id = B.backup_set_id
JOIN msdb.dbo.backupmediafamily C ON B.media_set_id = C.media_set_id
WHERE
A.restore_date >= CONVERT(DATE, DATEADD(DAY, -7, GETDATE()))Visualizar databases há mais de 7 dias sem fazer backup
SELECT
A.[name] AS [database_name],
A.recovery_model_desc,
(SELECT SUM(CAST(size / 128 / 1024.0 AS NUMERIC(18, 2))) FROM sys.master_files WHERE A.[name] = [name]) AS size_GB,
MAX(B.backup_start_date) AS last_backup_date
FROM
sys.databases A
LEFT JOIN msdb.dbo.backupset B ON A.[name] = B.[database_name]
WHERE
(B.backup_set_id IS NULL OR DATEDIFF(DAY, B.backup_start_date, GETDATE()) > 7)
AND A.[name] NOT IN ('tempdb', 'model')
GROUP BY
A.[name],
A.recovery_model_desc
Visualizar backups/restore em andamento e estimar o tempo restante
SELECT
R.session_id,
R.command AS Ds_Operacao,
B.name AS Nm_Banco,
R.start_time AS Dt_Inicio,
CONVERT(VARCHAR(20), DATEADD(MS, R.estimated_completion_time, GETDATE()), 20) AS Dt_Previsao_Fim,
CONVERT(NUMERIC(6, 2), R.percent_complete) AS Vl_Percentual_Concluido,
CONVERT(NUMERIC(6, 2), R.total_elapsed_time / 1000.0 / 60.0) AS Qt_Minutos_Execucao,
CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0) AS Qt_Minutos_Restantes,
CONVERT(NUMERIC(6, 2), R.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS Qt_Horas_Restantes,
CONVERT(VARCHAR(MAX), ( SELECT
SUBSTRING(text, R.statement_start_offset / 2, CASE WHEN R.statement_end_offset = -1 THEN 1000 ELSE ( R.statement_end_offset - R.statement_start_offset ) / 2 END)
FROM
sys.dm_exec_sql_text(sql_handle)
)) AS Ds_Comando
FROM
sys.dm_exec_requests R WITH(NOLOCK)
JOIN sys.databases B WITH(NOLOCK) ON R.database_id = B.database_id
WHERE
R.command IN (
'BACKUP DATABASE',
'RESTORE DATABASE',
'ALTER INDEX REORGANIZE',
'AUTO_SHRINK option with ALTER DATABASE',
'CREATE INDEX',
'DBCC CHECKDB',
'DBCC CHECKFILEGROUP',
'DBCC CHECKTABLE',
'DBCC INDEXDEFRAG',
'DBCC SHRINKDATABASE',
'DBCC SHRINKFILE',
'KILL',
'UPDATE STATISTICS',
'DBCC'
)
AND R.estimated_completion_time > 0 
Plan cache
Para um melhor entendimento dos scripts dessa sessão, recomendo a leitura da série de posts Performance Tuning.
Visualizar scripts
Visualizar plano das consultas em execução
SELECT
B.start_time,
A.session_id,
B.command,
A.login_name,
A.[host_name],
A.[program_name],
B.logical_reads,
B.cpu_time,
B.writes,
B.blocking_session_id,
C.query_plan
FROM
sys.dm_exec_sessions AS A WITH (NOLOCK)
LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id
OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS C
WHERE
A.session_id > 50
AND A.session_id <> @@SPID
AND (A.[status] <> 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0))
ORDER BY
B.start_time
Visualizar planos em cache
SELECT
cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.text AS QueryText,
qp.query_plan AS QueryPlan
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
ORDER BY
ExecutionCount DESC
Identificar consultas pesadas utilizando a plancache
SELECT TOP(100)
DB_NAME(C.[dbid]) as [database],
B.[text],
(SELECT CAST(SUBSTRING(B.[text], (A.statement_start_offset/2)+1,
(((CASE A.statement_end_offset
WHEN -1 THEN DATALENGTH(B.[text])
ELSE A.statement_end_offset
END) - A.statement_start_offset)/2) + 1) AS NVARCHAR(MAX)) FOR XML PATH(''), TYPE) AS [TSQL],
C.query_plan,
A.last_execution_time,
A.execution_count,
A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
A.total_worker_time / 1000 AS total_worker_time_ms,
A.last_worker_time / 1000 AS last_worker_time_ms,
A.min_worker_time / 1000 AS min_worker_time_ms,
A.max_worker_time / 1000 AS max_worker_time_ms,
((A.total_worker_time / a.execution_count) / 1000) AS avg_worker_time_ms,
A.total_physical_reads,
A.last_physical_reads,
A.min_physical_reads,
A.max_physical_reads,
A.total_logical_reads,
A.last_logical_reads,
A.min_logical_reads,
A.max_logical_reads,
A.total_logical_writes,
A.last_logical_writes,
A.min_logical_writes,
A.max_logical_writes
FROM
sys.dm_exec_query_stats A
CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) B
OUTER APPLY sys.dm_exec_query_plan (A.plan_handle) AS C
ORDER BY
A.total_elapsed_time DESC
Identificar as Stored Procedures pesadas utilizando a plancache
SELECT TOP(100)
B.[name] AS rotina,
A.cached_time,
A.last_execution_time,
A.execution_count,
A.total_elapsed_time / 1000 AS total_elapsed_time_ms,
A.last_elapsed_time / 1000 AS last_elapsed_time_ms,
A.min_elapsed_time / 1000 AS min_elapsed_time_ms,
A.max_elapsed_time / 1000 AS max_elapsed_time_ms,
((A.total_elapsed_time / A.execution_count) / 1000) AS avg_elapsed_time_ms,
A.total_worker_time / 1000 AS total_worker_time_ms,
A.last_worker_time / 1000 AS last_worker_time_ms,
A.min_worker_time / 1000 AS min_worker_time_ms,
A.max_worker_time / 1000 AS max_worker_time_ms,
((A.total_worker_time / A.execution_count) / 1000) AS avg_worker_time_ms,
A.total_physical_reads,
A.last_physical_reads,
A.min_physical_reads,
A.max_physical_reads,
A.total_logical_reads,
A.last_logical_reads,
A.min_logical_reads,
A.max_logical_reads,
A.total_logical_writes,
A.last_logical_writes,
A.min_logical_writes,
A.max_logical_writes
FROM
sys.dm_exec_procedure_stats A
JOIN sys.objects B ON A.[object_id] = B.[object_id]
ORDER BY
A.execution_count DESC
Identificar eventos de Key Lookup através da plancache
/*********************************************************************************************
Find Key Lookups in Cached Plans v1.00 (2010-07-27)
(C) 2010, Kendal Van Dyke
Feedback: mailto:[email protected]
License:
This query is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this query, in whole or in part, is prohibited without the author's express
written consent.
Note:
Exercise caution when running this in production!
The function sys.dm_exec_query_plan() is resource intensive and can put strain
on a server when used to retrieve all cached query plans.
Consider using TOP in the initial select statement (insert into @plans)
to limit the impact of running this query or run during non-peak hours
*********************************************************************************************/
DECLARE @plans TABLE
(
query_text NVARCHAR(MAX),
o_name sysname,
execution_plan XML,
last_execution_time DATETIME,
execution_count BIGINT,
total_worker_time BIGINT,
total_physical_reads BIGINT,
total_logical_reads BIGINT
);
DECLARE @lookups TABLE
(
table_name sysname,
index_name sysname,
index_cols NVARCHAR(MAX)
);
WITH query_stats
AS (
SELECT
[sql_handle],
[plan_handle],
MAX(last_execution_time) AS last_execution_time,
SUM(execution_count) AS execution_count,
SUM(total_worker_time) AS total_worker_time,
SUM(total_physical_reads) AS total_physical_reads,
SUM(total_logical_reads) AS total_logical_reads
FROM
sys.dm_exec_query_stats
GROUP BY
[sql_handle],
[plan_handle]
)
INSERT INTO @plans
(
query_text,
o_name,
execution_plan,
last_execution_time,
execution_count,
total_worker_time,
total_physical_reads,
total_logical_reads
)
SELECT /*TOP 50*/
sql_text.[text],
CASE
WHEN sql_text.objectid IS NOT NULL THEN ISNULL(OBJECT_NAME(sql_text.objectid, sql_text.[dbid]), 'Unresolved')
ELSE CAST('Ad-hoc\Prepared' AS sysname)
END,
query_plan.query_plan,
query_stats.last_execution_time,
query_stats.execution_count,
query_stats.total_worker_time,
query_stats.total_physical_reads,
query_stats.total_logical_reads
FROM
query_stats
CROSS APPLY sys.dm_exec_sql_text(query_stats.sql_handle) AS [sql_text]
CROSS APPLY sys.dm_exec_query_plan(query_stats.plan_handle) AS [query_plan]
WHERE
query_plan.query_plan IS NOT NULL;
;WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
, lookups
AS ( SELECT
DB_ID(REPLACE(REPLACE(keylookups.keylookup.value('(Object/@Database)[1]', 'sysname'), '[', ''), ']', '')) AS [database_id],
OBJECT_ID(keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') + '.' + keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') + '.' + keylookups.keylookup.value('(Object/@Table)[1]', 'sysname')) AS [object_id],
keylookups.keylookup.value('(Object/@Database)[1]', 'sysname') AS [database],
keylookups.keylookup.value('(Object/@Schema)[1]', 'sysname') AS [schema],
keylookups.keylookup.value('(Object/@Table)[1]', 'sysname') AS [table],
keylookups.keylookup.value('(Object/@Index)[1]', 'sysname') AS [index],
REPLACE(keylookups.keylookup.query('
for $column in DefinedValues/DefinedValue/ColumnReference
return string($column/@Column)
').value('.', 'varchar(max)'), ' ', ', ') AS [columns],
plans.query_text,
plans.o_name,
plans.execution_plan,
plans.last_execution_time,
plans.execution_count,
plans.total_worker_time,
plans.total_physical_reads,
plans.total_logical_reads
FROM
@plans AS [plans]
CROSS APPLY execution_plan.nodes('//RelOp/IndexScan[@Lookup="1"]') AS keylookups(keylookup) )
SELECT
lookups.[database],
lookups.[schema],
lookups.[table],
lookups.[index],
lookups.[columns],
index_stats.user_lookups,
index_stats.last_user_lookup,
lookups.execution_count,
lookups.total_worker_time,
lookups.total_physical_reads,
lookups.total_logical_reads,
lookups.last_execution_time,
lookups.o_name AS [object_name],
lookups.query_text,
lookups.execution_plan
FROM
lookups
INNER JOIN sys.dm_db_index_usage_stats AS [index_stats] ON lookups.database_id = index_stats.database_id
AND lookups.[object_id] = index_stats.[object_id]
WHERE
index_stats.user_lookups > 0
AND lookups.[database] NOT IN ( '[master]', '[model]', '[msdb]', '[tempdb]' )
ORDER BY
index_stats.user_lookups DESC,
lookups.total_physical_reads DESC,
lookups.total_logical_reads DESC
Identificar conversão implícita através da plancache
SELECT TOP ( 100 )
DB_NAME(B.[dbid]) AS [Database],
B.[text] AS [Consulta],
A.total_worker_time AS [Total Worker Time],
A.total_worker_time / A.execution_count AS [Avg Worker Time],
A.max_worker_time AS [Max Worker Time],
A.total_elapsed_time / A.execution_count AS [Avg Elapsed Time],
A.max_elapsed_time AS [Max Elapsed Time],
A.total_logical_reads / A.execution_count AS [Avg Logical Reads],
A.max_logical_reads AS [Max Logical Reads],
A.execution_count AS [Execution Count],
A.creation_time AS [Creation Time],
C.query_plan AS [Query Plan]
FROM
sys.dm_exec_query_stats AS A WITH ( NOLOCK )
CROSS APPLY sys.dm_exec_sql_text(A.plan_handle) AS B
CROSS APPLY sys.dm_exec_query_plan(A.plan_handle) AS C
WHERE
CAST(C.query_plan AS NVARCHAR(MAX)) LIKE ( '%PlanAffectingConvert%ConvertIssue%CONVERT_IMPLICIT%' )
AND B.[dbid] = DB_ID()
AND B.[text] NOT LIKE '%sys.dm_exec_sql_text%' -- Não pegar a própria consulta
ORDER BY
A.total_worker_time DESC
Planos em cache com alertas de Missing Index
WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
query_plan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '')) AS database_id,
OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' + n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,
(
SELECT DISTINCT
c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM
n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE
cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS equality_columns,
(
SELECT DISTINCT
c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM
n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE
cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS inequality_columns,
(
SELECT DISTINCT
c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM
n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE
cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS include_columns
INTO
#MissingIndexInfo
FROM
(
SELECT
query_plan
FROM
(
SELECT DISTINCT
plan_handle
FROM
sys.dm_exec_query_stats WITH ( NOLOCK )
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE
tp.query_plan.exist('//MissingIndex') = 1
) AS tab(query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE
n.exist('QueryPlan/MissingIndexes') = 1;
-- Trim trailing comma from lists
UPDATE
#MissingIndexInfo
SET
equality_columns = LEFT(equality_columns, LEN(equality_columns) - 1),
inequality_columns = LEFT(inequality_columns, LEN(inequality_columns) - 1),
include_columns = LEFT(include_columns, LEN(include_columns) - 1);
SELECT
*
FROM
#MissingIndexInfo;
Consultas em cache utilizando paralelismo
SELECT TOP(20)
st.[text] AS [SqlText],
cp.cacheobjtype,
cp.objtype,
DB_NAME(st.[dbid]) AS [DatabaseName],
cp.usecounts,
qp.query_plan
FROM
sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE
cp.cacheobjtype = 'Compiled Plan'
AND qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; max(//p:RelOp/@Parallel)', 'float') > 0
ORDER BY
cp.usecounts DESC;
SQL Server Agent e Database Mail
Visualizar scriptsÉ isso aí, pessoal!
Espero que tenham gostado desse artigo e te ajude no seu dia a dia!
Comentários (0)
Carregando comentários…