Particionar a tabela do SQL Server por ano, período contínuo de 35 dias

No momento, o SQL Server não tem uma maneira de especificar declarativamente uma janela de partição deslizante. É necessário executar o T-SQL para remover o limite antigo e criar um novo. Um script T-SQL (talvez encapsulado em um procedimento armazenado) pode ser agendado diariamente para essa finalidade.

Geralmente, é melhor dividir e mesclar apenas partições vazias, mas isso não é possível no seu caso devido ao histórico de partições anuais, além da partição de janela de 35 dias na mesma tabela. Um método para fazer isso com relativa eficiência é empregar um esquema de partição, função e tabela de preparo idênticos. SPLITIsso permite que você opere na partição de janela de 35 dias sem movimentação excessiva de dados e registro que seriam necessários com MERGEpartições não vazias.

O script abaixo desliza a janela diária e move os dados para fora da janela ativa usando DELETE...OUTPUT(100 mil linhas diárias). O trabalho pesado para os dados dentro da janela ativa (cerca de 3,5 milhões de linhas) é feito com CREATE INDEX...WITH(DROP_EXISTING=ON). Eu esperaria que isso funcionasse bem dentro de sua janela de manutenção de 3 horas, provavelmente segundos ou minutos na pior das hipóteses.

Isso pressupõe que sua tabela e índices estejam alinhados. Abaixo está o exemplo DDL.

CREATE PARTITION FUNCTION RangePartFunction (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101'); 
CREATE PARTITION FUNCTION RangePartFunction_Staging (datetime2)
    AS RANGE RIGHT FOR VALUES ('20150101', '20160101', '20170101', '20180101');
CREATE PARTITION SCHEME RangePartScheme
    AS PARTITION RangePartFunction TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);
CREATE PARTITION SCHEME RangePartScheme_Staging
    AS PARTITION RangePartFunction_Staging TO ([PRIMARY], Part_2015, Part_2016, Part_2017, Part_2018);

--partition for last 35 days
DECLARE @Start35DayWindowDate datetime2 = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
ALTER PARTITION SCHEME RangePartScheme
    NEXT USED Part_35Day;
ALTER PARTITION SCHEME RangePartScheme_Staging
    NEXT USED Part_35Day;
ALTER PARTITION FUNCTION RangePartFunction()
    SPLIT RANGE(@Date);
ALTER PARTITION FUNCTION RangePartFunction_Staging()
    SPLIT RANGE(@Date);
GO
--example tables
CREATE TABLE dbo.Table1(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000) 
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1(Col2, Col1)  ON RangePartScheme(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1(Col2) ON RangePartScheme(Col1);
CREATE TABLE dbo.Table1_Staging(
      Col1 datetime2
    , Col2 int
    , Col3 char(1000) 
) ON RangePartScheme(Col1);
CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1)  ON RangePartScheme_Staging(Col1);
CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) ON RangePartScheme_Staging(Col1);
GO

Script de manutenção diária da partição (veja comentários embutidos):

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;

    DECLARE @Start35DayWindowDate datetime2  = CAST(DATEADD(day, -35, CAST(SYSDATETIME() AS date)) AS datetime2);
    DECLARE @LastBoundaryDate datetime2 = (
        SELECT MAX(CAST(value AS datetime2))
            FROM  sys.partition_functions AS pf
            JOIN sys.partition_range_values AS prv ON prv.function_id = pf.function_id
            WHERE 
                pf.name =   N'RangePartFunction'
        );

    IF @Start35DayWindowDate > @LastBoundaryDate
    BEGIN

        --switch last partition into staging table
        ALTER TABLE dbo.Table1
            SWITCH PARTITION $Partition.RangePartFunction(@LastBoundaryDate)
            TO dbo.Table1_Staging PARTITION $Partition.RangePartFunction_Staging(@LastBoundaryDate);
        --merge original function (with empty partition)
        ALTER PARTITION FUNCTION RangePartFunction()
            MERGE RANGE(@LastBoundaryDate);

        --create new yearly partition when the 35-day window passes the first of the year
        --NOTE: current year filegroup must be created and this script changed with the new filegroup name before February 6th of each year
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction()
                SPLIT RANGE(@LastBoundaryDate);
        END;

        --split original function for new 35-day window boundary
        ALTER PARTITION SCHEME RangePartScheme
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction()
            SPLIT RANGE(@Start35DayWindowDate);

        --rebuild staging table using original scheme
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme(Col1);

        --switch partition with new window from staging table back into original table
        ALTER TABLE dbo.Table1_Staging
            SWITCH PARTITION $Partition.RangePartFunction(@Start35DayWindowDate)
            TO dbo.Table1 PARTITION $Partition.RangePartFunction(@Start35DayWindowDate);

        --move remaining rows into original table historical yearly file group (single day older than 35 days)
        DELETE dbo.Table1_Staging
            OUTPUT deleted.* INTO dbo.Table1;

        --apply same changes to staging function, scheme, and table to prepare for next day
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            MERGE RANGE(@LastBoundaryDate);
        ALTER PARTITION SCHEME RangePartScheme_Staging
            NEXT USED Part_35Day;
        ALTER PARTITION FUNCTION RangePartFunction_Staging()
            SPLIT RANGE(@Start35DayWindowDate);
        IF @LastBoundaryDate = DATEADD(year, DATEDIFF(year, '', @LastBoundaryDate), '')
        BEGIN
            ALTER PARTITION SCHEME RangePartScheme_Staging
                NEXT USED Part_2019;
            ALTER PARTITION FUNCTION RangePartFunction_Staging()
                SPLIT RANGE(@LastBoundaryDate);
        END;
        CREATE CLUSTERED INDEX cdx ON dbo.Table1_Staging(Col2, Col1) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
        CREATE NONCLUSTERED INDEX idx ON dbo.Table1_Staging(Col2) WITH(DROP_EXISTING=ON) ON RangePartScheme_Staging(Col1);
    END;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO