O tempo limite do semáforo expirou

Question

 Pessoal

    O que poderia estar ocasionando o erro abaixo e qual seria a maneira de controlar ou resolver um tipo de erro assim ?

Onde estou utilizando do SQL Server 2017 com Windows Server 2012 R2 Standard 

 Msg 121 level 20 state 0

A transport-Level error has occurred when receiving results from the server. ( provider: TCP Provider, error 0 - O tempo limite do semáforo expirou. )

Answer 1

Neibala,

Em qual situação este erro esta sendo apresentado?

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 2

Olá Nei,

Ainda nos diga , qual o efeito do erro ?  Está ocorrendo desconexão ?  O Banco está parando ?  O que está ocorrendo e quando está ocorrendo ? 

---

Se esta resposta lhe ajudou, marque-a como útil para que outra pessoa com dúvida ou problema semelhante possa encontrar resposta ou ajuda mais facilmente. * Jefferson Clyton Pereira da Silva - [ MCSA | MCP | MCTS | MTA | Analista de Banco de Dados - Sql Server e Oracle ]

Answer 3

Junior Galvão / Jeferson / Grupo

     Então este tipo de mensagem está ocorrendo tanto na execução de query, como também quando foi executado backup até mesmo de banco de dados menores e isto logo no ínicio do starting, ai veio a dúvida, isto seria por qual motivo ? E como conseguiria identificar melhor um tipo de erro assim ? E teria alguma maneira de controlar algo neste sentido ?

Obs. Vi em diversos site/foruns, onde boa parte de erros assim estaria ligado ao Sistema Operacional / Hardware, muito pouco ligado a banco de dados, ai veio a maior dúvida por se algo mais identificado em versões como SQL Server 2008 até 2012 para trás, sendo que o ambiente que estou trabalhando no momento estaria bem voltado ao SQL Server 2017 e alguns Servidores com SQL Server 2014, então até que ponto isto estaria ligado realmente a Sistema Operacional / Hardware e no caso de ligado a banco de dados até que ponto seria ? ( Aproveitando a experiência de vocês, onde ajudaria a identificar melhor o que poderia estar acontecendo no momento, onde não está ficando tão claro para a identificação real do problema. )

Onde no caso ai está desconectando, parando a execução da query / backup.

Answer 4

Neibala,

Estou desconfiado do consumo de memória por parte deste SQL Server.

Você chegou a verificar o quanto de memória tem alocado para este servidor ou instância quando esta mensagem é apresentada.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 5

Junior Galvão

    Primeiramente agradeço a sua atenção no assunto. Então na parte da memória, do que acompanhei e venho acompanhando.

    Não ultrapassa a 91%, sendo que de 507gb está disponível para o SQL Server 450gb.

    Vi pela DMV ( dm_os_threads ) um count(*)  está chegando a 876 já pela DMV ( dm_os_sys_info ) o max_workers_count está em 1472. Veja se teria algo que poderia estar com outro número e que serviria de alerta para identificar o problema que possa estar tendo de momento ?

Answer 6

Neibala,

Obrigado pelo retorno, em relação ao consumo de memória se realmente você tem está quantidade considerável de memória para o SQL Server, ótimo....

Então, dois detalhes que você destacou que conseguiu identificar, o max_workers_count: representa o número máximo de workers que podem ser criados, este indicador é importante pois mostra justamente o poder de processamento do SQL Server, em adicional o quanto ele esta sendo exigido.

Agora em relação ao count disparado para a dm_os_threads, seria interessante analisarmos algo mais específico.

Vamos tentar identificar as Threads que estão sendo iniciadas pelo SQL Server, execute trecho de código extraído da documentação oficial:

-- Threads criados pelo SQL Server --
SELECT *  
  FROM sys.dm_os_threads  
  WHERE started_by_sqlservr = 1
Go
-- Threads criados por outros processos --
SELECT *  
  FROM sys.dm_os_threads  
  WHERE started_by_sqlservr = 0
Go

