How can we improve Azure SQL Database?

Support Rebuilding Indexes Online for Tables with Large Objects

Following the guidance from

http://blogs.msdn.com/b/cbiyikoglu/archive/2010/04/03/handling-error-40522-while-creating-dropping-and-rebuilding-large-indexes-in-sql-azure.aspx

I attempted to rebuild a heavily fragmented index using Online=ON:

ALTER INDEX ALL ON [Backups] REBUILD WITH(ONLINE = ON)

And received the following error:

'ONLINE INDEX DDL WITH LARGE OBJECT' is not supported in this version of SQL Server.

My table contains a varbinary(max) field. This is a serious flaw. Please fix.

201 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    DPedenDPeden shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    anonymousanonymous shared a merged idea: Enable sys.dm_db_index_physical_stats() so we can index fragmentation levels.  ·   · 

    17 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Ewart MacLucasEwart MacLucas commented  ·   ·  Flag as inappropriate

        Hi Guy, we just migrated our database to Azure and had a tight timeframe to load it. It's loaded now but one unexpected thing was a key table is highly fragmented in it's clustered index - odd in itself as we figured a load would be fresh with no fragmentation.. now we need to schedule an outage we'd dearly like to avoid, to rebuild the table.

      • Shane MiltonShane Milton commented  ·   ·  Flag as inappropriate

        When addressing these problems, please also consider making it easier to manipulate cached execution plans and other maintenance issues that come along with index maintenance!

      • Shannon LowderShannon Lowder commented  ·   ·  Flag as inappropriate

        If you upgrade to premium database services for a very short time, you should be able to complete long running operations. Once complete, you can downgrade back to regular. I'm not sure this is a supported use of premium, but it gets you around the long running query/high resource utilization issue.

      • Anonymous commented  ·   ·  Flag as inappropriate

        This is affecting us as well. Neither offline nor online index builds work for large tables with large objects. There should be some solution to this.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
        would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

      • MikeMike commented  ·   ·  Flag as inappropriate

        Throws this error with nvarchar(4000) also. This is very important because it negatively impacts both performance and billing.

      • Shane MiltonShane Milton commented  ·   ·  Flag as inappropriate

        I agree with the original poster. This is a SERIOUS flaw. I have a table w/ 250M records in it and it has nvarchar(max) as it contains up to ~32k unicode characters (although usually much smaller). There is no known work-around to this as far as I can tell. :-/

      • Saravanan SubburayalSaravanan Subburayal commented  ·   ·  Flag as inappropriate

        currently the following data columns are not supported for REBUILD ONLINE.
        * XML index
        * Spatial index
        * Large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml

        For the time being, get to know the max size of binary info that will accomodate in the varbinary column and provide, for eg., if you are sure that it will not have data more than 1024 b, then change varbinary(max) -> varbinary(1024).

      • AnonymousAnonymous commented  ·   ·  Flag as inappropriate

        With the product we're developing it is difficult to gauge per-tenant and overall database usage. This is required so that we can effectively split tenants across databases.

      Feedback and Knowledge Base