Solved

automated sliding window (times 3)

Posted on 2010-08-13
2
390 Views
Last Modified: 2012-05-10
SQL v2008, horizontally partitioned order message database, basically five tables.  The data is never purged, partitions necessary to ease maintenance, among other things.  Each table has its own function/scheme. All defined the same, RANGE RIGHT on datetime2 key, the partitions are quarterly -- to align with the fiscal business quarters:    
Qtr1  1/1 - 3/31,   Qtr2  4/1 - 6/30,   Qtr3  7/1 - 9/30,   Qtr4 10/1 - 12/31

These are my partitions:    OLD, Qtr4, Qtr3, Qtr2, Qtr1, Current Week
(OLD = anything outside of current business year, the current week is the hottest, I want to isolate to its own partition)

Everything has come together very well, i am near ready, but I'm having a heck of a time automating my sliding windowS.  I've got two, possibly three cases to account for:

1.  At the end of every week, merge the previous week into the current quarter, then split out a
new partition for the new week.  Thus, moving data from the previous week to current quarter
filegroup, and create the new week on the current week filegroup.

2. When the current week is also the current quarter boundary, I need to be sure it's on the correct filegroup, so i don’t have to move data more than once.  The first week of the quarter I'd create the current week partition on the new quarter filegroup, rather than the week filegroup.  The following week would be the similar to other weeks where I split out the new week, but I would then need to skip the previous week MERGE operation so I'm back to 6 partitions.

3. I'm unsure, but I am wondering if for the normal change of quarters, aside from #2, need I also perform a merge/split as I am doing at the end of each week, in item #1?

I've put the procedure below together for my weekly job, but I was just targeting one table.  Now that it's there, I'm trying to figure out how to pass Scheme AND Function in there, such that I could use the two as parms, and use one procedure, to handle all tables.

That's question number 1.  If anyone has time and expertise, I've got some more.....  ;-)

Thank you in advance.
/****** Object:  StoredProcedure [dbo].[usp_MergeSplitWeekly]    Script Date: 08/12/2010 14:30:26 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





CREATE PROCEDURE [dbo].[usp_MergeSplitWeekly] 

AS

SET NOCOUNT ON;

/* Managing the RANGE RIGHT partition.

At the end of every week, we merge the previous week into the current quarter, then split out a 

new partition for the new week.  This will move the data from the previous week onto the current quarter 

filegroup and create the new week on the current week filegroup. 

EXEC dbo.usp_MergeSplitWeekly

*/

BEGIN

	DECLARE @week DATETIME2,

		@next DATETIME2



		--get current partition date boundary

		SELECT @week =   CAST(MAX(prv.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 = 'tablenamePF';



		--get new partition range for next week

		SET @next = DATEADD(day,7,@week)



		ALTER PARTITION SCHEME tablenamePS

		NEXT USED [Current_Week];

		

		ALTER PARTITION FUNCTION tablenamePF()

		MERGE RANGE (@week);

		

		ALTER PARTITION FUNCTION tablenamePF()

		SPLIT RANGE (@next);	

		

		IF @@ERROR <> 0

		BEGIN

			RAISERROR('Failure to perform the weekly merge/split.',16,-1)

			RETURN;

		END



END



SET NOCOUNT OFF;







GO

Open in new window

0
Comment
Question by:dbaSQL
  • 2
2 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 33429157
Actually, in the post above, the only parm I need is for the function (tablenamePF).  The 'Current_Week' will always be the NEXT USED in the weekly merge/split.

Any advice?
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 0 total points
ID: 33430601
I got the weekly thing together.  Probably lacking a bit, but it works.  I'll figure out the rest of it.
/****** Object:  StoredProcedure [dbo].[usp_MergeSplitWeekly]    Script Date: 08/12/2010 14:30:26 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





ALTER PROCEDURE [dbo].[usp_MergeSplitWeekly] (

 @pfName VARCHAR(25),

 @psName VARCHAR(25),

 @debug bit = 0

)

AS

SET NOCOUNT ON;

/* Managing the RANGE RIGHT partition.

At the end of every week, we merge the previous week into the current quarter, then split out a 

new partition for the new week.  This will move the data from the previous week onto the current quarter 

filegroup and create the new week on the current week filegroup. 

EXEC dbo.usp_MergeSplitWeekly @pfName = 'tablenamePF',@psName = 'tablenamePS',@debug = 1

*/

BEGIN

      DECLARE @sql1 NVARCHAR(2000),

              @sql2 NVARCHAR(200),

              @sql3 NVARCHAR(4000)



	--get current partition date boundary

	SET @sql1 = N'

	DECLARE @week DATETIME2

	DECLARE @next DATETIME2



	SELECT @week = 

	    CAST(MAX(prv.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 = '''+@pfName+''';

            '

            

      --get new partition range for next week

      SET @sql2 = N'

      SET @next = DATEADD(day,7,@week);

      

      '

      

      SET @sql3 = N'

	    ALTER PARTITION SCHEME '+@psName+'

            NEXT USED [Current_Week];

            

            ALTER PARTITION FUNCTION '+@pfName+'()

            MERGE RANGE (@week);

            

            ALTER PARTITION FUNCTION '+@pfName+'()

            SPLIT RANGE (@next);    '



	IF(@debug = 1)

	BEGIN

		PRINT (@sql1 + @sql2 + @sql3)

	END

	ELSE

		EXEC (@sql1 + @sql2 + @sql3)



END



SET NOCOUNT OFF;







GO

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 50
Group by and order by clause 28 36
BULK LOGGED - log full 9 15
sql calculate averages 18 30
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now