Temos que entender o que esta alocado de Threads criados pelo SQL Server, como também, o que esta sendo criado pelo Windows.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 7

Junior Galvão  / Jefferson  / Grupo 

    Agradeço novamente a atenção no assunto e vendo a forma que você pontuou acima que até deixou mais claros alguns pontos. E vendo a sua solicitação e vendo a quantidade de dados, veja se da forma que enviei abaixo vai ajudar a facilitar a sua solicitação, pois vendo a quantidade de dados que estaria retornando, onde de repente agora teria algum filtro que ajude a visualizar melhor o que você estava pensando em visualizar, caso contrário basta me avisar que envio realmente da forma que solicitou ai só veja qual seria a melhor maneira de enviar vendo o volume da dados que (+/-) estaria enviando, para facilitar até mesmo a sua visualização.

select 
count(*) as qtde
from sys.dm_os_threads tb_t with (nolock)

/*
qtde
-----------
632
*/

select 
min(tb_t.creation_time) mindtcreation
,max(tb_t.creation_time) maxdtcreation
,count(*) as qtde
from sys.dm_os_threads tb_t with (nolock)
where tb_t.started_by_sqlservr = 1

/*
mindtcreation           maxdtcreation           qtde
----------------------- ----------------------- -----------
2021-03-31 08:13:20.133 2021-04-13 12:25:01.357 559
*/

select 
tb_t.status 
,count(*) as qtde
,sum(count(*)) over ( order by count(*) desc) tot_qtde_Acu_Geral
from sys.dm_os_threads tb_t with (nolock)
where tb_t.started_by_sqlservr = 1
group by tb_t.status 
  
/*
status      qtde        tot_qtde_Acu_Geral
----------- ----------- ------------------
16          541         541
20          10          551
18          8           559
*/



select 
min(tb_t.creation_time) mindtcreation
,max(tb_t.creation_time) maxdtcreation
,count(*) as qtde
from sys.dm_os_threads tb_t with (nolock)
where tb_t.started_by_sqlservr = 0
 
/*
mindtcreation           maxdtcreation           qtde
----------------------- ----------------------- -----------
2021-03-31 08:13:18.113 2021-04-13 12:25:01.290 73

*/

select 
tb_t.status 
,count(*) as qtde
,sum(count(*)) over ( order by count(*) desc) tot_qtde_Acu_Geral
from sys.dm_os_threads tb_t with (nolock)
where tb_t.started_by_sqlservr = 0
group by tb_t.status 

/*
status      qtde        tot_qtde_Acu_Geral
----------- ----------- ------------------
2           73          73
*/

select 
tb_s.max_workers_count 
from sys.dm_os_sys_info tb_s with (nolock)


/*
max_workers_count
-----------------
1472
*/
  
  
select
count(*) as qtde
from sys.dm_os_workers tb_w with (nolock)

/*
qtde
-----------
552
*/


select 
tb_s.status 
,sum(tb_s.active_workers_count) as active_workers_count
,sum(sum(tb_s.active_workers_count)) over ( order by sum(tb_s.active_workers_count) desc) tot_workers_Acu_Geral
,count(*) as qdte
,sum(count(*)) over ( order by sum(tb_s.active_workers_count) desc,count(*) desc) tot_qtde_Acu_Geral
from sys.dm_os_Schedulers tb_s with (nolock)
group by  tb_s.status 
order by sum(tb_s.active_workers_count) desc,count(*) desc, tb_s.status 

/*
status                                                       active_workers_count tot_workers_Acu_Geral qdte        tot_qtde_Acu_Geral
------------------------------------------------------------ -------------------- --------------------- ----------- ------------------
VISIBLE ONLINE                                               68                   68                    40          40
VISIBLE OFFLINE                                              26                   94                    24          64
HIDDEN ONLINE                                                12                   106                   13          77
VISIBLE ONLINE (DAC)                                         1                    107                   1           78
*/


select 
sum(tb_s.work_queue_count) as work_queue_count
,count(*) as qtde
from sys.dm_os_schedulers  tb_s with (nolock)


