Post reply

Seperate Max fileds from the others to Another FileGroup

  • January 18, 2015 at 2:13 am

    #314706

    Hi

    we have many max fields in our database .

    A role in performance says : It's better to seperate this fields to another FileGroup with separate file.

    Is the result good for performance? and what are the risks?

    thank you

  • January 19, 2015 at 11:57 pm

    #1771299

    is there any Ideas?

  • January 20, 2015 at 5:29 am

    #1771396

    Huh?

    Don't understand the question.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 20, 2015 at 5:35 am

    #1771403

    Do you mean varchar(max) and nvarchar(max) columns? Where did you read that it's best to separate them, and how would you propose to do it?

    John

  • January 21, 2015 at 2:32 am

    #1771719

    Yes I mean Varchar(max) and nvarchar(max)

    I read :

    - If these fields be on one file group along with other fields, Delete or update on these fields can cause much defragment on data.

    because one fields can get more than one page or most of the space of one page, Whereas other fields are very smaller than.

    on the other hand :

    If there are on other file group on other disk , read of them can be parallel and be faster.

    are these true ?

  • January 21, 2015 at 2:43 am

    #1771720

    I suppose it depends on your data. You'd have to put the columns in questions into separate tables, which would require changes to your database schema and your code. For that reason, I'd only do it if I had identified a problem that I thought this could fix. And of course, only after thorough testing.

    What is the source of the advice you quoted, incidentally?

    John

  • January 21, 2015 at 2:44 am

    #1771721

    Maybe, maybe not. No hard answer there. Depends if you're seeing IO contention or not. If not, then splitting them out is a huge amount of work for minimal gain.

    Deletes and updates won't cause fragmentation because larger pieces of LOB data is stored out-of-row.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2015 at 7:14 am

    #1771798

    I often suggest this course of action when shops have issues with online reindexing. We put the varchar(max) cols into their own table and the main table can now be done online.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 21, 2015 at 7:17 am

    #1771801

    Separating the lobs into a different table has good and valid reasons for doing. Separating them onto a separate filegroup (TEXTIMAGE ON ... and recreating the table) however is not necessarily as useful or recommended.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2015 at 8:01 pm

    #1772049

    Thank you GilaMonster

    Can you tell me about the benefits of separate LOBs into another table?

    (That I canconvince our developer team.)

  • January 22, 2015 at 12:33 pm

    #1772367

    When I was scripting a table with a varchar(max) field I noticed it includes:

    TEXTIMAGE_ON [PRIMARY]

    In MSDN for CREATE TABLE https://msdn.microsoft.com/en-us/library/ms174979(v=sql.120).aspx it says:

    TEXTIMAGE_ON { filegroup| "default" }

    Are keywords that indicate that the text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns (including geometry and geography) are stored on the specified filegroup.

    So this would be the answer to "how would you do it?"

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply