Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We currently have a legacy webservice that stores each xml request/response in Sql Server. The data only needs to persist for 3 days before it is considered expired. Sql Server is not good at deleting rows since every delete forms part of the transaction log. The db currently grows at 6-10gb per day and this is going to increase. Only around 1% of the responses that are stored are ever recalled therefore this is a very write heavy application. Each request/response xml document can be upto 14k in size.

What storage mechanism would you choose for upto 50/100gb of data per day?

I understand the solution is not sustainable and I am really looking for a tactical fix since we cannot easily change how all our clients query and re-query the data. We could look into a db that has native support for TTL (Riak, Postgres) etc or maybe a file/blob s3/azure storage solution is a better fit? The issue with a cloud blob storage solution could be lookup performance if we had to scan multiple buckets (since buckets have capacity limits) especially compared to the current sql server single table lookup.

Open to ideas and suggestions?

share|improve this question
    
I'm unfamiliar with your problem domain: What is TTL? –  Colin 't Hart Oct 15 '13 at 12:36
    
time to live e.g redis.io/commands/ttl –  redsquare Oct 15 '13 at 12:38
1  
It seems SQL Server does support some form of partitioning. Maybe the latter examples on technet.microsoft.com/en-us/magazine/2007.03.partitioning.aspx help you? –  Colin 't Hart Oct 15 '13 at 12:48
1  
According to my reading of the article you can "SWITCH" partitions with tables and then simply drop the table. –  Colin 't Hart Oct 15 '13 at 12:59
1  
Are you deleting batches of rows every day, or do rows expire exactly after 3 days? Implementing either table partitioning or partitioned views will solve this if you're deleting every day. Either way, a properly designed clustered index on the table will go a long way. I'd like to look at the table schema. Using pre-insert compression will solve the space problem (at the expense of increased CPU). –  Jon Seigel Oct 15 '13 at 14:25

4 Answers 4

I have created a very simple demo of how partition switching might work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
IF OBJECT_ID('dbo.largeTable1') IS NOT NULL DROP TABLE dbo.largeTable1
IF EXISTS ( SELECT * FROM sys.partition_schemes WHERE name = 'ps_date' ) DROP PARTITION SCHEME ps_date
IF EXISTS ( SELECT * FROM sys.partition_functions WHERE name = 'pf_date' ) DROP PARTITION FUNCTION pf_date
GO

CREATE PARTITION FUNCTION pf_date (DATE) AS RANGE RIGHT FOR VALUES ( '1 Jan 2013', '1 Feb 2013', '1 Mar 2013', '1 Apr 2013', '1 May 2013', '1 Jun 2013', '1 Jul 2013', '1 Aug 2013', '1 Sep 2013', '1 Oct 2013', '1 Nov 2013', '1 Dec 2013' );
GO

-- !!TODO don't use ALL TO PRIMARY, instead create individual files and filegroups
CREATE PARTITION SCHEME ps_date AS PARTITION pf_date ALL TO ( [PRIMARY] )
GO

IF OBJECT_ID('dbo.largeTable') IS NULL
CREATE TABLE dbo.largeTable 
    ( 
    rowId INT IDENTITY, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    dateAdded DATE DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION,

    CONSTRAINT pk PRIMARY KEY(dateAdded, rowId) 
    ) ON [ps_date](dateAdded)
GO


CREATE TABLE dbo.largeTable1
    ( 
    rowId INT IDENTITY, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    dateAdded DATE DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION,

    CONSTRAINT pk2 PRIMARY KEY(dateAdded, rowId) 
    ) ON [PRIMARY]
GO


-- Create some dummy data
INSERT INTO dbo.largeTable DEFAULT VALUES
GO 5

-- Multiply the data a bit
INSERT INTO dbo.largeTable ( someData, dateAdded, addedBy ) 
SELECT someData, DATEADD( month, -2, dateAdded ), addedBy
FROM dbo.largeTable
UNION ALL
SELECT someData, DATEADD( month, -1, dateAdded ), addedBy
FROM dbo.largeTable 
UNION ALL
SELECT someData, DATEADD( month, 1, dateAdded ), addedBy
FROM dbo.largeTable
GO


-- Have a look at the data
SELECT 'before' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Switch out oldest partition with data and truncate it
ALTER TABLE dbo.largeTable SWITCH PARTITION 9 TO dbo.largeTable1
GO

TRUNCATE TABLE dbo.largeTable1
GO

SELECT 'after' s, $PARTITION.pf_date( dateAdded ) p, dateAdded, COUNT(*) AS records
FROM dbo.largeTable
GROUP BY dateAdded
GO

-- Merge the range as no longer required
ALTER PARTITION FUNCTION pf_date() MERGE RANGE ( '1 Sep 2013' );
GO

TRUNCATE TABLE can be a minimally logged operation under certain conditions. Please consult the Data Loading Performance Guide for a fuller treatment on the topic. There is also a section on "Deleting All Rows from a Partition or Table".

Good luck!

share|improve this answer
    
thats a great help thanks –  redsquare Oct 15 '13 at 17:12
    
quick q, can you query the partitions as one? Or have a got to query each partition in turn to 'find by id' –  redsquare Oct 15 '13 at 17:38
    
No you can query them as one. It's one of the strengths of partitioning. –  wBob Oct 15 '13 at 18:04
    
thanks again mate –  redsquare Oct 15 '13 at 18:15

I see that you looking for very quick turn around and partitioning might work well as long as you have Enterprise Version of SQL SERVER. just wanted to show the alternate "customized horizontally partitioned method". As you said there is not much time to rollout bigger changes to each client; in my view once tested internally this kind of only DB changes works fastest.

assume this the current system. and we are trying to come up with design that allows easy delete of expired data w/o performance impact on existing system.

    if object_id('mytesttable','u') is not null
        drop table mytesttable
    go
    create table mytesttable
    (
        id      int identity
        ,value  nvarchar(max)
        ,lastdt date default getdate()
    )
    go

    if object_id('pr_insert_mytesttable','p') is not null
        drop procedure pr_insert_mytesttable
    go
    create procedure pr_insert_mytesttable
    (
        @value nvarchar(max)
    )
    as
    begin
    begin try
    begin tran

        insert into mytesttable (value)
        select @value

    commit tran
    end try
    begin catch
        rollback tran
    end catch
    end
    go


    insert mytesttable (value,lastdt)
                select '<my data on 10-01-2013>','10-01-2013'
    union all   select '<my data on 10-02-2013>','10-02-2013'
    union all   select '<my data on 10-03-2013>','10-03-2013'
    union all   select '<my data on 10-04-2013>','10-04-2013'
    union all   select '<my data on 10-05-2013>','10-05-2013'
    union all   select '<my data on 10-06-2013>','10-06-2013'
    union all   select '<my data on 10-07-2013>','10-07-2013'
    union all   select '<my data on 10-08-2013>','10-08-2013'
    union all   select '<my data on 10-09-2013>','10-09-2013'
    union all   select '<my data on 10-010-2013>','10-10-2013'
    union all   select '<my data on 10-011-2013>','10-11-2013'
    union all   select '<my data on 10-012-2013>','10-12-2013'
    union all   select '<my data on 10-013-2013>','10-13-2013'
    union all   select '<my data on 10-014-2013>','10-14-2013'
    union all   select '<my data on 10-015-2013>','10-15-2013'

this is your current table looks like

Current table Structure

As you mentioned lets take the expiration threshold to 3 days. idea is create individual table that has max of 3 days of worth data. and create API layer that will figure out where to put incoming data w/o changing client side code. so end result will be 5 different table storing max of 3 days of data. API will create new tables as needed.

you will need one time existing data migration script; something like below. if you execute below script it will create new table and copy appropriate data from original table as shown in below picture.

    Declare @mindt date, @maxdt date, @sql nvarchar(max)='', @tblname nvarchar(200)='', @paradef nvarchar(1000)=''
    select @mindt = MIN(lastdt),@maxdt=MAX(lastdt) from mytesttable
    while @mindt <= @maxdt
    begin

        set @mindt = DATEADD(day,3,@mindt)
        set @tblname = 'mytesttable'+'_'+Replace(cast(@mindt as nvarchar(10)),'-','')

        set @sql = N'
                        if object_id('''+@tblname+N''',''u'') is not null
                            drop table '+@tblname+N'
                    '
        execute sp_executesql @sql  

        set @sql=   N'          
                        create table '+@tblname+N'
                        (
                            id      int identity
                            ,value  nvarchar(max)
                            ,lastdt date default getdate()
                        )
                    '
        execute sp_executesql @sql

        set @sql = N'
                        Delete from '+ @tblname+N'

                        ;set identity_insert '+ @tblname+N' ON
                        insert into '+@tblname+N' (id,value,lastdt)
                        select id,value,lastdt
                        from mytesttable
                        where lastdt < @mindt and lastdt >= DATEADD(day,-3,@mindt) 
                        ;set identity_insert '+ @tblname+N' OFF

                        --select * from '+ @tblname+N'
                    '
        set @paradef=N'@mindt date' 
        execute sp_executesql @sql,@paradef,@mindt
    end

New System

now what would the insert API will look like? this is a modified insert API that will look if table exists where this data fits or new tables needs to create.

    if object_id('pr_insert_mytesttable_modified','p') is not null
        drop procedure pr_insert_mytesttable_modified
    go
    create procedure pr_insert_mytesttable_modified
    (
        @value nvarchar(max)
    )
    as
    begin
    begin try
    begin tran
        declare  @sql nvarchar(MAX)=''
                ,@chktblname nvarchar(200)=''
                ,@inserttblname nvarchar(200)=''
                ,@today date=getdate()
                ,@paradef nvarchar(1000)=''

        set @inserttblname = 'mytesttable'+'_'+Replace(cast(@today as nvarchar(10)),'-','')
        set @chktblname = 'mytesttable'+'_'+Replace(cast(DATEADD(day,-3,@today) as nvarchar(10)),'-','')

        IF EXISTS (select top 1 1 from sys.sysobjects where name = @chktblname)
        BEGIN
            SET @inserttblname = @chktblname
        END
        ELSE
        BEGIN
            set @sql = N'
                            if object_id('''+@inserttblname+N''',''u'') is not null
                                drop table '+@inserttblname+N'
                        '
            execute sp_executesql @sql  
            set @sql=   N'          
                            create table '+@inserttblname+N'
                            (
                                id      int identity
                                ,value  nvarchar(max)
                                ,lastdt date default getdate()
                            )
                        '
            execute sp_executesql @sql
        END


        set @sql = N'
                        insert into '+@inserttblname+N' (value)
                        select @value
                    '
        set @paradef=N'@value NVARCHAR(MAX)'    
        execute sp_executesql @sql,@paradef,@value

    commit tran
    end try
    begin catch
        rollback tran
    end catch
    end
    go

so why to go over this dynamic code generation?

this does not require Enterprise version.

the code generation is not that complex as it looks. its one time creation only.

A simple DROP TABLE API will do what you need for fastest delete.

At any given time select is not looking across more than 3 days of worth data in you case.

As you drop entire tables no issue to deal with after Delete/Truncate like fragmentation.

share|improve this answer

Unless I'm oversimplifying this, you could create a dedicated file group for the table in question, perhaps on an SSD for performance. Assuming you are expecting to remove approximately the same number of rows as are being added, you only need enough room for 3 days worth of data (perhaps I'd go with enough room for 2 weeks worth just for wiggle room).

[Edit] - Since you are concerned about transaction log entries from all the deletes, instead of putting this table into its own filegroup, I would consider putting the table into its own database will SIMPLE recovery. The original database could then have a simple view that points to the table in the new database. Everything else in my answer stays the same. [/Edit]

Rows that are removed from the table will make room for new rows added to the table. As long as the table has a clustering key that increments with each new row, something like an IDENTITY(1,1() for the primary key, the table will act in a similar manner to a log file in that it will reuse the available space in the filegroup.

To prove this, I've created a simple demo that INSERTS and DELETES many rows repeatedly. sys.dm_db_database_page_allocations is inspected to see if pages are accumulating or being reused. In my test, 3 pages were allocated and constantly reused. For a mostly-write environment like yours, this should work very well. Storing the file group on an SSD will also allow the occasional read query to be very responsive, assuming proper indexing.

CREATE TABLE RolloverTest
(
    ID INT NOT NULL 
                 CONSTRAINT PK_RolloverTest PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData VARCHAR(255) NOT NULL 
                 CONSTRAINT DF_RolloverTest_SomeData DEFAULT (NEWID())
);

DECLARE @LoopCount INT = 0;
DECLARE @MaxLoops INT = 10000;
DECLARE @ILoopCount INT = 0;
DECLARE @IMaxLoops INT = 100;
DECLARE @AllocatedPages INT = 0;
SELECT @AllocatedPages = COUNT(allocated_page_page_id) 
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('RolloverTest'), 
            NULL, NULL, 'LIMITED');
WHILE @LoopCount < @MaxLoops
BEGIN
    SET @ILoopCount = 0;
    WHILE @ILoopCount < @IMaxLoops
    BEGIN
        DELETE FROM RolloverTest WHERE ID = (SELECT MIN(ID) FROM 
                        RolloverTest);
        SET @ILoopCount = @ILoopCount + 1;
    END
    SET @ILoopCount = 0;
    WHILE @ILoopCount < @IMaxLoops
    BEGIN
        INSERT INTO RolloverTest DEFAULT VALUES;
        SET @ILoopCount = @ILoopCount + 1;
    END
    SET @LoopCount = @LoopCount + 1;
END
SELECT * FROM RolloverTest;
SELECT COUNT(allocated_page_page_id) - @AllocatedPages AS NewPagesAllocated
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('RolloverTest'), 
            NULL, NULL, 'LIMITED');

To remove rows from the table, simply run a SQL Agent Job every 15 minutes that removes rows that are older than 72 hours, or 3 days, etc, depending on the date data stored in the table.

share|improve this answer
    
wow, thanks for the detailed advice and response. If we are writing thousands of rows a minute is it possible the delete job would cause contention though? –  redsquare Oct 16 '13 at 10:37
    
using an SSD would help reduce contention since they provide such a high I/O rate. Also, if you configured the database to use row versioning, that should reduce contention. –  Max Vernon Oct 16 '13 at 15:53

I would investigate the use of Partitioned tables to support what you are after.

Create two tables: - Partitioned table (partitioned on Date, possibly multiple partitions per day) - Non Partitioned table (identical schema to your partitioned table)

You would then:

  1. Write to your partitioned table as normal (by having multiple partitions in a single day you can optimize to support your write actions)
  2. Switch out your old partitions with minimal log actions/blocking (you'll be switching out partitions that are old and no longer being written to, thereby not blocking the write actions)
    • Loop through each of your old partitions
      1. Switch your partition to the non partitioned table
      2. Truncate your non partitioned table
      3. Next partition
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.