/*
work_queue_count     qtde
-------------------- -----------
0                    78
*/

select 
sum(tb_s.active_workers_count) qtde
from sys.dm_os_Schedulers tb_s with (nolock)

/*
qtde
-----------
107
*/


select 
tb_t.task_state 
,count(*) as qtde
,sum(count(*)) over ( order by count(*) desc) tot_Acu_Geral
from sys.dm_os_tasks tb_t with (nolock)
group by tb_t.task_state 
order by count(*) desc, tb_t.task_state 

/*
task_state                                                   qtde        tot_Acu_Geral
------------------------------------------------------------ ----------- -------------
SUSPENDED                                                    95          95
RUNNING                                                      24          119
DONE                                                         12          131
*/


select 
tb_t.wait_type 
,count(*) as qtde 
,sum(count(*)) over ( order by count(*) desc) tot_Acu_Geral
from sys.dm_os_waiting_tasks tb_t with (nolock)
group by tb_t.wait_type 
order by count(*) desc, tb_t.wait_type 

/*
wait_type                                                    qtde        tot_Acu_Geral
------------------------------------------------------------ ----------- -------------
LAZYWRITER_SLEEP                                             4           12
LOGMGR_QUEUE                                                 4           12
XTP_PREEMPTIVE_TASK                                          4           12
BROKER_TRANSMITTER                                           2           18
CLR_AUTO_EVENT                                               2           18
SLEEP_TASK                                                   2           18
BROKER_EVENTHANDLER                                          1           35
BROKER_TO_FLUSH                                              1           35
CHECKPOINT_QUEUE                                             1           35
DIRTY_PAGE_POLL                                              1           35
DISPATCHER_QUEUE_SEMAPHORE                                   1           35
HADR_FILESTREAM_IOMGR_IOCOMPLETION                           1           35
HADR_NOTIFICATION_DEQUEUE                                    1           35
KSOURCE_WAKEUP                                               1           35
ONDEMAND_TASK_QUEUE                                          1           35
QDS_ASYNC_QUEUE                                              1           35
QDS_PERSIST_TASK_MAIN_LOOP_SLEEP                             1           35
REQUEST_FOR_DEADLOCK_SEARCH                                  1           35
SP_SERVER_DIAGNOSTICS_SLEEP                                  1           35
SQLTRACE_INCREMENTAL_FLUSH_SLEEP                             1           35
WAIT_XTP_HOST_WAIT                                           1           35
XE_DISPATCHER_WAIT                                           1           35
XE_TIMER_EVENT                                               1           35
*/


select 
tb_r.status 
,count(*) as qtde
,sum(count(*)) over ( order by count(*) desc) tot_Acu_Geral
from sys.dm_exec_requests tb_r with (nolock)
group by tb_r.status 
order by count(*) desc, tb_r.status

/*
status                         qtde        tot_Acu_Geral
------------------------------ ----------- -------------
background                     31          31
sleeping                       19          50
running                        4           54
*/

Answer 8

Neibala,

Que bom que estamos conseguindo avançar....

Analisando os seus dados, da um pouco de clareza que concorrência de works que estão sendo direcionados ao SQL Server, o que esta fazendo justamente que ele tenha que estar sempre alocando memória para deixar as querys e seus planos de execução atualizados.

Estive pensando na possibilidade de limparmos os caches de execução das Stored Procedures e demais recursos que possam estar alocando a área de buffer.

O que exatamente este servidor SQL Server atende em seu ambiente? É o mesmo que você utiliza para o AlwaysOn?

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 9

Junior Galvão  / Jefferson  / Grupo 

   Na questão que você comentou de concorrência de works, como eu poderia controlar algo neste sentido ? Já na questão que você comentou de planos de execução, como roda um Sistema de terceiro, até pode ocorrer dos planos estarem sempre alocando memória. Só que isto funcionava anteriormente redondo com SQL Server 2014 e por um bom período com SQL Server 2017 e de uns dias para cá, começou a dar mensagem de semáforo, sendo que agora tem melhor Servidor / memória e até mesmo por ser SQL Server 2017, poderia ser algo de configuração que precisaria ajustar para este novo Servidor, pois assim que o SQL Server estaria entendendo melhor as configurações deste novo Servidor, que tem até mais memória.

    Já na questão de limpar os caches como você estaria pensando em limpar ? Poderia me enviar qual seria a maneira de limpar ?  Na parte de AlwaysOn eu não utilizo neste Servidor.

   E na parte de ambiente estaria ligado a um Call-Center ligado a atendimento ( Ligações )

Answer 10

Neibala,

Vamos lá:

 Na questão que você comentou de concorrência de works, como eu poderia controlar algo neste sentido ? Teríamos que verificar o que a sua aplicação esta processando, provavelmente devem estar ocorrendo a abertura e não encerramento de sessões...

Bom antes de qualquer coisa, faça o seguinte, execute o comando abaixo em seu servidor e observe se alguma mudança nesta mensagem para de ser exibida:

netsh interface tcp set global autotuning=disabled

Veja este vídeo também: https://youtu.be/VJKGJYmxNOI

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 11

Junior Galvão / Grupo

    Na parte de estar encerrando a sessões eu conseguiria identificar de uma outra maneira, alguma sessão que deveria estar fechada ou que o SQL Server considere que para ela deveria estar fechada ? ( Ai estava pensando se eu identificar uma sessão em sleeping seria uns dos caminhos ou teria uma outra maneira mais seguro/clara. )

    E no caso do comando enviado vendo o assunto, tinha visto algo relacionado a equipamento de uma certa forma antigos, que pode dar determinados problemas e vendo o que você enviou e vendo os 2 link´s abaixo, existiria um outra maneira de identificar que realmente eu teria de desativar, apenas para certificar que realmente seria necessário o meu caso ? ( Pois imagine o meu caso, quando foi instalado o Windows Server 2012 r2 standard passou por determinados pré-requisitos e quando foi instalar o SQL Server 2017, passou por mais outros pré-requisitos e vendo que anteriormente não tinha esses problemas é como se estivesse algo que de certa forma, não estaria bem configurado. )

   E vendo que o meu problema ligado a semáforo que é meio que intermitente, estava pensando as várias situações e vendo as pesquisas e estudos, poderia ser algo ligado a memória que as query estaria consumindo e ai teria de ter mais memória ou melhor a performance das minhas querys. E ai vi uma vez que ao fazer um simples backup deu erro logo quase ao iniciar e vendo uma situação assim o que você apontaria com um caminho, por mais que não seja o mais correto, mais que poderia ser um caminho a ser analisado ?

https://docs.microsoft.com/pt-br/troubleshoot/windows-server/networking/receive-window-auto-tuning-for-http

https://www.dell.com/support/kbdoc/pt-br/000139146/windows-server-desativar-o-descarregamento-e-o-ajuste-autom%c3%a1tico-do-tcp-chimney-pode-melhorar-o-desempenho-do-iscsi

Answer 12

Neibala,

Então, neste caso, se eu entendi você fez um simples backup é já obteve erro! É isso mesmo?

Se for, você esta sofrendo de sobrecarga de memória ou até mesmo pressão.

Saberia nos dizer como esta configurada as questões de memória virtual desta máquina?

Execute o código abaixo para identificarmos o quanto de memória este seu SQL Server esta conseguindo realmente alocar:

Select counter_name, cntr_value,
       cast((cntr_value/1024.0)/1024.0 as numeric(10,4)) as Gbs,
       cast((cntr_value/1024.0)/1024.0*1024 as numeric(10,2)) as Mbs
From sys.dm_os_performance_counters
Where counter_name like '%Target server_memory%'
Or counter_name like '%Total server_memory%'
Go

Já este outo código vai nos permitir identificar os contadores de memória: Total, Livre e Em uso:

