MS SQL 2014 Partitioning Data
I am looking at partitioning some tables within a database based on an identifier within the tables. Looking at one table in isolation that contains Transactions I want to partition it based on blocks of customers. e.g. from ID 0 to 5000, 5001 to 10000, 10001 to 15000, etc. I have created a FileGroup called TransactionData and have partitioned everything into 20 ranges with the last partition containing anything outside the ranges. This appears to have moved data out of the .mdf file into the new .ndf file for the transactions.
My question is: Would it be better to have multiple .ndf files for each partition or is it fine to have just one containing all partitions relating to transactions?
At the moment its configured as:
TransactionData (FileGroup) = Partition1, Partition2, Partition3, Partition4, etc
Is there any advantage to this:
TransactionData = Partition1
TransactionData1 = Partition2
TransactionData2 = Partition3
TransactionData3 = Partition4
Is there any IO benefit or Loss with SQL in having more .ndf files containing smaller volumes of partitioned data?
The transaction data in total is around 10GB if that helps.
My question is: Would it be better to have multiple .ndf files for each partition or is it fine to have just one containing all partitions relating to transactions?
At the moment its configured as:
TransactionData (FileGroup) = Partition1, Partition2, Partition3, Partition4, etc
Is there any advantage to this:
TransactionData = Partition1
TransactionData1 = Partition2
TransactionData2 = Partition3
TransactionData3 = Partition4
Is there any IO benefit or Loss with SQL in having more .ndf files containing smaller volumes of partitioned data?
The transaction data in total is around 10GB if that helps.
ASKER
Data is accessed mainly by CustomerID and transactions are pulled based on date ranges but always for specific CustomerID's. So fetching transactions between two dates for a CustomerID would be typical.
The current dataset being worked on is around 10GB for trans but this will be considerably larger based on different datasets. My thinking is that the Query Analyser would be able to fetch the data quicker if the table was partitioned say into 10 blocks based on ranges of CustomerID's. If the CustomerID was in partition 10 which held 2 million rows it would be quicker to fetch than in a unpartitioned table containing 20 million records. Also if the partitioned data was held in a separate FileGroup this could be held on a different disk array to the main database which might provide better performance.
Is it worth looking at partitioning data in this way or will it only be beneficial if the volumes are in the 100's of millions of records?
The current dataset being worked on is around 10GB for trans but this will be considerably larger based on different datasets. My thinking is that the Query Analyser would be able to fetch the data quicker if the table was partitioned say into 10 blocks based on ranges of CustomerID's. If the CustomerID was in partition 10 which held 2 million rows it would be quicker to fetch than in a unpartitioned table containing 20 million records. Also if the partitioned data was held in a separate FileGroup this could be held on a different disk array to the main database which might provide better performance.
Is it worth looking at partitioning data in this way or will it only be beneficial if the volumes are in the 100's of millions of records?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
10GB? Not bigger? Then I don't see any need for paritioning the table.. Why do you want to partition it?