Partitioning in SQL Server 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Muhammad Shujaat... » Partitioning in SQL Server 2008

38 posts, Page 4 of 4

«««1 2 3 4

Partitioning in SQL Server 2008

Rate Topic

Display Mode

Topic Options

Author

Message

chapman.tim

Posted Friday, October 01, 2010 9:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:33 AM
Points: 49, Visits: 236

Of course, if you're partitioning by date (the way it was really intended), and you always include a date in the predicate, the benefits of partitioning would likely outweigh the drawbacks.

Post #996770

Francis Incourt

Posted Friday, October 01, 2010 11:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 25, 2012 6:30 AM
Points: 1, Visits: 33

Nice post. I have the following situation :

My table contains 2 years of history and 5 billions of records.
I took the option to create partition per week. Each week is about 70 millions of rows and size on disk of each partition is about 4-5 GB.
After 2 years, I have 100+ physical partitions and same number of filegroups, one per partition.

I must say it runs quiet well and my table is used only to feed an olap cube.
Is this the right approach ?

My collegue told me that create so many partitions was not necessary and that SQL server would better perform if there were , let's say 4 or 5 big partitions of 200 GB each ...

Any feedback would be appreciated.

Post #996871

chapman.tim

Posted Monday, October 04, 2010 6:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:33 AM
Points: 49, Visits: 236

I really don't think it is the number of partitions that really matter in this case, more of how you're using them. If you're always accessing your data based on the date (which is the field you're partitioning on), then its the right setup. I assume you're using the partitioning to load data based on a SWITCH statement, correct? Also, I believe SQL 2008 SP2 now allows up to 15000 partitions.

Post #997500

jslingrowd

Posted Wednesday, November 03, 2010 11:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 10:09 PM
Points: 2, Visits: 25

Is there any significant performance gain to partition tables on SSD drives? Is partitioning mainly to reduce seek times in rotational disk drives? Thx.

Post #1015466

pdbrigham-671614

Posted Tuesday, November 09, 2010 3:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:47 PM
Points: 1, Visits: 23

There is an error with the create partition scheme; there are not enough filegroups to support the partition function.

You create more filegroups or share partitions across filegroups like:

CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartFunc TO([FG1], [FG2],[FG1], [FG2],[FG1],[FG2])
GO

Post #1018294

nahka163

Posted Monday, April 30, 2012 10:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 9:55 AM
Points: 1, Visits: 29

Hi, I am new in table partitioning and this article gave me a clear picture of how to partition a table in 2008. I hope you are writing more articles about this subject matter. Step by step a working partitioning of a table would be appreciated. Thanks again you have been a great help.

Alia

Post #1292653

jansub07

Posted Monday, June 18, 2012 12:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 7:30 AM
Points: 7, Visits: 44

What happen if one of my client using standard edition as i use partition in my product?
Any other way to introduce the partition concept in standard edition

Post #1317153

KTD

Posted Tuesday, September 25, 2012 1:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:33 AM
Points: 9, Visits: 178

I have been looking for some info on A.) Putting my most recent data and indexes in the partitioned table on SSD drives. B.) You touched on using Partitioned tables to archive the oldest data. I haven't found much on these topics.

Post #1364253

« Prev Topic | Next Topic »

38 posts, Page 4 of 4

«««1 2 3 4

Permissions