Partitioned Table question

[sparkbyte]

I have a table that we keep a rolling 14 day amount of data in. The daily delete remove approx 30K rows. This slow the server greatly during the delete. process runs at night.

What i was thinking was that i could make a partitioned table that keeps the data split.

Partition 1 is all rows <= 14 days
Partition 2 is all rows > 15 days

Created 2 file groups and to DB files to test with.

-- Create File Groups for Partitioning Data
ALTER DATABASE SQL_TEST ADD FILEGROUP WDSCRN;
go
ALTER DATABASE SQL_TEST ADD FILEGROUP WDSCRN_Archive;
Go

--  Create the Physical DB files for partitioning
ALTER DATABASE SQL_TEST
ADD FILE 
  (NAME = N'WDSCRN',
  FILENAME = N'D:\SQL\SQL_TEST_WDSCRN.ndf',
  SIZE = 5MB,
--  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [WDSCRN];
Go
ALTER DATABASE SQL_TEST
ADD FILE 
  (NAME = N'WDSCRN_Archive',
  FILENAME = N'D:\SQL\SQL_TEST_WDSCRN_Archive.ndf',
  SIZE = 5MB,
--  MAXSIZE = 100MB,
  FILEGROWTH = 5MB)
TO FILEGROUP [WDSCRN];
Go

Problem I have now is that all the date range examples partiion by specific date ranges. How would you partition by a rolling date range???


Thanks

John Fuhrman

 

[mrdenny]

Instead of messing around with partitioning (which will require that you have Enterprise Edition if you don't already have it) I would recommend that you just do your deletes in small batches.

select null

while @@rowcount <> 0
begin
     delete top (1000) from YourTable
     where DateColumn > dateadd(dd, -14, getdate())
end

I've used this exact code of tables that had the same 14 day rolling window. My table has 600 million records and I was deleting tens of millions of rows per day.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[sparkbyte]

Denny, why the "Select NULL" in your script?



Thanks

John Fuhrman

 

[gmmastros]

It forces @@RowCount to be 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom