SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate Sliding Window Partition Management: Part I


Automate Sliding Window Partition Management: Part I

Author
Message
Paul Brewer
Paul Brewer
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1304
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.
Hugh Scott
Hugh Scott
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 190
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



Del Lee
Del Lee
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1337
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
Hugh Scott
Hugh Scott
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 190
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



Del Lee
Del Lee
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1337
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
mark.johnston@pnl.gov
mark.johnston@pnl.gov
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!:-D

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



rrinehart77
rrinehart77
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 228
-- 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search