SQL Server Performance

Tables in Different physical files of the same FG

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Aug 11, 2005.

Page 1 of 2

  1. PAMUR New Member


    This site tells us that to get best performance on a join of four or more tables
    provided all other criteria is met, we have to place the tables in diffirent physical files of the same filegroup.

    How to place tables on same filegroup but different physical files?
    PAMUR, Aug 11, 2005
    #2

  2. dineshasanka Moderator

    quote:Originally posted by PAMUR


    This site tells us that to get best performance on a join of four or more tables

    Can you give us the reference please. I mean the URL which has stated the above
    dineshasanka, Aug 11, 2005
    #2

  3. FrankKalis Moderator

    FrankKalis, Aug 11, 2005
    #2

  4. Luis Martin Moderator

    There is no way to do that.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, Aug 11, 2005
    #2

  5. satya Moderator

    I tone what have been said above and linkshttp://www.sql-server-performance.com/ac_filegroup_performance.asp andhttp://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054945,00.html for your information.

    A filegroup is a property of a SQL Server database and cannot contain the operating system files of more than one database, though a single database can contain more than one filegroup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 11, 2005
    #2

  6. PAMUR New Member

    Hi

    Thanks a lot, I was confuesed by the statement posted on page

    "http://www.sql-server-performance.com/tuning_joins.asp",
    in seventh topic given as folows

    "For very large joins, consider placing the tables to be joined in separate physical files in the same filegroup".

    Thanks again
    PAMUR, Aug 11, 2005
    #2

  7. PAMUR New Member

    I mean in Seventh topic from the bottom of the page
    PAMUR, Aug 11, 2005
    #2

  8. ghemant Moderator

    Hi,
    i feel :

    quote:"For very large joins, consider placing the tables to be joined in separate physical files in the same filegroup. This allows SQL Server to spawn a separate thread for each file being accessed, boosting performance. [6.5, 7.0, 2000] Updated 10-18-2004".

    that means your havily used tables / indexs on seperate physical file in same FG,that means it boost the performance by treating as seperate thread (multi threaded), like the swap files for linux and pagefile.sys for windows.

    point me if i m wrong !?



    hsGoswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
    ghemant, Aug 11, 2005
    #2

  9. PAMUR New Member

    Ok then how do I place tables in physical file of my choice?

    quote:
    --------------------------------------------------------------------------------
    "For very large joins, consider placing the tables to be joined in separate physical files in the same filegroup. This allows SQL Server to spawn a separate thread for each file being accessed, boosting performance. [6.5, 7.0, 2000] Updated 10-18-2004".
    --------------------------------------------------------------------------------


    Please answer. I am confused.
    PAMUR, Aug 11, 2005
    #2

  10. ghemant Moderator

    using alter table statement you can do this refer BOL for more


    regards

    hsGoswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
    ghemant, Aug 11, 2005
    #2

  11. FrankKalis Moderator

    The more files on different disks you have, the merrier to performance. This does not only hold true for JOIN operations. Thanks for bringing this to my attention. I'll see if I can expand on this tip in the near future. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
    FrankKalis, Aug 11, 2005
    #2

  12. satya Moderator

    As referred in the link above about filegroups tips you can get optimum performance by placing in different filegroups, as referred above review information from books online.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 11, 2005
    #2

  13. PAMUR New Member

    Thanks a lot.<br /><br />I know how to place files in different file groups. And yes we create physical files in different disks and assign a file group to each of the phisical files. I mean one file group can be on many physical files. <br /><br />I can choose to store tables on file groups. But how can I choose to save table on a particular physical file?<br /><br />Again as suggested I will refer to BOL and get back.<br /><br />Once again thaks to you all.<br /><br /><br />Regards [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />
    PAMUR, Aug 12, 2005
    #2

  14. satya Moderator

    While creating the table as you can specify the required Filegroup and that filegroup has been assigned to a file on operating system. I haven't seen such statement while updating data to a specific filegroup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 12, 2005
    #2

  15. PAMUR New Member

    Yes we can do that. We can spcify a required file group and the file group belogs to one or more physical files. <br /><br />For instance I have created three PhyFile1, PhyFile2 and PhyFile3 and have a file group Primary an all three physicals. Now i want a table tab1 in PhyFile1. How do I do it? <br /><br />I think it can't be done. But the statement says it can be. <br /><br />quote:<br />--------------------------------------------------------------------------------<br />"For very large joins, consider placing the tables to be joined in separate physical files in the same filegroup. This allows SQL Server to spawn a separate thread for each file being accessed, boosting performance. [6.5, 7.0, 2000] Updated 10-18-2004".<br />--------------------------------------------------------------------------------<br /><font color="red"><font size="6"><br /><b>Can it be done?</b></font id="size6"></font id="red"><br /><br />Alter statement only provides to change the table to a file group, not a physical file.<br /><br />Regards<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
    PAMUR, Aug 12, 2005
    #2

  16. FrankKalis Moderator

    I think we do have a misunderstanding here. <br />A filegroup consists of at least one physical file. You are able to add more files to a filegroup if wanted. for each file SQL Server might start a separate thread in order to retrieve the data faster. As long as these files reside on separated physical disks, it is likely to improve performance, if the files are on the same disk, it's pretty useless. When you now create a table, you are able to specify to which filegroup this table belongs. The rest is, AFAIK, out of your reach. So, to the best of my knowledge the answer to your question is NO!<br /><br />As I've said before, I will start a discussion among the contributors to this site about the tip here you've referenced. We'll work out a way to clarify the intention of this tip. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
    FrankKalis, Aug 12, 2005
    #2

  17. satya Moderator

    I fairly agree the statement itself is a sort of bit confusing, depending on your understanding it is not possbile as I haven't seen such a way to do so.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, Aug 12, 2005
    #2

  18. PAMUR New Member



    Thanks a lot.

    I will be waiting for more on the following.

    ------------------------------------------------------
    The more files on different disks you have, the merrier to performance. This does not only hold true for JOIN operations. Thanks for bringing this to my attention. I'll see if I can expand on this tip in the near future. -- Frank Kalis
    ---------------------------------------------------

    Thanks to all.

    PAMUR, Aug 12, 2005
    #2

  19. FrankKalis Moderator

    I've started the discussion I've mentioned. I've bookmarked this thread and you post here when we modified the tip. Again, thank you for bringing this to attention!

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Aug 12, 2005
    #2

  20. PAMUR New Member

    Frank! Could you please send me a link in English.<br /><br />Thanks [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
    PAMUR, Aug 16, 2005
    #2

  21. FrankKalis Moderator

    Sorry, what link? Actually I had an error in my last reply. I meant to say: "...and I'll post here when we modified the tip...". But for now as an update. I will modify the wording of the tip. After thinking a little on this, I think there are two scenarios possible here (simplified):

    1. Add as many disks as you can, as this will allow SQL Server to start multiple threads to retrieve the data faster.
    2. Consider placing these large tables in different filegroups on separated physical files.

    From my interpretation of this tip, I would tend to rewrite it to the second scenario.

    What do you think?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Aug 17, 2005
    #2

  22. PAMUR New Member

    That is fare. You can do that.

    Regards
    PAMUR, Aug 17, 2005
    #2

  23. PAMUR New Member

    I think that should remove the confusion, and the statement is clear.

    Regards
    PAMUR, Aug 17, 2005
    #2

  24. FrankKalis Moderator

    Fine. Well, then, I'll revive this threads once I've rewritten the tip. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
    FrankKalis, Aug 18, 2005
    #2
Page 1 of 2

Share This Page