SQL Server 2014 In-Memory OLTP: What exactly is a “dusty corner”?

There’s a set of performance counters for the new In-Memory OLTP in SQL Server 2014. You might have overlooked them because they’re not with the other SQL Server counters, but are in their own group that begins with “XTP” (eXtreme Transaction Processing).

Looking over those counters, both the XTP Garbage Collection and XTP Phantom Processor groups contain a counter that refers to “Dusty Corner scan retries”. So what, exactly, is a dusty corner? Searching both Books Online and the “SQL Server 2014 In-Memory OLTP TDM White Paper” yields no references, but the academic Sigmod-2013 paper contains a helpful description.

The term has to do with how the In-Memory OLTP feature stores data in memory and how its garbage collection works. Because a single copy of a row’s data is stored along with multiple index pointers (both hash and BwTree indexes are ultimately pointer-based), all of index pointers must be “unlinked” before an old row can be garbage collected. Since threads can unlink old pointers while running queries, any index ranges with a lot of activity will quickly unlink the appropriate pointers. However, if an index or index range is rarely used, special scans by the system garbage collector will be needed to find these pointers. They’re “hiding” in the dusty corners (apparently dust forms on unused index ranges, I envision Carol Burnett, duster in hand 😉

So the presence of dusty corner scans means some index ranges aren’t being used much. If, by looking at index usage, you can determine that an entire index is almost never being used (Database Engine Tuning Advisor doesn’t tune indexes for in-memory tables, that I’m aware of), that index would be a candidate for removal. However, in-memory tables don’t support filtered indexes so, if another part of the index range is frequently used, you’ll have to decide if it’s worth letting old versions hang around for longer. Until those dusty corner scans unlink that last pointer.

I’ll be doing preconference seminars covering all the SQL Server 2014 features in detail at SQLIntersection in Orlando and DevWeek London in April, as well as a performance-centered new and old feature seminar at Addskills in Stockholm later this month. if you’re at any of these locations and would like to see how these features would help you and you’re clients, I’ll see you there.

Cheers, @bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.