;With CTEOsMemory
As
(
Select 
    (total_physical_memory_kb / 1024) as MemoriaFisicaTotal, 
    (available_physical_memory_kb / 1024) as MemoriaFisicaDisponivel
from sys.dm_os_sys_memory)
Select MemoriaFisicaTotal As 'Memória RAM Total em GBs',
       MemoriaFisicaDisponivel As 'Memória RAM Dispónivel em GBs',
       (MemoriaFisicaTotal-MemoriaFisicaDisponivel) As 'Memória RAM Alocada em GBs para uso'
From CTEOsMemory
Go

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 13

Junior Galvão / Grupo

     Agradeço mais uma vez atenção e veja as informações abaixo. Veja se teria algo que possa estar mais configurado vendo os valores e informações abaixo e as enviadas anteriormente.

 Retorno   

  

Então, neste caso, se eu entendi você fez um simples backup é já obteve erro! É isso mesmo?

Retorno : Foi exatamente isto que feito em um determinado horário, que foi feito um teste de backup e ocorreu o erro. Por sorte conseguimos identificar este tipo de erro.

Se for, você esta sofrendo de sobrecarga de memória ou até mesmo pressão.

Retorno : Em um caso assim o que você recomendaria agora vendo todas essas informações e até mesmo as abaixo. 

Saberia nos dizer como esta configurada as questões de memória virtual desta máquina?

Retorno : Só para não te informar algo equivocado, como eu conseguiria identificar está configuração, com maior certeza para passar para você ?

Execute o código abaixo para identificarmos o quanto de memória este seu SQL Server esta conseguindo realmente alocar:

Retorno : Veja os dados abaixo e qualquer dúvida basta me avisar.

counter_name                      cntr_value       Gbs          Mbs
=================  =======    ======  ======
Target Server Memory (KB)  460800016    439.4531   450000.02 Total Server Memory (KB)    460798912    439.4521   449998.94

Memória instalada (RAM) - Windows
496 GB

Server Properties ( Name  - Memory ) - Server SQL
507841 MB

Maximum server memory (in MB) - Configurado manualmente
450000

Gerenciador de Tarefas - Windows
CPU 12%
Memória 91%

Memória RAM Total GBs    Memória RAM Dispónivel GBs   Memória RAM Alocada GBs p/uso ===============   ==================  ======================
507840                            41307                                  466533

Answer 14

Neibala,

Se você observou o resultado apresentado pelo coluna Memória RAM Dispónivel GBs, você tem neste momento 41 Gbs de memória livre no seu ambiente isso incluíndo o próprio Sistema Operacional.

Você já esta usando  Memória RAM Alocada GBs p/uso um total de 466Gbs, com certeza o SQL Server já esta fazendo uso de memória física + virtual pois ele gosta de memória (kkkkk).

O que parece realmente é que você esta com uma alocação de memória que exige do sistema operacional, em adicional do SQL Server uma pressão, e isso faz com que os temporizados de controle de processamento tenham que ficar analisando e atualizando os tempos.

Vamos identificar o valor de Page Life Expectancy o seu SQL Server esta apresentando:

SELECT * 
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND object_name LIKE '%Buffer Manager%'
Go

Depois podemos pensar na limpeza dos caches de memória das consultas Ad-Hoc que o seu ambiente pode estar apresentando para ver se isso ajuda na diminuição ou ocorrência desta mensagem, mas este procedimento você tem que tomar cuidado:

CREATE procedure stpLimpa_Adhoc_Cache 
AS
BEGIN
	SET NOCOUNT ON

	if object_id('tempdb..#Temp_Trace') is not null
		drop table #Temp_Trace

	SELECT top 1000 IDENTITY(int,1,1) Id , plan_handle,size_in_bytes
	into #Temp_Trace
	FROM sys.dm_Exec_cached_plans AS cp
	WHERE cacheobjtype = 'Compiled Plan' 
		AND cp.objtype = 'Adhoc' 
		AND cp.usecounts < 20
		and size_in_bytes > 1000 -- in bytes
	order by size_in_bytes desc
	
	declare @plan_handle varbinary(64), @Loop int, @Qtd_Registros int

	set @Loop = 1
	select @Qtd_Registros = count(*) from #Temp_Trace

	while @Loop <= @Qtd_Registros
	begin
		select @plan_handle = plan_handle	 
		from #Temp_Trace
		where Id = @Loop
		
		DBCC FREEPROCCACHE (@plan_handle)
		
		set @Loop = @Loop + 1
	end
