SQL SERVER — truncate partition performance
Today I show you three examples of deleting rows and it’s performance. I will compare deleting rows on non-partitioned table and truncating partition on partitioned table.
Our example is a table containing 0.75 billion records. [Date] range is 2000–01–01 to 2020–12–31.
First example (DELETE) — non-clustered index on [Date] column and clustered on [Station] and [Alpha2] columns:
DELETE FROM [dbo].[GHCN_RSI]
WHERE [Date] BETWEEN ‘2020–01–01’ AND ‘2020–12–31’
33.561.170 records has been deleted in 7:59 min.
Second (DELETE) — clustered index on [Date] column, no additional indexes:
33.561.170 records has been deleted in 00:27 min.
Third example (TRUNCATE) — clustered index on [Date] column, partitioned table:
Let’s create partition function. One partition for each year.
CREATE PARTITION FUNCTION udf_PartitionYear(date)
AS RANGE RIGHT
Partition schema assigned to partition function:
CREATE PARTITION SCHEME ups_PartionYear
AS PARTITION udf_PartitionYear
ALL TO ([PRIMARY])
Index assigned to partition schema:
CREATE CLUSTERED INDEX IX_GHCN_PARTITION_Date
ON dbo.GHCN_PARTITION ([Date])
ON ups_PartionYear ([Date])
We can check created partitions.
SELECT partition_id, partition_number, used_page_count, row_count FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID(‘dbo.GHCN_PARTITION’)
Let’s check where is the data in a range of year 2020.
WHERE function_id = 65536
Now, we can truncate partition.
Operation were finished instantaneous.