Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
Добрый день.
Столкнулся с проблемой в MS SQL 2008 R2, при выполнении загрузки данных из временных таблиц в постоянные у сервера начинает расти потребление оперативной памяти. Загрузку данных в таблицу произвожу инсертом через селект.
На сервере физически 16Гб ОЗУ. Для SQL сервера я выделил от 8 до 12Гб.
Когда я смотрю вот таким скриптом чем занята память SQL сервера:
+
USE tempdb
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);

то получаю вот такие данные:
TableName	index_id	IndexName	BufferSize MB	EmptySize MB	DirtySize MB
NULL	        NULL	        NULL	        5602.68	        824.05	        1728.47
sys.service_broker_map	1	I_CLUST	0.02	0.01	0.00
sys.service_broker_map	2	I_SECONDARY	0.02	0.01	0.00

И вот мне из этих данных мне не ясно, чем заняты 5,6Гб ОЗУ, что скрывается за этими NULL.
Как посмотреть более подробно?
4 апр 14, 11:01    [15831750]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 888
free-admin,

DBCC MEMORYSTATUS
4 апр 14, 11:19    [15831855]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
К сожалению DBCC MEMORYSTATUS дает только численную информацию по памяти.
А мне бы хотелось узнать, чем именно занят буфер и почему он не освобождается сам, а только растет при последующем выполнении команд, пока не упрется в ограничение в 12Гб.
После выполнения блоков команд я делаю коммит и по идее буфер должен освобождаться, а он только растет.
4 апр 14, 11:46    [15832047]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
free-admin
А мне бы хотелось узнать, чем именно занят буфер и почему он не освобождается сам, а только растет при последующем выполнении команд, пока не упрется в ограничение в 12Гб.
Буфер занят данными. Не освобождается, потому что настроен так, что ему разрешено брать 12 Гб, вот он их и берет.

free-admin
После выполнения блоков команд я делаю коммит и по идее буфер должен освобождаться, а он только растет.
По чьей идее?
4 апр 14, 12:03    [15832156]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
Я столкнулся с тем, что когда буфер забит полностью, то процесс загрузки данных в таблицы останавливается.
Возможно я не там ищю ошибку, но мне хочется понять, какими данными забивается буфер и как часто он освобождается?
Скрипт который я показал выше, он показывает размер буфера для базы tempdb.
Если буфер забит под базу tempdb, то как же остальные базы на сервере, где они смогут взять место в ОЗУ под буфер?
4 апр 14, 12:10    [15832209]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
free-admin
Я столкнулся с тем, что когда буфер забит полностью, то процесс загрузки данных в таблицы останавливается.
"Останавливается" - это какая-то ошибка?

free-admin
Возможно я не там ищю ошибку, но мне хочется понять, какими данными забивается буфер и как часто он освобождается?
Скрипт который я показал выше, он показывает размер буфера для базы tempdb.
Если буфер забит под базу tempdb, то как же остальные базы на сервере, где они смогут взять место в ОЗУ под буфер?
Покажите результат вот этого запроса: 15743206
4 апр 14, 12:15    [15832245]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
Результат выполнения скрипта:
+
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


К сообщению приложен файл. Размер - 72Kb
4 апр 14, 12:38    [15832427]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
Из ваших 12 Гб: 9+ Гб занимает Buffer Pool, т.е. кеш с данными, 2+ Гб занимает кеш планов выполнения (что, имхо, как-то много).
4 апр 14, 12:50    [15832511]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
Подскажите где можно прочитать про кеш планов.
Как можно посмотреть какими данными занят кеш Buffer Pool.
Что можно предпринять, чтобы кеш планов не был таким большим?
Из-за чего он может так много занимать места?
4 апр 14, 12:58    [15832568]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
Содержимое BP:
select
       db_name(bd.database_id) as db,
       count(*)as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.hobt_id
                    and (au.type = 1 or au.type = 3)
        union all
        select       object_id as objectid,
                           object_name(object_id) as name,
                           index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
                on au.container_id = p.partition_id
                    and au.type = 2
    ) as obj
        on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by bd.database_id, obj.name, ind.name, obj.index_id
order by cached_pages_count desc
4 апр 14, 13:01    [15832584]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
Ну и что хочу сказать. Изучать матчасть - нужное дело, но вы почему-то решили, что все ваши проблемы из-за того, что "сервер жрет много памяти".

Я бы сначала разбирался с тем, чего не хватает вашим "отановившимся" запросам.

И да, версию сервера покажите.
4 апр 14, 13:04    [15832604]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
Кстати, судя по Page Life Expectancy, памяти вам точно хватает.

Сообщение было отредактировано: 4 апр 14, 13:06
4 апр 14, 13:05    [15832618]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64) Nov 30 2012 17:11:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Пытаюсь понять что не хватает моим запросам.
Включал трасировку запроса, но там с первого раза не сумел все понять.
4 апр 14, 13:14    [15832681]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
free-admin
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64) Nov 30 2012 17:11:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Пытаюсь понять что не хватает моим запросам.
Включал трасировку запроса, но там с первого раза не сумел все понять.
Ловите ваш запрос, смотрите его статус через sp_who2 какой-нибудь, смотрите ожидания через sys.dm_os_waiting_tasks.
4 апр 14, 13:15    [15832690]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL 2008 R2 потребляет много памяти ОЗУ  [new]
free-admin
Member

Откуда:
Сообщений: 21
Спасибо вам за ответы, буду смотреть дальше что не хватает запросам.

Есть еще один вопрос он тоже относится наверное к памяти, поэтому задам его здесь.

Когда я отслеживал выполнение запроса, то видел сообщения sort warning и в этой строке был id транзакции.
Как можно найти sql запрос по id транзакции или посмотреть всю транзакцию?
Если я правильно понял, то чтобы не появлялось сообщение sort warning, то нужно увеличить параметр "Минимальный объем памяти для запроса"?
4 апр 14, 13:28    [15832785]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить