Issuing sp_configure memory displays all of the memory parameters and determines the difference between max memory and total logical memory, which is the amount of memory available for dynamic growth. For example:
sp_configure memory
Msg 17411, Level 16, State 1: Procedure 'sp_configure', Line 187: Configuration option is not unique. Parameter Name Default Memory Used Config Value Run Value Unit Type ------------------------------ ----------- ----------- ------------ ---------- --------------------- ---------- additional network memory 0 0 0 0 bytes dynamic allocate max shared memory 0 0 0 0 switch dynamic compression memory size 0 152 0 0 memory pages(2k) dynamic engine memory log size 0 2 0 0 memory pages(2k) dynamic heap memory per user 4096 0 4096 4096 bytes dynamic kernel resource memory 4096 8344 4096 4096 memory pages(2k) dynamic lock shared memory 0 0 0 0 switch static max memory 33792 300000 150000 150000 memory pages(2k) dynamic memory alignment boundary 16384 0 16384 16384 bytes static memory per worker process 1024 4 1024 1024 bytes dynamic messaging memory 400 0 400 400 memory pages(2k) dynamic pci memory size 32768 0 32768 32768 memory pages(2k) dynamic shared memory starting address 0 0 0 0 not applicable static total logical memory 33792 110994 55497 55497 memory pages(2k) read-only total physical memory 0 97656 0 48828 memory pages(2k) read-only transfer utility memory size 4096 8194 4096 4096 memory pages(2k) dynamic An additional 30786 K bytes of memory is available for reconfiguration. This is the difference between 'max memory' and 'total logical memory'.
