locked
partition table filegroups questions RRS feed

  • Question

  • Hi, I need to create a partitioned table that stores 12 month(12 partitions, total 400 million records) worth of data.

    the server has 4 CPUs, not sure # of threads.

    1. How many filegroups taht I need to create? one file per filegroup?

    2. 4 files per filegroup?

    3. it depends on how many threads that the server has? xp_configure shows max worker threads =0 (run_value)

    4. Any other suggestion?

     

    Thnaks, a lot.


    mecn
    Friday, March 11, 2011 5:59 PM

Answers

  • Hi,

    Each partition is required to map to a filegroup which can be different or same filegroup. So, if the table has 12 partitions, you have all of them mapped to one filegroup or at most 12 different filegroups. I am not sure how your data will be accessed, do all partitions supports read and write or some of them are only support read/write. Typically, the more files in each filegroup, the more write/read preformance you may get. More information about partitioned tables, please refer to http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx.

    For 4 CPUs on the server, the default maximum worker threads are 256 for 32-bit OS and 512 for 64-bit OS. However, if this is not prefered, you can configure max worker threads option manually. For more information, see: http://msdn.microsoft.com/en-us/library/ms187024.aspx.

    Hope this helps. If you have any question, please feel free to let me know.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Jeen Philip Thursday, March 17, 2011 1:38 AM
    • Marked as answer by Alex Feng (SQL) Sunday, March 20, 2011 9:04 AM
    Wednesday, March 16, 2011 7:57 AM

All replies

  • You can create all 12 partitions on primary file.... It depends on your disks configurations, generally I would go with 12 files (each for partiton) based on  your narrative
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 13, 2011 8:17 AM
  • Hi,

    Each partition is required to map to a filegroup which can be different or same filegroup. So, if the table has 12 partitions, you have all of them mapped to one filegroup or at most 12 different filegroups. I am not sure how your data will be accessed, do all partitions supports read and write or some of them are only support read/write. Typically, the more files in each filegroup, the more write/read preformance you may get. More information about partitioned tables, please refer to http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx.

    For 4 CPUs on the server, the default maximum worker threads are 256 for 32-bit OS and 512 for 64-bit OS. However, if this is not prefered, you can configure max worker threads option manually. For more information, see: http://msdn.microsoft.com/en-us/library/ms187024.aspx.

    Hope this helps. If you have any question, please feel free to let me know.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Jeen Philip Thursday, March 17, 2011 1:38 AM
    • Marked as answer by Alex Feng (SQL) Sunday, March 20, 2011 9:04 AM
    Wednesday, March 16, 2011 7:57 AM
  • Thanks for replying.
    mecn
    Wednesday, March 16, 2011 9:53 PM