Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Partitioning in SQL Server 2008 Expand / Collapse
Author
Message
Posted Friday, October 1, 2010 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:25 AM
Points: 49, Visits: 269
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
Posted Friday, October 1, 2010 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:21 AM
Points: 1, Visits: 34
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
Posted Monday, October 4, 2010 6:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 5, 2014 10:25 AM
Points: 49, Visits: 269
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
Posted Wednesday, November 3, 2010 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 7, 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
Posted Tuesday, November 9, 2010 3:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 3:26 PM
Points: 1, Visits: 26
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
Posted Monday, April 30, 2012 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, June 18, 2012 12:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 3:31 AM
Points: 8, Visits: 106
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
Posted Tuesday, September 25, 2012 1:22 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 6:47 AM
Points: 28, Visits: 330
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.
Post #1364253
Posted Wednesday, June 26, 2013 5:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:24 AM
Points: 1, Visits: 14
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,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',
SIZE = 2MB),
( NAME = FG1_2_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',
SIZE = 2MB),
( NAME = FG1_3_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',
SIZE = 2MB),
( NAME = FG1_4_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',
SIZE = 2MB),
( NAME = FG1_5_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',
SIZE = 2MB),
( NAME = FG1_6_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBFG1.ndf',
SIZE = 2MB)
LOG ON
( NAME = db_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyPartitionPracticeDBlog.ndf',
SIZE = 2MB,
FILEGROWTH = 10% );
GO
USE MyPartitionPracticeDB
GO

2)
--It will create 6 partition... 5 for mentioned range and one for left out values
CREATE PARTITION FUNCTION partFunc (int) AS
RANGE LEFT FOR VALUES (1000, 2000, 3000, 4000, 5000);

SELECT * FROM sys.partition_functions

3)
CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION Partfunc TO
([FG1], [FG1], [FG1], [FG1], [FG1], [FG1])
GO

SELECT * FROM SYS.PARTITION_RANGE_VALUES

4)
CREATE TABLE MyPartionedTable
(
ID int PRIMARY KEY,
Name VARCHAR(50)
)
ON MyPartitionScheme(ID)


Thanks,
Rajiv Singh
Post #1467576
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse