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

Откуда:
Сообщений: 55
Добрый день.

Подскажите как понять что SQL Server-у памяти не хватает?

Какие признаки?
5 мар 18, 17:46    [21238605]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
SQL Server 2008
5 мар 18, 17:47    [21238607]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
Как-то запросом это понять можно?
5 мар 18, 17:48    [21238610]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 35460
https://www.google.ru/search?q=mssql memory bottleneck&oq=mssql memory bottleneck&aqs=chrome..69i57.9247j0j7&sourceid=chrome&ie=UTF-8
5 мар 18, 17:50    [21238622]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 2166
дампер
Добрый день.

Подскажите как понять что SQL Server-у памяти не хватает?

Какие признаки?

Признак первый.
SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd='LAZY WRITER')
      ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

И неофициальная ссылка.
Признак второй.
SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;  

И официальная ссылка.
5 мар 18, 18:05    [21238671]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
Гавриленко Сергей Алексеевич
https://www.google.ru/search?q=mssql memory bottleneck&oq=mssql memory bottleneck&aqs=chrome..69i57.9247j0j7&sourceid=chrome&ie=UTF-8



Смотрю представление:
sys.dm_os_performance_counters

и ни как не могу понять какой процент в кэш попадает (


Он же в процентах быть должен... в perfmon посмотреть не могу.

К сообщению приложен файл. Размер - 32Kb
5 мар 18, 18:10    [21238683]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
Andy_OLAP
дампер
Добрый день.

Подскажите как понять что SQL Server-у памяти не хватает?

Какие признаки?

Признак первый.
SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd='LAZY WRITER')
      ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

И неофициальная ссылка.
Признак второй.
SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;  

И официальная ссылка.






Спасибо.

Пытаюсь понять что выводит :)
5 мар 18, 18:13    [21238705]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
Andy_OLAP
Признак первый.
SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd='LAZY WRITER')
      ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'




PLE_SECS = 1637 сек вроде больше 300 сек.

Сильно ли 1637 - страшное значение?

... пока со всторым скриптом разбираюсь...
5 мар 18, 18:17    [21238714]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 2166
дампер
Andy_OLAP
Признак первый.
SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd='LAZY WRITER')
      ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'




PLE_SECS = 1637 сек вроде больше 300 сек.

Сильно ли 1637 - страшное значение?

... пока со всторым скриптом разбираюсь...

Запустите тяжелый и длинный запрос. Если после его окончания значение PLE упадет практически до нуля - памяти для SQL так мало, что в кэши ничего не помещается.
5 мар 18, 18:31    [21238751]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
Andy_OLAP
дампер
пропущено...



PLE_SECS = 1637 сек вроде больше 300 сек.

Сильно ли 1637 - страшное значение?

... пока со всторым скриптом разбираюсь...

Запустите тяжелый и длинный запрос. Если после его окончания значение PLE упадет практически до нуля - памяти для SQL так мало, что в кэши ничего не помещается.



Понял, благодарю!


... пока со вторым запросом разбираюсь, там sql_memory_utilization_percentage=100
5 мар 18, 18:35    [21238757]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

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

разюбираюсь с

sys.dm_os_process_memory



там есть два последних поля, они сейчас 0.


Если бы с памятью было бы все плохо - они бы стали 1 ?

К сообщению приложен файл. Размер - 29Kb
5 мар 18, 18:46    [21238790]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Andy_OLAP
Member

Откуда: я знаю, что Зенит - чемпион
Сообщений: 2166
дампер
Andy_OLAP
пропущено...

Запустите тяжелый и длинный запрос. Если после его окончания значение PLE упадет практически до нуля - памяти для SQL так мало, что в кэши ничего не помещается.



Понял, благодарю!


... пока со вторым запросом разбираюсь, там sql_memory_utilization_percentage=100

Используй Силу, Люк! (c)

Можно скачать официальную утилиту мелкомягких под названием RamMap, прочитать кошерную статью из официального блога, учесть различные нюансы.
И понять, что куда идет на сервере.
5 мар 18, 18:56    [21238819]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
invm
Member

Откуда: Москва
Сообщений: 7799
дампер,

Оценить можно так:
select
 counter_name, cntr_value
from
 sys.dm_os_performance_counters
where
 counter_name in (N'Total Server Memory (KB)', N'Target Server Memory (KB)')
Если Target > Total, то памяти не хватает.
5 мар 18, 19:05    [21238840]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 842
Andy_OLAP
дампер
пропущено...



PLE_SECS = 1637 сек вроде больше 300 сек.

Сильно ли 1637 - страшное значение?

... пока со всторым скриптом разбираюсь...

Запустите тяжелый и длинный запрос. Если после его окончания значение PLE упадет практически до нуля - памяти для SQL так мало, что в кэши ничего не помещается.

ерунду сказали, и что он получит? может такой выборки никогда не будет? ну да после этого запроса упадет PLE до нуля, но это не показатель.
Как выше уже говорили:
ple за период, а не за запрос или час
buffercash
операци lazy wrire
логи сиквела смотрите, если серверу совсем плохо, то это отразится в логах.
плюс есть еще пару счетчиков, связанные со страницами и dmv, но не могу точно сказать.
все это надо анализировать совместно, а не отдельно,

Как то на курсах в MS говорили, что идеально когда память на сервере равна объему бд, но это применимо когда бд небольшие.
У меня бд десятки Тб, памяти столько не поставишь, а даже еще сделаешь хуже серверу с огромным объемом памяти.

А конкретно по вашему вопросу и случаю, описали бы какой объем у вас есть, размер бд, и почему такой вопрос задаете.
6 мар 18, 00:30    [21239260]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 842
invm
дампер,

Оценить можно так:
select
 counter_name, cntr_value
from
 sys.dm_os_performance_counters
where
 counter_name in (N'Total Server Memory (KB)', N'Target Server Memory (KB)')
Если Target > Total, то памяти не хватает.

не показатель, отчасти.
6 мар 18, 00:30    [21239261]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
дампер
Member [заблокирован]

Откуда:
Сообщений: 55
invm
дампер,

Оценить можно так:
select
 counter_name, cntr_value
from
 sys.dm_os_performance_counters
where
 counter_name in (N'Total Server Memory (KB)', N'Target Server Memory (KB)')
Если Target > Total, то памяти не хватает.




А если равны? о_О

У меня они равны.
6 мар 18, 10:22    [21239729]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
invm
Member

Откуда: Москва
Сообщений: 7799
дампер
У меня они равны.
Тогда смотрите другие счетчики:
Page Life Expectancy
Buffer Cache hit ratio
Page reads / sec
Page writes / sec
Lazy writes / sec
Memory Grants Pending
Total Server Memory
Target Server Memory
Available Mbytes
Pages / sec
Paging File % Usage

Почему вы вообще решили, что у вас проблемы с памятью?
6 мар 18, 10:57    [21239846]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
aleksrov
Member

Откуда:
Сообщений: 768
Смешались кони люди. Дельное только invm сказал.

Так, по порядку: Запустите тяжелый и длинный запрос. Если после его окончания значение PLE упадет практически до нуля - памяти для SQL так мало, что в кэши ничего не помещается.
Вообще не показатель. У меня в системе есть хранимка которая выполняется раз в 30 мин. и собирает статистику, выполняется минуты 3-5, выгребает чуть ли не 50 ГБ при кэше в 20. PLE падает до нуля, а Lazy Write счетчик до 900 подскакивает. Этот запрос выбирает данные которые потом и нафиг не нужны следующим запросом, в итоге PLE у меня не бывает больше 1700 примерно, а после этого запроса счетчик Page Read какое то время показывает довольно высокие значения. Вывод, будет скоро брать оперативу, в раза 3 увеличим и будет у меня счастье. Я к тому что надо знать нагрузку и понимать ее приничины, а не просто смотреть на цифры.

Buffer Cache hit ratio не показатель - https://www.red-gate.com/simple-talk/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

