How to optimize SQL Server for many players in MU Online

Every MU Online server runs smoothly with 15 players. The problem shows up at the first crowded event, at launch, at the moment 200 people try to log in at the same time: login freezes, "save character" lags, Castle Siege stutters. In the overwhelming majority of cases the culprit is not the GameServer - it is a poorly configured SQL Server. The MuOnline database receives a flood of short reads and writes (login, position save, inventory, event log) and, if memory, tempdb and the recovery model are not tuned, everything queues up. This guide shows how to prepare SQL Server for scale. The numeric values are examples that vary by SQL version and by your VPS's size - what does not vary is the method.

Prerequisites

• SQL Server installed (2014, 2017, 2019 or 2022 for modern servers; 2008 R2 still common on classic/Season 6) with the MuOnline database restored;
• SQL Server Management Studio (SSMS) up to date;
• sa access or a login with sysadmin permission;
• Knowledge of how much RAM and how many cores the VPS has (Task Manager -> Performance, or systeminfo);
• Preferably an SSD disk (NVMe ideally) for the data and log files;
• A recent backup before touching any configuration.

Survey your server's current setup before optimizing:

Step 1 - Fix minimum and maximum memory

By default SQL Server tries to consume almost all available RAM, leaving Windows and the MuServer without breathing room - which causes disk paging and freezes. Set a cap. Starting rule: leave enough RAM for the OS and the MuServer, and give the rest to SQL.

-- Example for a 16 GB VPS (varies by version/size)
-- Reserves ~5 GB for Windows + MuServer, gives ~11 GB to SQL
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'min server memory (MB)', 4096;
EXEC sp_configure 'max server memory (MB)', 11264;
RECONFIGURE;

A rough starting-point reference:

Step 2 - Choose the recovery model

The recovery model controls how the transaction log (.ldf) behaves. For MU Online, Simple is usually the right choice: the log is recycled automatically and does not grow uncontrolled, as long as you keep frequent Full/Differential backups.

-- Check the current model
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MuOnline';

-- Change to Simple (light log, no point-in-time)
ALTER DATABASE MuOnline SET RECOVERY SIMPLE;

Use Full only if you need point-in-time recovery (restore to the exact minute before an incident) - and, in that case, schedule frequent log backups, or the .ldf swallows the disk.

Step 3 - Configure tempdb correctly

tempdb is where SQL does the "dirty work": sorts, temporary tables, versioning. Under the load of many players, it becomes a contention point. Two actions solve most problems: create multiple data files and pre-size them.

-- See the current tempdb configuration
SELECT name, physical_name, size/128.0 AS SizeMB
FROM sys.master_files WHERE database_id = DB_ID('tempdb');

-- Adjust size and growth of the main file (example)
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 512MB, FILEGROWTH = 128MB);

-- Add extra files: rule of thumb = 1 per core, up to 8, all equal
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'C:\SQLData\tempdb2.ndf', SIZE = 512MB, FILEGROWTH = 128MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'C:\SQLData\tempdb3.ndf', SIZE = 512MB, FILEGROWTH = 128MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'C:\SQLData\tempdb4.ndf', SIZE = 512MB, FILEGROWTH = 128MB);

The number of files should match the VPS's cores (up to a cap of 8). All of the same size and same growth - SQL distributes the load across equal files; files of different sizes unbalance the allocation. If possible, put tempdb on a separate disk or on the fastest SSD.

Step 4 - Adjust MAXDOP and Cost Threshold

MU Online fires thousands of small queries (login, save, inventory reads). For that profile, letting SQL parallelize too much gets in the way. Adjust the maximum degree of parallelism (MAXDOP) and the cost threshold for parallelism:

-- MAXDOP: for an OLTP load (many small queries), limiting helps.
-- Common rule: number of cores per NUMA node, capped at 8. Example:
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- Cost threshold: the default 5 is too low and makes trivial queries
-- parallelize for nothing. Raising it to ~50 is a classic recommendation.
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

These values are examples and vary by version and by the number of cores. The principle: MU's short queries do not benefit from aggressive parallelism; leaving the cost threshold at the default 5 only creates overhead.

Step 5 - Separate data and log across healthy disks/files

