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:
1> 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 heap memory per user 4096 0 4096 4096 bytes 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 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 An additional 189006 K bytes of memory is available for reconfiguration. This is the difference between 'max memory' and 'total logical memory'.
