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

Appropriate range for partitioning Expand / Collapse
Author
Message
Posted Sunday, December 09, 2012 3:54 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 4:50 AM
Points: 108, Visits: 1,086
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
Posted Sunday, December 09, 2012 8:50 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 @ 4:21 AM
Points: 41,507, Visits: 34,422
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
Posted Monday, December 10, 2012 2:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 4:50 AM
Points: 108, Visits: 1,086


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
Posted Monday, December 10, 2012 2:44 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 @ 4:21 AM
Points: 41,507, Visits: 34,422
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
Posted Monday, December 10, 2012 3:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1394511
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse