Monitoramento no SQL Server – O que fazer quando receber um alerta de crescimento do Tempdb?
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
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 |
Gostou desse Post? Curta, […]