New Azure services and evolution of the Service/SQL Server relationship

Today, two new service offerings for the Azure platform were introduced, DocumentDB and Azure Search. These are exciting in themselves and worth a look-see but, to me, they are also occurrences of the phasing out of the “database as an application server” concept, formalized in the SQL Server 2005 timeframe. At least, in the cloud. […]

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

SQL Intersection in April, I’ll be there!

In mid-April, April 13-16 to be exact, the spring installment of SQL Intersection comes to Orlando. I’ll actually be there a little earlier and leave a little later than that. This conference has a whole lineup of speakers that like to add a little depth to how you’re used to thinking about your databases. There’s […]

Expertseminarium in Stockholm on performance and SQL Server 2014

This March, I’ll be presenting a 2-day seminar as part of AddSkills Expertseminarium series in Stockholm on “Performance and new developer features in SQL Server 2014″. I’ll start with techniques to measure and monitor query performance in general, as well as troubleshooting query performance problems. But from there, I’ll cover the features in SQL Server […]

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

Correlating SQL Server 2014 cardinality events and query plan

I’ve been doing some investigation of the new cardinality estimator in SQL Server 2014. I’ve always thought the best way to see how the estimation process worked was through the query_optimizer_estimate_cardinality XEvent. I wrote about this event in an earlier CTP1 post. Joe Sack used this event to show how the new estimator uses techniques […]

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

Batch Mode improvement for columnstores in SQL Server 2014 CTP1

This post is a continuation of my previous posts on columnstore indexes in SQL Server 2014 CTP1 and query plans. The test bed is the same as in previous posts, based on an enlarged version of a fact table in AdventureWorks2012DW. One of the things that improves query speed when using columnstore indexes is the usage of […]

Columnstore indexes and cardinality estimates in SQL Server 2014 CTP1

This post is a continuation of my previous post about SQL Server 2014 Columnstore and Query Plans. I’ll take a look at how the new cardinality estimates affects columnstore index query plans. I’ve written a bit about the new cardinality estimator in SQL Server 2014, as well as looked into how to tell what the […]

Columnstore Indexes and Query Plans in SQL Server 2014 CTP1

Today I thought I’d try out some of the new, highly-touted columnstore improvements in SQL Server 2014. First off, to figure out what’s new I used three main sources: one is the “canon”, the whitepaper, “Enhancements to SQL Server Column Stores“, presented at Sigmod 2013. The second source is the SQL Server 2014 CTP1 Books Online […]

Tracing The New Cardinality Estimation in SQL Server 2014

According to the whitepaper “Mission-Critical Performance and Scale with SQL Server and Windows Server” there’s a new cardinality estimator in SQL Server 2014. The whitepaper doesn’t say a lot about how it works, but does mention “trace information will be provided”. That’s XEvent trace of course, and the trace event is “query_optimizer_estimate_cardinality”. I decided to […]

New query plan items in SQL Server 2014 CTP1

Every new version of SQL Server includes new or expanded features that effect query plans. Most (sometimes not all) of the query plan changes (and most of the functionality changes) can be discovered by “diff-ing” the XML schema for query plans between the current/new version and the previous version. I’ve done this before to enumerate […]

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