Inquiridor
O tempo limite do semáforo expirou

Pergunta
-
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. )
Todas as Respostas
-
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]
-
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 ]
-
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.- Editado neibala terça-feira, 6 de abril de 2021 03:23 Melhor identificação
-
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]
-
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 ?
-
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:
Temos que entender o que esta alocado de Threads criados pelo SQL Server, como também, o que esta sendo criado pelo Windows.-- 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
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]
-
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 */
- Editado neibala terça-feira, 13 de abril de 2021 15:42 Erro de digitação.
-
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]
-
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 )
-
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]
-
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 -
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]
-
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- Editado neibala terça-feira, 20 de abril de 2021 23:25 Formatação
-
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]
-
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 */
-
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]