Partitioning

Question

Partitioning

mah_j

SSCrazy

Points: 2931
More actions
December 15, 2012 at 6:11 am

#266252

Hi
I have problem with partitioning.I am testing partitioning ,but i am a bit confused about it.
I don't know the concept of partitioned index,unpartitiond index,aligned or unaligned and when we should use them.How is the storing of indexes in partitioning?
Although I have read a lot (MSDN and searches),I can't figure them out.
would anyone please help me by suggesting a link or a brief explanation?

Viewing 6 posts - 1 through 5 (of 5 total)

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

mah_j SSCrazy Points: 2931 More actions · Answer 1

Any suggestion for that?

In my table (340G_140000000Row) Most of reports run are against the most recent one year of data.My table has a clustered index(primary key) on 2 fields : [Number] [int],[TransDate] [datetime]. I created partition over [TransDate] and all the indexes ON DB_PartitionScheme ([TransDate]),but the logical reads of this table and cpu time increase in test db.

I don't know why it is in this way?what should I do?

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

mah_j (12/16/2012)
I created partition over [TransDate] and all the indexes ON DB_PartitionScheme ([TransDate]),but the logical reads of this table and cpu time increase in test db.

Not surprising.

I don't know why it is in this way?what should I do?

Because partitioning is not primarily for performance. It's for maintainability, data loads. You can get performance improvements out of partitioning, but usually not automatically.

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

mah_j SSCrazy Points: 2931 More actions · Answer 3

Thanks

You mean besides that, I should check other options like indexing ,memory management,.....?

I have another question about partitioning(sorry I am not perfect at SQL).

I will be glad ,if you can help me please.

In my implicit table (with 2 fields for clustered index(primary key)),is it possible to have partition key on a field which is not the subset of index key?

when I want to create the clustered index it says:

Column 'kind' is partitioning column of the index 'PK_Trans'. Partition columns for a unique index must be a subset of the index key.

and then if I use ON [PRIAMARY] like following script,I don't have partitioning on that table any more.

ALTER TABLE [dbo].[Trans] ADD CONSTRAINT [PK_Trans] PRIMARY KEY

CLUSTERED([Number] ASC, [TransDate] ASC)WITH (

PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE

= OFF,

SORT_IN_TEMPDB =

OFF,

IGNORE_DUP_KEY =

OFF,

ONLINE = OFF,

ALLOW_ROW_LOCKS

= ON,

ALLOW_PAGE_LOCKS

= ON

) ON [DB_PartitionScheme]([kind])-- or use [Primary]

GO

So how can I implement partitioning on this table with partitioning key which is defferent from index key?

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

mah_j (12/16/2012)
You mean besides that, I should check other options like indexing ,memory management,.....?

I mean that if you want to performance tune your query, then performance tune your query. Don't expect to partition the table and magically get a massive speed improvement.

In my implicit table (with 2 fields for clustered index(primary key)),is it possible to have partition key on a field which is not the subset of index key?
when I want to create the clustered index it says:
Column 'kind' is partitioning column of the index 'PK_Trans'. Partition columns for a unique index must be a subset of the index key.

The message is pretty clear there. "Partition columns for a unique index must be a subset of the index key."

and then if I use ON [PRIAMARY] like following script,I don't have partitioning on that table any more.

Well, no. ON PRIMARY says to put the table on the primary file group. To partition you put the table ON <partition scheme>

So how can I implement partitioning on this table with partitioning key which is defferent from index key?

When partitioning a unique index (and the primary key is unique), the partition key must be part of the index key. To partition that primary key, you have to add the partition column to the index. There's no way around that.

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

mah_j SSCrazy Points: 2931 More actions · Answer 5

mah_j

SSCrazy

Points: 2931

December 16, 2012 at 6:19 am

#1568941

Thanks a lot for your help:-)