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 «««123

Automate Sliding Window Partition Management: Part I Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 3:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 90, Visits: 1,070
I think there may be a bug in the SQL Server views sys.data_spaces and sys.destination_data_spaces when you run the switch out/merge statements in certain circumstances.

http://www.sqlservercentral.com/Forums/Topic1548437-2799-1.aspx

The problem is documented in the forum thread above with a reproduction, I'm not 100% sure but it doesn't seem to be working correctly.
Post #1548643
Posted Friday, March 7, 2014 6:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 26, 2015 6:54 AM
Points: 401, Visits: 178
Yikes! This was a looooong time ago.

For what it's worth, we used this process in more or less the format presented (in this article and the next two articles) for four years with no troubles. The application (and associated data mart) were retired last year when we converted over everything to a new system. Unfortunately, no one scoped out reporting or BI for the new system, so we're back to running direct queries on the operational database.

The more things change, the more they stay the same.

Regards,

Hugh



Post #1548717
Posted Friday, March 7, 2014 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 240, Visits: 496
The first paragraph seems to imply that partitioning will improve the performance of deletes (while purging data). How does partitioning help with that?



Del Lee
Post #1548739
Posted Friday, March 7, 2014 7:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 26, 2015 6:54 AM
Points: 401, Visits: 178
Del Lee (3/7/2014)
The first paragraph seems to imply that partitioning will improve the performance of deletes (while purging data). How does partitioning help with that?


Instead of deleting the data by individual rows or by chunks of rows (dml), I can remove an entire month of data using a series of non-logged ddl operations. There's an overview of partitioning (SQL 2008 R2) here: http://technet.microsoft.com/en-us/library/ms190787(v=sql.105).aspx.

Regards,

Hugh Scott



Post #1548746
Posted Friday, March 7, 2014 9:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 240, Visits: 496
I've read the link you gave before and am generally familiar with partitioning. It doesn't really indicate go into how you can purge records using non logged ddl operations. I don't really see a partition option in the TRUNCATE TABLE command, and I would assume the DROP PARTITION FUNCTION command doesn't affect the data itself (just removes the partitioning). So, I'm still not following you on how you are purging the records. Can you be a little more specific about the series of non-logged ddl operations?



Del Lee
Post #1548783
Posted Friday, March 7, 2014 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:30 PM
Points: 1, Visits: 37
amenjonathan (12/16/2010)
Chris Hamam (12/15/2010)
Hugh Scott (12/14/2010)
amenjonathan (12/14/2010)

Anybody have any idea what happens when you do not add the partitioned column to an index? Does it still create an aligned index (by secretly adding it anyway)?


Nope. The index gets created on the default file group. Not that I would know from experience!

Regards,

Hugh



From vague memory and recollections of BoL, the partitioned column is added (as an included column) to a non-clustered index only if there is no clustered index... although please don't quote me on this!


I think this is impossible actually. You must have a clustered index on the partitioned column in order to create a partitioned table. But in the same thread, every non-clustered index contains the clustered index as a reference, unless I'm remembering incorrectly. A non-clustered index on a heap table...not sure what the heck is going on in that scenario. haha!


FYI, you can partition a table that is stored as a heap (i.e. no clustered index). Partitioning a table in place which contains existing data, however, is much easier done by creating a partitioned clustered index. Rebuilding the clustered index on a new partition scheme (or no partition scheme) with DROP_EXISTING allows the partition scheme to be changed in only the time it takes to build a clustered index on the table.

The partition column must be part of the clustered index definition. It is optional for non-clustered indexes that use a partition scheme, but is added automatically as an included column, I believe.

Best,

--Mark



Post #1548850
Posted Tuesday, April 1, 2014 9:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 2:09 PM
Points: 3, Visits: 182
-- Create Sample Partitioned Table
CREATE TABLE OrderEvents (
OrderCloseDate datetime not null,
OrderNum int not null,
[Status] char(2) null,
StatusDate datetime not null)
ON ps_FACT_DATA_DATE (MyDate)
GO

Msg 1911, Level 16, State 1, Line 2
Column name 'MyDate' does not exist in the target table or view.

Should the MyDate in the partitionscheme call be OrderCloseDate? or is the partitiondb.sql script including missing something else? I get an error running this portion of the script and figured it was because this column isn't part of the table so it can't partition on it.

Thanks for looking,

-Ryan
Post #1557303
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse