February 7, 2011 at 5:54 pm
I've written a script that uses table partitioning, but the $PARTITION function returns a value I didn't expect (I think it is wrong, but I'm not certain). There are only two partitions in each table, but after the switch all the data is in ONE partition (nope, no merge!). I have a [small?] sample script below that exhibits the behavior, you'll have to change the path to something appropriate, but other than that everything you need to see my issue is in the script.
Thanks for your thoughts....
ALTER DATABASE Test
ADD FILEGROUP OrderFileGroup1;
GO
ALTER DATABASE Test ADD FILE
(
NAME = OrderFile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.EVAL2008\MSSQL\DATA\OrderFilegroup1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) TO FILEGROUP OrderFileGroup1
GO
ALTER DATABASE Test
ADD FILEGROUP OrderFileGroup2;
GO
ALTER DATABASE Test ADD FILE
(
NAME = OrderFile2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.EVAL2008\MSSQL\DATA\OrderFilegroup2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) TO FILEGROUP OrderFileGroup2
GO
ALTER DATABASE Test
ADD FILEGROUP OrderFileGroup3;
GO
ALTER DATABASE Test ADD FILE
(
NAME = OrderFile3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.EVAL2008\MSSQL\DATA\OrderFilegroup3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) TO FILEGROUP OrderFileGroup3
GO
CREATE PARTITION FUNCTION AnnualDateRange_PartitionFunction (datetime2)
AS RANGE RIGHT FOR VALUES (
'1/1/2009'
)
GO
CREATE PARTITION SCHEME AnnualDateRange_PartitionScheme
AS PARTITION AnnualDateRange_PartitionFunction TO (
OrderFilegroup1
,OrderFilegroup2
)
CREATE TABLE LargeOrder (
OrderPK int IDENTITY (1,1)
, OrderDate datetime2 CHECK (OrderDate > '1/1/2008' AND OrderDate IS NOT NULL)
, BigOrderData char (7000)
) ON AnnualDateRange_PartitionScheme (OrderDate)
GO
INSERT INTO LargeOrder (OrderDate, BigOrderData)
VALUES ('7/15/2008', NEWID()), ('7/15/2009', NEWID())
--Values are in the partitions we expect:
SELECT *
, $PARTITION.AnnualDateRange_PartitionFunction(OrderDate) AS PartitionNumber
FROM LargeOrder
ORDER BY OrderDate DESC
CREATE PARTITION FUNCTION Archive_PartitionFunction (datetime2)
AS RANGE RIGHT FOR VALUES (
'1/1/2008'
)
GO
CREATE PARTITION SCHEME Archive_PartitionScheme
AS PARTITION Archive_PartitionFunction TO (
OrderFileGroup3
,OrderFileGroup1
)
CREATE TABLE LargeOrderArchive (
OrderPK int IDENTITY (1,1)
, OrderDate datetime2
, BigOrderData char (7000)
) ON Archive_PartitionScheme (OrderDate)
GO
INSERT INTO LargeOrderArchive (OrderDate, BigOrderData)
VALUES ('7/15/2007', NEWID())
--Values are in the partition we expect:
SELECT *
, $PARTITION.AnnualDateRange_PartitionFunction(OrderDate) AS PartitionNumber
FROM LargeOrderArchive
ORDER BY OrderDate DESC
ALTER TABLE LargeOrder SWITCH PARTITION 1 TO LargeOrderArchive PARTITION 2
GO
--Bug here? Why is it all in partition 1? Shouldn't one row be in partition1 and one in partition2?
SELECT *
, $PARTITION.AnnualDateRange_PartitionFunction(OrderDate) AS PartitionNumber
FROM LargeOrderArchive
February 9, 2011 at 7:45 am
If there are no ideas, can anyone repo it (and does it look odd to you too)?
I admit this is on a vanilla (unupdated) SQL2K8 Eval edition, so I wouldn't be surprised if it was something specific to the build.
Thanks,
Chad
February 14, 2011 at 8:43 am
Have you done a query against the system tables to see if that's still the case?
SELECT T.name, P.* FROM sys.tables T
INNER JOIN sys.partitions P
ON T.object_id = P.object_id
This query will tell you how many rows are in each partition on each table. I haven't tried the $Partition function yet. I'll give it a try on my tables and see how it matches up.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 14, 2011 at 10:03 am
Chad, I'll give it a bash tomorrow on one of our SQL2008 R2 boxes. Got to rush home now, after all it is Valentine's day, so please bear with me.
February 14, 2011 at 10:15 am
I haven't had a chance to run the code and won't until after work today at the earliest, but from reading through it it looks like your SWITCH statement violates the partition parameters. You're trying to force a date of 2007 into a partition of 2008. Isn't what you want there
ALTER TABLE LargeOrder SWITCH PARTITION 1 TO LargeOrderArchive PARTITION 1
Again, my apologies if I'm misreading this, but I will try to run it tonight and get a more definite answer.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 14, 2011 at 10:39 am
Thanks Stefan and Jan, I appreciate your replies.
Stefan - I'll run the query against sys.partitions tonight - I left the laptop at home today (bad move), so I don't have it here with me. It's a good place to check, thanks for the suggestion.
LargeOrder partition 1 is constrained from 1/1/2008 (inclusive) to 1/1/2009 (exclusive) by the check constraint and the partition function working together.
LargeOrderArchive partition 2 is constrained to 1/1/2008 (inclusive) and later by the partition function, but is empty so will switch with LargeOrder partition 1 since the data coming in is more restricted in LargeOrder than required by LargeOrderArchive (ok - I'm way new to partitioning, but this is my understanding of how it works and the code ran, so I assume it's ok. I reserve the right to be completely and totally wrong :-D).
What I was trying to demo was a sliding window where every year you take the bottom partition out of LargeOrder and place it at the top of LargeOrderArchive (optionally merging along the way). One thing I did notice is that if I change it so that instead of using a check constraint I create an extra empty partition at the bottom of LargeOrder, then $PARTITION works right. I thought that was quite odd.
Thanks,
Chad
February 14, 2011 at 10:46 am
IMO you are fooling yourself by using the wrong PF with the correct table in your select query.
SELECT *
, $PARTITION.AnnualDateRange_PartitionFunction(OrderDate) AS PartitionNumber
, $PARTITION.Archive_PartitionFunction(OrderDate) AS PartitionNumberArch
FROM LargeOrderArchive
ORDER BY OrderDate DESC
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
February 14, 2011 at 10:49 am
ALZDBA (2/14/2011)
IMO you are fooling yourself by using the wrong PF with the correct table in your select query.
SELECT *
, $PARTITION.AnnualDateRange_PartitionFunction(OrderDate) AS PartitionNumber
, $PARTITION.Archive_PartitionFunction(OrderDate) AS PartitionNumberArch
FROM LargeOrderArchive
ORDER BY OrderDate DESC
Nice catch! I missed that on my readthrough.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 14, 2011 at 11:16 am
hooo boy. :sick:
I guess that's what code review is for, I think you nailed it. Thanks!
Thanks,
Chad
February 14, 2011 at 11:35 am
Actually you've posted a nice example on how partitioning with sliding windows can help out very much when trying to keep "current" tables small and not losing online data with your Archive tables, from an availability point of view.
The switch operation is very fast, so who will notice 😉
From recovery point of view, you'll have to keep in mind, your two partitioned table are tied together. You'll need to take that total volmune into account if you reuse filegroups for partitions.
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
February 14, 2011 at 11:43 am
Yes, I've noticed that. I'm not sure what "industry" does, but I'm guessing that frequently after doing the swap, you move the archive data to another database or to a new partition at least. You don't save any time on the move, but you do ease contention on the main table. And I guess if your archive table is partitioned in another database, once you've moved the data over there the switch is quick on that end too.
Thanks,
Chad
February 15, 2011 at 6:13 am
ALZDBA (2/14/2011)
IMO you are fooling yourself by using the wrong PF with the correct table in your select query.
SELECT *
, $PARTITION.AnnualDateRange_PartitionFunction(OrderDate) AS PartitionNumber
, $PARTITION.Archive_PartitionFunction(OrderDate) AS PartitionNumberArch
FROM LargeOrderArchive
ORDER BY OrderDate DESC
Really well spotted, ALZDBA. Hadn't seen that either.
February 15, 2011 at 6:27 am
In a clear moment you may be able to spot this kind of things .... if you have been bitten by it yourself frequently enough 😀
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply