Table Partition

Question

Table Partition

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 6, 2012 at 11:11 am

#256042

I have 2 urgent queries regarding the table partitons. Kindly assist -
1. I have a table which is quite large....and client has asked to implement table partition against one of the table columns which contains data as -> 0,1,2,etc....(non date field). But the problem is, the table is located in a Database which does have any file/file groups other than primary.
- Is this necessary to create file and filegroups by altering the database and putting the range values from the table in each data files?
- Then , do I need to recreate the table?
- What is the effect of table partition on Database Mirror and Replication and overall partition?
- On what basis/consideration, will I create the range function?
Script ::: 1
Create Partition Range Function
CREATE PARTITION FUNCTION Name (INT)
AS RANGE LEFT FOR
VALUES (10);
GO
Script ::: 2
CREATE PARTITION SCHEME scheme_name
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], filegroup1);
GO
Please suggest me some effective plan to implement this task.
Regards
Sourav
Thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

First question: Why is the table to be partitioned? What's the goal here?

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Adding to Gail's point, looking at your questions it looks like you may want to refresh your "Table Partitioning 101" skills, start here http://msdn.microsoft.com/en-us/library/ms190787.aspx

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Hi Gail,

The table has more than 35 million+ records and of which while trying to extract the results against the particular columns (the columns holds values as : 0,1 and 2 etc..), it takes longer time.

So, we wanted to check whether it is possible to partition against the columns based on the values!

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Sure you can. It quite likely won't do a thing for performance, but you can partition it if you really want to.

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Can you please help me addressing the below queries?

How detrimental it would be for Replication/Db-Mirror and reports running against the tables?

1. I have a table which is quite large....and client has asked to implement table partition against one of the table columns which contains data as -> 0,1,2,etc....(non date field). But the problem is, the table is located in a Database which does have any file/file groups other than primary.

- Is this necessary to create file and filegroups by altering the database and putting the range values from the table in each data files?

- Then , do I need to recreate the table?

2. What is the effect of table partition on Database Mirror and Replication and overall partition?

3. On what basis/consideration, will I create the range function?

Thanks.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Sourav-657741 (4/6/2012)
The table has more than 35 million+ records and of which while trying to extract the results against the particular columns (the columns holds values as : 0,1 and 2 etc..), it takes longer time.

Wondering if you have the proper indexing strategy in place to extract data from that particular table - usually you do not partition because of performance issues, mostly because partitioning rarely helps with performance.

Having said that, 35M rows is not really a large table - worked on a particular system where partitions used to hold in excess of 35M rows a piece.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You do realize that it may be better to look at the queries and the tables to determine if the queries and/or indexes can be improved before you look at partitioning the table(s).

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Hi Paul

If I am wrong I believe partitioning the table helps in improving the performance because we are placing the ranges of the data in individual File groups....so instead of searching the records on the entire table, it will search on the respective filegroup where it has been placed with the filegroups!!

What do you think?

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Yes, partitioning can provide performance benefits. The question is, however, will partitioning benefit the queries that are being run? You really need to look at the queries and the current indexing to decide if partitioning will actually benefit you or will rewriting the queries and/or adjusting the indexing provide you with a better return on your time and effort?

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 10

Thanks Lynn.

As on today, the queries which I find are running against are joining between two/three/four tables....with the specific column in question to be filtered with some data!

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Sourav-657741 (4/6/2012)
Thanks Lynn.
As on today, the queries which I find are running against are joining between two/three/four tables....with the specific column in question to be filtered with some data!

That's nice. Unfortunately, you are the only one who can see the queries, tables, and indexes. In addition, I don't think any of us are mind readers.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 12

Sourav-657741 (4/6/2012)
If I am wrong I believe partitioning the table helps in improving the performance because we are placing the ranges of the data in individual File groups....so instead of searching the records on the entire table, it will search on the respective filegroup where it has been placed with the filegroups!!
What do you think?

Different filegroups would help if each filegroup sits on a different spindle but this will actually help if the underlying problem is disk i/o contention - is the problem i/o contention?

Your reasoning about partitions is right - for an indexless table where SQL Server is forced to do a full table scans on each query. This is why a proper indexing strategy is needed, to avoid full table scans that can be avoided.

Question #1:

Are there multi-column indexes where the first column is the one that sets the 0,1,2,3...,n range you plan to use as partition key?

Question #2:

How many rows out of the 35M is expected to be returned by each query?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Sourav-657741 (4/6/2012)
If I am wrong I believe partitioning the table helps in improving the performance because we are placing the ranges of the data in individual File groups....so instead of searching the records on the entire table, it will search on the respective filegroup where it has been placed with the filegroups!!

That's only true if the filegroups are on separate physical drives, the data is being read from disk not memory and the bottleneck is IO related.

There is nothing intrinsic in filegroups that improves performance just by itself. If you have proper indexing on the table, then SQL can read just the range it needs, not search the entire table.

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