November 1, 2007 at 9:54 am
I have a problem whereby I am trying to get rid of a whole load of data from a partition. I have tried to switch it out to a new table and partition set, however have run into a problem whereby the primary key is not defined with the partition key (date). Deletes of a single day take around 2 hours for about 22 million rows.
Any suggestions how I could drop the partiton #2 from the list below and save myself about 22 hours of deletes?
Parition | Min Date | Max Date | Rows in partition
2| 2007-08-21 00:00:00 |2007-08-31 00:00:00| 237472509
3| 2007-09-01 00:00:00 |2007-09-30 00:00:00| 692648700
4| 2007-10-01 00:00:00 |2007-10-29 00:00:00| 517096588
(from when trying to perform the switch)
Msg 7733, Level 16, State 4, Line 1
'ALTER TABLE SWITCH' statement failed. The table 'NationalLD.dbo.tblCDR' is partitioned while index 'PK_tblCDR_ID' is not partitioned.
November 1, 2007 at 12:02 pm
That drops the function, would that not lead to the data no longer being partitioned? I only want to be rid of the data on a single partition.
November 1, 2007 at 2:05 pm
did you check Kimberly's very good white paper on partitioning ?
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
it contains very good sample scripts !:smooooth:
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
November 1, 2007 at 11:46 pm
You can have a look at the link given below
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 2, 2007 at 8:49 am
The articles are great, however I don't see a resolution to my problem.
By the looks of things I will have to drop the pk index and recreate it as a part of the partition scheme, which could take a while given that it's a 1,447,217,797 row table. Yikes
November 5, 2007 at 12:23 am
- as you have read, you must take very good care when designing and creating partitions and indexes.
- be sure indexes are alligned !
- indeed, reorg. 1T rows isn't going to be over in a second. Document your actions with the reasons why they are performed so you can take these lessons for future partitioned objects.
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
November 6, 2007 at 8:23 am
ALZDBA (11/5/2007)
- as you have read, you must take very good care when designing and creating partitions and indexes.- be sure indexes are alligned !
Unfortunately I was not involved in the design or creation of this, and are really just helping out as a favor to some folks.
Ah, gotta love taking over from someone elses work
:w00t:
January 23, 2008 at 6:16 am
I'm receiving the same error message, but it's complaining in reverse that my table isn't partitioned, although it plainly is!
Warning: The specified partition 1 for the table myDb.dbo.myTable' was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.
Msg 7733, Level 16, State 3, Line 1
'ALTER TABLE SWITCH' statement failed. The index 'Index38' is partitioned while table 'myDb.dbo.myTable' is not partitioned.
Also, looking in SSMS at the table properties, the box for "Table is Partitioned" has a value of false. I can query as follows and confirm that I did in fact partition the table properly, so does anyone know why SQL Server doesn't think my table is partitioned and how I can fix that?
SELECT
distinct $partition.MonthlyPartititionsFunction(myDateColumn) AS PartitionNumber, myDateColumn
FROM
dbo.myTable
ORDER BY
myDateColumn;
1 1/1/2000
2 2/1/2000
(etc.)
I should also note that after I created the partitioned table, I created a clustered Primary Key Index that was NOT partitioned (since the PK is different than the partitioning key). Could that be my problem?
January 23, 2008 at 6:34 am
That's probably your issue. With the Primary Key not being partitioned, effectively neither is the data any longer.
I've found that any indexes on the table have to have the partitioning column as a part of the index, otherwise partitioning really doesn't function correctly, and you can certainly not switch out any data.
January 23, 2008 at 6:35 am
I should also note that after I created the partitioned table, I created a clustered Primary Key Index that was NOT partitioned (since the PK is different than the partitioning key). Could that be my problem?
did you specify a filegroup for the PK.
If not, sqlserver will apply the same as for the table in case of a partitioned one.
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 28, 2008 at 4:47 am
I currently have all partitions and this PK index residing on the PRIMARY filegroup.
I should specify what I'm trying to accomplish, I suppose. I'm trying to figure out how to migrate data to an "archiving" FileGroup in a sliding window fashion. For instance, if a partition's data is older than 2 years, I want to move to more inexpensive disk since it will not require the same performance as more recent data. I would do this migration on a monthly basis, to always roll off that 25 month old partition to the other FileGroup.
I'm really just trying to move one partition's data to a different filegroup. Does anyone have an example of how to do that?
January 28, 2008 at 5:18 am
- Can you post the DDL for the PF, PS, the table and all its indexes ?
and the results for :
/*
show partitioned objects
*/
select distinct
p.[object_id],
TbName = OBJECT_NAME(p.[object_id]),
index_name = i.[name],
index_type_desc = i.type_desc,
partition_scheme = ps.[name],
data_space_id = ps.data_space_id,
function_name = pf.[name],
function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i
on p.[object_id] = i.[object_id]
and p.index_id = i.index_id
inner join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
on ps.function_id = pf.function_id
-- WHERE p.[object_id] = object_id('t_Days')
GO
/*
show partitioned objects range values
*/
select p.[object_id],
OBJECT_NAME(p.[object_id]) AS TbName,
p.index_id,
p.partition_number,
p.rows,
index_name = i.[name],
index_type_desc = i.type_desc,
i.data_space_id,
ds1.NAME AS [FILEGROUP_NAME],
pf.function_id,
pf.[name] AS Pf_Name,
pf.type_desc,
pf.boundary_value_on_right,
destination_data_space_id = dds.destination_id,
prv.parameter_id,
prv.value
from sys.partitions p
inner join sys.indexes i
on p.[object_id] = i.[object_id]
and p.index_id = i.index_id
inner JOIN sys.data_spaces ds
on i.data_space_id = ds.data_space_id
inner JOIN sys.partition_schemes ps
on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf
on ps.function_id = pf.function_id
inner join sys.destination_data_spaces dds
on dds.partition_scheme_id = ds.data_space_id
and p.partition_number = dds.destination_id
INNER JOIN sys.data_spaces ds1
on ds1.data_space_id = dds.data_space_id
left outer JOIN sys.partition_range_values prv
on prv.function_id = ps.function_id
and p.partition_number = prv.boundary_id
;
go
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 28, 2008 at 5:52 am
I must give credit to
1) Microsoft for 1/2 the answer (:)
http://www.microsoft.com/technet/technetmag/issues/2007/03/Partitioning/default.aspx
and
2) Sarah Henwood at
http://sqlblog.com/blogs/sarah_henwood/default.aspx
My quest to migrate data in one partition to another Filegroup (to archive onto more inexpensive disk) was accomplished as follows:
drop TABLE multiple_partition
go
drop PARTITION SCHEME Primary_Left_Scheme
go
drop PARTITION FUNCTION Left_Partition
go
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO ([ArchiveFileGroup], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
-- determine which partition is on which FileGroup (credit to Sarah Henwood!!!)
select ds.name AS [Filegroup Name], ds.type, destination_id AS [partition number], dds.partition_scheme_id, ps.name as [partition scheme]
from sys.data_spaces ds
join sys.destination_data_spaces dds
on (ds.data_space_id = dds.data_space_id)
join sys.partition_schemes ps
on (ps.data_space_id = dds.partition_scheme_id)
where ps.name = 'Primary_Left_Scheme'
order by ds.name, ps.name ASC
--Merge partition to migrate the data to the archiving group
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
-- Set next used before re-splitting on Archiving FG
ALTER PARTITION SCHEME primary_left_scheme
NEXT USED [ArchiveFileGroup]
-- re-split now on the archiving filegroup
ALTER PARTITION FUNCTION Left_Partition()
SPLIT RANGE (10)
--Re-Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
-- Re-determine which partition is on which FileGroup (notice that Partition 2 is now on the [[ArchiveFileGroup]]
select ds.name AS [Filegroup Name], ds.type, destination_id AS [partition number], dds.partition_scheme_id, ps.name as [partition scheme]
from sys.data_spaces ds
join sys.destination_data_spaces dds
on (ds.data_space_id = dds.data_space_id)
join sys.partition_schemes ps
on (ps.data_space_id = dds.partition_scheme_id)
where ps.name = 'Primary_Left_Scheme'
order by ds.name, ps.name ASC
January 29, 2008 at 1:48 am
thank you for the feedback (urls and demo script) 😎
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply