December 21, 2008 at 5:37 pm
Hi Guys
I have a very huge existing table, called 'Transaction_Summary', with 82 columns!!! containing more that 100 mil records and because search queries get really slow and in order to make the quicker I have to partition table.
The partition boundary will be a non-primary key CHAR(4) field called "Channel". In order to partition this table I've done the following steps:
I created 5 partitions for my table as the following code:
CREATE PARTITION FUNCTION ParitioningTest_PartitionRange(nchar(4)) AS RANGE LEFT FOR VALUES ('CARD', 'CBO', 'CHEQ', 'CUST', 'TELL')
CREATE PARTITION SCHEME ParitioningTest_PartitionScheme AS PARTITION ParitioningTest_PartitionRange TO ([FG_CARD], [FG_CBO], [FG_CHEQ],[FG_CUST], [FG_TELL], [FG]);
Now I dont know how to move existing records from 'Transaction_Summary' table to this partitions.
I should mention that all this partitioning is for this "Transaction_Summary" which has more than 100 million records and there in a CHAR(4) field in this table called "Channel" which doesnt have an index(there is an id column which is primary key" and I want to do the partitioning based on this filed, I know if that filed was a primary key I could say Drop index Transaction_Summary_IXC on Transaction_Summary with (Move To [ParitioningTest_PartitionScheme] (Channel) ) But I dont know what to do in this case, could you please help me?
Regards,
Hooman
December 21, 2008 at 6:20 pm
I believe you're looking for ALTER TABLE / SWITCH. There's a few caveats to it, but it's all covered in Books Online.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2008 at 7:00 pm
So what I've done so far is not going to help me at all?
December 22, 2008 at 12:21 am
Since partitioning is an advanced topic, it is highly recommendable to read BOL and to read the white paper on partitioning.
(make sure you have the latest version of BOL !)
-SQL2005 http://msdn.microsoft.com/en-us/library/ms188706(SQL.90).aspx
-SQL2008 http://msdn.microsoft.com/en-us/library/ms188706.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 22, 2008 at 4:50 am
Hooman Shamsborhan (12/21/2008)
So what I've done so far is not going to help me at all?
I don't believe so... I believe you need to setup a new single partition table, like it says in BOL, and use SWITCH to move the data to it. Then, you can start partitioning the table further.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2008 at 8:34 am
1) As someone else mentioned, partitioning IS and advanced topic. You really should get a a professional to assist you in this endeavour.
2) It is possible that partitioning won't provide you any benefit at all from a performance standpoint. It depends in large part on your data distribution and data access patterns.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 22, 2008 at 4:16 pm
Thanks for the recommendation but in this case I've been told to partition on what field and I've let them know that it might be inefficient but they insist so that's not my responsibility at this point!And it is not possible to get an sql expert at the moment and I have to do it:(
So any help that you could give me is highly appreciated.
Regards,
Hooman
December 23, 2008 at 8:02 am
The best help we can give you has already been given: reference Books Online (BOL). See here as a starting place for your studies: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d913c8d-b79b-4b1f-93b9-098dd33b07a8.htm.
That link goes in the URL area in BOL, btw, not in a web browser.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 30, 2008 at 12:10 am
[font="Verdana"]
Hi Again
What I did, I read the stuff I was recommended and I started doing it first I created the file groups and then adding the files to database and then created the partition function and then partition schema and then I created an exact copy of the table I want to partition plus all indexes and PK but I still have a problem as soon as I create the PK tha table is not partitioned anymore and I dont know the reason and couldnt find any thing.
This is What I did:
USE PartitionTestBIG
GO
ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CARD
GO
ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CBO
GO
ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CHEQ
GO
ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CUST
GO
ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_TELL
GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_CBO,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_CBO.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_CBO;
GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_CHEQ,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_CHEQ.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_CHEQ;
GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_CUST,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_CUST.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_CUST;
GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_TELL,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_TELL.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_TELL;
GO
CREATE PARTITION FUNCTION TranactionSummary_Channel(nchar(4))
AS
RANGE LEFT FOR VALUES ('CARD', 'CBO', 'CHEQ', 'CUST', 'TELL')
GO
CREATE PARTITION SCHEME TranactionSummary_Schema
AS
PARTITION TranactionSummary_Channel
TO ([FG_CARD], [FG_CBO], [FG_CHEQ], [FG_CUST], [FG_TELL], [Data5])
GO
--The whole table hasnt been mentioned here
CREATE TABLE [dbo].[Transaction_Summary_2](
[Transaction_Serial_No] [bigint] NOT NULL,
[Channel] [nchar](4) NOT NULL,
[Card_Organisation] [nvarchar](11) NULL,
[Card_No] [nvarchar](24) NULL,
[Merchant_No] [nvarchar](19) NULL,
.
.
.
[Ratio_Current_Amount_AVG_3M] [decimal](19, 2) NULL,
[Card_Function_Identifier] [nvarchar](6) NULL
) ON TranactionSummary_Schema (Channel)
CREATE NONCLUSTERED INDEX [IX_ASB_Action_Taken] ON [dbo].[Transaction_Summary_2]
(
[Action_Taken] ASC
)
INCLUDE ( [Card_No],
[Transaction_DateTime]) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_ASB_Card_No] ON [dbo].[Transaction_Summary_2]
(
[Card_No] ASC,
[Transaction_DateTime] DESC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_ASB_Confirmed_Fraud] ON [dbo].[Transaction_Summary_2]
(
[Confirmed_Fraud] ASC
)
INCLUDE ( [Transaction_DateTime],
[Card_No]) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Channel] ON [dbo].[Transaction_Summary_2]
(
[Channel] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Customer_No] ON [dbo].[Transaction_Summary_2]
(
[Customer_No] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Fraud_Alert] ON [dbo].[Transaction_Summary_2]
(
[Fraud_Alert] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Process_Status] ON [dbo].[Transaction_Summary_2]
(
[Process_Status] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Terminal_Id] ON [dbo].[Transaction_Summary_2]
(
[Terminal_Id] ASC,
[Transaction_DateTime] DESC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Date] ON [dbo].[Transaction_Summary_2]
(
[Transaction_Date] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB1] ON [dbo].[Transaction_Summary_2]
(
[Account_No_Stem] ASC,
[Account_No_Branch] ASC,
[Account_No_Suffix] ASC,
[Account_No_Bank] ASC,
[Transaction_DateTime] ASC
)
INCLUDE ( [Transaction_Amount],
[Transaction_Date]) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB2] ON [dbo].[Transaction_Summary_2]
(
[Card_Organisation] ASC,
[Merchant_Location] ASC,
[Country_Code] ASC,
[Account_No_Stem] ASC,
[Account_No_Branch] ASC,
[Account_No_Bank] ASC
)
INCLUDE ( [Transaction_DateTime],
[Latitude],
[Longitude]) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB3] ON [dbo].[Transaction_Summary_2]
(
[Workflow_Status] ASC,
[Reschedule_DateTime] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB4] ON [dbo].[Transaction_Summary_2]
(
[Fraud_Case_Number] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB5] ON [dbo].[Transaction_Summary_2]
(
[Merchant_No] ASC
) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB6] ON [dbo].[Transaction_Summary_2]
(
[Assigned_Team] ASC,
[Assigned_User] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [Index1]
CREATE NONCLUSTERED INDEX [IX_TransactionDateTime] ON [dbo].[Transaction_Summary_2]
(
[Transaction_DateTime] ASC
) ON [Index1]
I can create all the indexes without any problem but as soon I do this:
--This is the part that causes problem
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
) ON [Data5] --ON TranactionSummary_Schema(Channel)
--to here
the table won't remain partitioned.
Could you please help me and let me know what you think?
Regards,
Hooman
[/font]
December 30, 2008 at 5:34 am
Hooman Shamsborhan (12/30/2008)
[font="Verdana"]...
--This is the part that causes problem
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
) ON [Data5] --ON TranactionSummary_Schema(Channel)
--to here
....
[/font]
If you create all indexes without mentioning a filegroup, it will apply your partitioning schema to the indexes, this way you'll be able to restore (if needed) a single partition, without having to rebuild all indexes, which will be offline during this restore !
Because you created your PK clustered on DATA5, all data will reside in DATA5, hence no partitioning !
Create your PK nonclustered, or create it clustered but without mentioning a filegroup !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 30, 2008 at 4:26 pm
[font="Verdana"]
Hi Again
I'm back again with a sea of troubles even in the last day of the year!!!
I tried to create PK without mentioning the filegroup at the end, like this:
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
)
but it didnt work and i got this error message:
Msg 1908, Level 16, State 1, Line 1
Column 'Channel' is partitioning column of the index 'PK_Transaction_Summary_2'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I even tried without mentioning "CLUSTERED"
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY
(
[Transaction_Serial_No] ASC
)
but still got the same error.
If i define "Channel" field in the PK it'd work
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Channel] ASC,
[Transaction_Serial_No] ASC
)
but it really is not a part of PK so I was wondering whether you guys can tell me a way out of this, please :crying:
[/font]
December 30, 2008 at 6:34 pm
Hooman Shamsborhan (12/30/2008)
[font="Verdana"]Happy New YearHi Again
I'm back again with a sea of troubles even in the last day of the year!!!
I tried to create PK without mentioning the filegroup at the end, like this:
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
)
but it didnt work and i got this error message:
Msg 1908, Level 16, State 1, Line 1
Column 'Channel' is partitioning column of the index 'PK_Transaction_Summary_2'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
I even tried without mentioning "CLUSTERED"
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY
(
[Transaction_Serial_No] ASC
)
but still got the same error.
If i define "Channel" field in the PK it'd work
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Channel] ASC,
[Transaction_Serial_No] ASC
)
but it really is not a part of PK so I was wondering whether you guys can tell me a way out of this, please :crying:
[/font]
Did you review Books Online thoroughly as advised?
From here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f1745145-182d-4301-a334-18f799d361d1.htm
we get this:
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
| {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
}
| [ WITH { CHECK | NOCHECK } ] ADD
{
|
|
checking the clickable-link for we see this:
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
(column [ ASC | DESC ] [ ,...n ] )
[
WITH FILLFACTOR = fillfactor
| WITH ( [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name ... )
| filegroup | "default" } ]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression FOR column [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
My review of those two collections of requirements seems to say you are missing this from your PK alter statement:
[ ON { partition_scheme_name ( partition_column_name ... )
here is the text for that section:
ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" }
Specifies the storage location of the index created for the constraint. If partition_scheme_name is specified, the index is partitioned and the partitions are mapped to the filegroups that are specified by partition_scheme_name.
On a separate note: I wonder if it wouldn't have been easier and more obvious if you had created the table with the clustered PK on the partition scheme to begin with. Also and more importantly for tables you intend to add a clustered index to it is usually best to create the clustered index BEFORE creating the non-clustered indexes. Every one of those will now be rebuilt (a loggable event) when you add the clustered index because that changes the table from a heap. I didn't note if you have added data yet, but if not and you have a lot to add in initially I would actually keep the NC indexes off until the data load is complete.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 30, 2008 at 9:21 pm
[font="Verdana"]
Hi
I tried that one if I do below queries I'll get those error messages:
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
) ON TranactionSummary_Schema (Channel)
The error will be:
Msg 1908, Level 16, State 1, Line 1
Column 'Channel' is partitioning column of the index 'PK_Transaction_Summary_2'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Or if i dont specify any filegroup of schema like this:
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
)
I'll get this error:
Msg 1908, Level 16, State 1, Line 1
Column 'Channel' is partitioning column of the index 'PK_Transaction_Summary_2'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
So it doenst work at all unless if I put the partition key in the PK
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Channel] ASC,
[Transaction_Serial_No] ASC
) ON [TranactionSummary_Schema]([Channel])
but the fact is that there is no need for channel to be a part of the PK.
And as you mentioned I tried to create the PK when I create the table that one doesnt work as well.
Regards,
Hooman
[/font]
December 31, 2008 at 6:18 am
We told you partitioning was a advanced topic.
You must read the mentioned white papers to get the actual details why and when you need to include the partitioning column !
It doesn't need to be the first column in you (non-)clustering index, so you'll still be able to tune the index columns for performance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 1, 2009 at 5:35 pm
[font="Verdana"]
Hi
I read the article, probably I dont have a high IQ, could you please let me know what I've done wrong and what I should do?
If I knew the answer I wouldnt mention it here and spend your time. I know it s an advanced topic but I also said I should do it and it s what I have been doing for the last four years, started doing stuff that you dont know at the beginning, I thought that s pretty normal in IT world!!!
Regards,
Hooman
[/font]
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply