Monitoramento no SQL Server – O que fazer quando receber um alerta de crescimento do Tempdb? – Fabricio Lima

Monitoramento no SQL Server – O que fazer quando receber um alerta de crescimento do Tempdb?

19 de agosto de 202020 de agosto de 2020Fabrício LimaAdministração de Banco de Dados, DBA Remoto, Virtual PASS BR

Fala Pessoal,

Em Setembro de 2019 liberei uma nova versão dos meus scripts para a criação de 40 alertas no SQL Server:

https://www.fabriciolima.net/blog/2019/09/22/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server/

Legal Fabrício, mas o que eu faço quando receber esses Alertas?

Esse é mais um vídeo para ajudar a responder essa pergunta:

Queries utilizadas nesse post:

select *
from Traces..Alert_Parameter
where Nm_Alert = 'Tempdb MDF File Utilization'


-- Analisar a utilização do tempdb
SELECT a.name AS LogicalName,
'SizeinMB' = (size/128)
,fileproperty(a.name, 'spaceused' )/128 as UsedinMB
,(size/128) -fileproperty (a.name,'SpaceUsed')/128 AS FreeInMB
,'Free%'=cast (((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0)/(a.size/128.0)*100 as numeric(15))
, ((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0) / SUM ((a.size/128.0)-(fileproperty(a.name,'SpaceUsed')/128)) OVER (PARTITION BY fg.data_space_id) As [PropFree%]
,fg.name
FROM sysfiles a LEFT join sys.filegroups fg 
ON a.groupid = fg.data_space_id



update Traces..Alert_Parameter
set Vl_Parameter_2 = 4000  --default é 10000 MB
where Nm_Alert = 'Tempdb MDF File Utilization'

sp_whoisactive

--Query que pode ajudar a pegar o que está no tempdb
;with tab(session_id, host_name, login_name, totalalocadomb, text)
as(
SELECT a.session_id,
b.host_name,
b.login_name,
( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 / 128 AS totalalocadomb,
d.TEXT
FROM sys.dm_db_session_space_usage a
JOIN sys.dm_exec_sessions b ON a.session_id = b.session_id
JOIN sys.dm_exec_connections c ON c.session_id = b.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS d
WHERE a.session_id > 50
--AND ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 / 128 > 10 -- Ocupam mais de 10 MB
)
select top 20 * from tab order by 4 desc


SELECT a.name AS LogicalName,
'SizeinMB' = (size/128)
,fileproperty(a.name, 'spaceused' )/128 as UsedinMB
,(size/128) -fileproperty (a.name,'SpaceUsed')/128 AS FreeInMB
,'Free%'=cast (((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0)/(a.size/128.0)*100 as numeric(15))
, ((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0) / SUM ((a.size/128.0)-(fileproperty(a.name,'SpaceUsed')/128)) OVER (PARTITION BY fg.data_space_id) As [PropFree%]
,fg.name
FROM sysfiles a LEFT join sys.filegroups fg 
ON a.groupid = fg.data_space_id

kill 66
kill 65



----- rquivo para testar a carga no TEMPDB
--drop table #StressTempDB

 SELECT TOP 1000000000
        IDENTITY(INT,1,1) AS RowNum
   INTO #StressTempDB
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2,
        master.sys.all_columns ac3;
GO

select *

from Traces..Alert_Parameter

where Nm_Alert = 'Tempdb MDF File Utilization'

-- Analisar a utilização do tempdb

SELECT a.name AS LogicalName,

'SizeinMB' = (size/128)

,fileproperty(a.name, 'spaceused' )/128 as UsedinMB

,(size/128) -fileproperty (a.name,'SpaceUsed')/128 AS FreeInMB

,'Free%'=cast (((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0)/(a.size/128.0)*100 as numeric(15))

, ((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0) / SUM ((a.size/128.0)-(fileproperty(a.name,'SpaceUsed')/128)) OVER (PARTITION BY fg.data_space_id) As [PropFree%]

,fg.name

FROM sysfiles a LEFT join sys.filegroups fg

ON a.groupid = fg.data_space_id

update Traces..Alert_Parameter

set Vl_Parameter_2 = 4000 --default é 10000 MB

where Nm_Alert = 'Tempdb MDF File Utilization'

sp_whoisactive

--Query que pode ajudar a pegar o que está no tempdb

;with tab(session_id, host_name, login_name, totalalocadomb, text)

as(

SELECT a.session_id,

b.host_name,

b.login_name,

( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 / 128 AS totalalocadomb,

d.TEXT

FROM sys.dm_db_session_space_usage a

JOIN sys.dm_exec_sessions b ON a.session_id = b.session_id

JOIN sys.dm_exec_connections c ON c.session_id = b.session_id

CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS d

WHERE a.session_id > 50

--AND ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 / 128 > 10 -- Ocupam mais de 10 MB

)

select top 20 * from tab order by 4 desc

SELECT a.name AS LogicalName,

'SizeinMB' = (size/128)

,fileproperty(a.name, 'spaceused' )/128 as UsedinMB

,(size/128) -fileproperty (a.name,'SpaceUsed')/128 AS FreeInMB

,'Free%'=cast (((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0)/(a.size/128.0)*100 as numeric(15))

, ((a.size/128.0)-fileproperty(a.name,'SpaceUsed')/128.0) / SUM ((a.size/128.0)-(fileproperty(a.name,'SpaceUsed')/128)) OVER (PARTITION BY fg.data_space_id) As [PropFree%]

,fg.name

FROM sysfiles a LEFT join sys.filegroups fg

ON a.groupid = fg.data_space_id

kill 66

kill 65

----- rquivo para testar a carga no TEMPDB

--drop table #StressTempDB

SELECT TOP 1000000000

IDENTITY(INT,1,1) AS RowNum

INTO #StressTempDB

FROM master.sys.all_columns ac1,

master.sys.all_columns ac2,

master.sys.all_columns ac3;

Gostou desse Post?

Curta, comente, compartilhe…

Curta nossa página no Facebook , LinkedIn e Instagram para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.

Até a próxima.

Abraços,

Fabrício Lima

CEO na Fabrício Lima Soluções em BD

Microsoft Data Platform MVP

Instagram:@fabriciofrancalima

Linkedin: https://www.linkedin.com/in/fabriciofrancalima/

Consultoria: [email protected]

Relacionado

2 thoughts on “Monitoramento no SQL Server – O que fazer quando receber um alerta de crescimento do Tempdb?”

Daniel disse:

7 de maio de 2021 às 22:34

Caramba, essa aqui salvou legal agora…
Show de bola..Obrigado

Responder
1. Fabrício Lima disse:
  
  20 de junho de 2021 às 17:03
  
  kkkk top. Que bom que ajudou.
  
  Responder