Смотрите счетчики которые написал invm, смотрите самые дорогие хранимки и запросы в кэше по i\o
select top 50 
s.name + '.' + p.name as [Procedure] 
,qp.query_plan as [Plan] 
,(ps.total_logical_reads + ps.total_logical_writes) / 
ps.execution_count as [Avg IO] 
,ps.execution_count as [Exec Cnt] 
,ps.cached_time as [Cached] 
,ps.last_execution_time as [Last Exec Time] 
,ps.last_physical_reads as [Last Ph reads] 
,ps.total_physical_reads as [Total Ph reads] 
,ps.total_logical_reads as [Total Log Reads] 
,ps.last_logical_reads as [Last Log Reads] 
,ps.total_logical_writes as [Total Log Writes] 
,ps.last_logical_writes as [Last Log Writes] 
,ps.total_worker_time as [Total Worker Time]--в микросекундах 
,ps.last_worker_time as [Last Worker Time]--в микросекундах 
,ps.total_elapsed_time as [Total Elapsed Time]--в микросекундах 
,ps.last_elapsed_time as [Last Elapsed Time]--в микросекундах 
from 
sys.procedures as p with (nolock) join sys.schemas s with (nolock) on 
p.schema_id = s.schema_id 
join sys.dm_exec_procedure_stats as ps with (nolock) on 
p.object_id = ps.object_id 
outer apply sys.dm_exec_query_plan(ps.plan_handle) qp 
order by 
[Avg IO] desc 
option (recompile); 






select top 50 
substring(qt.text, (qs.statement_start_offset/2)+1, 
(( 
case qs.statement_end_offset 
when -1 then datalength(qt.text) 
else qs.statement_end_offset 
end - qs.statement_start_offset)/2)+1) as [Sql] 
,qs.execution_count as [Exec Cnt] 
,(qs.total_logical_reads + qs.total_logical_writes) 
/ qs.execution_count as [Avg IO] 
,qp.query_plan as [Plan] 
,qs.total_logical_reads as [Total Logic Reads] 
,qs.last_logical_reads as [Last Logic Reads] 
,last_physical_reads 
,qs.total_logical_writes as [Total Logic Writes] 
,qs.last_logical_writes as [Last Logic Writes] 
,qs.total_worker_time as [Total Worker Time] --время исп. CPU 
,qs.last_worker_time as [Last Worker Time] 
,qs.total_elapsed_time/1000 as [Total Elps Time] 
,qs.last_elapsed_time/1000 as [Last Elps Time] 
,qs.creation_time as [Compile Time] 
,qs.last_execution_time as [Last Exec Time] 
,qs.query_plan_hash 
,qs.query_hash 
from 
sys.dm_exec_query_stats qs with (nolock) 
cross apply sys.dm_exec_sql_text(qs.sql_handle) qt 
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp 
order by [Avg IO] desc option (recompile) 
--order by [Total Worker Time] desc option (recompile) 


Также если совсем все хреново можно еще посмотреть dm_exec_query_resource_Semaphores и dm_exec_query_memory_grants

Также учтите, что нехватка памяти может быть не нехваткой как таковой, а просто плохо написанным запросом.
6 мар 18, 12:24    [21240138]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Eleanor
Member

Откуда:
Сообщений: 1717
дампер
разбираюсь с
sys.dm_os_process_memory

там есть два последних поля, они сейчас 0.
Если бы с памятью было бы все плохо - они бы стали 1?

Это значит, что прямо сейчас памяти у вас хватает. Было бы 1 - прямо сейчас не хватает.

Можно посмотреть, не было ли нехватки памяти в прошлом (обычно в течение нескольких дней) с помощью запроса к sys.dm_os_ring_buffers.
EventTime - время события,
IndicatorsProcess = 2 - Sql Server-у не хватало памяти,
IndicatorsSystem = 2 - ОС не хватало памяти.

Но, честно говоря, использую этот запрос только для сравнения со средними значениями. Если сообщений о нехватки памяти за день неожиданно стало больше, чем было раньше - стоит посмотреть, что произошло.
6 мар 18, 12:55    [21240239]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
step_ks
Member

Откуда:
Сообщений: 924
invm
дампер,

Оценить можно так:
select
 counter_name, cntr_value
from
 sys.dm_os_performance_counters
where
 counter_name in (N'Total Server Memory (KB)', N'Target Server Memory (KB)')
Если Target > Total, то памяти не хватает.

