How to diagnose a specific slow SQL query in your MU Online server's database

Quick answer

A MU Online server can have a perfectly healthy GameServer and ConnectServer and still suffer freezes visible to the player — slow login, a ranking screen that hangs when opened, a sluggish guild list — because a single poorly optimized SQL query is monopolizing the database. Unlike a generic infrastructure issue, this type of bottleneck is surgical: one specific query, running frequently, without the right index. This tutorial teaches you to identify exactly which query is the culprit, understand why it's slow, and fix it without breaking the rest of the system.

Typical symptoms of a specific slow query

The first sign that the problem is one specific query (and not the entire database server) is the location of the symptom: one specific game action hangs (opening the ranking, logging in, listing a guild, processing a trade) while everything else stays smooth. If everything is slow at the same time, the problem tends to be general resources (CPU, disk, SQL Server memory); if one action hangs while others run normally, the problem is a specific query competing for resources or stuck on a lock.

Available diagnostic tools

Tool	Use	When to use it
SQL Server Profiler	Captures queries in real time with duration	One-off diagnosis, test environment
Extended Events	Modern replacement for Profiler, lighter weight	Production, continuous capture without much impact
Activity Monitor	Quick view of active sessions and waits	First look during the incident
DMVs (`sys.dm_exec_query_stats`)	Aggregated query stats since the last restart	Finding the most costly query historically
Execution Plan	Shows how SQL Server decided to fetch the data	After identifying the candidate query

Step 1 — Capture the query at the moment of the symptom

With Extended Events (recommended in production because it has lower overhead than classic Profiler), create a session filtering by minimum duration:

CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.client_hostname)
    WHERE duration > 1000000 -- 1 second, in microseconds
)
ADD TARGET package0.event_file(SET filename = N'SlowQueries.xel');
GO
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

Reproduce the symptom in-game (open the ranking, log in) and then stop the session to analyze the .xel file in SQL Server Management Studio.

Step 2 — Find the most costly query historically

If the symptom is intermittent and you couldn't capture it live, use the accumulated statistics DMVs:

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count AS avg_time_us,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
      (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
            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_time_us DESC;

This query sorts by average execution time — candidate number 1 is almost always the same query the player complains is hanging.

Step 3 — Read the execution plan

With the query identified, request the actual execution plan (Include Actual Execution Plan in SSMS) and look for warning signs:

Element in the plan	What it means	Action
Table Scan on a large table	No index is being used	Create an index on the WHERE/JOIN column
Index Scan (not Seek)	Index exists but isn't selective enough	Review the index columns
Yellow warning icon	Estimated rows very different from actual	Update statistics (`UPDATE STATISTICS`)
Repeated Key Lookup	Index covers the filter but not the returned columns	Create a covering index (INCLUDE)
High-cost Sort	ORDER BY without an index that already delivers the order	Index on the sort column

Step 4 — Classic cases in the MU Online database

Some patterns repeat across practically every emulator (IGCN, MuEMU, X-Team):

General ranking (SELECT TOP N ... ORDER BY Resets DESC): without an index on the Resets/level column, SQL Server scans the entire character table every time the ranking is opened. An index on the sort column solves this in most cases.
Login (SELECT * FROM MEMB_INFO WHERE memb___id = @id): should be instant; if it's slow, there's usually a missing index (or even a missing primary key) on the login column, or the table has high fragmentation.
Guild list / guild member: joins between the guild and character tables without an index on the foreign key generate double Table Scans.
Trade/chat log: log tables grow indefinitely; without an index and without a cleanup routine, queries against recent history get progressively slower over time.

Step 5 — Create the right index

After identifying the candidate column, create a targeted non-clustered index:

CREATE NONCLUSTERED INDEX IX_Character_Resets
ON Character (ResetCount DESC)
INCLUDE (CharacterName, Level, Class);

The INCLUDE avoids the "Key Lookup" by already delivering the columns the query requests in the SELECT, without needing them to be part of the index's sort key.

Step 6 — Validate the gain

Run the query again with SET STATISTICS TIME ON and SET STATISTICS IO ON before and after creating the index, comparing logical reads and CPU time. A real improvement usually reduces logical reads by an order of magnitude (from thousands to tens), not just a few milliseconds.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- run the query here

Parameter sniffing: when the same query swings from fast to slow

If the query is sometimes fast and sometimes slow with similar data, the issue may be an execution plan reused for an atypical parameter (parameter sniffing). Symptoms: the first run of the day is slow, then fast, or the opposite. Common fixes: OPTION (RECOMPILE) on the specific query (extra CPU cost per execution, but the plan is always appropriate), or rewriting the procedure with local variables to force a more generic plan.

Preventive index and statistics maintenance

Indexes fragment over time (the constant INSERT/UPDATE/DELETE of an online game generates this quickly). Schedule periodic maintenance:

-- Rebuild indexes with high fragmentation (>30%)
ALTER INDEX ALL ON Character REBUILD;
-- Update statistics so the optimizer has correct data
UPDATE STATISTICS Character;

Run this during a low-traffic window (overnight), never during peak hours — a rebuild consumes I/O and can compete with the game itself for the same resources.

Common errors and fixes

Symptom	Likely cause	Fix
Ranking takes several seconds to open	Missing index on the sort column	Create a non-clustered index with INCLUDE
Slow login for only some players	High fragmentation on the account table	Rebuild the index and check the login lookup key
Query alternates between fast and slow	Parameter sniffing	`OPTION (RECOMPILE)` or rewrite with a local variable
Everything is slow at once, not just one action	General resource problem, not a query problem	Check the database server's CPU/disk/memory
Performance degrades over time with no code changes	Log/history growing without cleanup or an index	Create an archiving/purge routine and index the date column

Slow query diagnosis checklist

Symptom localized to a specific game action, not the whole server.
Extended Events or DMV used to capture the exact query.
Execution plan analyzed for Table/Index Scans.
Index created on the identified filter/sort columns.
Gain validated with STATISTICS IO/TIME before and after.
Parameter sniffing ruled out or handled if slowness is intermittent.
Index/statistics maintenance routine scheduled for overnight.

After fixing the specific query, it's worth reviewing the overall health of the database and the GameServer as a whole so the next bottleneck doesn't go unnoticed — the MU Online server creation tutorial covers the baseline configuration that supports this kind of maintenance.

Frequently asked questions

How do I know the problem is a query and not the database server overall?

If the database server's CPU/disk is normal but a specific game command hangs (slow login, ranking taking forever, trade freezing), the problem is localized. Run Activity Monitor or Extended Events during the symptom to isolate the exact query before touching hardware.

Do I need advanced SQL skills to use the execution plan?

The basics already help a lot: look for warning (yellow) icons in the graphical plan and operators like Table Scan/Index Scan on large tables — they almost always indicate a missing index. You don't need to master everything to solve the most common cases.

Is creating a new index risky?

Yes. Indexes speed up reads but cost on writes (every INSERT/UPDATE also has to update the index) and take up disk space. On high-write tables, like connection logs, weigh the trade-off before creating too many indexes.

What is parameter sniffing and why does it make a query slow sometimes?

It's when SQL Server reuses an execution plan optimized for a specific parameter value, but that plan is poor for other values. The query is 'sometimes' fast and 'sometimes' slow with the same data, which confuses diagnosis if you don't know to look for it.

Does an index rebuild automatically fix slowness?

It helps when the cause is fragmentation, but it doesn't fix poorly written queries or a missing proper index. Run rebuilds as periodic maintenance, not as a magic fix for every performance problem.

Bruno

Events, maps & items editor

Bruno specializes in MU Online events, maps, bosses and item economy. He documents every detail based on real gameplay.