Is this a good way to approach the partitioning.

Question

Post reply

Is this a good way to approach the partitioning.

bhushanvinay

SSCrazy

Points: 2253
More actions
March 28, 2009 at 2:09 am

#219481

SET NOCOUNT ON
GO
DROP TABLE DependentTable
GO
DROP TABLE PrimaryTable
GO
GO
DROP PARTITION SCHEME ArchiveSwitchScheme
GO
DROP PARTITION FUNCTION ArchivePartitionRange
GO
CREATE PARTITION FUNCTION ArchivePartitionRange (INT)
AS RANGE LEFT FOR VALUES (0);
GO
CREATE PARTITION SCHEME ArchiveSwitchScheme
AS PARTITION ArchivePartitionRange
TO ( TestFgArchive,TestFgPrimary);
GO
CREATE TABLE PrimaryTable
(
ID INT NOT NULL,
OTHERID INT
)ON ArchiveSwitchScheme(ID)
GO
/****** Object: Index [PK__PrimaryTable__36B12243] Script Date:
03/25/2009 10:20:21 ******/
ALTER TABLE [dbo].[PrimaryTable] ADD PRIMARY KEY NONCLUSTERED
([ID] ASC )
GO
CREATE TABLE DependentTable
( ID INT NOT NULL,
REFID INT NOT NULL,
OTHERID INT
) ON ArchiveSwitchScheme(REFID)
GO
ALTER TABLE DependentTable ADD CONSTRAINT
PK_DependentTable PrimaryTable KEY NONCLUSTERED (ID,REFID)
GO
ALTER TABLE [DependentTable] WITH NOCHECK
ADD CONSTRAINT [FK_DependentTable_PrimaryTable] FOREIGN
KEY([REFID])
REFERENCES [PrimaryTable]([ID]) ON UPDATE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE DependentTable CHECK CONSTRAINT
[FK_DependentTable_PrimaryTable]
GO
DECLARE @I INT;
SELECT @I = 0;
WHILE @I != 500
BEGIN
SELECT @I = @I +1;
INSERT INTO PrimaryTable VALUES(@I,@I);
INSERT INTO DependentTable VALUES(@I,@I,@I);
END
GO
SELECT *,$PARTITION.ArchivePartitionRange(ID) FROM PrimaryTable
SELECT *,$PARTITION.ArchivePartitionRange(REFID) FROM DependentTable
GO
UPDATE PrimaryTable
SET ID = ID *-1
WHERE ID IN ( SELECT TOP 50 ID FROM PrimaryTable)
GO
SELECT *,$PARTITION.ArchivePartitionRange(ID) FROM PrimaryTable
SELECT *,$PARTITION.ArchivePartitionRange(REFID) FROM DependentTable
Regards
Vinay

Viewing 10 posts - 1 through 9 (of 9 total)

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

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 1

Hi

Instead of the code can you give us what is requirement? and how the the data access pattern??

Thanks -- Vijaya Kadiyala

http://www.dotnetvj.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

bhushanvinay SSCrazy Points: 2253 More actions · Answer 2

Hi,

Thank you for your response, we want the partition in such a way that all the dependent records are also partitioned correctly.

and we have a live partition where data will be comming in and we have a archive partition where we want to keep all the rest of the data.

a very simple requirement.

but we want to see that the cascade will not have a huge affect on the data comming in by locking tables causing deadlocks and other un necessary behaviour.

Regards

Vinay

Regards
Vinay

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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

-WP: http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

It is an advanced topic, so prepare it very detailed and experiment with it to feel its pros and it cons.

another thread regarding partitioning advise:

http://www.sqlservercentral.com/Forums/Topic623669-360-1.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

Paul White SSC Guru Points: 150467 More actions · Answer 4

Hi bhushanvinay,

While I have no doubt that the scheme you outline would work, I imagine it would be a nightmare on a busy system. That's true even if the archival process ran in small chunks, pausing between each, and running at a low deadlock priority. You are right to question the locking that would result, and I would also point to the inefficiency of moving data between partitions - even before you factor in the cascade operation (which runs internally at the serializable isolation level, I believe).

The typical approach here is that of the 'sliding window'. While the eventual movement of rows between one filegroup and another is always going to be an expensive operation, you can take advantage of a metadata SWITCH in the intermediate steps, and schedule the operation for a maintenance period.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

bhushanvinay SSCrazy Points: 2253 More actions · Answer 5

Thanks Paul,

So you say to have a sliding window, but how do i make sure about the data in the dendent tables also get archived in the same way.

The sliding window scenario holds good if i can partition both the table on the same partition key. ie., i may have a datetime partition in my primary but in my dependent i may just have the id and no datetime.

how do i go about to see what data should move where?

This is more of a organising the partition question. Thank you for comming back to the question much appriciate the answer aswell.

Regards

Vinay

Regards
Vinay

Paul White SSC Guru Points: 150467 More actions · Answer 6

Hey Vinay,

Typically you would write a procedure to implement the sliding window, and it would 'slide the window' on both tables inside a transaction.

Sorry for the quick reply but I have to catch a train now...

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

bhushanvinay SSCrazy Points: 2253 More actions · Answer 7

Any quick examples, for the above one.

i have shown how i am creating a Primary and Dependent tables.

so how do you want to structure the proc. Can a proc be a partitioning scheme.

Regards

Vinay

Regards
Vinay

Paul White SSC Guru Points: 150467 More actions · Answer 8

bhushanvinay (4/6/2009)
Any quick examples, for the above one.
Regards
Vinay

Hey Vinay,

I think that the contributions so far, and a little googling should enable you to knock something together - learning by doing is much more fun than copy & paste! 🙂

Have fun with it!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

bhushanvinay SSCrazy Points: 2253 More actions · Answer 9

Well,

It go's like this, i was just looking for a example of how to do this pice of work, i learnt myself how to partion the table and put together a sample.

the copy paste works as a knowhow of what i am taking about, the truth is always in the code and not in the presentation of it.

and it makes it easy to understand what one is talking about.

Thats fine !! i see where you are comming from, and admire your honest openion

There is a lot of theory on copy paste i can go about it on and on. from son to grandson.....

Thanks for the locking help though.

Regards

Vinay

Regards
Vinay