Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Недостаточно физической памяти для запросов  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5136
http://msmvps.com/blogs/gladchenko/archive/2012/06/19/1811346.aspx#comments

Добрый день, коллеги.
Наткнулся на полезный скрипт, который выдал мне на одном из серверов предупреждение о недостаточности физ памяти.

WHEN XMLRecord.value('(ResourceMonitor/IndicatorsProcess)[1]', 'tinyint') = 2 AND
XMLRecord.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') = 0

тут мне пишет о недостатке физ. памяти.
Подскажите как можно поподробнее узнать в какую сторону делать подкоп.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
19 фев 13, 17:09    [13950217]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Для того, чтобы добавить в сервер памяти землекопы (обычно) не нужны.
19 фев 13, 17:20    [13950302]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
LenaV
Member

Откуда: USA
Сообщений: 6796
сколько стоит памяти сейчас на сервере?
сколько выделена для SQL Server?
19 фев 13, 17:44    [13950470]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5136
pkarklin, сервер максимально забит памятью.
LenaV, 144 гига всего, 120 на скл.
19 фев 13, 22:45    [13951687]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
LenaV
Member

Откуда: USA
Сообщений: 6796
посмотрите, что у вас там еще на сервере крутится.
хватает ли ОС памяти.

проверьте логи SQL Serverа.
там могут быть ошибки памяти.
если есть - давайте их сюда.

попробуйте оптимизировать наиболее тяжелые по i/o запросы,
может быть большие таблицы сканируются.
20 фев 13, 02:37    [13952496]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Александр52
pkarklin, сервер максимально забит памятью.
LenaV, 144 гига всего, 120 на скл.

И чем память занята?
+ Memory Overview
DECLARE @Perf TABLE (object_name nvarchar(20), counter_name nvarchar(128), instance_name nvarchar(128), cntr_value BIGINT, formatted_value NUMERIC(20, 2), ShortName NVARCHAR(20))
INSERT INTO @Perf(object_name, counter_name, instance_name, cntr_value, formatted_value, ShortName)
SELECT 
  CASE 
    WHEN PATINDEX('%:Memory Manager%', object_name)> 0 THEN 'Memory Manager'
    WHEN PATINDEX('%:Buffer Manager%', object_name)> 0 THEN 'Buffer Manager'
    WHEN PATINDEX('%:Plan Cache%', object_name)> 0 THEN 'Plan Cache'
    WHEN PATINDEX('%:Buffer Node%', object_name)> 0 THEN 'Buffer Node' -- 2008
    WHEN PATINDEX('%:Memory Node%', object_name)> 0 THEN 'Memory Node' -- 2012
    ELSE NULL 
  END AS object_name,
  CAST(RTRIM(counter_name) AS NVARCHAR(100)) AS counter_name, 
  RTRIM(instance_name) AS instance_name, 
  cntr_value,
  CAST(NULL AS DECIMAL(20,2)) AS formatted_value,
  SUBSTRING(counter_name,  1, PATINDEX('% %', counter_name)) ShortName
FROM sys.dm_os_performance_counters 
WHERE (object_name LIKE '%:Buffer Node%' OR object_name LIKE '%:Buffer Manager%' OR object_name LIKE '%:Memory Node%' OR object_name LIKE '%:Plan Cache%')
  AND (counter_name LIKE '%pages %' OR counter_name = 'Page life expectancy' OR counter_name LIKE '%Node Memory (KB)%')
  OR  (object_name LIKE '%:Memory Manager%'
        AND RTRIM(counter_name) IN ('Granted Workspace Memory (KB)', 'Maximum Workspace Memory (KB)',
                                    'Memory Grants Outstanding',     'Memory Grants Pending',
                                    'Target Server Memory (KB)',     'Total Server Memory (KB)',
                                    -- for 2012
                                    'Free Memory (KB)',              'Reserved Server Memory (KB)',
                                    'Database Cache Memory (KB)',    'Stolen Server Memory (KB)')
      )

-- Convert values from pages and KB to MB and rename counters accordingly
UPDATE @Perf
SET 
  counter_name = REPLACE(REPLACE(counter_name, 'pages', '(MB)'), '(KB)', '(MB)'), 
  formatted_value = 
  CASE 
    WHEN counter_name LIKE '%pages' THEN cntr_value/128. 
    WHEN counter_name LIKE '%(KB)' THEN cntr_value/1024. 
    ELSE cntr_value
  END

-- Update counter/object names so they look like in 2012
UPDATE PC
SET 
  object_name = REPLACE(object_name, 'Buffer', 'Memory'),
  counter_name = ISNULL(M.NewName, counter_name)  
FROM @Perf PC
  LEFT JOIN
  (
    SELECT 'Free (MB)' AS OldName, 'Free Memory (MB)' AS NewName UNION ALL
    SELECT 'Database (MB)', 'Database Cache Memory (MB)' UNION ALL
    SELECT 'Stolen (MB)', 'Stolen Server Memory (MB)' UNION ALL
    SELECT 'Reserved (MB)', 'Reserved Server Memory (MB)' UNION ALL
    SELECT 'Foreign (MB)', 'Foreign Node Memory (KB)'
  ) M ON M.OldName = PC.counter_name
  AND NewName NOT IN (SELECT counter_name FROM @Perf WHERE object_name = 'Memory Manager') 
WHERE object_name IN ('Buffer Manager', 'Buffer Node')


-- Build Memory Tree
DECLARE @MemTree TABLE (Id int, ParentId int, counter_name nvarchar(128), formatted_value NUMERIC(20, 2), ShortName NVARCHAR(20))

-- Level 5
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = 1223,
  ParentId = 1222,
  instance_name + ' (MB)' as counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Plan Cache' 
  AND counter_name IN ('Cache (MB)')
  AND instance_name <> '_Total'

-- Level 4
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = 1222,
  ParentId = 1220,
  'Plan ' + counter_name as counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Plan Cache' 
  AND counter_name IN ('Cache (MB)')
  AND instance_name = '_Total'
UNION ALL

SELECT
  Id = 1112,
  ParentId = 1110,
  counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Memory Manager' 
  AND counter_name IN ('Reserved Server Memory (MB)')
UNION ALL
SELECT
  Id = P.ParentID + 1,
  ParentID = P.ParentID,
  'Used Workspace Memory (MB)' AS counter_name,
  SUM(used_memory_kb)/1024. as formatted_value,
  NULL AS ShortName
FROM sys.dm_exec_query_resource_semaphores 
  CROSS JOIN (SELECT 1220 AS ParentID UNION ALL SELECT 1110) P
GROUP BY P.ParentID

-- Level 3
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = CASE counter_name 
           WHEN 'Granted Workspace Memory (MB)' THEN 1110 
           WHEN 'Stolen Server Memory (MB)' THEN 1220 
           ELSE 1210
         END,
  ParentId = CASE counter_name 
               WHEN 'Granted Workspace Memory (MB)' THEN 1100 
               ELSE 1200 
             END,
  counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Memory Manager' 
  AND counter_name IN ('Stolen Server Memory (MB)', 'Database Cache Memory (MB)', 'Free Memory (MB)', 'Granted Workspace Memory (MB)')

-- Level 2
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT
  Id = CASE WHEN counter_name = 'Maximum Workspace Memory (MB)' THEN 1100 ELSE 1200 END,
  ParentId = 1000,
  counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Memory Manager' AND 
  counter_name IN ('Total Server Memory (MB)', 'Maximum Workspace Memory (MB)') 

-- Level 1
INSERT @MemTree(Id, ParentId, counter_name, formatted_value, ShortName)
SELECT 
  Id = 1000,
  ParentId = NULL,
  counter_name, 
  formatted_value,
  ShortName
FROM @Perf
WHERE object_name = 'Memory Manager' AND 
  counter_name IN ('Target Server Memory (MB)')

-- Results:

-- PLE and Memory Grants
SELECT counter_name AS [Counter Name], cntr_value as Value
FROM @Perf
WHERE 
  object_name = 'Memory Manager' 
  AND counter_name IN ('Memory Grants Outstanding', 'Memory Grants Pending', 'Page life expectancy')

-- Memory tree
;WITH CTE
AS
(
SELECT 0 as lvl, counter_name, formatted_value,  CAST(NULL AS DECIMAL(20,2)) As Perc, Id, NULL AS ParentId, ShortName
FROM @MemTree
WHERE ParentId IS NULL
UNION ALL
SELECT CTE.lvl+1,
  CAST(REPLICATE(' ', 6*(CTE.lvl)) + NCHAR(124) + REPLICATE(NCHAR(183), 3) + MT.counter_name AS NVARCHAR(128)), 
  MT.formatted_value, CAST(ISNULL(1.0*MT.formatted_value/NULLIF(CTE.formatted_value, 0),0) AS DECIMAL(20,2)) AS Perc, MT.Id, MT.ParentId, MT.ShortName
FROM @MemTree MT
  INNER JOIN CTE ON MT.ParentId = CTE.ID
)
SELECT 
  counter_name AS [Counter Name], formatted_value AS Value, Perc AS [%]
FROM CTE
ORDER BY ISNULL(ID, 10000), formatted_value DESC

+ Buffer Cache for Instance
SELECT 
  CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END  as DatabaseName, 
  CAST(COUNT(*)/128. AS NUMERIC(20,2)) AS [BufferSize MB], 
  CAST(SUM(CAST(free_space_in_bytes AS BIGINT)) / (1024. * 1024) AS NUMERIC(20,2)) AS [EmptySize MB], 
  CAST(SUM(is_modified/128.) AS NUMERIC(20,2)) AS [DirtySize MB], 
  CAST(AVG((free_space_in_bytes/ (1024. * 1024))/(1/128.)) AS NUMERIC(20,2)) AS [EmptySize %], 
  COUNT(*) AS PagesInCache
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY PagesInCache desc
OPTION(MAXDOP 1)

+ Buffer Cache for Database
USE [YourDB]
SELECT 
  CASE WHEN a.container_id = 0 
    THEN '<<<Marked for deferred drop>>>'
  ELSE
    OBJECT_SCHEMA_NAME(p.[object_id]) + '.' + OBJECT_NAME(p.[object_id]) 
  END AS [TableName],
  p.index_id, 
  CASE WHEN i.index_id IS NOT NULL THEN ISNULL(i.name, '<<<HEAP>>>') ELSE NULL END AS [IndexName], 
  CAST(a.CNT/128. AS NUMERIC(20,2)) AS [BufferSize MB],  
  CAST(a.free_space_in_mb AS NUMERIC(20,2)) AS [EmptySize MB], 
  CAST(a.cnt_is_modified/128. AS NUMERIC(20,2)) AS [DirtySize MB],
  CAST(a.free_space_in_mb/(a.CNT/128.) AS NUMERIC(20,2)) AS [EmptySize %], 
  a.RowCountNonLeaf AS RowsCached_NonLeaf,
  a.RowCountLeaf AS RowsCached_Leaf,
  p.rows AS RowsInTable,
  CAST(ROUND(CASE WHEN p.rows < a.RowCountLeaf THEN 1. ELSE ISNULL(1.0 * a.RowCountLeaf / NULLIF(p.rows,0),0) END, 4)  AS DECIMAL(8,4)) AS [CachedLeaf %],
  p.partition_number,
  a.used_pages AS PagesOnDisk,
  a.CNT AS PagesInCache,
  a.cnt_is_modified AS DirtyPages
FROM
  (
    SELECT 
      a.container_id,
      COUNT(*) AS CNT,
      MAX(used_pages) AS used_pages,
      SUM(CAST(free_space_in_bytes AS BIGINT)) / (1024. * 1024) AS free_space_in_mb, 
      SUM(CASE WHEN b.is_modified = 1 THEN 1 ELSE 0 END) AS cnt_is_modified,
      SUM(CASE WHEN a.type = 1 and page_level =  0 and page_type IN ('INDEX_PAGE','DATA_PAGE') THEN b.row_count ELSE 0 END ) AS RowCountLeaf,
      SUM(CASE WHEN a.type = 1 and page_level <> 0 and page_type IN ('INDEX_PAGE','DATA_PAGE') THEN b.row_count ELSE 0 END ) AS RowCountNonLeaf
    FROM sys.dm_os_buffer_descriptors AS b
      LEFT JOIN sys.allocation_units AS a ON a.allocation_unit_id = b.allocation_unit_id
    WHERE b.database_id = DB_ID()
    GROUP BY a.container_id
  ) a
  LEFT JOIN sys.partitions AS p ON a.container_id = p.hobt_id 
  LEFT JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE ABS(ISNULL(p.[object_id],101)) > 100
ORDER BY PagesInCache DESC
OPTION(MAXDOP 1);
20 фев 13, 04:08    [13952537]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
Александр52
Member

Откуда: Кокосовые острова ส็็็็็
Сообщений: 5136
Mind, http://clip2net.com/s/2R17u - Memory Overview
LenaV, запросы более-менее оптимизированы, индексы на местах. В логах ошибок о нехватке памяти нет. Однако в диспетчере задач заметил количество свободной памяти "0". 115 на sql - остальная в режиме ожидания.
20 фев 13, 10:53    [13953276]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Александр52
Mind, http://clip2net.com/s/2R17u - Memory Overview
LenaV, запросы более-менее оптимизированы, индексы на местах. В логах ошибок о нехватке памяти нет. Однако в диспетчере задач заметил количество свободной памяти "0". 115 на sql - остальная в режиме ожидания.
PLE конечно проседает. Что свидетельствует о постоянном вымывании кэша данных. Для 115 гигов оперативы значения PLE в идеале должны быть не меньше нескольких тысяч. у вас OLTP или OLAP нагрузка? Если OLTP, то я присоединяюсь к Лене, смотрите самые тыжелые по i/o запросы.

+ Например вот так
DECLARE @Top INT
SET @Top = 50

;WITH QUERYSTATS
AS
(
SELECT
  t.dbid,
  DB_NAME(t.dbid) AS DatabaseName,
  DB_NAME(qt.[dbid]) AS [ObjectDB],
  OBJECT_SCHEMA_NAME(qt.objectid, qt.[dbid]) + '.' + OBJECT_NAME(qt.objectid, qt.[dbid]) AS [ObjectName],
  substring(qt.text, (statement_start_offset/2)+1 , case when statement_end_offset IN(0,-1) then 2147483647 else ((statement_end_offset - statement_start_offset)/2) + 1 end) sql_text,

  plan_handle,
  sql_handle,
  query_hash,
  execution_count as exec_count,
  CAST(ISNULL(1.0*execution_count/NULLIF(DATEDIFF(minute, creation_time, GETDATE()), 0), 0) AS DECIMAL(20, 2)) AS [exec/min], 

  statement_start_offset as start_offset,
  statement_end_offset as end_offset,
  creation_time, last_execution_time,
  DATEDIFF(Minute, creation_time, GETDATE()) AS [Age in Cache(min)],

  -- 1. Execution time
  total_elapsed_time/1000 as total_time_ms,
  last_elapsed_time/1000 as last_time_ms,
  min_elapsed_time/1000 as min_time_ms,
  max_elapsed_time/1000 as max_time_ms,
  total_elapsed_time/1000/NULLIF(execution_count,0) as avg_time_ms,

  -- 2. CPU time
  total_worker_time/1000 as total_cpu_ms,
  last_worker_time/1000 as last_cpu_ms,
  min_worker_time/1000 as min_cpu_ms,
  max_worker_time/1000 as max_cpu_ms,
  total_worker_time/1000/NULLIF(execution_count,0) as avg_cpu_ms,
  1000*total_worker_time/NULLIF(total_elapsed_time, 0) as [cpu_ms/sec],

  -- 3. IO
  total_physical_reads,
  last_physical_reads ,
  min_physical_reads ,
  max_physical_reads,
  total_physical_reads/NULLIF(execution_count,0) as avg_physical_reads,
  CAST(1.*total_physical_reads/NULLIF(total_elapsed_time, 0) AS DECIMAL(20,2)) as [physical_reads/sec],
  
  total_logical_writes, 
  last_logical_writes ,
  min_logical_writes ,
  max_logical_writes,
  total_logical_writes/NULLIF(execution_count,0) as avg_logical_writes,
  CAST(1.*total_logical_writes/NULLIF(total_elapsed_time, 0) AS DECIMAL(20,2)) as [logical_writes/sec],
  total_logical_reads ,
  last_logical_reads ,
  min_logical_reads ,
  max_logical_reads,
  total_logical_reads/NULLIF(execution_count,0) as avg_logical_reads,
  CAST(1.*total_logical_reads/NULLIF(total_elapsed_time, 0) AS DECIMAL(20,2)) as [logical_reads/sec],
  (total_physical_reads + total_logical_writes) AS [total IO],
  (total_physical_reads + total_logical_writes)/NULLIF(execution_count, 0) AS [avg IO],
  (total_logical_reads + total_logical_writes)/NULLIF(execution_count, 0) AS [logical]
FROM
  (    
      SELECT qs.*, epa.dbid
      FROM sys.dm_exec_query_stats qs
      CROSS APPLY (SELECT TOP 1 CONVERT(smallint, value) AS dbid
                   FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                   WHERE attribute IN ('dbid', 'dbid_execute') and value <> 32767
                   ORDER BY attribute) AS epa
  ) T
  CROSS APPLY sys.dm_exec_sql_text([sql_handle]) AS qt
)

SELECT TOP(@Top) 
  DatabaseName, exec_count, [exec/min],
  [total_physical_reads], [avg_physical_reads], [physical_reads/sec], [min_physical_reads], [max_physical_reads], [last_physical_reads], 
  total_time_ms, avg_time_ms, min_time_ms, max_time_ms, last_time_ms, sql_text, 
  sql_handle, plan_handle,
  query_hash,
  ObjectDB, ObjectName, creation_time, last_execution_time,
  [Age in Cache(min)]
FROM QUERYSTATS
ORDER BY [total_physical_reads] DESC
20 фев 13, 21:18    [13957528]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
LenaV
Member

Откуда: USA
Сообщений: 6796
да.
Page Life Expectancy у вас маленькая,
но это возможно потому что у вас 500+ ГБ data warehouse на сервере?

посмотрите какие обьекты у вас висят в памяти.
пересмотрите индексы на самых больших и активных таблицах.
нет ли сканов кластерных индексов.
замените иx на не-кластерные,
или постройте не-кластерный по тем же полям
оптимизатор скорее всего его выбирет для запросов.
это все увеличит несколько нагрузку на диски, но освободит память.
протестируйте на тестовом сервере основательно.

удачи.
20 фев 13, 21:28    [13957569]     Ответить | Цитировать Сообщить модератору
 Re: Недостаточно физической памяти для запросов  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а "внезапно" память не ликается? имеет место при должном умении
скажем, sql dll / sql clr / xml / хитрые линкед сервера / злоупотребление sp_OA_ / ...
да мало ли что еще
20 фев 13, 22:04    [13957678]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить