Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 sql 2008 R2 съедает память  [new]
lex452
Member

Откуда:
Сообщений: 590
Есть sql 2008 R2 на сервере 16 гб памяти, существует проблема-sql сервер съедает практически всю память, а также весь файл подкачки, причем неважно какого он размера. Будь он хоть 50 гб.
Если ограничивать использование памяти сервером, то возникают блокировки, если не ограничить то ест память неизвестно куда.
Главная особенность в том, что диспетчер задач показывает что в данный момент сервер потребляет около 14 гб, а всего вместе с файлом подкачки занять 50 гб. И решить проблему с памятью помогает только перезапуск службы SQL server.
Из-за чего такое возможно? И как отследить что именно съедает всю память?
15 мар 14, 21:21    [15730619]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
lex452,

https://www.sql.ru/forum/1080907/maximum-server-memory
15 мар 14, 21:33    [15730684]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
Так и должно быть. Сервер забирает память динамически, ничего страшного в этом нет.
17 мар 14, 11:57    [15736957]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
lex452
Member

Откуда:
Сообщений: 590
Что ж тут нормального? На самой машине ничего не работает когда сервер всю память съедает. Иногда перегружать приходится
17 мар 14, 13:35    [15737918]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
lex452
Что ж тут нормального? На самой машине ничего не работает когда сервер всю память съедает. Иногда перегружать приходится
В смысле - ничего не работает? Эксель что ли не запускается?
Ограничивать потребление памяти серверу не пробовали?
17 мар 14, 13:42    [15737973]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Glory
Member

Откуда:
Сообщений: 104751
lex452
Что ж тут нормального?

Нормально то, что программа ведет себя так, как ее сконфигурировали

lex452
На самой машине ничего не работает когда сервер всю память съедает.

Вам дали ссылку на то, как ограничить доступную память
17 мар 14, 13:47    [15738015]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
lex452
Если ограничивать использование памяти сервером, то возникают блокировки
Какие блокировки? Каким запросом вы это выяснили? Почему вы решили что это связано с ограничением памяти? Какое именно ограничение вы поставили?
17 мар 14, 23:59    [15742278]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
lex452,

Может быть серверу не хватает памяти?
Или это недавно началось?
А если посмотреть куда он ее сожрал - как идет распределение памяти в SQL?

+
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


Сообщение было отредактировано: 4 апр 14, 12:12
18 мар 14, 10:10    [15743206]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
lex452
Что ж тут нормального? На самой машине ничего не работает когда сервер всю память съедает. Иногда перегружать приходится

Так и не надо больше ничего, кроме сервера, запускать.
18 мар 14, 11:24    [15743649]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
SU-V500
Member

Откуда: Moscow
Сообщений: 40
AnyKey45,

Воту меня например вот такой результат. Память всегда практически вся выжрана. Как мне понять где попа и с чего начать? Просто постоянно тупо уведичивать объём ОЗУ тоже не фонтан.

К сообщению приложен файл. Размер - 49Kb
18 мар 14, 11:26    [15743672]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
Виртуальную память распределяет Windows, а не сервер, кстати сказать. Причем здесь блокировки и объем памяти? Или это фраза типа "в розетке замыкает"?
18 мар 14, 11:27    [15743675]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
SU-V500
Member

Откуда: Moscow
Сообщений: 40
Владислав Колосов,

Владислав, а как понять, что сиквел уже лезет в page file. Я так понимаю это жуть как плохо для перфоманса? Может выкобчать на ДБ серверах педж файл вообще?
18 мар 14, 11:34    [15743756]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
Уж не знаю, возможно, что для Ваших задач недостаточно 16Гб оперативной памяти. Windows может сбрасывать в PageFile страницы оперативной памяти, которые считаются устаревшими, с ее точки зрения, при запросе памяти от приложений.

exec sp_configure 'max server memory (MB)'
что показывает?
18 мар 14, 11:59    [15743990]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
SU-V500
Member

Откуда: Moscow
Сообщений: 40
Владислав Колосов,

2147483647
18 мар 14, 12:05    [15744061]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
aleks2
Guest
SU-V500
Владислав Колосов,

2147483647

И на что ты жалуешься?
+ дай учетке сервера право Lock Pages In Memory.
18 мар 14, 12:07    [15744085]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Рука уже устала: https://www.sql.ru/forum/1080907/maximum-server-memory
18 мар 14, 12:10    [15744121]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
SU-V500
Member

Откуда: Moscow
Сообщений: 40
aleks2,

Комрады, я н на что н ежалуюсь, слава богу всё работает нормально. Я просто для себя хочу разобраться а собсвтенно гуда израсходовалась память. Т.е. как она сортитться внутри сиквел сервиса. Пока я вижу что практически всё это буфферный пулл. Но читаю стастьи, там пишут что переодически идёт освобожение памяти из под буфферного пулла, что у себя допустим я вообще не наблюдаю. Т.е. как запустит сиквел, потихоньку начал жрать, сожрал всё через дня два - три и житвёт себе так.
18 мар 14, 12:43    [15744557]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Glory
Member

Откуда:
Сообщений: 104751
SU-V500
там пишут что переодически идёт освобожение памяти из под буфферного пулла, что у себя допустим я вообще не наблюдаю.

MSSQL сам по своей инициативе память не освобождает.
Об этом его должна попросить оп.система.
18 мар 14, 12:48    [15744613]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
SU-V500
Member

Откуда: Moscow
Сообщений: 40
Glory,

Хорошо, ну вот сиквел съел всю память, а как понять что ему хватило и всё впорядке?
18 мар 14, 12:59    [15744756]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Glory
Member

Откуда:
Сообщений: 104751
SU-V500
а как понять что ему хватило и всё впорядке?

Есть счетчики - попадания в кэш, свопинг страниц памяти и тд
18 мар 14, 13:02    [15744784]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
AnyKey45
Member

Откуда: Ekaterinburg-Moscow-EU
Сообщений: 219
lex452 ,
простыми словами, как я понимаю:
в свойствах сервака пропиши максимум памяти 14000 и выбери configured values
чтобы не жрал стока памяти у тебя он, чтобы запрос, который я привел выше показывал не 30000, а 14000 в Target Server Memory

хз как это на работе продакшена только отразится

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

Например:

В моем случае exec sp_configure 'max server memory (MB)' выдает:
name minimum maximum config_value run_value
max server memory (MB) 16 2147483647 8500 8500

И запрос, который я привел выше выдает:
Counter Name Value
Target Server Memory (MB) 8500.00

Было бы у меня 100 гигов - тоже были бы все заняты, просто меньше бы обращался сервак к файлам базы(чаще попадание в кеш)
18 мар 14, 13:41    [15745164]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8570
Ну так если освобождения буферного пула нет, ошибок страниц мало, то это есть гуд и оперативки достаточно.
Также можно обратить на количество физических чтений при выполнении запросов - если значение намного меньше логических, то сервер не ограничен дисковой подсистемой и памяти достаточно.
18 мар 14, 14:29    [15745736]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
aleks2
Guest
Владислав Колосов
Ну так если освобождения буферного пула нет, ошибок страниц мало, то это есть гуд и оперативки достаточно.
Также можно обратить на количество физических чтений при выполнении запросов - если значение намного меньше логических, то сервер не ограничен дисковой подсистемой и памяти достаточно.


Хе-хе. Это фсе равно как неандертальцу про устройство адронного коллайдера расказывать.

Тредстартеру надо
1. Дать серверу право запрещать своп своей памяти - право Lock Pages In Memory.
Если этого не сделать своп приобретает особенно идиотский характер.
Сервер просит ОСь: дай ишо памяти.
ОСь любезно выгружает память сервера и дает ему же освободившуюся.
Результат - дикий свопинг.

2. А вот ограничение MaxMemory - это вторично.
18 мар 14, 17:13    [15747529]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
4GTnpSWd
Member

Откуда: Москва
Сообщений: 529
[quot aleks2]
Владислав Колосов

Тредстартеру надо
1. Дать серверу право запрещать своп своей памяти - право Lock Pages In Memory.
- а где эту настройку найти?

Ограничение нашел, но Lock Pages In Memory - там нет.
в моем случае SQL 2005 .
32 гига оперативки. SQL отъедает порядка 27гигов и дальше кто нить из юзеров пытается отчет запустить в 1С и получает сообщение что Недостаточно памяти.

Сейчас ограничил 10 гигами. посмотрим что будет дальше.
27 мар 14, 11:21    [15793456]     Ответить | Цитировать Сообщить модератору
 Re: sql 2008 R2 съедает память  [new]
Glory
Member

Откуда:
Сообщений: 104751
4GTnpSWd
дальше кто нить из юзеров пытается отчет запустить в 1С и получает сообщение что Недостаточно памяти.

Это не серверное сообщение скорее всего.

4GTnpSWd
- а где эту настройку найти?

В Windows Group Policy для учетной записи, которую использует SQL Server
27 мар 14, 11:31    [15793502]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить