How to partition large database tables on your MU Online server

MU Online servers that run for months accumulate millions of rows in log tables, item history, and account activity — and it's common to see the admin panel, the ranking, or even the GameServer itself freeze because a simple query has to scan a table with 40 million rows. Table partitioning is the database technique that solves this problem: it physically splits a large table into smaller pieces (partitions) without changing how the application sees the table. In this tutorial you'll learn what to partition, how to create a partition function and partition scheme in SQL Server, how to migrate an existing table without losing data, and how to maintain partitions over time with a maintenance routine (sliding window).

Why MU Online tables grow so fast

MuServer logs practically every relevant event: login/logout, item trades, Chaos Machine use, chat, PK, boss drops, cash shop purchases. On a server with 500 concurrent players, the LogRecords table (or your emulator's equivalent) can pass 100,000 rows per day. After six months that's 18 million rows in a single table with no physical segmentation at all — every SELECT filtered by date does a full table scan, even with an index, because the optimizer still has to decide which pages to read within a monolithic structure.

Diagnosing the problem before partitioning

Before you go create partitions, confirm the bottleneck is really data volume. Run the execution plan for the slowest queries on the admin panel and the ranking and check whether a Table Scan or Clustered Index Scan shows up instead of a Seek. Also check the table's physical size with sp_spaceused 'LogRecords' and the production response time with SET STATISTICS TIME ON. If the bottleneck is a missing index, fix that first — partitioning a poorly indexed table just trades one problem for a bigger, harder-to-reverse one.

Choosing the right partition key

The practical rule: the chosen column needs to appear in the WHERE clause of most of the queries that are currently slow. Partitioning by a column nobody filters on produces zero partition elimination — SQL Server keeps scanning every partition.

Creating the partition function and partition scheme

In SQL Server, native partitioning uses two pieces: the partition function, which defines the boundaries, and the partition scheme, which maps each range to a physical filegroup.

-- Monthly partition function (RANGE RIGHT: the boundary belongs to the next partition)
CREATE PARTITION FUNCTION PF_LogPorMes (datetime)
AS RANGE RIGHT FOR VALUES (
    '2026-01-01', '2026-02-01', '2026-03-01',
    '2026-04-01', '2026-05-01', '2026-06-01'
);

-- Partition scheme mapping each range to a filegroup
CREATE PARTITION SCHEME PS_LogPorMes
AS PARTITION PF_LogPorMes
ALL TO ([PRIMARY]);

Using ALL TO ([PRIMARY]) simplifies the start, throwing every partition into the default filegroup. On larger servers, it's worth creating one filegroup per partition so you can back up/restore old partitions incrementally on their own.

Migrating an existing table to the new scheme

You can't turn a regular table into a partitioned one with a simple ALTER TABLE once it already has data and keys. The safe path is:

-- 1. Create the new table, already partitioned, with the same structure
SELECT * INTO LogRecords_New FROM LogRecords WHERE 1 = 0;

-- 2. Recreate the clustered index over the partition scheme
CREATE CLUSTERED INDEX CIX_LogRecords_New
ON LogRecords_New (CreateDate)
ON PS_LogPorMes (CreateDate);

-- 3. Copy the data in batches (avoids locking production)
INSERT INTO LogRecords_New
SELECT TOP (100000) * FROM LogRecords
ORDER BY CreateDate;
-- repeat in batches until exhausted

-- 4. Swap the names inside a short maintenance window
EXEC sp_rename 'LogRecords', 'LogRecords_Old';
EXEC sp_rename 'LogRecords_New', 'LogRecords';

Do the batch copy outside peak hours (late night, when fewer players are online) and only run the name swap once the gap between the two tables is small enough to copy in seconds.

Sliding window: keeping only the history you need

The biggest practical advantage of partitioning log tables is the sliding window: instead of running a DELETE on millions of old rows (a slow operation that generates a huge amount of transaction log), you SWITCH the entire partition into a staging table and then truncate that table — an almost instant operation.

-- Move the oldest partition into an empty table of the same schema
ALTER TABLE LogRecords SWITCH PARTITION 1 TO LogRecords_Staging;
TRUNCATE TABLE LogRecords_Staging;

-- Then create the next boundary for the upcoming month
ALTER PARTITION FUNCTION PF_LogPorMes()
SPLIT RANGE ('2026-07-01');

Schedule this routine as a monthly job in SQL Server Agent, aligned with whatever retention policy you decide on (for example, keeping 6 months of detailed log and archiving the rest).

Validating the gain with partition elimination

After partitioning, confirm that queries actually use the right partition instead of scanning everything. Enable the execution plan and look for the Actual Partition Count attribute on the scan operator — it should show 1 (or very few partitions), not the total. If the number of partitions accessed equals the total, the query isn't filtering on the partition key and the gain is zero.

SET STATISTICS IO ON;
SELECT COUNT(*) FROM LogRecords
WHERE CreateDate >= '2026-06-01' AND CreateDate < '2026-07-01';

Compare logical reads before and after — the reduction is usually an order of magnitude on large tables.

Impact on backups and index maintenance

Partitioned tables let you reindex only the "hot" partitions (the most recent ones, with more fragmentation from constant inserts) instead of rebuilding the entire index every night. This drastically shrinks the maintenance window on servers with tables in the tens of gigabytes.

Watching out for foreign keys and replication

If the partitioned table has foreign keys referencing, or being referenced by, other MuServer tables (for example, Character referencing AccountCharacter), confirm the partition key doesn't break referential integrity — in SQL Server, foreign keys work normally with partitioned tables, but migration planning needs to account for the creation order and the downtime window of each dependent table. In environments with replication (for example, a read database for the site/ranking), test the migration on the replica environment first.

Monitoring partition growth

Once deployed, periodically monitor the size of each partition to confirm the distribution stays balanced — a disproportionately large partition (for example, a month with a special event that generated excessive logging) can bring back isolated slowdowns.

SELECT p.partition_number, p.rows, au.total_pages * 8 / 1024 AS SizeMB
FROM sys.partitions p
JOIN sys.allocation_units au ON au.container_id = p.hobt_id
WHERE p.object_id = OBJECT_ID('LogRecords')
ORDER BY p.partition_number;

Common errors and fixes

Partitioning checklist

• Identified the real bottleneck via the execution plan (not assumed off the top of your head).
• Chosen a partition key aligned with the filters of the heaviest queries.
• Partition function and partition scheme created and tested in a staging environment.
• Migration done via a new table plus batch copy, with a short name-swap window.
• Sliding window routine scheduled for automatic purge/archiving.
• Partition elimination validated with STATISTICS IO before/after.
• Periodic monitoring of each partition's size set up.
• Backup and reindexing adjusted to work per partition.

With the log tables under control, the natural next step is to review the entire server infrastructure in light of expected load — from database sizing to GameServer and ConnectServer topology described in the MU Online server creation tutorial.

Frequently asked questions