Move your ledger history table to a different filegroup

[Guest PieterVanhove]

This article will guide you through the steps to move the ledger history table to a different filegroup in SQL Server 2022.

 

 

 

When you create an updatable ledger table, a history table is automatically created. The history table captures the historical values of rows changed because of updates and deletes in the updatable ledger table. The schema of the history table mirrors that of the updatable ledger table it's associated with. When you create an updatable ledger table, you can either specify the name of the schema to contain your history table and the name of the history table or you have the system generate the name of the history table and add it to the same schema as the ledger table. These are the only history table options that you can specify at creation time of your ledger table.

 

 

 

A lot of my customers asked me if it was possible to create the history table on a different filegroup to improve the performance. Many databases will work well with a single data file and a single transaction log file, but to maximize performance, you can put heavily accessed tables on different filegroups. Using different filegroups will improve performance, because of parallel I/O, if the files linked to the filegroups are located on different physical disks. A ledger history table can be one of those heavily accessed tables, especially when a lot of updates and deletes are executed on a ledger table.

 

 

 

As mentioned before, at creation time, you can’t specify any extra table options for the history table, but you can ALTER the history table once the ledger table is created. Creating the history table in advance with the same schema as the ledger table, like you can do with temporal history tables, is not supported for ledger tables.

 

 

 

Below I have described the different steps to move the history table to a different filegroup, once the ledger and the history table are created.

I restored the Microsoft sample database WideWorldImportersDW on a local SQL Server 2022 and added an extra filegroup History to the database. The filegroup is stored on my C-drive for demo purposes. For better performance, you should create one or multiple files on a separate disk.

 

 

 

USE [master]

GO

ALTER DATABASE WideWorldImportersDW ADD FILEGROUP [HISTORY]

GO

ALTER DATABASE WideWorldImportersDW ADD FILE (NAME = N'WideWorldImportersDW_History', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW_History.ndf', SIZE = 131072KB, FILEGROWTH = 131072KB)

TO FILEGROUP [HISTORY]

GO

 

 

 

Secondly, I’ve created a very basic updatable ledger table Balance and the history table BalanceHistory. In this case, the ledger and history table are both created in the PRIMARY filegroup.

 

 

 

CREATE SCHEMA [Account];

GO

CREATE TABLE [Account].[balance]

(

[CustomerID] INT NOT NULL PRIMARY KEY CLUSTERED,

[LastName] VARCHAR (50) NOT NULL,

[FirstName] VARCHAR (50) NOT NULL,

[balance] DECIMAL (10,2) NOT NULL

)

WITH

(

SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Account].[balanceHistory]),

LEDGER = ON

);

 

 

 

To move the history table to a new filegroup, you need to create a clustered index on the table on the new filegroup you want, in my case the History filegroup.

 

 

 

CREATE CLUSTERED INDEX CIX_CustomerID

ON [Account].[balanceHistory](CustomerID)

ON [HISTORY};

 

 

 

When you check the properties of the history table afterwards, you should see that the History filegroup is used.

 

 

 

 

 

Conclusion

 

 

Placing a history table on a filegroup other than the ledger table can improve the query performance and the database verification. Another advantage of using a separate filegroup is that you can implement table partitioning for the history table. You could also implement filegroup backups which can be useful in the case of very large history tables.

 

Continue reading...