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

Partitioning Expand / Collapse
Author
Message
Posted Saturday, December 15, 2012 6:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:36 PM
Points: 108, Visits: 1,087
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?

Post #1396888
Posted Sunday, December 16, 2012 2:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:36 PM
Points: 108, Visits: 1,087

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?
Post #1396975
Posted Sunday, December 16, 2012 3:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 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 #1396978
Posted Sunday, December 16, 2012 4:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:36 PM
Points: 108, Visits: 1,087
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?

Post #1396981
Posted Sunday, December 16, 2012 6:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 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 #1396986
Posted Sunday, December 16, 2012 6:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 21, 2014 11:36 PM
Points: 108, Visits: 1,087
Thanks a lot for your help
Post #1396987
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse