How to Optimize SQL Server for Better Performance on MU Server

Why SQL Server Performance Is Critical for MU Servers

The MU Online GameServer performs hundreds of read and write operations against the database every second — every character movement, dropped item, XP gained, and account connection passes through SQL Server. A poorly configured database results in lag, disconnections, and character data corruption.

This guide covers specific adjustments for typical installations with the MuOnline database running on Windows Server 2008/2012/2016, SQL Server 2008 through 2019, covering Seasons 4 through 13.

Step 1 — Configure Maximum SQL Server Memory

By default, SQL Server consumes all available RAM, leaving the GameServer and Windows without resources.

1.1 Open SSMS and execute:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Set the RAM ceiling (adjust based on your server)
-- Server with 8 GB RAM: reserve 2 GB for the OS and GameServer
EXEC sp_configure 'max server memory (MB)', 6144;
RECONFIGURE WITH OVERRIDE;

1.2 Check current consumption before and after:

SELECT 
    physical_memory_in_use_kb / 1024 AS MB_in_use,
    page_fault_count
FROM sys.dm_os_process_memory;

Step 2 — Configure the Database Recovery Mode

The MuOnline database rarely needs point-in-time recovery. Keeping FULL mode generates enormous logs with no practical benefit.

2.1 Switch to SIMPLE mode:

ALTER DATABASE MuOnline SET RECOVERY SIMPLE;
ALTER DATABASE MuOnline_Log SET RECOVERY SIMPLE;  -- if a separate log database exists

2.2 Shrink the log file immediately:

USE MuOnline;
DBCC SHRINKFILE (MuOnline_log, 50);   -- shrinks to 50 MB
DBCC SHRINKFILE (MuOnline_data, 0);   -- releases unused pages

Step 3 — Create Indexes on Critical Tables

The tables Character, AccountCharacter, and AccountMDItem are the most heavily accessed during gameplay. Without proper indexes, every lookup performs a full table scan.

3.1 Essential indexes for the Character table:

USE MuOnline;

-- Index by account (required for login and character listing)
CREATE INDEX IX_Character_AccountID 
ON dbo.Character (AccountID)
INCLUDE (Name, cLevel, MapNumber);

-- Index by name (used in GameServer lookups and trade systems)
CREATE INDEX IX_Character_Name 
ON dbo.Character (Name);

-- Composite index for the ranking system
CREATE INDEX IX_Character_Ranking 
ON dbo.Character (cLevel DESC, Resets DESC, MasterLevel DESC);

3.2 Indexes for AccountCharacter (session control):

CREATE INDEX IX_AccChar_Account 
ON dbo.AccountCharacter (AccountID)
INCLUDE (GameIDC);

CREATE INDEX IX_AccChar_ConnectStat 
ON dbo.AccountCharacter (ConnectStat, ServerCode);

3.3 Indexes for stored items:

-- Account warehouse table
CREATE INDEX IX_AccountMDItem_AccountID 
ON dbo.AccountMDItem (AccountID);

-- Character items table (Season 6+)
CREATE INDEX IX_Items_CharName 
ON dbo.Items (AccountID, CharName);

Step 4 — Update Statistics and Rebuild Indexes

Fragmented indexes and stale statistics degrade performance over time, especially after wipes or major events.

4.1 Weekly maintenance script — create as a SQL Agent Job:

USE MuOnline;

-- Update statistics for all tables
EXEC sp_updatestats;

-- Rebuild indexes fragmented above 30%
DECLARE @TableName NVARCHAR(256);
DECLARE @IndexName NVARCHAR(256);
DECLARE @SQL NVARCHAR(MAX);

DECLARE idx_cursor CURSOR FOR
    SELECT 
        OBJECT_NAME(i.object_id),
        i.name
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
    JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
    WHERE ps.avg_fragmentation_in_percent > 30
      AND i.name IS NOT NULL;

OPEN idx_cursor;
FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON dbo.[' + @TableName + '] REBUILD;';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM idx_cursor INTO @TableName, @IndexName;
END;

CLOSE idx_cursor;
DEALLOCATE idx_cursor;

Step 5 — Optimize GameServer Connection Settings

The GameServer configuration file defines how it connects to SQL. Locate the file under GameServer/Data/ — it may be named DBServer.ini, GameServerInfo.cfg, or similar depending on your version.

5.1 Recommended settings for DBServer.ini (Season 6):

[DBServer]
ConnectServer     = 127.0.0.1      ; always use local IP when possible
Port              = 1433
DatabaseName      = MuOnline
UserID            = muonline_user   ; NEVER use sa in production
Password          = StrongPassword123!
ConnectionTimeout = 30
QueryTimeout      = 60
MaxConnections    = 50

5.2 Create a dedicated SQL user with minimal permissions:

-- Create login
CREATE LOGIN muonline_user WITH PASSWORD = 'StrongPassword123!';

-- Create user in the database
USE MuOnline;
CREATE USER muonline_user FOR LOGIN muonline_user;

-- Grant only what is necessary
ALTER ROLE db_datareader ADD MEMBER muonline_user;
ALTER ROLE db_datawriter ADD MEMBER muonline_user;
GRANT EXECUTE TO muonline_user;

Step 6 — Monitor Slow Queries in Real Time

6.1 Identify the queries consuming the most CPU:

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_ms DESC;

6.2 Identify connection blocking (useful during events):

SELECT 
    blocking_session_id AS blocker,
    session_id AS blocked,
    wait_type,
    wait_time / 1000 AS wait_seconds,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1, 200) AS query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE blocking_session_id > 0;

Step 7 — Schedule Automatic Maintenance via SQL Agent

7.1 Create a Job in SQL Agent (SSMS → SQL Server Agent → Jobs → New Job):

• Name: MuOnline - Weekly Maintenance
• Schedule: Every Monday at 04:00 AM
• Step 1: Run the index rebuild script from Step 4
• Step 2:

USE MuOnline;
DBCC SHRINKFILE (MuOnline_log, 100);
EXEC sp_updatestats;
PRINT 'Maintenance completed at: ' + CONVERT(VARCHAR, GETDATE(), 120);

Quick Troubleshooting Reference

With these optimizations applied, Season 6 servers handling 200-500 concurrent players typically reduce database lag by 60-80%. Monitor the diagnostic counters with the queries above weekly and re-adjust the memory limits as your server load grows over time.

Perguntas frequentes