Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Page Compression for Out-of-Row LOB Storage

Disappointingly page compression does not apply to out-of-row LOB storage for data types such as VARCHAR(MAX) and NVARCHAR(MAX) [and also TEXT and NTEXT].

Would you consider adding this in a future release?

4 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 10

    <=-=Mar 8 2011 9:34AM=-=>

    Thanks for contacting SQL Server team with the feedback. We will consider this in a future release. At this time, you can compress Out-of-row BLOBs by storing them as FileStream on a compressed volume. But I do understand that this is not your scenario as the data types you mentioned are not suitable for Filestream

    Thanks
    Sunil

    <=-=Mar 8 2011 1:30PM=-=>

    Sunil, thanks for the feedback. I had considered this option, but we’re using database mirroring as a resiliency solution which, unfortunately, doesn’t support Filestream.

    Thanks
    Chris

    <=-=Jan 15 2012 11:47AM=-=>

    Chris, I updated the title of your request. You are not really saying PAGE compression for out of row or LOB data. What you are saying is that we need the ability to compress this data as well. Also, I added a variance of UNICODE compresion as well.

    thanks
    Sunil

    <=-=Dec 1 2014 9:06PM=-=>

    Chris, Sorry for the delayed answer. We support FS with AlwaysOn so at least partially your issue can be handled. We still don’t have support for in-row MAX data types. For now, I am closing this connect as we have not addressed this in two releases. Please create a new connect request if you feel strongly about it

    thanks
    Sunil

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Solomon Rutzky commented  ·   ·  Flag as inappropriate

        This absolutely needs to happen. Please, please do this. And it should include the VARBINARY(MAX) and XML datatypes as well. I assume that doing this for one LOB type would extend to all LOB types, but just in case it doesn't I figured I would add those to the request.

        Please note that in-row MAX data _can_ be PAGE compressed, but only if it is under a certain size. I tested with VARCHAR(MAX) and saw that 6000 character/byte rows would not compress, but 4000 character/byte rows did.

        On a related note, please see "Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?" ( https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/ ) for a detailed analysis of the new UTF-8 feature.

      • Jeff Moden commented  ·   ·  Flag as inappropriate

        I second the problem. We are frequently required to delete blob data by setting it to NULL in a mostly vain attempt to recover disk space. LOB_COMPACTION simply does not work for out of row storage and it really needs to. It was quite the surprise, as well, because all of the documentation suggests that it will.

        Referring to the final message above by Sunil, do we really need to open a new item? Can't you just take this one to the bank?

      Feedback and Knowledge Base