Appropriate range for partitioning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Appropriate range for partitioning

Appropriate range for partitioning

Rate Topic

Display Mode

Topic Options

Author

Message

mah_j

Posted Sunday, December 09, 2012 3:54 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

Hi

I have a large table which there is just select and insert on it.It has a field named 'TranType'(int) and for some of the types we just have insert and no select at all.

I want to use partitioning to separate this data into another file.but there is not any particular range for values . For example the types with no select are:1004,1006,1007,1009,1010,2000

How can i use the CREATE PARTITION FUNCTION in this case?

CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (????);

Another basic question about partitioning:
I have a table like this

CREATE TABLE [dbo].[Trans]
(
[Num] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[ID] [bigint] NULL,
[Kind] [int] NULL,
[Success] [bit] NULL,
[CNum] [nvarchar](20) NULL,
[Data] [nvarchar](4000) NULL,
[Counter] [int] NOT NULL,
[IP] [nvarchar](25) NULL,
[Result] [int] NULL,
CONSTRAINT [PK_Trans] PRIMARY KEY CLUSTERED([Num] ASC, [Date] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY]

so it has a Clustered Index For PRIMARY KEY and 6 NonClustered Indexes.

for partitioning on the feild "kind" should i drop all the FK and PRIMARY KEY,and create a new CLUSTERED Index on kind feild?

there isnt any way that i dont have to drop all this indexes and FK s?

Post #1394392

GilaMonster

Posted Sunday, December 09, 2012 8:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

First question. Why are you partitioning? What's the purpose, what's the goal?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1394399

mah_j

Posted Monday, December 10, 2012 2:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:36 AM
Points: 86, Visits: 984

There is a sharply increase in the size of transactions that is inserted in that table,and if i can do partitioning the remaining range of kind field will be half,and the performance of select statements on the table will be increased,also i think the page split would be decreased in the partition that i want to select from it.

Post #1394502

GilaMonster

Posted Monday, December 10, 2012 2:44 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

If you are partitioning with the idea that queries are automatically faster on partitioned tables, don't waste your time.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1394504

Bhuvnesh

Posted Monday, December 10, 2012 3:06 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

mah_j (12/10/2012)

also i think the page split would be decreased in the partition that i want to select from it.

NO , partitoning is not related with page spliting and
for partitiioning see this http://www.brentozar.com/archive/2012/03/how-decide-if-should-use-table-partitioning/

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1394511

« Prev Topic | Next Topic »

Permissions