Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning


Partitioning

Author
Message
mah_j
mah_j
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1261
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?
mah_j
mah_j
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1261
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
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
mah_j
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1261
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
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
mah_j
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1261
Thanks a lot for your help:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search