Partitioning in SQL Server 2008

Question

Partitioning in SQL Server 2008

Viewing 9 posts - 31 through 38 (of 38 total)

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

chapman.tim SSC-Addicted Points: 417 More actions · Answer 1

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.

Francis Incourt SSC Rookie Points: 35 More actions · Answer 2

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.

chapman.tim SSC-Addicted Points: 417 More actions · Answer 3

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.

jslingrowd Grasshopper Points: 18 More actions · Answer 4

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

SQLBrigade SSC Rookie Points: 25 More actions · Answer 5

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

nahka163 SSC Rookie Points: 39 More actions · Answer 6

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

jansub07 Say Hey Kid Points: 709 More actions · Answer 7

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

KTD Say Hey Kid Points: 671 More actions · Answer 8

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.

I don't always test my SQL scripts, but when I do, I test in Production.

rajiv.singh1237 Valued Member Points: 67 More actions · Answer 9

Correct example: Updated existing code

1)

USE master

GO

CREATE DATABASE MyPartitionPracticeDB

ON PRIMARY

( NAME = db_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDB.mdf',

SIZE = 50MB),

FILEGROUP FG1

( NAME = FG1_1_dat,