Introduction

As a SQL Server DBA, one of the biggest challenges I’ve faced is dealing with rapidly growing history tables. I’ve seen them balloon from a few gigabytes to terabytes in size, causing all sorts of performance headaches. But at the same time, the application teams are often hesitant to archive old data due to potential requests from other teams who may need it. In this article, I’ll share my experiences and advice on how to strike the right balance between optimizing database performance and meeting data retention requirements.

Why Archiving Data is Crucial

When history tables grow unchecked, it can lead to some major problems:

1. Slower queries – As tables get bigger, queries take longer to scan through all that data. This can cause unacceptable delays for end users.
2. Increased storage costs – Terabytes of data means you need a lot more disk space, which gets expensive fast.
3. Harder maintenance – Key maintenance tasks like index rebuilds, statistics updates, and backups all take much longer on huge tables.
4. Compliance issues – Some data may need to be retained for a certain time period for regulatory or legal reasons. Archiving lets you keep that data accessible without impacting production performance.

I once worked on a system where a key table had grown to over 5 billion rows. Queries that used to take seconds were timing out after 10 minutes. It was a nightmare! We had to take the application offline for hours to move old data out and rebuild the indexes. Lesson learned – don’t let it get to that point.

Strategies for Smart Archiving

So what’s the solution? The key is to implement an archiving strategy early, before the problem gets out of hand. Here are some best practices I recommend:

• Define clear data retention policies with the business. Know exactly what data needs to be kept and for how long.
• Archive data to separate history tables on a regular schedule, like monthly or quarterly. This keeps the active tables manageable.
• Consider moving archived data to cheaper, slower storage since it’s accessed less frequently. Options include table/index partitioning, filegroups on slower disks, or even Azure SQL Database elastic pools.
• Make sure your archiving process is fully automated using scripts or tools. You don’t want to be doing this manually!
• Have a plan for providing archived data to other teams in a reasonable timeframe. Could you restore a backup to a separate server for them to query?
• For super old data, consider aggregating it before archiving so you still have the important summary info without all the details. For example, sales data could be aggregated from individual transactions to daily or monthly totals.

Here’s an example of how you could implement a basic archiving process using T-SQL:

First, let’s say we have a Sales table that keeps growing over time:

CREATE TABLE Sales (
    SalesID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    SalesDate datetime NOT NULL,
    Amount decimal(10,2) NOT NULL
);

We can create a separate SalesHistory table with the same structure to hold the archived data:

CREATE TABLE SalesHistory (
    SalesID int NOT NULL,
    CustomerID int NOT NULL,
    SalesDate datetime NOT NULL,
    Amount decimal(10,2) NOT NULL
);

Then, we can use a T-SQL script to periodically move old data from Sales to SalesHistory. For example, to archive sales older than 1 year:

INSERT INTO SalesHistory
SELECT * FROM Sales
WHERE SalesDate < DATEADD(year, -1, GETDATE());

DELETE FROM Sales
WHERE SalesDate < DATEADD(year, -1, GETDATE());

If you need to aggregate old data before archiving to save space, you could use queries like:

INSERT INTO SalesHistoryAggregated
SELECT
    CAST(SalesDate AS date) AS SalesDate,
    SUM(Amount) AS TotalAmount
FROM SalesHistory
GROUP BY CAST(SalesDate AS date);

TRUNCATE TABLE SalesHistory;

This aggregates the individual sales transactions into daily totals before archiving.

Performance Impact

When you run the INSERT INTO ... SELECT query to move old data from the Sales table to the SalesHistory table, there are several potential performance impacts to consider:

1. Transaction Log Growth:
2. Locking and Blocking:
3. I/O Operations:
4. Indexes on the Target Table:
5. Transaction Duration:

To mitigate these performance impacts, consider the following strategies:

• Break the archiving process into smaller batches to reduce transaction duration and lock contention.
• Perform the archiving during off-peak hours or maintenance windows to minimize impact on concurrent transactions.
• Ensure that the necessary indexes are in place on the Sales table to optimize the WHERE clause filtering.
• Consider using partition switching (as mentioned in the previous example) for faster data movement if the tables are partitioned.
• Monitor the transaction log growth and ensure sufficient log space is available.
• Test the archiving process in a non-production environment to assess its performance impact and optimize accordingly.

Impact on tempdb Space

When you execute the INSERT INTO ... SELECT query to move data from the Sales table to the SalesHistory table, SQL Server may utilize the tempdb system database for various operations. The tempdb database is used for temporary storage and is heavily utilized by SQL Server for tasks such as temporary tables, work tables, sort operations, and row versioning.

Here are a few scenarios where the archiving process can impact tempdb space:

1. Sort Operations:
2. Temporary Tables:
3. Row Versioning:

To mitigate the impact on tempdb space, consider the following strategies:

1. Monitor tempdb Space Usage:
2. Optimize Query Performance:
3. Adjust tempdb Configuration:
4. Schedule Archiving During Off-Peak Hours:

By monitoring and managing tempdb space usage, optimizing query performance, and properly configuring tempdb, you can mitigate the impact of the archiving process on tempdb and ensure smooth operation of your SQL Server environment.

To check the database log size with free space and the tempdb size with free space, you can use the following T-SQL queries:

-- Database Log Size and Free Space
SELECT 
    DB_NAME() AS DatabaseName,
    CAST(total_log_size_mb AS DECIMAL(10,2)) AS TotalLogSizeMB,
    CAST(used_log_space_mb AS DECIMAL(10,2)) AS UsedLogSpaceMB,
    CAST(used_log_space_mb / total_log_size_mb * 100 AS DECIMAL(5,2)) AS UsedLogSpacePercentage,
    CAST(log_space_percent_used AS DECIMAL(5,2)) AS LogSpacePercentUsed
FROM 
    (SELECT 
        DB_ID() AS database_id,
        CAST(total_log_size_in_bytes / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS total_log_size_mb,
        CAST(used_log_space_in_bytes / 1024.0 / 1024.0 AS DECIMAL(10,2)) AS used_log_space_mb,
        used_log_space_in_percent AS log_space_percent_used
    FROM sys.dm_db_log_space_usage) AS log_space_usage

UNION ALL

-- tempdb Size and Free Space
SELECT 
    'tempdb' AS DatabaseName,
    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS TotalSizeMB,
    CAST(SUM(CASE WHEN is_percent_growth = 0 THEN size ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10,2)) AS DataSizeMB,
    CAST(SUM(CASE WHEN is_percent_growth = 1 THEN size ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10,2)) AS LogSizeMB,
    CAST(SUM(CASE WHEN state = 0 THEN size ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10,2)) AS FreeSpaceMB
FROM 
    tempdb.sys.database_files;

Table Partitioning

For even better performance on large tables, we could use SQL Server’s partitioning feature.

Let’s say we partition the Sales table by SalesDate:

CREATE PARTITION FUNCTION pf_SalesDate (datetime)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01');

CREATE PARTITION SCHEME ps_SalesDate
AS PARTITION pf_SalesDate ALL TO ([PRIMARY]);

CREATE TABLE Sales (
    SalesID int IDENTITY(1,1),
    CustomerID int NOT NULL,
    SalesDate datetime NOT NULL,
    Amount decimal(10,2) NOT NULL
) ON ps_SalesDate(SalesDate);

Now, archiving data is as simple as switching out the old partition:

ALTER TABLE Sales SWITCH PARTITION 1 TO SalesHistory;

This is much faster than moving rows individually.

Partition Performance Considerations

When you run the ALTER TABLE ... SWITCH PARTITION statement to switch a partition from the Sales table to the SalesHistory table, there are several performance considerations:

1. Metadata Operation:
2. Locking and Blocking:
3. Partition Alignment:
4. Indexing and Constraints:
5. Partition Function and Scheme Design:
6. Storage and I/O:

It’s important to note that the actual performance impact of the ALTER TABLE ... SWITCH PARTITION statement may vary depending on factors such as the size of the tables, the number of partitions, the concurrent workload, and the overall system configuration.

Conclusion

Archiving SQL Server data is all about striking the right balance. You need to keep your active tables and indexes lean and fast, while still meeting the business requirements for data retention and accessibility. It takes some planning and effort to set up a good archiving strategy, but it’s so worth it to avoid those 2am emergency downtimes due to an overgrown history table bringing your application to its knees. Trust me on that one!

The key is to be proactive, have clear retention policies defined upfront, automate the archival process, and work closely with your app teams and business users to make sure everyone’s needs are met. Do that and you’ll be able to enjoy the benefits of a high-performance database for years to come.