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 […]

Compiled T-SQL? Don’t throw away all your SQLCLR code just yet

The speed increase from memory-optimized tables in SQL Server 2014 is nice, but I thought, to get the most bang for your buck for really need to use compiled stored procedures in addition. After looking at some of the C code these produce, it almost looked like I was doing direct vector branching into the […]

In-memory OLTP – read_set, write_set, and scan_set

I was looking through some in-memory OLTP DMVs in SQL Server 2014 and came across these in the DMV sys.dm_xtp_system_memory_consumers: WRITE_SET_LOOKASIDE SCAN_SET_LOOKASIDE READ_SET_LOOKASIDE Thought it might be nice to see how these work, but explain first I’ll explain what they’re about. By the way, a lookaside is just a cache, sometimes of pointers to memory addresses. You’ve probably heard of […]

SQL Server 2014 memory-optimized table variables – metadata and garbage collection

I’d been doing some experimentation with memory-optimized table types in SQL Server 2014 and thought it was about time to write about it. Yesterday however, there was an excellent post on them at SQL Server team blog. So I’ll stay away from restating their points for the most part, and encourage you to read their […]

Tracking execution stats of SQL Server 2014 natively-compiled sprocs

Haven’t had time to blog much lately. Finding out strange and interesting things about SQL Server 2014 to present at some seminars and conferences next year. “Shameless self-promotion” posts for those events will be forthcoming. 😉 A few days ago, an experiment with tracking compiled stored procedures turned up some “interesting” results. Thought I’d write about […]

Integrating SQL Server 2014 constructs into the T-SQL canon

So I was going through the features in SQL Server 2014, seeing how and where they fit into the canon of SQL Server’s T-SQL. I’m going to add some of this for my advanced T-SQL Immersion Event at the end of September. SQL Server 2014 is supposed to be a release with no T-SQL changes at all. […]

Hekaton, read committed isolation, and statement-level inconsistency

It’s stated in SQL Server 2014 Books Online that “The following isolation levels are supported for transactions accessing memory-optimized tables: SNAPSHOT, REPEATABLE READ, and SERIALIZABLE”. But later on, on the same page, it’s stated the “The isolation level READ COMMITTED is supported for memory optimized tables with autocommit transactions”. “Autocommit transaction” is just another way […]

Speaking on Hekaton at Portland Oregon SQL Server User Group

As you can probably tell by the last couple of blog postings, I’ve be doing a bit of experimenting with the SQL Server 2014 Hekaton feature in the past few weeks. I’ll be doing a hopefully demo-heavy (but I do have a few slides) talk next Thursday July 25 at the Portland SQL Server User Group […]

Transaction dependencies and speculative reads with memory-optimized tables

In reading the whitepaper about “High-Performance Concurrency Control Mechanisms for Main-Memory Databases”, I was intrigued by the discussion of speculative reads and transaction dependencies. It’s not always good to use information from an academic whitepaper as details of an implementation, because the real implementation might be differ slightly from the description of the whitepaper’s implementation. […]

A more complex discussion of user transactions and memory-optimized tables

In the last blog post, I tried out a simple example to show the difference between the three transaction isolation levels supported by memory-optimized tables. I only used user-transactions in T-SQL, and only showed the behavior of transactions consisting of SELECT statements, when INSERT/UPDATE/DELETE activity was taking place in other sessions running concurrently. But of […]

A gentle introduction to transaction isolation levels and Hekaton tables

I finished reading the Books Online and CTP1 whitepaper introduction to transactions and memory-optimized tables a couple of days ago. Although I know what the transaction isolation levels mean, I was sort of surprised by some of the descriptions and found them a bit confusing. So I decided to go back to basics and write […]

More XEvents in SQL Server 2014 CTP1 – don’t wait any longer

Don’t get me wrong, there’s almost no one who enjoys a good SQL Profiler diagnostic trace more than I do. But, starting in the early CTPs of SQL Server 2008, I’ve become impressed with the diagnostic enablement afforded through Extended Events. Enough to hack through that first beta (sans docs, with only metadata) and blog […]

Hekaton data and code – where does that stuff actually live?

When investigating new features, I’m prone to use what I call the Rumpelstiltskin method, that is, I’m happier if I know where these features “live” in the file system (not exactly Rumpelstiltskin, where the reward came by guessing his name, but hopefully I’m not stretching the analogy too far). With this in mind, I started off […]

Thinking about Hekaton? Then think about collations.

Among the new features in SQL Server 2014, the most compelling, at least from my point of view, is the in-memory tables and procedures known as project Hekaton. Because the tables are organized completely differently than traditional SQL Server tables there are, naturally, some limitations around Hekaton tables and procedures. In looking over the list […]