f SQL Server 主機記憶體使用率超過90%合理嗎?? ~ 迪貝之家

Pages

SQL Server 主機記憶體使用率超過90%合理嗎??

前一陣子為了這個事情被煩的有點受不了,我就乾脆設上max server memory 的值,沒再被煩過了,微軟文章
Troubleshooting Performance Problems in SQL Server 2008
裡頭包了一份troubleshooting的指南文件,可以做為參考


我節錄出它建議在OS Performance counter要看的值及怎麼看這些值

External Physical Memory Pressure

Look at the Memory: Available [M, K]Bytes performance counter. If the available memory amount is low, external memory pressure might be present. The exact value depends on many factors, such as the total amount of installed physical memory or the architecture (32-bit or 64-bit); however, you can start looking into this when the value drops below 50-100 MB. As with any troubleshooting strategy, keeping a baseline of a normally operating system provides you with a good reference value to look for.
If external memory pressure exists and you are seeing memory-related errors, you will need to identify major consumers of the physical memory on the system. To do this, look at the Process: Working Set performance counters and identify the largest consumers.
The total use of physical memory on the system can be roughly accounted for by summing the following counters:
· Process object, Working Set counter for each process
· Memory object
o Cache Bytes counter for system working set
o Pool Nonpaged Bytes counter for size of unpaged pool
o Available Bytes
o Modified Page List Bytes counter
· If there’s no external pressure, the Process: Private Bytes counter should be close to the size of the working set (Process: Working Set), which means that no memory is paged out.
Note that the corresponding performance counters do not count memory that is allocated through AWE mechanisms. Thus the information is insufficient if AWE is enabled or locked pages are used. In this case, you need to look at the memory distribution inside SQL Server to get a full picture. You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through the AWE mechanism.

至少人家微軟的文件是這麼說的嘛!!

如果想從資料庫面來看,人家文件也是有提到看哪一個DMV
sys.dm_os_process_memory.process_physical_memory_low 值如果為1, indicates that the process is responding to physical memory low notification
或者

sys.dm_os_sys_memory.system_memory_state_desc
給了你這個字串"Available physical memory is low"



也許你會問,DMV的資料怎麼來的?? 2012 Internals這本書有寫
SQLOS

以前舊有的機制如果不適用,就該把它改掉阿!!莫名其妙造成系統負責人額外的Loading很好玩嗎 ??