Post reply

Separate UserObejts's FileGroup And SystemObjects's FileGroup

  • November 11, 2015 at 6:31 am

    #305003

    Hi professionals

    I want to Separate User Object File Group from System Objects File Group

    1- Is this effective for IO Performance ?

    2- I want to do this by Create Index .... . With This command Tables And Indexes will Transfer.

    And what about another objects : stored Procedures and .. ? How Can I transfer Them?

    Thank you

  • November 11, 2015 at 6:35 am

    #1839085

    MotivateMan1394 (11/11/2015)

    Hi professionals

    I want to Separate User Object File Group from System Objects File Group

    1- Is this effective for IO Performance ?

    No.

    And what about another objects : stored Procedures and .. ? How Can I transfer Them?

    Procedures, views, functions and other modules are stored in the system tables, and system tables are always on PRIMARY

    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

  • November 11, 2015 at 6:47 am

    #1839086

    Thank you for this Decisive answer.

    I will forget better performance

    , is there any another advanteges for this separation ?

  • November 13, 2015 at 6:45 pm

    #1839772

    is there any another advanteges for this separation ?

  • November 13, 2015 at 7:41 pm

    #1839775

    With the understanding that you'll sacrifice a bit of performance, there are many advantages to splitting things up in separate file groups using a thing called "partitioning". It also comes with a set of caveats whether you use a Partitioned View or a Partitioned Table.

    The advantages depend largely on the type of table you apply it to but it can greatly reduce the amount of time you spend indexing large tables, greatly reduce the time for backups if it's for something like an audit table or a closed invoice table, and can also allow for Online Piecemeal restores if done correctly. And, if done correctly, it's a bit (a lot, actually) of a pain to implement and, like I said, will reduce performance a bit. Partitioning should be done only if you think a table will grow to a size where index maintenance, backups, or "get back in business quickly" restores become a real duration problem. Given the right kind of temporally based table, it can also reduce archival of old data from minutes/hours to sub-second times... again, only if you did the partitioning correctly.

    Just to backup what Gail said, it won't help performance of code. Only good code and proper indexing can do that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 14, 2015 at 1:36 am

    #1839793

    MotivateMan1394 (11/13/2015)

    is there any another advanteges for this separation ?

    If you just intend to split system objects from user objects and nothing else, then no. If you're using it as the start of more splitting, then maybe, providing you know exactly what you want from splitting filegroups and why you're splitting them. However if you're just going to split stuff up at random with no goal, then you'll get no advantage.

    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

Viewing 6 posts - 1 through 5 (of 5 total)

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