Os Fios Adormecidos Retêm Memória

Se você está tentando rastrear o uso de memória e quais consultas são os maiores infratores, execute as seguintes etapas:

1. Identifique qual banco de dados na instância está consumindo mais memória

   -- DB using most cache
   DECLARE @total_buffer INT
   SELECT @total_buffer = cntr_value
      FROM sys.dm_os_performance_counters
      WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
      AND counter_name IN ('Total Pages', 'Database Pages');
   
   WITH src AS
   (
      SELECT
          database_id, db_buffer_pages = COUNT_BIG(*)
          FROM sys.dm_os_buffer_descriptors
          --WHERE database_id BETWEEN 5 AND 32766
          GROUP BY database_id
   )
   SELECT
      [db_name] = CASE [database_id] WHEN 32767
          THEN 'Resource DB'
          ELSE DB_NAME([database_id]) END,
      db_buffer_pages,
      db_buffer_MB = db_buffer_pages / 128,
      db_buffer_percent = CONVERT(DECIMAL(6,3),
          db_buffer_pages * 100.0 / @total_buffer)
   FROM src
   ORDER BY db_buffer_MB DESC;
   

2. Pesquise quais objetos dentro do referido banco de dados estão consumindo mais memória

   --objects in DB using most space
   USE DatabaseName
   GO
   WITH src AS
   (
       SELECT
           [Object] = o.name,
           [Type] = o.type_desc,
           [Index] = COALESCE(i.name, ''),
           [Index_Type] = i.type_desc,
           p.[object_id],
           p.index_id,
           au.allocation_unit_id
       FROM
           sys.partitions AS p
       INNER JOIN
           sys.allocation_units AS au
           ON p.hobt_id = au.container_id
       INNER JOIN
           sys.objects AS o
           ON p.[object_id] = o.[object_id]
       INNER JOIN
           sys.indexes AS i
           ON o.[object_id] = i.[object_id]
           AND p.index_id = i.index_id
       WHERE
           au.[type] IN (1,2,3)
           AND o.is_ms_shipped = 0
   )
   SELECT
       src.[Object],
       src.[Type],
       src.[Index],
       src.Index_Type,
       buffer_pages = COUNT_BIG(b.page_id),
       buffer_mb = COUNT_BIG(b.page_id) / 128
   FROM
       src
   INNER JOIN
       sys.dm_os_buffer_descriptors AS b
       ON src.allocation_unit_id = b.allocation_unit_id
   WHERE
       b.database_id = DB_ID()
   GROUP BY
       src.[Object],
       src.[Type],
       src.[Index],
       src.Index_Type
   ORDER BY
       buffer_pages DESC;
   

3. Revise quais consultas estão atingindo os referidos objetos na memória

   USE DatabaseName
   GO
   
   SELECT DISTINCT TOP 100 
           s.total_logical_reads / s.execution_count
       , SUBSTRING(t.TEXT, (s.statement_start_offset / 2) + 1, (
               (
                   CASE s.statement_end_offset
                       WHEN - 1
                           THEN DATALENGTH(t.TEXT)
                       ELSE s.statement_end_offset
                       END - s.statement_start_offset
                   ) / 2
               ) + 1) AS statement_text
       , s.execution_count AS ExecutionCount
       , s.max_elapsed_time AS MaxElapsedTime
       , ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime
       , s.creation_time AS LogCreatedOn
       , *
   FROM sys.dm_exec_query_stats s
   CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
   WHERE TEXT LIKE '%ObjectName_From_QueryAbove%'
       AND TEXT NOT LIKE '%sys.dm_exec_query_stats s%'
   ORDER BY s.total_logical_reads / s.execution_count DESC
   

Espero que isso lhe dê algumas perguntas problemáticas. Você pode descobrir que simplesmente não tem a quantidade adequada de memória na instância para a carga de trabalho que ela está tentando acomodar. Alternativamente, você pode encontrar uma consulta escrita por um usuário faltando algo simplesmente irritante como um predicado (por exemplo, a WHEREcláusula).

Se você quiser se aprofundar no plano de execução, pode pegar plan_handlee enviar para esta consulta:

SELECT query_plan 
FROM sys.dm_exec_query_plan (PLAN_HANDLE_FROM_Query_3)

e revisar o plano de execução. Se você não conseguir ajustar a referida consulta, isso provavelmente não será tão útil para você.

Depois de descobrir o que está usando a memória na instância, você pode ter uma ideia melhor de como colocar o sistema de volta em um estado íntegro.

Eu uso a consulta abaixo sem nada excluído porque quero ver onde estão as esperas em segundo plano do SQL Server e todas estarão na mesma porcentagem. Itens como HADR_FILESTREAM_IOMGR_IOCOMPLETION, XE_TIMER_EVENTe REQUEST_FOR_DEADLOCK_SEARCH. Se o seu RESOURCE_SEMAPHOREestiver abaixo do plano de fundo do sistema, não há problema. Se estiver acima do plano de fundo do sistema, poste o que é.

SELECT TOP 20 'Waits' as Waits
  ,GETDATE() AS check_time
  ,[Wait type] = wait_type
  ,[Wait time (s)] = wait_time_ms / 1000
  ,[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
  ,waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

RESOURCE_SEMAPHORE significa que você tem consultas que estão aguardando uma concessão de memória.

eu perguntaria

 sys.dm_exec_query_memory_grants 

para ver o que está consumindo toda a memória do espaço de trabalho de consulta e o que está aguardando memória. Este artigo fornece algumas boas dicas para solucionar problemas de RESOURCE_SEMAPHORE . Infelizmente, isso normalmente significa que você precisa ajustar uma consulta ou consultas; portanto, se for um código de terceiros, talvez seja necessário voltar ao fornecedor. Dependendo do problema específico, você pode melhorá-lo adicionando um índice, mas isso é apenas especulação.