Merry-Go-Round Scans: A Culprit For Performance Variances?

Q: Our developers use SQL Server 2005 Developer Edition for testing, and we also use the Developer Edition for our stress-testing boxes.We often see significantly different performance for some queries when we roll out an application to production on the Standard Edition.What could be causing the variance?

A: The key to solving this mystery is to remember that the SQL Server 2005 Developer Edition is feature-identical to the Enterprise Edition. The only differences between the products are the price and how they're licensed. So if you rephrase the question—for example, "Will we see a performance-difference between the Enterprise and Standard Editions?"—then the performance problem you're referring to makes a bit more sense.The Enterprise Edition might have certain performance optimizations that you won't find in the Standard Edition.

Related: Get a Head Start on SQL Server 2005 Performance Tuning Techniques

However, the mystery can sometimes go a bit deeper. Some people might read the Microsoft article "Features Supported by the Editions of SQL Server 2005" at Microsoft Developer Network and think, well heck, I'm using features that are in both the Enterprise and Standard Editions, so I expect the performance on both machines to be the same. But most people miss the fact that the Enterprise Edition supports an Enhanced Read-ahead and Scan option. In the database world, this feature is often called a merry-go-round scan. If you search online for the phrase "merry go round scans," the search engine returns several links to information pertaining to this type of scan.A great resource about this type of advanced scanning is the Microsoft article "Reading Pages."

Here's a simple example of how the Enhanced Read-ahead and Scan option works. Assume that UserA and UserB both issue the SELECT * FROM Customer command, which results in a table scan. UserA issues the command first; UserB issues the command 20 seconds later. The table has 100 million rows (it's a very big table), and the scan is running on a machine that has Standard Edition installed.The table scan for UserA begins reading the Customer table on the first page of the table.Twenty seconds later, the scan for UserB starts reading pages that UserA has already read, even though some of the pages might already have been flushed back out of cache. Sometimes this process creates tremendous disk contention and draws on memory.

The Enterprise Edition performs an Enhanced Read-ahead and Scan a little differently (e.g., you'll immediately notice that calliope music plays during the scan). Unlike with the Standard Edition, when UserA issues the SELECT * FROM Customer command, the Enterprise Edition begins scanning the Customer table.When UserB issues the same command 20 seconds later, the table scan for UserB starts exactly where UserA is currently reading. UserA and UserB's queries will both read the last page of the Customers table at about the same time, but then UserB's scan will go back to the beginning of the Customer table to scan the pages that UserA had scanned before UserB began its query.This process dramatically reduces disk contention and the draw on memory. Note that when you perform this type of query on Enterprise Edition, multiple reads of the same data might yield the output in different order unless the user specifies an ORDER BY clause. The ORDER BY clause (i.e., [ORDER BY Order_Item [ASC | DESC] [, ...]] ) sorts query results based on the column and direction (ascending or descending) arguments you specify. For example, if you enter the following command for a query:

ORDER BY Name, City, State asc 

the query results are sorted by Name, City, and State in ascending order.

You'll find other performance differences between the Enterprise and Standard Editions. For example, only the Enterprise Edition supports both the parallel Database Console Commands (DBCC) and the CREATE INDEX SQL command, but you know when you're running these commands to expect a slight performance difference. It's the Enhanced Read-ahead and Scan option that seems to trip people up the most when they're trying to account for unknown performance differences between the Enterprise and Standard Editions.