END

Ressalto que este procedure não é de minha autoria, trata-se de um script compartilhado pelo Fabricio Lima - MVP, o qual é meu amigo e parceiro MVP, particularmente eu uso muito esta implementação em clientes que trabalham com ERPs Microsiga, Totvs e Protheus.

Vale também analisar os clerks de memória que podem estar fazendo alto consumo no seu ambiente.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 15

Junior Galvão / Grupo

    Veja as informações abaixo dos script solicitados e até mesmo o caso da parte envolvendo memória clerks e até vendo um outro assunto que você comentou de memória virtual, vendo os resultados veja se seria isto que você tinha comentado em outra parte e veja em que ponto eu poderia estar dando maior atenção. Mesmo que depois veja que poderia ser por outro caminho, pois pelo menos estaria, vendo em quais pontos devo dar uma devida atenção, onde isto pode me ajudar até mesmo em outras situações do dia-dia. Então nesta hora é válido a atenção em determinados pontos que pode indicar algo que deve ser dado uma certa atenção.

SELECT 
tb_opc.* 
FROM sys.dm_os_performance_counters tb_opc with (nolock)
WHERE tb_opc.counter_name = 'Page life expectancy'
AND tb_opc.object_name LIKE '%Buffer Manager%'
Go

/*
object_name                   counter_name           instance_name   cntr_value  cntr_type
---------------------------  ---------------------   --------------- ----------- ----------
SQLServer:Buffer Manager     Page life expectancy                    27425       65792
*/

select
top 15
tb_m.type
,tb_m.name
,tb_m.pages_kb / 1024 as [size_mb]
,tb_m.memory_node_id as [idMemory_node]
,tb_m.virtual_memory_reserved_kb 
,tb_m.virtual_memory_committed_kb 
,tb_m.page_allocator_address
,tb_m.memory_clerk_address 
from sys.dm_os_memory_clerks tb_m with (nolock)
order by size_mb desc,idMemory_node 

/*
type                              name                     size_mb              idMemory_node virtual_memory_reserved_kb virtual_memory_committed_kb page_allocator_address memory_clerk_address
--------------------------------  ------------------------ -------------------- ------------- -------------------------- --------------------------- ---------------------- --------------------
MEMORYCLERK_SQLBUFFERPOOL          Default                 136990               1             0                          0                           0x000000765AC04D30     0x000000765AC04CF0
MEMORYCLERK_SQLBUFFERPOOL          Default                 135084               2             0                          0                           0x0000007659BC8D30     0x0000007659BC8CF0
MEMORYCLERK_SQLBUFFERPOOL          Default                 134846               0             402538508                  11994424                    0x000000765A008D30     0x000000765A008CF0
CACHESTORE_SQLCP                   SQL Plans                16405               0             0                          0                           0x000000765515ED10     0x000000765515ECD0
MEMORYCLERK_SQLQERESERVATIONS      Default                  11739               0             0                          0                           0x000000765A014F60     0x000000765A014F20
OBJECTSTORE_LOCK_MANAGER           Lock Manager : Node 1      958               1             0                          0                           0x0000007659BB9070     0x0000007659BB9030
OBJECTSTORE_LOCK_MANAGER           Lock Manager : Node 2      955               2             0                          0                           0x00000076585FD070     0x00000076585FD030
USERSTORE_SCHEMAMGR                SchemaMgr Store            361               0             0                          0                           0x0000007650AA5420     0x0000007650AA53E0
CACHESTORE_OBJCP                   Object Plans               209               0             0                          0                           0x000000765515E0C0     0x000000765515E080
MEMORYCLERK_SQLCLR                 Default                    165               0             25197248                   26364                       0x000000765A00E810     0x000000765A00E7D0
MEMORYCLERK_SQLQUERYEXEC           Default                    128               0             0                          0                           0x000000765A00A810     0x000000765A00A7D0
MEMORYCLERK_SQLSTORENG             Default                    110               0             32832                      32832                       0x000000765A00D6B0     0x000000765A00D670
MEMORYCLERK_SOSNODE                SOS_Node                   108               0             0                          0                           0x000000765A0002C0     0x000000765A000280
MEMORYCLERK_SOSNODE                SOS_Node                    85               1             0                          0                           0x000000765A000A10     0x000000765A0009D0
USERSTORE_DBMETADATA               PONTOMARCACAO               81               1             0                          0                           0x0000007644C92910     0x0000007644C928D0


*/

Answer 16

Neibala,

O que cenário é mais embaixo mesmo, as questões de alocação de memória pelo retorno parecem estar sendo suportadas pelo SQL Server aparentemente, mas altos por exemplo: MEMORYCLERK_SQLBUFFERPOOL este é um clerk interno do SQL Server o primeiro esta consumindo 136GBs de memória valor alto mas como você tem recurso para ele esta satisfatório.

Eis a questão que temos que validar! Parece-me que temos um possível cenário de pressão de memória.

O PLE também parece estar dentro dos padrões, ainda mais se tomarmos como base as orientações da Microsoft: Page Life Expectancy (PLE) in SQL Server (sqlshack.com)

"De acordo com o padrão da Microsoft, seguimos uma regra de 300 segundos. Se o PLE for menos de 300 segundos (5 Minutos), então a pressão da memória é muito alta, e temos que cuidar do lado de desempenho. Ainda assim, esse cálculo foi para apenas 4GB de alocação de memória para o SQL Server.

Para o seu servidor, deve ser diferente de acordo com a fórmula. Você deve se preocupar com a instância do SQL Server quando o PLE cair do que o cálculo abaixo:

PLE (Expectativa de Vida da Página) = ((Alocação de memória tampão (GB)) / 4 ) * 300

Por exemplo, a máquina é configurada com 128GB e 110GB é alocada para a instância do SQL Server, então o limiar PLE será o mais abaixo:

PLE (Page Life Expectancy) = (110 / 4) * 300

PLE (Expectativa de Vida da Página) = 8250 Segundos

Ninguém pode dizer que número será recomendado para o limite padrão porque cada SQL Server tem configurações diferentes de acordo com o caso de uso e exigência. Se o seu PLE cair de repente, então a primeira coisa deve ser verificar a alocação de memória.

É quantidade suficiente de tamanho para o SQL Server? Se sim, então verifique a solicitação de execução atual, existe algum segmento de longa duração que consuma mais memória? Se sim, então revise a declaração de consulta que está executando.

O desempenho será baseado no disco, que é um pouco mais lento que a memória primária. Eventualmente, ele produz um monte de IO de disco e estressa no armazenamento porque a memória não está sendo processada eficientemente pelo servidor. Problemas desnecessários de uso da memória devem ser observados e ter a chance de otimizá-lo com uma investigação adequada."

Fiz um pequeno cálculo do seu ambiente: 

PLE (Page Life Expectancy) = (466 / 4) * 300

PLE (Expectativa de Vida da Página) = 34.950 Segundos, sendo que você apresentou 27425.

Este link vai te ajudar a validar mais sobre as alocações de memória: Monitoring Memory Clerk and Buffer Pool Allocations in SQL Server (sqlshack.com)

Agora, temos que verificar as questões de disco, mas antes veja o este link: SQL Server Performance ,memory pressure and memory usage (SQL Server DBA) (sqlserver-dba.com)

Para finalizar a questões de memória e tirarmos da frente uma possível pressão.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]