ÇÔ¸Õ¡ÒÃãªé¤ÓÊÑè§ MEMORYSTATUS DBCC à¾×è͵ÃǨÊͺ¡ÒÃãªé˹èǤÇÒÁ¨Óã¹ SQL Server 2005

µÑǨѴ¡ÒÃ˹èǤÇÒÁ¨Ó

   Memory Manager                 KB 
   ------------------------------ --------------------
   VM Reserved                    1761400
   VM Committed                   1663556
   AWE Allocated                  0
   Reserved Memory                1024
   Reserved Memory In Use         0

   (5 row(s) affected)

• vm ·Õè¨Í§äÇé: ¤èÒ¹ÕéáÊ´§ÂÍ´à§Ô¹â´ÂÃÇÁ¢Í§¾×é¹·ÕèÇèÒ§·ÕèÍÂÙèàÊÁ×͹ (VAS) ·ÕèÁÕÊÓÃͧ¢Í§ sql server
• vm ·Õè¡Ó˹´: ¤èÒ¹ÕéáÊ´§ÂÍ´à§Ô¹â´ÂÃÇÁ¢Í§ VAS ·ÕèÁÕ¡Ó˹´ SQL Server VAS ·ÕèÁØè§ÁÑè¹·Õèä´éÃѺ¡ÒÃàª×èÍÁ⧡Ѻ˹èǤÇÒÁ¨Ó¡ÒÂÀÒ¾
• awe ·Õè»Ñ¹Êèǹ: ¤èÒ¹ÕéáÊ´§ÂÍ´à§Ô¹â´ÂÃÇÁ¢Í§Ë¹èǤÇÒÁ¨Ó·ÕèÁÕ¡ÒûѹÊèǹ¼èÒ¹·Ò§¡Åä¡ AWE º¹ SQL Server ÃØè¹ 32 ºÔµ ËÃ×Í ¹Õé¤èÒ¡ÒÃáÊ´§ÂÍ´à§Ô¹â´ÂÃÇÁ¢Í§Ë¹èǤÇÒÁ¨Ó·Õè¶Ù¡Åç͡ྨ·Õèãªéà¹×éÍ·Õ躹àÇÍÃìªÑ¹ 64 ºÔµ¢Í§¼ÅÔµÀѳ±ì
• ˹èǤÇÒÁ¨Ó·ÕèʧǹäÇé: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèʧǹäÇéÊÓËÃѺ¡ÒÃàª×èÍÁµèÍâ´Â੾ÒмÙé´ÙáÅÃкº (DAC)
• ˹èǤÇÒÁ¨Ó·ÕèʧǹäÇéã¹ÃÐËÇèÒ§¡ÒÃãªé: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèʧǹäÇé·Õè¡ÓÅѧ¶Ù¡ãªé

ÊÃØ»¡ÒÃãªé˹èǤÇÒÁ¨Ó

   Memory node Id = 0             KB 
   ------------------------------ --------------------
   VM Reserved                    1757304
   VM Committed                   1659612
   AWE Allocated                  0
   MultiPage Allocator            10760
   SinglePage Allocator           73832

   (5 row(s) affected)

• vm ·Õè¨Í§äÇé: ¤èÒ¹ÕéáÊ´§ VAS ·Õè¨Í§äÇé â´Âà¸Ã´·ÕèàÃÕ¡ãªéº¹â˹´¹Õé
• vm ·Õè¡Ó˹´: ¤èÒ¹ÕéáÊ´§ VAS ·Õè¡Ó˹´ â´Âà¸Ã´·ÕèàÃÕ¡ãªéº¹â˹´¹Õé
• awe ·Õè»Ñ¹Êèǹ: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèÁÕ¡ÒûѹÊèǹ¼èÒ¹·Ò§¡Åä¡ AWE ã¹¼ÅÔµÀѳ±ìÃØè¹ 32 ºÔµ ËÃ×Í ¤èÒ¹ÕéáÊ´§ÂÍ´à§Ô¹â´ÂÃÇÁ¢Í§Ë¹èǤÇÒÁ¨Ó·Õèãªé â´Âྨ·Õè¶Ù¡ÅçÍ¡ÍÂÙèÃØè¹ 64 ºÔµ¢Í§¼ÅÔµÀѳ±ì
  
  ã¹Ãкºà»Ô´ãªé§Ò¹ NUMA ¤èÒ¹ÕéÍÒ¨äÁè¶Ù¡µéͧ ËÃ×ͤèÒź ÍÂèÒ§äáçµÒÁ ·Ñé§awe ·Õè»Ñ¹Êèǹvalue in the Memory Manager section is a correct value. To track memory that is allocated by individual NUMA nodes, useSQL Server: Buffer Nodeperformance objects. (For more information, see˹ѧÊ×Íà«ÔÃì¿àÇÍÃì sql ẺÍ͹äŹì.)
• MultiPage Allocator: This value shows the memory that is allocated through the multipage allocator by threads that are running on this node. This memory comes from outside the buffer pool.
• SinglePage Allocator: This value shows the memory that is allocated through the single-page allocator by threads that are running on this node. This memory is stolen from the buffer pool.

Aggregate memory

   MEMORYCLERK_SQLGENERAL (node 0)                                  KB 
   ---------------------------------------------------------------- --------------------
   VM Reserved                                                      0
   VM Committed                                                     0
   AWE Allocated                                                    0
   SM Reserved                                                      0
   SM Commited                                                      0
   SinglePage Allocator                                             592
   MultiPage Allocator                                              2160

   (7 row(s) affected)

   MEMORYCLERK_SQLGENERAL (node 1)                                  KB 
   ---------------------------------------------------------------- --------------------
   VM Reserved                                                      0
   VM Committed                                                     0
   AWE Allocated                                                    0
   SM Reserved                                                      0
   SM Commited                                                      0
   SinglePage Allocator                                             136
   MultiPage Allocator                                              0

   (7 row(s) affected)

   MEMORYCLERK_SQLGENERAL (Total)                                   KB 
   ---------------------------------------------------------------- --------------------
   VM Reserved                                                      0
   VM Committed                                                     0
   AWE Allocated                                                    0
   SM Reserved                                                      0
   SM Commited                                                      0
   SinglePage Allocator                                             728
   MultiPage Allocator                                              2160

   (7 row(s) affected)

   MEMORYCLERK_SQLGENERAL (Total)                                   KB 
   ---------------------------------------------------------------- --------------------
   VM Reserved                                                      0
   VM Committed                                                     0
   AWE Allocated                                                    0
   SM Reserved                                                      0
   SM Commited                                                      0
   SinglePage Allocator                                             768
   MultiPage Allocator                                              2160

   (7 row(s) affected)

• SM Reserved: This value shows the VAS that is reserved by all clerks of this kind that are using the memory-mapped files API. This API is also known asshared memory.
• SM Committed: This value shows the VAS that is committed by all clerks of this kind that are using memory-mapped files API.

select 
	type,
	sum(virtual_memory_reserved_kb) as [VM Reserved],
	sum(virtual_memory_committed_kb) as [VM Committed],
	sum(awe_allocated_kb) as [AWE Allocated],
	sum(shared_memory_reserved_kb) as [SM Reserved], 
	sum(shared_memory_committed_kb) as [SM Committed],
	sum(multi_pages_kb) as [MultiPage Allocator],
	sum(single_pages_kb) as [SinlgePage Allocator]
from 
	sys.dm_os_memory_clerks 
group by type

Buffer distribution

   Buffer Distribution            Buffers
   ------------------------------ -----------
   Stolen                         553
   Free                           103
   Cached                         161
   Database (clean)               1353
   Database (dirty)               38
   I/O                            0
   Latched                        0

   (7 row(s) affected)

• Stolen:Stolen memorydescribes 8-KB buffers that the server uses for miscellaneous purposes. These buffers serve as generic memory store allocations. Different components of the server use these buffers to store internal data structures. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.
• ÇèÒ§: This value shows committed buffers that are not currently being used. These buffers are available for holding data. Or, other components may request these buffers and then mark these buffers asStolen.
• Cached: This value shows the buffers that are used for various caches.
• Database (clean): This value shows the buffers that have database content and that have not been modified.
• Database (dirty): This value shows the buffers that have database content and that have been modified. These buffers contain changes that must be flushed to disk.
• I/O: This value shows the buffers that are waiting for a pending I/O operation.
• Latched: This value shows thelatchedbuffers. A buffer is latched when a thread is reading or modifying the contents of a page. A buffer is also latched when the page is being read from disk or written to disk. A latch is used to maintain physical consistency of the data in the page while it is being read or modified. A lock is used to maintain logical and transactional consistency.

Buffer pool details

   Buffer Counts                  Buffers
   ------------------------------ --------------------
   Committed                      1064
   Target                         17551
   Hashed                         345
   Stolen Potential               121857
   External Reservation           645
   Min Free                       64
   Visible                        17551
   Available Paging File          451997

   (8 row(s) affected)

• Committed: ºÑ¿à¿ÍÃì·Ñé§ËÁ´·ÕèÁÕáÊ´§¤èÒ¹ÕéÊÑ­­Ò. ºÑ¿à¿ÍÃì·Õè¡Ó˹´ãËéÁÕ˹èǤÇÒÁ¨Ó¡ÒÂÀÒ¾·Õèà¡ÕèÂÇ¢éͧ¡Ñºá¿éÁàËÅèÒ¹Ñé¹ ¡Ãкǹ¡ÒÃÊÑ­­Ò¤èÒÁÕ¢¹Ò´»Ñ¨¨ØºÑ¹¢Í§à¡çººÑ¿à¿ÍÃì ¤èÒ¹ÕéÁÕ˹èǤÇÒÁ¨Ó¡ÒÂÀÒ¾·ÕèÁÕ¡ÒûѹÊèǹ¶éÒà»Ô´ãªé§Ò¹¡ÒÃʹѺʹع AWE
• à»éÒËÁÒÂ: ¤èÒ¹ÕéáÊ´§¢¹Ò´¢Í§à»éÒËÁÒ¢ͧ·Õèà¡çººÑ¿à¿ÍÃì ¶éÒ¡ÒÃà»éÒËÁÒ¤èÒÁÕ¢¹Ò´ãË­è¡ÇèÒ¹Ñé¹ÊÑ­­Ò¤èÒ àµÔº¶Ù¡¾ÙºÑ¿à¿ÍÃì ¶éÒ¡ÒÃà»éÒËÁÒ¤èÒ¹éÍ¡ÇèÒÊÑ­­ÒÅ´¤èÒ à¡çººÑ¿à¿ÍÃì·ÕèÁÕ¢¹Ò´
• hashed: ¤èÒ¹ÕéáÊ´§Ë¹éÒ¢éÍÁÙÅáÅÐ˹éҴѪ¹Õ·Õè¨Ñ´à¡çºäÇéã¹·Õèà¡çººÑ¿à¿ÍÃì
• âÍ¡Òʶ١â¨Ã¡ÃÃÁ: ¤èÒ¹ÕéáÊ´§Ë¹éÒÊÙ§ÊØ´·ÕèÊÒÁÒö¶Ù¡â¨Ã¡ÃÃÁ¨Ò¡¾ÙºÑ¿à¿ÍÃì
• ExternalReservation: ¤èÒ¹ÕéáÊ´§Ë¹éÒ·Õè·Õèä´é¶Ù¡ÊÓÃͧÊÓËÃѺ¡ÒÃÊͺ¶ÒÁ·Õè¨Ð·Ó¡ÒôÓà¹Ô¹¡ÒÃàÃÕ§ÅӴѺËÃ×Í¡ÒôÓà¹Ô¹¡ÒÃáΪ ྨàËÅèÒ¹Õéä´éäÁèä´é¶Ù¡â¨Ã¡ÃÃÁ
• ¹Ò·Õ·ÕèÇèÒ§: ¤èÒ¹ÕéáÊ´§Ë¹éÒ·Õè¾ÙºÑ¿à¿ÍÃì¾ÂÒÂÒÁ·Õè¨ÐÁÕÍÂÙ躹ÃÒ¡Ò÷ÕèÇèÒ§
• ÁͧàËç¹ä´é: ¤èÒ¹ÕéáÊ´§ºÑ¿à¿ÍÃì·Õè¨Ð»ÃÒ¡¯¾ÃéÍÁ ºÑ¿à¿ÍÃìàËÅèÒ¹ÕéÊÒÁÒöà¢éÒ¶Ö§â´ÂµÃ§ä´éã¹àÇÅÒà´ÕÂǡѹ ¤èÒ¹ÕéÁÑ¡¨Ðà·èҡѺºÑ¿à¿ÍÃì·ÕèÃÇÁÍÂÙè ÍÂèÒ§äáçµÒÁ àÁ×èÍÁÕ¡ÒÃà»Ô´ãªé¡ÒÃʹѺʹع AWE ¤èÒ¹ÕéÍÒ¨ÁÕ¤èÒ¹éÍ¡ÇèҺѿà¿ÍÃì·ÕèÃÇÁ¡Ñ¹
• á¿éÁྨ¨Ô駷Õè¾ÃéÍÁãªé§Ò¹: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·Õè¾ÃéÍÁãªé§Ò¹ä´é¶Ù¡ÂÍÁÃѺ ¤èÒ¹Õé¶Ù¡áÊ´§à»ç¹¨Ó¹Ç¹¡ÔâÅ亵ì 8 ºÑ¿à¿ÍÃì ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé´Ù·ÕèËÑÇ¢éÍ "GlobalMemoryStatusEx ¿Ñ§¡ìªÑ¹" ã¹àÍ¡ÊÒûÃСͺ Windows API

¢Ñ鹵͹¡ÒÃᤪ

   Procedure Cache                Value
   ------------------------------ -----------
   TotalProcs                     4
   TotalPages                     25
   InUsePages                     0

   (3 row(s) affected)

• TotalProcs: ¤èÒ¹ÕéáÊ´§Çѵ¶Øà¡çºäÇéªÑèǤÃÒÇ·Ñé§ËÁ´·Õè»Ñ¨¨ØºÑ¹ÍÂÙèã¹á¤ª¡Ãкǹ¡Òà ¤èÒ¹Õé¨ÐµÃ§¡ÑºÃÒ¡ÒúѭªÕã¹¹Ñé¹sys.dm_exec_cached_plansdmv
  
  ËÁÒÂà˵Ø:à¹×èͧ¨Ò¡¡ÒÃÅѡɳÐẺ䴹ÒÁÔ¡¢Í§ÃÒÂÅÐàÍÕ´¹Õé µÃ§¡Ñ¹ÍÒ¨äÁèä´éá¹è¹Í¹ ¤Ø³ÊÒÁÒöãªé PerfMon à¾×è͵ÃǨÊͺsql Server: ᤪ¢Í§¡ÒÃÇҧἹÇѵ¶ØáÅÐsys.dm_exec_cached_plansDMV ÊÓËÃѺÃÒÂÅÐàÍÕ´à¡ÕèÂǡѺª¹Ô´¢Í§Çѵ¶Ø·Õèà¡çºäÇéªÑèǤÃÒÇ àªè¹·ÃÔ¡à¡ÍÃì ¡Ãкǹ¡Òà áÅÐÇѵ¶ØªÑèǤÃÒÇ
• TotalPages: ¤èÒ¹ÕéáÊ´§Ë¹éÒÊÐÊÁ·Õè¤Ø³µéͧÁÕà¾×èÍà¡çºÍͺà¨ç¡µì·Õèà¡çºäÇéªÑèǤÃÒÇ·Ñé§ËÁ´ã¹¢Ñ鹵͹¡ÒÃᤪ
• InUsePages: ¤èÒ¹ÕéáÊ´§Ë¹éÒ¡ÃдÒÉã¹á¤ª¡Ãкǹ§Ò¹·Õèàª×èÍÁ⧡Ѻ¡Ãкǹ¡Ò÷Õè¡ÓÅѧàÃÕ¡ãªé§Ò¹ ྨàËÅèÒ¹ÕéäÁèÊÒÁÒö¶Ù¡ÅзÔé§

˹èǤÇÒÁ¨Ó·ÕèÊèǹ¡ÅÒ§Çѵ¶Ø

   Global Memory Objects          Buffers
   ------------------------------ --------------------
   Resource                       126
   Locks                          85
   XDES                           10
   SETLS                          2
   SE Dataset Allocators          4
   SubpDesc Allocators            2
   SE SchemaManager               44
   SQLCache                       41
   Replication                    2
   ServerGlobal                   25
   XP Global                      2
   SortTables                     2

   (12 row(s) affected)

• ·ÃѾÂÒ¡Ã: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÃѾÂÒ¡ÃÇѵ¶Ø·Õèãªé·Ñé§ËÁ´ ¡Ãкǹ¡Ò÷ÃѾÂÒ¡ÃÇѵ¶Ø·Õèãªé§Ò¹ â´ÂàÍ繨Թ¡ÒÃà¡çº¢éÍÁÙÅ áÅÐâ¤Ã§ÊÃéÒ§·Ñé§à«ÔÃì¿àÇÍÃìµèÒ§ æ
• ¡ÒÃÅçÍ¡: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèãªéµÑǨѴ¡ÒÃ'ÅçÍ¡'
• xdes: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèãªéµÑǨѴ¡Ò÷Ãҹ᫤ªÑ¹
• setls: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·Õèãªé㹡ÒèѴÊÃÃâ¤Ã§ÊÃéÒ§µèÍà¸Ã´·Õèà¡çºâ»Ãá¡ÃÁ੾ÒзÕèãªéà¡çºÀÒÂ㹢ͧà¸Ã´
• ªØ´¢éÍÁÙÅ SE Allocators: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·Õèãªé㹡ÒèѴÊÃÃâ¤Ã§ÊÃéÒ§ÊÓËÃѺ¡ÒÃà¢éÒ¶Ö§µÒÃÒ§·Õè¼èÒ¹ ¡ÒÃÇÔ¸Õ¡ÒÃà¢éÒ¶Ö§¡ÒõÑ駤èÒ
• Allocators SubpDesc: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèãªéÊÓËÃѺ¡ÒèѴ¡Òà subprocesses ÊÓËÃѺẺÊͺ¶ÒÁẺ¢¹Ò¹ ¡ÒôÓà¹Ô¹¡ÒÃÊÓÃͧ¢éÍÁÙÅ ¡ÒôÓà¹Ô¹¡Òä׹¤èÒ ¡ÒôÓà¹Ô¹¡Òðҹ¢éÍÁÙÅ ¡ÒôÓà¹Ô¹¡ÒÃá¿éÁ ÁÔàÃÍÃì áÅÐà¤ÍÃìà«ÍÃìẺÍЫԧâ¤Ã¹ÑÊ subprocesses àËÅèÒ¹Õé¨ÐàÃÕ¡ÇèÒ¡Ãкǹ¡ÒÃẺ¢¹Ò¹.
• SchemaManager SE: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèµÑǨѴ¡ÒÃẺἹãªé㹡ÒÃà¡çº¢éÍÁÙÅàÁµÒ¢Í§·Õèà¡çºâ»Ãá¡ÃÁ੾ÒÐ
• SQLCache: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·Õè¶Ù¡ãªéà¾×èÍà¡çº¢éͤÇÒÁ ¢Í§¡ÒÃÃÒ§ҹ੾ÒСԨ áÅÐ ¢Í§¤ÓªÕéᨧ¾ÃéÍÁ
• ¡ÒäѴÅÍ¡: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·Õèà«ÔÃì¿àÇÍÃì·ÕèãªéÊÓËÃѺ¡ÒèÓÅͧẺÀÒÂã¹ subsystems
• ServerGlobal: ¤èÒ¹ÕéáÊ´§Çѵ¶Øã¹Ë¹èǤÇÒÁ¨Óà«ÔÃì¿àÇÍÃìÊèǹ¡ÅÒ§·Õèãªéà»ç¹ generically subsystems ËÅÒÂ
• xp ÊÒ¡Å: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó¢ÂÒ¡ÒÃãªé¡Ãкǹ§Ò¹·Õèà¡çºäÇé
• àÃÕ§ÅӴѺµÒÃÒ§: ¤èÒ¹ÕéáÊ´§Ë¹èǤÇÒÁ¨Ó·ÕèàÃÕ§ÅӴѺµÒÃÒ§·Õèãªé

ẺÊͺ¶ÒÁÇѵ¶Ø˹èǤÇÒÁ¨Ó

   Query Memory Objects           Value
   ------------------------------ -----------
   Grants                         0
   Waiting                        0
   Available (Buffers)            14820
   Maximum (Buffers)              14820
   Limit                          10880
   Next Request                   0
   Waiting For                    0
   Cost                           0
   Timeout                        0
   Wait Time                      0
   Last Target                    11520

   (11 row(s) affected)

   Small Query Memory Objects     Value       
   ------------------------------ ----------- 
   Grants                         0
   Waiting                        0
   Available (Buffers)            640
   Maximum (Buffers)              640
   Limit                          640

   (5 row(s) affected)

• ͹ØÁѵÔ: This value shows the running queries that have memory grants.
• Waiting: This value shows the queries that are waiting to obtain memory grants.
• Available: This value shows the buffers that are available to queries for use as hash workspace and as sort workspace. ¡Ãкǹ¡ÒÃAvailablevalue is updated periodically.
• ¨Ó¹Ç¹ÊÙ§ÊØ´: This value shows the total buffers that can be given to all queries for use as workspace.
• ¢Õ´¨Ó¡Ñ´: This value shows the query execution target for the big query queue. This value differs from theMaximum (Buffers)value because theMaximum (Buffers)value is not updated until there is change in the queue.
• Next Request: This value shows the memory request size, in buffers, for the next waiting query.
• Waiting For: This value shows the amount of memory that must be available to run the query to which theNext Requestvalue refers. ¡Ãкǹ¡ÒÃWaiting Forvalue is theNext Requestvalue multiplied by a headroom factor. This value effectively guarantees that a specific amount of memory will be available when the next waiting query is run.
• Cost: This value shows the cost of the next waiting query.
• Timeout: This value shows the time-out, in seconds, for the next waiting query.
• Wait Time: This value shows the elapsed time, in milliseconds, since the next waiting query was put in the queue.
• Last Target: This value shows the overall memory limit for query execution. This value is the combined limit for both the big query queue and the small query queue.

¡ÅѺ仴éÒ¹º¹ | ãËé¢éÍàʹÍá¹Ð