Database Reference
In-Depth Information
Implementing Sliding Window Scenario and Data Purge
OLTP systems often have the requirement of keeping data for a specific time. For example, an Order Entry system
could keep orders for a year and have the process, which is running the first day of the every month, to delete older
orders. With this implementation, called a
sliding window
scenario, you have a window on the data that
slides
and
purges the oldest data, based on a given schedule.
The only way to implement a sliding window scenario with non-partitioned data is by purging the data with
DELETE
statements. This approach introduces huge I/O and transaction log overhead. Moreover, it could contribute to
concurrency and blocking issues in the system. Fortunately, data partitioning dramatically simplifies this task, making
purge a metadata-only operation.
When you implement a sliding window scenario, you usually partition the data based on the purge interval. Even
though it is not a requirement, it helps you to keep the purge process on a metadata level. As an example, in the Order
Entry system described above, you could partition the data on a monthly basis.
In the case of partitioned views, the purge process is simple. You need to drop the oldest table, create another
table for the
next partition period
data, and then recreate the partitioned view. It is essential to have the
next partition
period data
table predefined to make sure that there is always a place where the data can be inserted.
Partitioned table implementation is similar. You can purge old data by switching the corresponding partition to a
temporary table, which you can truncate afterwards. For the next month's data, you need to use the split partition function.
There is the catch, though. In order to keep the operation on a metadata level and reduce time that the schema
modification (SCH-M) lock is held, you should keep the rightmost partition empty. This prevents SQL Server from
moving data during the split process, which can be very time consuming in the case of large tables.
■
even metadata-level partition switch can lead to locking and blocking in very active oLtp systems. SQL Server
2014 introduces the concept of low-priority locks, which can be used to improve system concurrency during such
operations. We will discuss low-priority locks in detail in Chapter 23, “Schema Locks.”
Note
Let's look at an example, assuming that it is now June 2014 and the purge process will run on July 1st. As you can
see in Listing 15-27, the partition function
pfOrderData
has boundary values of 2014-07-01 and 2014-08-01. Those
values predefine two partitions: one for the July 2014 data and an empty rightmost partition that you would split
during the purge process.
Listing 15-27.
Sliding Window scenario: Object creation
create partition function pfOrderData(datetime2(0))
as range right for values
('2013-07-01','2013-08-01','2013-09-01','2013-10-01'
,'2013-11-01','2013-12-01','2014-01-01','2014-02-01'
,'2014-03-01','2014-04-01','2014-05-01','2014-06-01'
,'2014-07-01','2014-08-01' /* One extra empty partition */
);
create partition scheme psOrderData
as partition pfOrderData
all to ([FG1]);
create table dbo.OrderData
(
OrderId int not null,
OrderDate datetime2(0) not null,