Хм, можете пояснить или указать источник?
Допустим, Target=56GB, Total=40 GB. Т.е. серверу больше 40 пока не надо. Почему можно считать, что не хватает?
7 мар 18, 22:26    [21244185]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
invm
Member

Откуда: Москва
Сообщений: 7799
step_ks
Допустим, Target=56GB, Total=40 GB. Т.е. серверу больше 40 пока не надо. Почему можно считать, что не хватает?
Не обязательно не хватает, а может быть не хватает. Поэтому я и написал "оценить".
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1e51965d-fbb1-4f73-b977-5284516b3d45/total-server-memory-vs-target-server-memory-made-me-crazy?forum=sqldatabaseengine
If Total Server Memory is less than Target Server Memory it can be a sign of memory pressure
7 мар 18, 23:51    [21244348]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 1995
invm,

И самое главное
автор
However, I very rarely rely on this as a way to look for memory pressure, there a plenty of other counters that give you a better clue when SQL is in memory contention.
8 мар 18, 02:24    [21244498]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Col
Member

Откуда: Торонто
Сообщений: 152
Странно что еще не написали о самом простом:
DBCC MEMORYSTATUS
Материала для анализа там более чем достаточно.

Ну и совсем уж апокалиптический евент 17130
"Not enough memory for the configured number of locks"

Неужто никто не наступал?
8 мар 18, 23:51    [21245509]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 1995
aleksrov
смотрите самые дорогие хранимки и запросы в кэше по i\o
В случае серьезного memory pressure кэш планов будет достаточно быстро вымываться, так что толку на него смотреть особо нету. Да и впринципе, не понятно какая связь между средним количеством логических операций и памятью? Ну допустим есть дорогие запросы, а как это покажет что "SQL Server-у памяти не хватает"?
9 мар 18, 01:54    [21245561]     Ответить | Цитировать Сообщить модератору
 Re: Как понять что SQL Server-у не хватает памяти?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 1995
Col
Странно что еще не написали о самом простом:
DBCC MEMORYSTATUS
Материала для анализа там более чем достаточно.
Я бы не назвал это самым простым, и скорее проблема в том что материала слишком много. Надо точно знать куда смотреть. У вас есть какие то четкие критерии, типа если это значение больше такого то, значит серверу памяти не хватает?

Col
Ну и совсем уж апокалиптический евент 17130
"Not enough memory for the configured number of locks"
Неужто никто не наступал?
Нет не случалось. Был другой весьма редкий случай "нехватки памяти".

CPU через 1-2 неделю после перезагрузки сервера стабильно уходило в 90%+, при этом все предложенные тут счетчики памяти не показывали вообще ничего, PLE намного больше 300, Free Memory так вообще 4Гб, всякие Total, Target тоже ничего вменяемого. Единственные аномалии были в высоком RESOURCE_SEMAPHORE_QUERY_COMPILE ну и соответственно Big Gateway-и проседали. В итоге, как оказалось, один из клерков (вроде этот USERSTORE_TOKENPERM) утекал, забирая себе всю память, вытесняя всех остальных, но только внутри Stolen Server Memory, не забирая память у Database Cache и не захватывая больше определенного лимита из Free Memory. Соответстенно PLE и прочие совсем не страдали, зато очень сильно страдал Plan Cache, что собственно и приводило к постоянным вытеснениям планов и компиляциям (не рекомпиляциям) ну и как следствие высокой CPU нагрузке.

Как раз таки только DBCC MEMORYSTATUS хоть как то намекал что преблема с памятью, но естественно нужно было знать, что конкретно искать, к тому же ожидания на Gateway были далеко не постоянными, так что нужно было еще и поймать тот момент когда это происходило.


Ко всему вышеперечисленному, я бы добавил что нужно смотреть на ожидания, а конкретно на:
PAGEIOLATCH_XX
RESOURCE_SEMAPHORE
RESOURCE_SEMAPHORE_QUERY_COMPILE

Плюс ожиданий в отличие от предложенных DMV, еще и в том, что они накапливают информацию, а не показывают ее на конкретный момент.
9 мар 18, 02:33    [21245568]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить