In-depth: SQL server performance

[In this reprinted #altdevblogaday in-depth piece, EEDAR's chief information officer Ted Spence shares some useful performance tuning lessons he's picked up with SQL over the years.] I first began working with SQL (the SQL-92 dialect) in 1995. At the time I'd only ever used raw disk IO for storage; and SQL was a complete shock. Every bit of data I needed could be stored using a single API, and the database server took care of all the hard work. In 1997, I switched to Microsoft SQL Server 6.5, and it quickly became my preferred database. Not everyone in the gaming world uses SQL, but if you do, here are some performance tuning lessons I've learned along the way. Part 1: Effective use of SQL Server SQL Server is almost completely devoid of "magic performance tricks." This article isn't going to be about tuning configuration settings; instead, it's going to cover how to give yourself enough flexibility and freedom to spot performance problems early, and make changes to improve performance when you identify a problem. Design Flexibility Before we start, let's cover a few helpful design ideas that can make performance tuning easier in the future. Each one of these could be discussed as its own topic, but let's do a quick top level view.

Isolating Tables

Move your data into specialized tables with useful primary keys.

I should also warn you that databases are easy to take for granted. SQL Server is a very friendly, deceptively simple program. Often the dev database you create will seem so fast that you'll be tempted to ignore performance tuning, until the production server grinds to a halt on deploy day. So let's review a few tricks to enable you to see these problems before they get pushed live. Spotting Performance Problems Everyone's best friend should be "Show Execution Plan." For each query you write, click the little button that shows either the estimated or actual execution plan. The results will look like this: An execution plan that could be optimized. In this screen, you see a "Table Scan" that consumes a large amount of time that could be optimized out by creating an index. It's the equivalent to having an unstructured array and scanning through every element blindly. Adding an index allows your database to use the equivalent of adding a red/black tree or hash table to look up data faster. The great value of SQL Server is that you can dynamically tune the indexes your application uses without modifying your code! You should look through the execution plan for the following:

-- SLOW WAY: Get weapons sales by querying three tables
SELECT i.item_name, i.item_id, h.price, h.unit_count
 FROM act_auctionhistory h
    INNER JOIN itm_items i ON i.item_id = a.item_id
    INNER JOIN itm_itemclasses ic ON ic.itemclass_id = i.itemclass_id
 WHERE ic.itemclass_name = 'weapon'
    GROUP BY i.item_id, i.item_name

-- FAST WAY: Get the same data but use only two tables at a time
DECLARE @cid INT
SELECT @cid = itemclass_id FROM itm_itemclasses WHERE itemclass_name = 'weapon'

SELECT i.item_name, i.item_id, h.price, h.unit_count
 FROM act_auctionhistory h
    INNER JOIN itm_items i ON i.item_id = a.item_id
 WHERE i.itemclass_id = @cid
    GROUP BY i.item_id, i.item_name

Here's another tip. Log on to your live server (make sure you use read-only privileges of course!). Right click on your SQL Server in the left hand pane and select "Activity Monitor", then "Recent Expensive Queries". I find that I get a lot of mileage out of just checking this page every few hours: Check this page regularly! So when you get see this screen, what do you look for and how do you make use of the information? Start by right clicking on each query and show the execution plan for each one. Log Your Database Activity Another worthwhile tip is to modify your code to write a log of database queries to your debug output. When you're using advanced ORM tools like Entity Framework or NHibernate, it's very easy to write code that has a side effect of generating unexpected database hits. For example, consider this simple logic:

for (int i = 0; i < itemarray.Length; i++) {
  if (itemarray[i].IsQuestObject()) {
    return true;
  }
}

In most cases this logic is fine and fast. But imagine that the programmer down the hall fixes a bug by making IsQuestObject() ping the database – all of a sudden you've got a nearly-invisible massive performance penalty. The database is often so fast that you don't notice these kinds of performance sinks. So I modify my ORM to emit debug output every time it executes a SQL statement. As a result, when running in debug, I see the following:

SQL: list_OrderedUserAccounts (Time: 00:00:00.0030000)
SQL: get_PermissionsByUser (Time: 00:00:00.0020000)
SQL: RetrievePermissionGroupObject (Time: 00:00:00.0020000)
SQL: get_JoinsByLabel (Time: 00:00:00.0010000)
SQL: get_JoinsByLabel (Time: 00:00:00.0010000)
SQL: get_JoinsByLabel (Time: 00:00:00.0010000)
SQL: ListProductObject (Time: 00:00:01.3680000)
SQL: list_OrderedPlatforms (Time: 00:00:00.0020000)

When I look at this output, I immediately check to see if I've accidentally written a loop that calls get_JoinsByLabel too many times. Then next I investigate the query "ListProductObject" to see why it took 1.5 seconds. Combine multiple queries Nothing improves performance quite like reducing the number of queries you execute. Rather than submitting ten queries rapidly, why not make use of a single stored procedure that returns ten result sets? Let's say you're working on the front page of your in-game auction house, but when the user mouses over an item name or a player name, you want to show some secondary details. You could do this by writing one query for the auction house page and a second query for each time the user hovers their mouse. But that would produce dozens of queries every time the mouse moved. Instead, let's create a single compound query that returns both the auction page and the popup text for everything. Even though at first glance you may think you're doing unnecessary work, SQL Server is already looking up all the objects using hash tables, and you're reducing query overhead significantly by reducing volume. Here's roughly how to do it:

CREATE PROCEDURE get_AuctionHouseFirstPage AS

-- Preparation: Select basic into a temporary table
SELECT auction_id, character_id, auction_date, …
 FROM ACT_Auctions, …
 INTO #temp_auction_page

-- First result set: Return the auction page
SELECT ... FROM #temp_auction_page

-- Second result set: Return pop-up information about characters
SELECT user_name, guild_name, …
 FROM CHR_Characters c
    INNER JOIN #temp_auction_page t ON c.char_id = t.char_id

-- Third result set: Return pop-up information about items
SELECT item_name, item_enchantment, ...
 FROM ITM_Items i
    INNER JOIN #temp_auction_page t ON i.item_id = t.item_id

For Next Time Many of you may be asking – what about SQL vs NoSQL databases? I encourage you to tread lightly and consider them carefully on their own merits. Unlike SQL, which provides specific guarantees to data behavior at the cost of performance, NoSQL databases have their own unique advantages and drawbacks. There are many situations in gaming that are extremely well suited for NoSQL implementations; but this is a complex issue that really deserves its own article. For the next SQL Server performance article, I'll look at the speed of database inserts and updates, and the side effects of some basic choices in software style. [This piece was reprinted from #AltDevBlogADay, a shared blog initiative started by @mike_acton devoted to giving game developers of all disciplines a place to motivate each other to write regularly about their personal game development passions.]