sqlskills-logo-2015-white.png

Survey: how much server memory is being wasted? (code to run)

In this survey I'd like you to run some code and then send me the results (and I'm sure someone will put together a PowerShell script to make it easy to run on multiple instances).

I want to know how much of your precious server memory is being wasted storing empty space on data file pages. I'm sure you'll be interested in it too!

Here's the code (works on 2005+):

SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
FROM sys.dm_os_buffer_descriptors;
GO

[Edit: forgot that many systems have way more memory than my 16GB laptop, so updated code above uses BIGINT now.

And here's a PowerShell script courtesy of Nic Cain, assuming server list in c:\temp\serverlist.txt:

$SQLQuery = 'SELECT
COUNT (*) * 8 / 1024 AS MBUsed,
SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
FROM sys.dm_os_buffer_descriptors;
GO'

$servers = get-content c:\temp\serverlist.txt
foreach ($srv in $servers)
{
invoke-sqlcmd -Server $srv -Database master -Query $SQLQuery
}

/Edit]

Send me the results for as many systems as you can, preferably production systems – either as a comment on this post, in email, or in a spreadsheet in email.

I'll collect all the results and explain what this means, how to drill in further, and what you can do about it in a week or two.

Thanks!

109 thoughts on “Survey: how much server memory is being wasted? (code to run)”

  3. Here’s what I get :):

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

    Reply

  4. I assume getting "Arithmetic Overflow" is bad? :)
    (changed the free_space_in_bytes to (convert(bigint,free_space_in_bytes))

    Here’s from several OLTP servers
    MBUsed MBEmpty
    28007 4187
    21153 8366
    19446 3703
    26287 8238
    23837 3768
    5084 1100
    21325 3694
    19552 11382

    Reply

  5. SQL Server 2008 R2 with 32GB of memory

    Original query:
    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

    Modified query
    SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    SUM (CAST(free_space_in_bytes as bigint)) / (1024 * 1024) AS MBEmpty
    FROM sys.dm_os_buffer_descriptors;

    MBUsed MBEmpty
    23846 3421

    Reply

  13. Environment 1:
    MBUsed MBEmpty
    5443 1997

    Environment 2:
    MBUsed MBEmpty
    1892 182

    Environment 3:
    MBUsed MBEmpty
    2926 234

    Reply

  19. Server Name MBUsed MBEmpty
    Dev_2005_1 594 337
    Dev_2005_2 483 306
    Dev_2005_3 2933 765
    Dev_2005_4 620 295
    Dev_2005_5 10976 1870
    Dev_2008_1 78 23
    Dev_2008_2 237 52
    Dev_2008_3 5022 1504
    Dev_2008_4 7208 1837
    Dev_2008_5 4254 123
    Prod_2005_1 974 261
    Prod_2005_2 23 11
    Prod_2005_3 416 81
    Prod_2005_4 92 41
    Prod_2005_5 593 356
    Prod_2005_6 722 177
    Prod_2005_7 639 81
    Prod_2005_8 2079 440
    Prod_2005_9 604 207
    Prod_2005_10 202 118
    Prod_2005_11 2464 460
    Prod_2005_12 810 162
    Prod_2005_13 8962 1402
    Prod_2008_1 237 42
    Prod_2008_2 271 101
    Prod_2008_3 46 21
    Prod_2008_4 568 166
    Prod_2008_5 689 144
    Prod_2008_6 1669 1330
    Prod_2008_7 3190 424
    Prod_2008_8 7068 1324
    Prod_2008_9 17280 1543

    Reply

  29. Busy OLTP Production System
    5320 Used
    424 Empty

    Production Sharepoint DB
    8452 Used
    1174 Empty

    Production Intranet APP DB Server
    8621 Used
    1068 Empty

    Reply

  30. SQL_Server_Version MBUsed MBEmpty
    SQL2005 Enterprise SP3 64-bit 18212 1578
    SQL2005 Enterprise SP2 64-bit 1355 108
    SQL2005 Enterprise SP3 64-bit 8571 1320
    SQL2005 Enterprise SP3 64-bit 2915 1428
    SQL2008R2 Standard 64-bit 4478 382
    SQL2005 Enterprise SP3 32-bit 610 34
    SQL2008R2 Standard 64-bit 4202 360
    SQL2008R2 Standard 64-bit 2884 368
    SQL2005 Standard SP3 64-bit 1683 1085
    SQL2005 Enterprise SP3 32-bit 5244 1230

    Reply

  31. Here’s a cross-section from servers from different walks of life:

    MBUsed MBEmpty
    3232 804
    1124 569
    5427 1497
    14814 5907
    47094 1970
    1581 330
    7370 950
    10210 4065
    30472 5193

    Both OLTP and OLAP servers are represented, no points for guessing which is which.

    Of course, now I want to know what I can do about all those gigabytes I didn’t know we’re wasting!

    Reply

  38. Rewrote as:

    SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    sum(cast(free_space_in_bytes as decimal)) / (1024 * 1024) AS MBEmpty
    FROM sys.dm_os_buffer_descriptors;
    GO

    Some values:

    Server Name MBUsed MBEmpty
    1 147 54.978977
    2 703 90.453300
    3 24250 2872.929636

    Reply

  39. Prod: 26296 7357

    Demo: 8831 828
    DEV2 6782 193
    DEV3 9770 223
    DEV4 12759 5418
    DEV5 1339 109
    QA 6301 153

    Reply

  41. MBUsed MBEmpty
    73 22
    40 15
    38 15
    73 21
    73 22
    71 27
    348 114
    291 51
    621 93
    320 81
    227 39
    66 24
    879 85
    1258 77
    168 60
    331 114
    544 53
    1142 194
    693 176
    1360 80
    877 79
    1282 89
    1001 118
    2601 625
    3855 922
    2446 382
    7052 1254
    1232 67
    8647 823
    7096 902
    3860 509
    8560 1354
    10617 1402
    10657 2573
    14334 2347
    16255 2088
    10094 6092
    10494 4725
    6801 1508
    17268 1630
    17894 2449
    17443 2272
    18271 1327
    19598 944
    20921 1511
    23054 1089
    20726 2658
    41680 2688
    40734 9049

    Reply

  47. MBUsed MBEmpty
    23398 5287

    MBUsed MBEmpty
    1231 329

    MBUsed MBEmpty
    5245 868

    MBUsed MBEmpty
    10740 2273

    MBUsed MBEmpty
    4200 1233

    MBUsed MBEmpty
    806 211

    Reply

  48. Paul,

    I had to make a change because it blew up on some boxes with Arithmetic overflow error converting expression to data type int.

    Below is the change

    SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    SUM(CONVERT(BIGINT,free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
    FROM sys.dm_os_buffer_descriptors;
    GO

    Reply

  49. Paul , BTW I needed to use BIGINT for mine – sending you results
    SELECT
    COUNT (*) * 8 / 1024 AS MBUsed ,
    SUM (convert(bigint,free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
    FROM sys.dm_os_buffer_descriptors;
    GO

    Reply

  52. Quick powershell to grab the results for multiple servers (assumes a list of servers in c:tempserverlist.txt)

    $SQLQuery = ‘SELECT
    COUNT (*) * 8 / 1024 AS MBUsed,
    SUM (CONVERT (BIGINT, free_space_in_bytes)) / (1024 * 1024) AS MBEmpty
    FROM sys.dm_os_buffer_descriptors;
    GO’

    $servers = get-content c:tempserverlist.txt
    foreach ($srv in $servers)
    {
    invoke-sqlcmd -Server $srv -Database master -Query $SQLQuery
    }

    Reply

  53. MBUsed MBEmpty
    —— ——-
    719 139
    287 21
    2345 286
    300 98
    1467 972
    8000 2734
    317 66
    1251 156
    1806 58
    72 21
    5109 201
    9608 4464
    693 51
    539 35
    10720 2653
    24090 919
    569 126
    10733 3633
    21523 6961
    6391 73
    5238 2320

    Reply

  58. WssPosStorage2.uhi.amerco 4473 979
    WssPosStorage3.uhi.amerco 4441 988
    WssPosStorage4.uhi.amerco 4315 994
    WssPosStorage1.uhi.amerco 4475 960
    WssCommon.uhi.amerco 5607 1807
    WssHistorical.uhi.amerco 16354 4267

    Reply

  59. All prod instances:
    Host MBUsed MBEmpty
    1 588 175
    2 274 116
    3 552 475
    4 118 65
    5 661 128
    6 1764 229
    7 53 18
    8 5283 2230
    9 1033 237
    10 622 51
    11 3138 383
    12 20135 3868
    13 7983 1936
    14 11704 6183
    15 10108 5485
    16 23258 2385
    17 6852 1401
    18 14916 2656
    19 15978 1449
    20 100830 11531
    21 103891 27513
    22 109138 18183
    23 109837 3467
    24 112721 13155

    Reply

  65. Hi Paul,

    Here is a snapshot of current wasted space on my prod environment. I’m curious of the avg waste ratio.

    MBUsed MBEmpty
    8575 1021
    16176 3680
    13646 5048
    18256 4650
    19139 5000
    15099 4293
    20598 6359
    20519 6373
    20070 6181
    20099 6226
    55978 17186
    20547 6338
    20141 6227
    84430 21028
    109593 28194
    38782 8310
    36211 12144

    Rgds,

    Vincent.

    Reply

  66. MBUsed MBEmpty
    387 43
    29 12
    1317 353
    675 148
    4368 196
    3511 331
    8606 3765 (Replication – Subscriber)
    685 58

    Reply

  67. Server MBUsed MBEmpty
    server1 438 58
    server2 1522 253
    server3 3481 434
    server4 3824 157
    server5 10659 2284
    server6 11601 3135
    server7 13290 3109

    Reply

  76. Prod Instances
    Used Empty
    111267 30389
    23994 4159
    23852 5267
    14302 1702
    11958 1877
    2994 253
    1001 133
    669 88
    592 131
    252 80

    Looking forward to seeing what to do about it.

    Reply

  81. Name:Used:Empty
    Prod1:5239:1764
    Prod2:3082:602
    Dev1:3699:732
    Rep1:4080:2670
    Bak1:4095:3846
    Tst1:2847:2660

    Reply

  83. MBUsed | MBEmpty
    3 | 1
    9 | 2
    12 | 3
    38 | 16
    46 | 16
    49 | 15
    50 | 18
    52 | 21
    55 | 21
    59 | 20
    60 | 24
    66 | 10
    67 | 25
    225 | 74
    254 | 70
    383 | 81
    488 | 104
    512 | 58
    514 | 66
    602 | 274
    635 | 89
    674 | 133
    778 | 81
    834 | 85
    870 | 162
    888 | 91
    960 | 144
    1302 | 52
    1490 | 260
    1558 | 64
    1667 | 292
    2405 | 2188
    2424 | 343
    2504 | 615
    2789 | 137
    5039 | 822
    5184 | 876
    5317 | 327
    5363 | 1628
    38157 | 5525
    56141 | 6550
    67323 | 17222

    Reply

  85. Our production servers (2005, 2008, 2008 R2):
    MBUsed MBEmpty
    219 46
    20 7
    61 18
    429 60
    563 291
    234 53
    617 185
    293 92
    683 33
    401 92
    350 130
    619 237
    2357 418
    985 638
    1569 225
    5776 1391
    564 38
    6325 312
    2870 283
    3861 253
    1390 75
    4917 1065
    124 12
    5067 1155
    5817 882
    6023 610
    2463 333
    1940 357
    20809 7663
    23834 924
    25388 4831
    23657 6663
    23718 7719
    482 175
    270 102
    10050 2340
    16012 1629
    10553 3067
    50990 3079

    Reply

  95. MbUsed MbEmpty
    455 63
    1088 210
    7196 1434
    182 31
    330 40
    518 108
    140 22
    925 151
    1698 273
    2105 363
    74 13
    1265 147
    896 170
    679 221
    420 46
    921 490
    298 113
    112 33
    816 228
    137 16
    2192 88
    2 0
    321 70
    701 20
    607 114
    2663 238
    1609 252
    2602 505
    6861 682
    128 30
    3257 658
    394 77
    298 80
    2450 456
    2057 245

    Reply

  96. Production ERP DB and DataWarehouse
    MBUsed MBEmpty
    17411 1292
    MBUsed MBEmpty
    8055 529
    Testing, development
    MBUsed MBEmpty
    1784 234

    Reply

  97. Used Empty
    132 30
    1580 238
    2449 113
    4393 218
    24703 2668
    17589 1277
    21072 2107
    14536 1496
    17647 4352
    3774 1331
    30406 2719

    Reply

  101. Hyper-V
    DEV 527 | 118 [2005/32bit]
    DEV 527 | 118 [2005/64bit]
    DEV 913 | 53 [2008/64bit]
    DEV 5 | 2 [2008R2/64bit]

    Physical
    PROD 7801 | 1417 [2005/32bit]
    PROD 643 | 82 [2005/64bit]
    PROD 4608 | 311 [2008/64bit

    Reply

  102. Physical
    Production 2005 SP3 32bit: 25325 MBUsed | 2129 MBEmpty
    Production 2005 SP3 32bit: 5879 MBUsed | 412 MBEmpty

    VMWare
    Production 2005 SP3 32bit: 1496 MBUsed | 110 MBEmpty

    Reply

  103. I’m not sure what the numbers mean, but the fact that servers D and A should in theory be processing almost the same amount of data, with the same pattern, makes me wonder why A uses 4x as much memory. The primary difference is with merge replication, D is 90% publisher and A is 90% subscriber, but only replicating on about 1GB of data. In total they store several hundred GB.

    Looking forward to your analysis.

    Server Used Empty Replication
    —–
    (System mostly idle or under load – similar values)
    D 4086 817 Publisher
    A 17165 3969 Subscriber

    Reply

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.

Collaborate With Us
Choose a Course