Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    262

    Unanswered: Partitioning , Need experts review.

    Good day,

    And thank you in advance to Mr.Flump.

    Recently, my SQL server administrator have asked me to review Partitioning as he is planning to go forward implementing it.

    Here is a detail of hardware.

    The servers are quad 2 x 16 server mechines , 64 GB RAM , 300 TB diskspace, with additional 300 TB tape .

    The data he means to partition spans over 4 server machines.

    Now the said data is the operational data of call center. listing states of America , Canada and United Kingdom .

    The customers are in all the states , even in the smallest PE .

    It is a customer info database with calling and DNC .

    Partition is proposed by the administrator in form of range partition ( i think ) . creating partition of each state. or the group of states by the range of area codes .

    I am against it uptill now . As i am mostly an oracle guy , and uptill now what i have read online , partitioning is more towards administrative functionality .. not towards performance .

    I invite you all experts to kindly prove me wrong .

    Tell me what you have learned about partitioning through your experience.

    Respect and Regards
    mish sy

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Partitioning (in the form of distributed partitioned views at least) can significantly improve performance in SQL Server 2005 and later.

    The trick is to create a partitioning key that will divide the work instead of dividing the data. These can be the same key, but my luck has not worked that way.

    With the geographic span from the UK to the Aleutians you're looking at 12 time zones. However, the population is no where near equally distributed and the transaction generation is probably more seriously skewed than the population. Finding a practical key to distribute the data across the processing partitions (servers) will be what my professors used to call "A non-trivial exercise" that will take some work. Finding a "perfect" key is impractical, but finding a useful key may be easy.

    Once this is done, you have the resources of N machines to process your SQL. There will be marshalling costs (to distribute the SQL appropriately across the servers), but that will probably be negligable. The net result is that once you find the appropriate partitioning key, your queries will usually run significantly faster.

    At a major client we took a database and application that was business critical, but was pushing hard against compliance deadlines because requried reporting was taking 90+ percent of the time allowed leaving every little margin for error. We were working with about 8 Tb of active data (about 7.3 TB historical, the rest OLTP) on one monster server. We created a six-way partition running as named instances on a seven node cluster, and cut the processing time down to less than 25% of the time allowed. This was expensive by Microsoft standards, but the total solution cost was less than just the Oracle license cost for the solution we replaced.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    I Thank you for sparing precious moments of your time to reply my query.

    The useful key can be the first 3 digits of the phone number as it is the only thing which can not be replicated for a phone number ( the total 3 digits and the rest of the phone number) .
    also the first 3 digits classify the data into states.

    I do not see the manageability of partitioning the data ' 1 partition per state' , i have said for the range of area codes ( eg. 219, 745,810) . and distributing it as RIGHT range in partition function .

    but again i am against it as yet i dont see any greater result which can not be gained by full text index, cluster index and server configuration. specially disk array.

    According to my tiny/impractical view, local servers residing in respective countries can be partitioned to their respective data set .

    But again the sql-server admin is continuously saying partitioning will improve the WHERE CLAUSE search and the overhead from the total .mdf files currently in singular form .

    what are your views .

    I have a feeling i am wrong , but sql-server is not my area of expertness .

    Respect and Regards
    mish sy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •