Survey: memory configuration

Time for another survey – this time around memory configuration.

Here is some code to run if you're on SQL Server 2008 and 2008 R2 (thanks Jonathan!):

SELECT
    [physical_memory_in_bytes] AS [PhysMemBytes],
    [physical_memory_in_use_kb] AS [PhysMemInUseKB],
    [available_physical_memory_kb] AS [PhysMemAvailKB],
    [locked_page_allocations_kb] AS [LPAllocKB],
    [max_server_memory] AS [MaxSvrMem],
    [min_server_memory] AS [MinSvrMem]
FROM
    sys.dm_os_sys_info
CROSS JOIN
    sys.dm_os_process_memory
CROSS JOIN
    sys.dm_os_sys_memory
CROSS JOIN (
    SELECT
        [value_in_use] AS [max_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'max server memory (MB)') AS c
CROSS JOIN (
    SELECT
        [value_in_use] AS [min_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'min server memory (MB)') AS c2

On 2012, the code is (thanks Bob!):

SELECT
    [physical_memory_kb] AS [PhysMemKB],
    [physical_memory_in_use] AS [PhysMemInUseKB],
    [available_physical_memory_kb] AS [PhysMemAvailKB],
    [locked_page_allocations_kb] AS [LPAllocKB],
    [max_server_memory] AS [MaxSvrMem],
    [min_server_memory] AS [MinSvrMem]
FROM
    sys.dm_os_sys_info
CROSS JOIN
    sys.dm_os_process_memory
CROSS JOIN
    sys.dm_os_sys_memory
CROSS JOIN (
    SELECT
        [value_in_use] AS [max_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'max server memory (MB)') AS c
CROSS JOIN (
    SELECT
        [value_in_use] AS [min_server_memory]
    FROM
        sys.configurations
    WHERE
        [name] = 'min server memory (MB)') AS c2

And on 2005/2000, if you want to participate, send me the amount of memory on the Windows server, plus the min and max server memory configuration for SQL Server. There's some PowerShell in one of the early comments that will help (thanks Eric!).

You can either send me email with the info or leave a comment on this post. I'll editorialize the results in a couple of weeks.

Thanks!

74 thoughts on “Survey: memory configuration

  1. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    68708712448 63586364 745056 61439424 61440 24576

  2. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    3212021760 379304 580100 357456 2147483647 16

  3. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    25759408128 20567052 3012500 19973504 19000 19000
    25759408128 20672156 2769612 19975808 19000 19000
    25759408128 20580024 2727692 19971264 19000 19000
    25759408128 20613144 2460440 19970176 19000 19000
    25759408128 20379216 3712044 19950848 19000 19000
    25760223232 20416900 3373632 20029248 19000 19000

  4. Results for three of the servers that I manage.

    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    4284706816 3459004 225556 0 3072 0
    8547024896 1484248 1227428 0 2147483647 16
    6433202176 234552 2632348 0 3072 0

  5. Example of one of my servers, it’s a failover cluster with 2 instances :

    Instance 1
    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    103064715264 43441624 52381152 42609152 40960 0

  6. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    34354425856 31784948 435716 31021264 30720 28672

  7. Server Name PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    DBS03\INS10 77299961856 5137236 35572772 4562944 16384 8192
    DBS03\INS11 77299961856 17677444 35572772 17180032 32768 16384
    DBS03\INS09 77299961856 15015612 35572772 14434304 32768 16384

  8. Some results:

    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    34358390784 26114712 5320096 25798400 24800 16000

    21473652736 8633092 11075544 8396800 8192 8192

    154552393728 138133944 6877084 136570720 2147483647 16

    4294500352 1408884 1300820 0 2147483647 16

    4294664192 74412 3601320 0 2147483647 8

  9. 6436184064 5491160 209436 0 5300 0
    8451117056 6475008 393936 0 6000 0
    8584306688 1759180 4858204 0 2147483647 0
    8451117056 6402860 214696 0 2147483647 0
    8451117056 967792 214444 0 2147483647 16

  10. A little powershell that will get numbers for 2000 / 2005 boxes.

    (gi SQLSERVER:\SQL\SERVERNAME\DEFAULT) | #Default Instance
    ft @{Name=’PhysMemBytes’;Expression={$_.PhysicalMemory * 1MB}},
    @{Name=’MaxSvrMem’;Expression={$_.Configuration.MinServerMemory.ConfigValue}},
    @{Name=’MinSvrMem’;Expression={$_.Configuration.MaxServerMemory.ConfigValue}} -auto

    (gi SQLSERVER:\SQL\SERVERNAME\INSTANCENAME) | #NamedInstance
    ft @{Name=’PhysMemBytes’;Expression={$_.PhysicalMemory * 1MB}},
    @{Name=’MaxSvrMem’;Expression={$_.Configuration.MinServerMemory.ConfigValue}},
    @{Name=’MinSvrMem’;Expression={$_.Configuration.MaxServerMemory.ConfigValue}} -auto

  11. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    77298716672 3088852 67548100 0 55287 0

  12. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    17173602304 8809504 5171640 8531264 2147483647 0

  13. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    ——————– ——————– ——————– ——————– —————— —————
    549752573952 493592984 32840764 487668064 475000 240000

  14. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    34349002752 30402280 1332752 0 28000 22000

  15. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    51529211904 46996692 1389748 0 44500 0

  16. Um…
    An error occurred while executing batch. Error message is: The SqlDbType enumeration value, 0, is invalid.
    Parameter name: SqlDbType

    I’ll try it again on a different server.

  17. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    17174654976 12851296 2522460 12582912 12288 12288

  18. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    34349076480 31838776 800800 0 2147483647 0
    8583667712 5439408 522100 0 2147483647 0

  19. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    8321032192 6895932 195632 0 2147483647 0

  20. Currently testing this, after memory upgrade

    server 1 (TEST) — 16GB physical memory. Max server memory 7168. Min server memory 0

    server 2 — 16GB physical memory. Max server memory 5120. Min server memory 0

  21. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    ————— ————— ————— ———- ———- ———-
    137428291584 128679968 840852 125982328 122880 10240

    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    ————— ————— ————— ———- ———- ———-
    137432039424 92130972 34919376 85933184 81920 2048

  22. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    25759109120 23709672 179952 0 2147483647 16

  23. PhysMemBytes – 17169604608
    PhysMemInUseKB – 15119728
    PhysMemAvailKB – 178532
    LPAllocKB – 0
    MaxSvrMem – 2147483647
    MinSvrMem – 16

  24. PhysMemBytes – 16100913152
    PhysMemInUseKB – 774028
    PhysMemAvailKB – 13857352
    LPAllocKB – 679936
    MaxSvrMem – 2147483647
    MinSvrMem – 0

  25. Dev (VM)
    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    8589467648 6382200 206420 0 2147483647 0

    Test (VM)
    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    21464137728 18036324 340484 0 2147483647 0

    Live (Physical)
    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    21464137728 18344648 277852 0 2147483647 0

  26. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    17174245376 12718388 429448 12483328 12000 16

  27. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    34358820864 31838988 251732 0 2147483647 16
    34350374912 31640300 390180 0 30000 0

  28. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    16910655488 15140268 211576 14895360 15360 12288

    Others have the same averages.

  29. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    51528912896 47252340 474284 0 45056 16
    60126314496 52108808 3243020 0 49152 0

  30. Server 2: 28GB memory (virtual server).
    Instance 1 – min server memory 0, max server memory 16GB.
    Instance 2 – min server memory 0, max server memory 5GB.
    Instance 3 – min server memory 0, max server memory 3GB.

    Available memory: stable at 1.4GB.

  31. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    51527774208 46612852 1120328 45779968 44000 16

  32. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    68708696064 59704688 4286756 59050560 56309 0

  33. We tend to simply subtract 2 Gb from total server memory, but not always…

    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    34359271424 30475696 551908 29958656 28672 0

  34. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    137435717632 117272136 11438952 115993920 110592 110592
    274874671104 222446468 39360516 219935424 209716 209716

  35. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    77298716672 69202468 3150672 68182528 65536 0

  36. PhysMemBytes, PhysMemInUseKB, PhysMemAvailKB, LPAllocKB, MaxSvrMem, MinSvrMem
    51526066176, 25715868, 20686708, 0, 22528, 0

  37. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    206144622592 182795520 7786384 181225472 172800 96000

  38. 2008:
    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    8579239936 3687724 1364008 0 6000 0
    17177415680 14146124 1119240 13848064 13312 0
    12817035264 8803868 2351980 0 10240 0
    6374883328 2956660 1927176 0 2600 16
    34356465664 9423360 20132116 0 24000 0
    21472681984 17058648 2178432 0 16000 0
    12874207232 7378884 1086008 0 6500 0

    2008R2:
    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    25759109120 231652 22341440 0 19000 0
    25759109120 21135880 2589148 0 20000 0
    25759109120 5910576 16290512 0 19000 0
    25759109120 3459428 19349776 0 18000 0
    34357583872 28066740 3787056 0 26000 0

  39. got error running in sql 2012
    [physical_memory_in_use] AS [PhysMemInUseKB],

    There is no [physical_memory_in_use] but there is [physical_memory_in_use_kb] however.

    PhysMemKB PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    134204244 128973476 1764360 127526948 126976 65536

    PhysMemKB PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    100652828 96054396 1793556 94938540 94208 49152

  40. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    68705935360 44041628 16757568 42979648 40960 0

  41. SQL2005

    From sp_configure:
    max server memory (MB) 16 2147483647 6281 6281
    Server is a 2 node cluster on machines with 8Gb

    From DBCC MEMORYSTATUS, first section "Memory Manager"
    VM Reserved 8601336
    VM Committed 6728032

  42. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    51526066176 42598468 3966328 41963840 40000 20000
    51526066176 42515916 4726832 41962496 40000 20000
    51526066176 45360012 597928 44881408 43000 21000

  43. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem
    ——————– ——————– ——————– ——————– ———–
    34346196992 31890072 190184 31099520 2147483647

  44. My list includes development, test and production machines. Still, someone will be addressing consistency.

    PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    8588025856 3214756 4205764 3109888 3037 1126
    17177956352 8295868 5074700 8020672 13312 2048
    17169174528 4163068 10179556 0 2147483647 1024
    8586362880 1691216 5422144 0 2147483647 0
    3757539328 2616572 175504 2546440 2599 1015
    34356166656 9864380 21262384 0 9216 2048
    8589467648 6710356 577004 0 6144 1024
    3757539328 2063564 611144 1900544 2147483647 8
    8588025856 4498548 1546820 0 4096 2048
    8588009472 1726916 5164184 0 6144 2048
    21471563776 1616048 16015504 1312192 9728 0
    21471563776 10615400 7075476 10209344 9728 0
    38644011008 4543932 29860160 0 4096 2048
    17177415680 11017308 2575192 10747520 10240 4096
    34357817344 568828 17713604 312512 12000 2000
    34356166656 30281720 615248 29826304 28672 2048
    8589467648 626792 5815004 0 2147483647 2048
    12884434944 9862276 1013208 0 9216 2048
    34356166656 524664 30531276 180800 30720 2048
    38644011008 4475956 24817704 0 4096 2048
    12884434944 9575876 1571984 0 9216 2048
    8588013568 5771064 1018948 5570560 6144 1000
    4294144000 1726392 299604 1656504 2048 8
    17177714688 10999584 4311468 10690560 14336 2048
    4294500352 539088 704044 485968 3072 0
    8588013568 5761764 1325608 5570560 6144 2048
    38644011008 34844320 660692 34419524 32768 4096
    38644011008 36028180 406700 0 2147483647 0
    4294144000 689440 2478352 0 2147483647 8
    12882993152 10857368 1191992 10682368 2147483647 8
    10669551616 3120624 197592 3017736 5120 2947
    8587792384 2003308 3248168 0 2147483647 16
    17177415680 5723828 5883580 5527240 2147483647 16
    4294144000 2085200 945740 0 2147483647 8
    12874207232 11627192 292356 11405388 2147483647 0
    17179402240 324180 15078444 0 2147483647 16
    4294144000 1998704 1398248 0 2147483647 0
    8589467648 7189320 324416 7091968 2147483647 16
    4294500352 2273956 649700 2099968 2000 1280
    8589467648 3170272 3607248 2975488 2147483647 16
    17176596480 8855028 2993572 0 8192 2048
    8589467648 1014880 6005108 828352 2147483647 16
    17176596480 8895764 2037848 0 8192 2048
    8589467648 1159280 4293836 984384 6000 2000
    8589467648 2972896 3312680 2803328 6000 2000
    17177948160 660696 5029856 515104 3072 0
    17179402240 15140544 535464 14924064 2147483647 16
    4294500352 3238748 450744 0 2147483647 2000000000
    4294500352 1395192 1773852 1246720 2147483647 16
    8589467648 1369144 5564324 1115968 6656 2048
    4294500352 3420296 322184 0 2147483647 2000000000
    8589467648 1762008 4678320 1590592 6000 2000
    3488931840 1768672 690924 0 2147483647 0
    8589467648 2086912 4404968 1885888 2147483647 0
    3488931840 1761988 478756 0 2147483647 0
    17178398720 3057368 2609232 2809856 6144 2048
    4294500352 2593404 767740 2510896 3072 2048
    4294500352 3050416 325736 0 2147483647 16
    17177432064 9286912 3988772 9085688 2147483647 2000
    17179402240 14669132 613404 14460416 14000 2000
    21471563776 7807636 11177560 7521920 8192 2048
    21471563776 2460920 16516868 0 8192 2048
    17178398720 1780716 12950520 0 13312000 2048
    4294500352 1449092 1013116 0 3072 2048
    17179402240 1419124 13136668 1123712 14000 4000
    34358267904 3354472 16738612 3055616 9000 2000
    4294500352 1615720 1628612 0 2147483647 16
    8589467648 7077328 560876 6941960 7168 2048
    68716204032 50913488 2063792 0 2147483647 16
    38644011008 31603456 4385856 0 30000 2048
    17169174528 15410512 382268 0 2147483647 2048
    4294144000 1138584 1753832 0 1024 0
    4294500352 1689628 906904 0 2147483647 0

  45. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    42949140480 39654948 502588 38658816 36864 8192

  46. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    549744099328 452899512 54634784 0 430080 307200

  47. 68718317568 58063052 4370144 0 55000 0
    8321032192 6090268 753336 0 2147483647 0
    4025237504 2052316 206784 0 3096 16
    12615999488 1798832 820740 0 2147483647 0
    8321032192 971380 3042268 0 2147483647 0
    6290989056 2255440 2943664 0 2048 0
    4026064896 253356 175780 194352 2147483647 16
    68717371392 55133060 7542316 53726720 51200 0
    8321032192 7476608 196232 0 2147483647 16

  48. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    25759109120 23285524 287736 0 2147483647 0

  49. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    8588058624 3407296 3047992 0 5632 0

  50. 137427120128 26877132 46890144 25778304 24576 12288
    137427120128 52368128 46890144 51555712 49152 24576
    137427120128 39380568 87298244 38661440 36864 18432
    51538010112 21760776 23179924 21304576 20480 10240
    51538010112 21912836 22902544 21490112 20480 10240
    51538010112 19858388 25899276 19339136 18432 9216
    137426976768 59161788 66280400 57991232 55296 27648
    51538010112 19633032 25285908 19174144 18432 9216
    137426173952 58891088 66921908 57991040 55296 27648
    137427120128 59242076 66055512 58032064 55296 27648
    51538014208 20110128 24900400 0 18432 9216
    137427120128 59314564 66339776 58037248 55296 27648
    51538014208 21822720 23904416 0 20480 10240

  51. PhysMemBytes PhysMemInUseKB PhysMemAvailKB LPAllocKB MaxSvrMem MinSvrMem
    274811375616 78682004 176773064 0 200000 200000
    268416492 205277020 53343424 0 200000 200000

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.