The data file (.mdf) and the log file (.ldf) have different I/O patterns: data is random read/write, log is sequential write. Whenever possible, keep them on separate disks and avoid growth in tiny chunks:

-- See size and autogrowth of the MuOnline files
SELECT name, physical_name, size/128.0 AS SizeMB,
       growth, is_percent_growth
FROM sys.master_files WHERE database_id = DB_ID('MuOnline');

-- Set growth in fixed MB (not %), avoiding fragmentation
ALTER DATABASE MuOnline MODIFY FILE (NAME = 'MuOnline_Data', FILEGROWTH = 256MB);
ALTER DATABASE MuOnline MODIFY FILE (NAME = 'MuOnline_Log',  FILEGROWTH = 128MB);

Step 6 - Enable Instant File Initialization

Without this setting, every time a data file grows, Windows physically zeroes out the space - which freezes SQL during the operation. By granting the Perform Volume Maintenance Tasks privilege to the SQL service account, data file growth becomes instant:

1. Open secpol.msc (Local Security Policy);
2. Go to Local Policies -> User Rights Assignment;
3. Open Perform volume maintenance tasks;
4. Add the SQL Server service account (e.g. NT SERVICE\MSSQLSERVER);
5. Restart the SQL Server service.

This speeds up data growth and backup restores. (It does not affect the log, which by design always needs to be zeroed.)

Step 7 - Keep statistics up to date

The SQL optimizer decides how to execute each query based on statistics. If they get stale - which happens quickly in a write-heavy MU database - SQL makes poor decisions and slow login appears. Ensure automatic updates and do a periodic manual refresh:

-- Ensure automatic statistics update
ALTER DATABASE MuOnline SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE MuOnline SET AUTO_CREATE_STATISTICS ON;

-- Manual refresh of all statistics (run during maintenance)
USE MuOnline;
EXEC sp_updatestats;

Schedule sp_updatestats to run daily during the quietest window, alongside index maintenance.

Step 8 - Find the real bottlenecks

Before and after optimizing, measure. SQL Server has system views that point exactly where the database is suffering. Two queries are worth their weight in gold:

-- Most expensive queries (total CPU time)
SELECT TOP 10
    qs.total_worker_time/qs.execution_count AS AvgCPU,
    qs.execution_count,
    SUBSTRING(st.text, 1, 120) AS Query
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgCPU DESC;

-- Missing indexes (SQL suggests what it would create)
SELECT TOP 10
    mid.statement AS TableName,
    migs.avg_user_impact AS ImpactPct,
    mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;

The first shows which operations consume CPU (usually character save or a poorly written ranking query). The second suggests indexes that SQL itself misses. Creating and maintaining those indexes is a subject in itself - handle it with care, since too many indexes hurt writes.

Step 9 - Validate under simulated load

Optimizing in a vacuum is misleading. Simulate a peak before launch:

1. Apply all the settings above and restart the SQL service;
2. Run sp_updatestats and index maintenance;
3. Invite a test group (or use controlled bots) to log in en masse;
4. Meanwhile, run the two bottleneck queries from the previous step;
5. Watch CPU and memory usage in Task Manager;
6. Adjust MAXDOP and memory if you see contention; repeat.

Common errors and fixes

Launch checklist

• Full backup taken before any change
• max server memory set, leaving RAM for Windows + MuServer + site
• Recovery model decided (Simple in most cases) and consistent with the backup policy
• tempdb with multiple equal, pre-sized files, preferably on SSD
• MAXDOP and cost threshold tuned to MU's OLTP profile
• Autogrowth in fixed MB for data and log, with pre-allocation
• Instant File Initialization enabled (Perform Volume Maintenance Tasks)
• AUTO_UPDATE_STATISTICS on and sp_updatestats scheduled
• Bottleneck queries (CPU and missing indexes) run and analyzed
• Data and log on SSD, ideally on separate disks
• Load test with mass login done before opening to the public
• SQL Server set to Automatic startup with Windows

With memory fixed, a multi-file tempdb, the correct recovery model and up-to-date statistics, the MuOnline database stops being the launch bottleneck. The logical next step is to close the performance loop by taking care of indexes and regular database maintenance - that is where the long-term gains consolidate and the server stays fast week after week.

Frequently asked questions