How to configure indexes and database maintenance on MU Online

A freshly restored MuOnline database is fast. That same database after three months of a full server — millions of position saves, inventories changed all day long, event logs piling up — gets slow if no one looks after the indexes. Tables become fragmented, statistics grow stale, and queries that used to fly start scanning entire tables. The result is that slow login and the lagging save, even with a VPS that has CPU to spare. The fix has two fronts: creating the right indexes on the tables MU queries constantly, and setting up a maintenance routine that rebuilds indexes and updates statistics automatically. This guide covers both. Table names, columns, and numeric thresholds here are examples that vary by version of the MuServer and of SQL — the method is the same.

Prerequisites

• The MuOnline database restored and in production (or a test clone to practice on);
• SQL Server Management Studio (SSMS) and a login with db_owner or sysadmin permission;
• A full, recent backup before touching indexes;
• A defined maintenance window (the server's lowest-traffic hour);
• A sense of which queries your server runs most (login, ranking, save) — that guides which indexes to create.

Before creating any index, understand the core tables of the MuOnline database. The most queried ones are usually:

Step 1 — Measure current fragmentation

Start by diagnosing. The query below lists the database's indexes and how fragmented each one is — that's what decides whether you rebuild, reorganize, or do nothing:

USE MuOnline;

SELECT
    OBJECT_NAME(ips.object_id) AS Tabela,
    i.name AS Indice,
    ips.avg_fragmentation_in_percent AS FragPct,
    ips.page_count AS Paginas
FROM sys.dm_db_index_physical_stats(DB_ID('MuOnline'), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 100          -- ignore tiny indexes
  AND i.name IS NOT NULL
ORDER BY ips.avg_fragmentation_in_percent DESC;

The FragPct column guides the action. Indexes with few pages (small tables) aren't worth maintaining — rebuilding them changes nothing noticeable. Focus on the large, volatile tables.

Step 2 — Identify missing indexes

SQL Server records suggestions for indexes that would have helped recent queries. They are a great starting point for knowing where to create them:

SELECT TOP 15
    OBJECT_NAME(mid.object_id) AS Tabela,
    migs.avg_user_impact AS ImpactoPct,
    migs.user_seeks + migs.user_scans AS Usos,
    mid.equality_columns AS ColunasIgualdade,
    mid.inequality_columns AS ColunasDesigualdade,
    mid.included_columns AS ColunasIncluidas
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
WHERE mid.database_id = DB_ID('MuOnline')
ORDER BY migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

Prioritize suggestions with high impact AND many uses — an index that would help 90% but is never used isn't worth the write cost. Treat the suggestions as a hint, not an order: SQL sometimes suggests overlapping indexes.

Step 3 — Create indexes with judgment

With the diagnosis in hand, create indexes on the columns MU actually searches. Common examples (table/column names vary by version):

-- Login: search by account in MEMB_INFO
CREATE NONCLUSTERED INDEX IX_MEMB_INFO_id
ON MEMB_INFO (memb___id);

-- Load the characters of an account
CREATE NONCLUSTERED INDEX IX_Character_Account
ON Character (AccountID)
INCLUDE (Name, cLevel, Class);   -- included columns avoid going back to the table

-- Find a character by name (used in commands and ranking)
CREATE NONCLUSTERED INDEX IX_Character_Name
ON Character (Name);

-- Members of a guild
CREATE NONCLUSTERED INDEX IX_GuildMember_Guild
ON GuildMember (G_Name);

INCLUDE is a powerful technique: it stores extra columns in the index so the query can answer without going back to the main table (a "covering index"). Use it on the columns the query returns but doesn't filter on.

Step 4 — Reorganize and rebuild indexes

Now the maintenance itself. REORGANIZE defragments gently and is always online (no blocking). REBUILD rebuilds from scratch — more effective, but heavier and, on the Standard/Express editions, it locks the table while it runs.

-- REORGANIZE: moderate fragmentation (~10% to 30%)
ALTER INDEX IX_Character_Account ON Character REORGANIZE;

-- REBUILD: high fragmentation (>30%)
ALTER INDEX IX_Character_Account ON Character REBUILD;

-- REBUILD ALL indexes of a table
ALTER INDEX ALL ON Character REBUILD;

-- ONLINE REBUILD (Enterprise/Developer only; doesn't lock the table)
ALTER INDEX ALL ON Character REBUILD WITH (ONLINE = ON);

After a REBUILD, the statistics for that index come out already updated. After a REORGANIZE, they don't — which is why the next step (updating statistics) is a mandatory part of the routine.

Step 5 — Update statistics

Statistics tell the optimizer how the data is distributed. When old, they lead SQL to bad plans even with perfect indexes. Update them in the same maintenance window:

-- Update statistics for the whole database
USE MuOnline;
EXEC sp_updatestats;

-- Or for a specific table, with a full scan (more precise)
UPDATE STATISTICS Character WITH FULLSCAN;

sp_updatestats is handy for the general routine; WITH FULLSCAN on a critical table gives the most precise statistics at the cost of reading everything. Combine them: a general routine with sp_updatestats, plus FULLSCAN on the two or three most-queried tables.

Step 6 — Build a smart maintenance script

Running a rebuild on everything, always, is wasteful and locks too much. The ideal is a script that decides per index based on fragmentation. This cursor does exactly that:

-- Smart maintenance: reorganize or rebuild based on fragmentation
USE MuOnline;
DECLARE @tabela NVARCHAR(200), @indice NVARCHAR(200), @frag FLOAT, @sql NVARCHAR(MAX);

DECLARE cur CURSOR FOR
SELECT OBJECT_NAME(ips.object_id), i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('MuOnline'), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 100 AND i.name IS NOT NULL
  AND ips.avg_fragmentation_in_percent > 10;

OPEN cur;
FETCH NEXT FROM cur INTO @tabela, @indice, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @frag > 30
        SET @sql = 'ALTER INDEX [' + @indice + '] ON [' + @tabela + '] REBUILD;';
    ELSE
        SET @sql = 'ALTER INDEX [' + @indice + '] ON [' + @tabela + '] REORGANIZE;';

    PRINT @sql;          -- log what is going to run
    EXEC sp_executesql @sql;
    FETCH NEXT FROM cur INTO @tabela, @indice, @frag;
END
CLOSE cur; DEALLOCATE cur;

-- Finish with a statistics update
EXEC sp_updatestats;

This script only touches what exceeds 10% fragmentation, chooses reorganize or rebuild per index, and finishes by updating statistics. It's the foundation of your automatic routine.

Step 7 — Schedule maintenance with SQL Server Agent

Set the script to run on its own during the lowest-traffic window (the small hours). In SSMS:

1. Expand SQL Server Agent → Jobs, right-click → New Job...;
2. General tab: name Manutencao Indices MuOnline;
3. Steps tab → New...: type Transact-SQL, database MuOnline, paste the script from the previous step;
4. Schedules tab → New...: Recurring, Weekly, on a low-traffic day and time (e.g. Sunday 05:00);
5. Notifications tab: enable email on failure, if configured;
6. OK to save.

Step 8 — Check database integrity (DBCC CHECKDB)

Index maintenance doesn't detect corruption. Run DBCC CHECKDB periodically to make sure the database is physically healthy — a perfect index is useless on a corrupted database:

-- Full integrity check (run during maintenance, it's heavy)
DBCC CHECKDB('MuOnline') WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- Expected: "CHECKDB found 0 allocation errors and 0 consistency errors"

If consistency errors appear, do not reach for REPAIR_ALLOW_DATA_LOSS on impulse — it can wipe data. Restore from a sound backup first; destructive repair is a last resort.

Step 9 — Control the growth of log tables

Many MU databases accumulate event, cash, and command log tables that only grow. They bloat the database, the maintenance, and the backup. Define a purge policy:

-- Example: delete event logs older than 90 days (name varies by version)
DELETE FROM T_Event_Log WHERE LogDate < DATEADD(DAY, -90, GETDATE());

Add a purge like this to the weekly routine (carefully and with a backup first), so the database doesn't grow indefinitely just from history.

Common errors and fixes

Launch checklist

• Full backup taken before touching indexes
• Current fragmentation measured with dm_db_index_physical_stats
• Suggested missing indexes collected after real load and evaluated by impact and use
• Indexes created on the login, character-load, guild, and ranking columns
• Duplicate/redundant indexes checked and removed
• Smart script (reorganize vs rebuild by fragmentation) tested on the clone
• Weekly maintenance job scheduled in SQL Server Agent (or a .bat via Task Scheduler on Express)
• Statistics update included at the end of the routine
• DBCC CHECKDB scheduled and returning 0 errors
• Purge policy for the log tables defined
• Heavy maintenance confined to the lowest-traffic window
• Performance gain validated by comparing before/after on a real query

With well-chosen indexes and a maintenance routine running on its own every week, the MuOnline database stays fast month after month, even with a packed server and millions of accumulated operations. Together with memory and tempdb tuning and a solid backup policy, you complete the tripod that sustains a stable, fast server from launch onward.

Frequently asked questions