none
O tempo limite do semáforo expirou RRS feed

  • 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. )

    quinta-feira, 25 de março de 2021 20:03

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]

    sexta-feira, 26 de março de 2021 23:23
  • 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 ]

    quinta-feira, 1 de abril de 2021 19:57
  • 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
    terça-feira, 6 de abril de 2021 03:21
  • 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]

    quarta-feira, 7 de abril de 2021 16:53
  • 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 ?

    sexta-feira, 9 de abril de 2021 14:29
  • 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]

    sexta-feira, 9 de abril de 2021 21:42
  • 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.
    terça-feira, 13 de abril de 2021 15:40
  • 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]

    terça-feira, 13 de abril de 2021 20:29
  • 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 )

    quarta-feira, 14 de abril de 2021 13:07
  • 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]

    quarta-feira, 14 de abril de 2021 17:58
  • 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

    quinta-feira, 15 de abril de 2021 01:56
  • 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]

    sábado, 17 de abril de 2021 12:19
  • 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
    terça-feira, 20 de abril de 2021 23:11
  • 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]

    quinta-feira, 22 de abril de 2021 11:45
  • 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
    
    
    */
    


    sexta-feira, 23 de abril de 2021 20:20
  • 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]

    segunda-feira, 26 de abril de 2021 17:29