Last week in Azure SQL Database – Part 1

This blog post is especially dedicated to those who attended my SQLIntersection post-conference talk on WASD just over one week ago. The announcements were made after the talk, and so the information here is mostly a delta for those who want to catch up. I think the post series will be useful above and beyond that, however. I’ll try not to paraphrase the announcements but may need to quote from it or provide URLs. I’ll also tell you where I’m currently a bit unclear about things.

Last week, Microsoft made some major announcements about the future of Azure SQL Database. As an aside, the announcements referred to it as “Azure SQL Database” (although there is one usage of the name Microsoft Azure SQL Database), so I’m going to refer to it as a new made-up acronym, ASD, rather than my previous acronym (WASD) for their previous name, Windows Azure SQL Database. “Azure SQL Database” is just too long. For folks who get the two confused, ASD is a platform as a service (PaaS) database offering based on the SQL Server code. As opposed to “SQL Server on an Azure VM”, which is an IaaS offering. With ASD, you don’t need to maintain a guest OS or maintain SQL Server software yourself. I’ve written about this previously.

The first announcement was a changing of the tiering and pricing structure. ASD goes from 2 tiers (Web and Business) to 6 tiers (Base, Standard S1 and S2, Premium P1, P2, P3). Even more important, the charges go from being database size-based to tier-based. To see the new pricing, go here (, scroll down, and click on “Basic, Standard, and Premium” button to see the chart. The difference between the tiers (besides size limit) is the level of service. More on that later. The chart mentions “Database size limit (included)”, but I’m unsure that there isn’t SOME charge based on the database size (as was the case with Web and Business). I can’t find it if there is, and the word “included”, tends to suggest that there isn’t additional per-GB data size charge. One (1) day is the minimum charge and charges are daily. I guess 1 day means “24 hours after you create the DB”, but haven’t tested that out yet.

You’ll have 1 year to convert from Web/Business to Base/Standard/Premium. The announcement doesn’t say what will happen if you don’t. The new tier structure is in “preview mode”, which has two main consequences. Firstly, to use preview mode you forgo your SLA, until the new tiers become GA. Secondly, to try it out, you need to sign-up/opt-in at Scroll until you see “New Service Tiers for SQL Databases” and click “Try it”. If you have multiple Azure subscriptions, you must do it once per subscription. It was almost immediately visible for me on either the old or new Azure portal.

The new service tiers use different sets of hardware, so you can’t use a new tier on an existing “server”. You need a new server. The new tier choices also show up when you Import a BACPAC into a database (on portal, +New/Data Services/SQL Database/Import), too. On “Import” and “New/Custom Create” I get choices of all old and new tiers. They don’t show up on “quick create” in the same path, however, but when I tried it (after making the new tiers available) “quick create” (on a new server) created a Basic (new-tier) database. Not sure I like that, because preview tiers have no SLA.

You can also convert between tiers for an existing database. On the portal, choose your database, and it’s under “Scale”. Scale (on a Basic database) didn’t give me Web/Business as a choice, only the new tiers as you’d expect. Be careful with this because, if I read the charges right, switching from Basic -> P1 Premium -> Basic will cost you $15 (the cost for 1 day of P1). I didn’t do this yet to find out.

There’s much more to come, but I do want to close this blog entry by quickly mentioning two other things:
1. When new tiers go GA, the uptime SLA goes from 99.9 to 99.95 for all new tiers.
2. On same day, it was announced that Azure Federation feature also will be discontinued after a year (Apr 2015). More on this later.

Cheeers, @bobbeauch

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

Managing Windows Azure SQL Database’s newest feature – Premium Database

The latest feature to make an appearance in Windows Azure SQL Database (actually it’s still a preview program) is the Premium Database. Physically, when you have a WASD database, you’re sharing that database and server with others. It’s possible that if one of your database “neighbors” decides to run a stress test, your database (and you) could be stressed as well. This is known as the “noisy neighbor” syndrome. Just like noisy neighbors in a hotel, you can call the management and complain and they may even “move” you, just like a hotel would. To guarantee a certain level of resources, WASD made the premium database available.

There’s two different reservation sizes, P1 and P2, and these sizes are defined in terms of CPU cores, concurrent active workers, number of sessions, data IOPS, and memory. The relevant reservation size guarantees you these resources. The reservations are defined using the term “service level objective” (SLO).

Once you’ve been accepted for the preview program, you can create a new premium level database using the Azure portal or PowerShell Azure cmdlets. You can also upgrade an existing database to premium and also downgrade from premium to “regular”, subject to limitations, as well as change the reservation size. BTW, the cmdlets are a boon for those of us that would like to manage WASD but don’t want to perform repetitive operations via a web-based GUI. I strongly suggest you give them a try if you’re doing any Azure work.

One of the things you need to get used to when moving from SQL Server to WASD is that WASD does not expose all the DMVs and metadata views that SQL Server does, BUT ALSO, WASD contains some DMV and metadata views that SQL Server does not have. To be able to work with the Premium Database feature, there are six new metadata tables, all beginning with “slo” in the DBO schema in the master database. For example, if I have a premium database named “consistant_db”, I can monitor the premium change history by using the SQL query:

FROM dbo.slo_assignment_history
WHERE database_name = ‘consistant_db’
ORDER BY operation_start_time DESC;

I can also monitor resource activity by looking at worker_counts in sys.resource_stats to make sure I’m getting my money’s worth or to decide when it’s best to update/downgrade to premium.

Keeping on top of WASD changes, as well as knowing how and when to use WASD can be, like keeping up with anything in the Azure space, chasing a moving target. At the SQLIntersection conference in Orlando in April, I’ll be presenting a full day post conference workshop on “Windows Azure SQL Database from A to Z”. Join me there for the whole story on managing, developing on, and interacting with this beast.

Cheers, @bobbeauch

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 just too many excellent speakers to list them all, you’ll need to peruse this for yourself.

On Saturday before the conference, I’m doing a full day of SQL Server 2014 material. This includes some in-depth material on the new storage engine/execution engine/multi-version concurrency engine that was previously known at Hekaton. But it’s not just about Hekaton, I’ll cover everything database-related that’s in the new release, like the new columnstore and new cardinality estimation enhancements.

During the conference I’ll compare and contrast Windows Azure SQL Database (WASD) and SQL Server in an Azure VM, for those of you investigating going “to the cloud” and trying to decide on the proper vehicle. Also I’ll have a session on that age-old debate about an ORM-based vs. a stored procedure-based application. And a look at what Microsoft’s contributing to Hadoop, and their Azure-based implementation.

After the conference, I’ll have a WHOLE DAY to tell you all about WASD for folks who want to learn about that in more depth. It’s called “Windows Azure SQL Database from A-Z”.

Sounds like a fun week. If you’re at the conference, don’t forget to stop by and say hi.

Cheers, Bob


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 2014 that were designed to give your SQL the best performance. Which features are those? Turns out that it’s just about all of them!

There’s more information and a more detiled outline at the AddSkills website, or you can contact them directly. Hope to see you there!

Cheers, @bobbeauch

Getting close to SQLskills developer immersion event in February

I’m gearing up for my first SQLskills Immersion Event of the year, Immersion Event for Developers (IEDEV) in Tampa on Feb 17-21.

This one is getting kind of jam-packed with material because, in addition to coverage of T-SQL and its associated topics like query and procedural code performance and transactions and locking, this event will round out your toolbox. They’ll be coverage of specialized techniques such as coding with SQLCLR (.NET procedures, types, and aggregates), SQL and XML, Service Broker, Spatial, and Fulltext Search, as well as designing for tracking changes. As with T-SQL, when using these special techniques, the emphasis is always on performance and using the right tool for the job. We’ll see a lot of query plans and delve into details about specialized index usage. I’ve also included some information and demos on how SQL Server 2014 features fit into the big picture.

Registering soon will give you time to get geared up too, for the sheer amount of information we’ll cover. And, as always, they’ll be some after-hours events and giveaways. Arrive well-rested… If you have specific questions about the coverage, feel free to send me mail before hand. Hope to see you in sunny Tampa in February!

Cheers, Bob

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 table’s C-based specific access routines. But, in the meantime, I’d worked with a friend of mine who was trying to convert some custom SQLCLR code to compiled T-SQL. He was using table variables in place of CLR objects and, although the code run way faster than the non-compiled T-SQL code, it was still 90% slower than his SQLCLR equivalent.

In general, you’ll get the biggest speed increase from a compiled sproc if you use do lots of logic in code, or use nested loops in plans that return lots of rows. I thought I had just the use case.

Once upon a time, someone had given me code that did a lot of calculations in SQLCLR and in T-SQL. The SQLCLR code uses 2-and-3-dimensional arrays as well as some data access. The T-SQL code was an unoptimized, line-by-line port of the SQLCLR code used multiple temporary tables and a cursor over outer iterator. You could remove the outer cursor and still SQLCLR performed 100 iterations (100 products in this case) 7-8 times faster than T-SQL performed 1 iteration (1 product). In other words, SQLCLR was about 700-800X faster than T-SQL. Folks used to tell me this was “cheating” because of the large number of SQL queries the T-SQL code did (100s of queries, many using INSERT INTO a temp table). Nevertheless, it was worth a try.

Converting the code was pretty straightforward.
Replace temp tables with strongly-typed in-memory table variables
Make sure each table variable has a key and index (they’re required in in-memory table variables)
Change a subquery into another select into table variable + main query (compiled sprocs don’t support subqueries yet)
Make sure everything follows the rules for schemabinding
Didn’t use the outer cursor, so the compiled T-SQL code either performed a single iteration or called the compiled proc from within non-compiled T-SQL cursor code

I also had to wait until CTP2, because the procedure used DATEADD. That’s wasn’t supported until CTP2 (good choice of what to support sooner, SQL team, thanks for adding date functions).

The results for switching to in-memory tables were encouraging:
SQLCLR – standard tables – 5 sec
TSQL – standard tables – 37 sec (x100 = 370 sec) – 740X slower
TSQL – in-memory tables – 10 sec (x100 = 100 sec) – 200x slower
SQLCLR doesn’t support in-memory tables through context connection. I didn’t try it with an external connection back into the same instance.

Switching to compiled sproc (and pre-compiling the sprocs to factor out the first-time compile overhead for each)
TSQL – in-memory tables – 10 sec (x100 = 100 sec)
Compiled TSQL (which only supports in-memory tables) – 10 sec (x100 – 100 sec)

So for this particular use case (just a straight port, didn’t try to optimize the code), the difference was minimal. BTW, I’m not pretending this is a benchmark or anywhere near, just a quick test and some round numbers for comparison.

Then I took a harder look at my code. The SQL code was doing a large *number* of SQL statements, each of which used and returned a small number of rows. The computations were done, not in program logic, but with iterations over SQL statements (not a good way to do custom computations in ANY case). Horrible use case, I guess. Lessons learned:
-In-memory tables are not multidimensional arrays or custom “objects” as far as speed is concerned.
-The SQL statement still has overhead over in-memory operations even with simple SQL in a compiled sproc
-Iterating with SQL statements doesn’t substitute for aggregations and computations done in a single SQL statement (I knew that one already).
-Remember where the sweet spot is for compiled stored procedures.

So folks, don’t throw away your custom SQLCLR sprocs that do custom computations just yet. But do give it a test. And push as much of the computation as possible into the SQL statement and optimize those SQL statements.

Cheers, Bob

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 to improve the query plan, I’d encourage you to read his series of posts. If you don’t know what a cardinality estimator is or why you’d care, Ben Nevarez’ article is a nice intro. And, as Joe says and I agree, documentation on the XEvent is sparse.

Lately, I thought it would be nice to demonstrate how the cardinality information emitted by the XEvent shows up in the query plan, and have been studying a number of plans v. event traces to find a pattern by brute force. But it was right in front of my face… While setting up the XEvent trace (again), I happened upon this description one of the event’s fields.

stats_collection_id – “ID of the stats collection generated for the input. This same ID will also appear in ShowplanXML as an attribute of the RelOp element when the event is enabled.”

Ohhh. So, you need to enable the event (i.e. turn on the XEvent trace), then run the query with the query plan turned on. Turning on either an estimated plan or actual plan will work, but actual plan is the best because you can see the estimated v. actual rows affected, and see not only whether the estimate was good, but how the cardinality estimator arrived at the estimate.

And when you do this, each plan iterator contains a new property, StatsCollectionId. Which corresponds to stats_collection_id in your XEvent trace. You need to show the properties window (click on the iterator to select it, then hit F4) to see this property. Or look in the XML directly (and we know how much fun that is…). So, graphic showplan and F4 is your friend.

The first time I tried this, with a really simple query, I was really confused. The plan consisted of a single ClusteredIndexScan, and that iterator had a StatsCollectionId of 1. The XEvent trace had 5 events with stats_collection_id of 2,2,3,3 and 5. Hmmm…

Then I tried a more complex plan, figuring that I had more chances to see some correlation. More iterators to try. Sure enough. There are no cardinality estimate rows for a scan. Because they don’t estimate cardinality for a scan, they know how many rows there are in each table or index. So you won’t find correlation between XEvent and scan iterator, or I just haven’t seen it so far. On every other iterator in the plan, there’s nice correlation. OK, now that we have correlation, let’s extract the information from the event.

There’s three main fields, defined in the event description like this:
1. input_relation – “The input relation on which cardinality is being updated”. This is, which plan logical operator (e.g. LogOp_GbAgg) is being estimated, what statistics were used as input.
2. calculator – “The strategy used for the estimate”. Which calculator was used to calculated cardinality. BTW, it is perfectly valid for this fields to be empty (empty Calculator element), guess that means they didn’t need a cardinality estimation strategy.
3. stats_collection – “The stats collection generated for the input”. What type of stats were used and which stats were loaded.

So each record in the trace represents Input -> Calculator -> Output …And you can read your way through a whole plan/events pair this way.

This post is getting kind of long (there will be more to come) and I’ll end with the observation that there are lots more XEvents in the trace than there are iterators in the plan. What’s the extra ones for? At this point I’m thinking that, since this information is gathered as the plan is being produced, these events represent intermediate steps in the estimation process, and only one step is tied to the plan iterator. But that’s a guess for now.

For your enjoyment, I’ve included an example with a SQL query, query plan, XEvent trace, and SQL/XML query to take apart the XEvent trace and look at what I thought were relevent subelements and attributes of the event fields. There are also some information and samples in my previous post, and in Joe’s posts, to get you started.

Cheers, @bobbeauch

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:

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 the L1 and L2 cache built into CPUs.

These sets and their corresponding lookasides have to do with how memory-optimized tables do transactions against multi-version concurrency control (MVCC) tables (memory-optimized tables in SQL Server 2014 use MVCC). The three basic transaction types supported by MVCC tables (lowest to highest isolation) are snapshot, repeatable read, and serializable. In all isolation levels, reads are performed as of the beginning of the transaction. So any of these levels will read rows where the begin-time of the transaction is between the begin-timestamp and end-timestamp of the row.

Snapshot transactions require no additional processing, simply read the correct versions. Repeatable read means that the version you’d read at the end of the transaction must be the same as you’d read at the end. Serializable means repeatable read and in addition, if you’re doing a scan (e.g. select rows where id is between 10 and 20) no new rows in your range were added between beginning and end of your transaction. READ_SET and SCAN_SET have to do with repeatable read and serializable transactions, respectively.

SQL Server MVCC accomplishes the additional checks by **re-reading the rows at commit time**. Sounds expensive. It needs to make this re-reading fast, so instead of doing the whole operation again, repeatable read transactions keep a pointer to each row they’ve read in a READ_SET and check the pointer again at commit time to see if they’re the same. You can see this behavior on a system with no other activity, in sys.dm_xtp_system_memory_consumers.

— execute sys.dm_xtp_system_memory_consumers and check the READ_SET_LOOKASIDE amount
select * from sys.dm_xtp_system_memory_consumers;
— now repeatable read transaction using AdventureWorks memory-optimized table sample
begin transaction
select * from Sales.SalesOrderHeader_inmem with (repeatableread);
— note READ_SET_LOOKASIDE should be bigger
select * from sys.dm_xtp_system_memory_consumers;

For a serializable transaction you need to ensure read consistency with the READ_SET and also ensure scans will produce the same rows and no additional rows with SCAN_SET. Choose a different table if you want to see them both increase, because, if there’s no other changes in Sales.SalesOrderHeader_inmem (from the first experiment) the READ_SET_LOOKASIDE will retain the same rows/same size:

— execute sys.dm_xtp_system_memory_consumers and check the READ_SET_LOOKASIDE and SCAN_SET_LOOKASIDE amount
select * from sys.dm_xtp_system_memory_consumers;
— now repeatable read transaction using AdventureWorks memory-optimzed table sample
begin transaction
select * from Production.Product_inmem with (serializable);
— note READ_SET_LOOKASIDE and SCAN_SET_LOOKASIDE should be bigger
select * from sys.dm_xtp_system_memory_consumers;

So what about the WRITE_SET then? Transactions that update MVCC rows replace the end-timestamp of the rows they update with the transaction ID while the transaction is running. A bit indicates whether the information is an end-timestamp or a transaction ID. The transaction ID also prohibits concurrent updates of the same row (it’s not a lock, transactions that attempt concurrent update will simply fail and roll back the transaction). They’ll also insert new rows with a transaction ID as a begin-timestamp.

Transaction commit happens like this:
Acquire an end-timestamp
Validation (see READ_SET and SCAN_SET above)
Harden to the transaction log
Update the timestamps in the updated/new rows

The WRITE_SET is used to make updating the timestamps fast. Because this entry is getting a bit long, I’ll leave it to the reader to observe WRITE_SET activity using the DMV. And I’ll also leave it to you to figure out how the transaction commit series of events guarantees transactionally consistent data. Hint: edge-cases and additional information is available in my previous blog entries of the subject.

Cheers, @bobbeauch

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 post.

First off, although you can use memory-optimized table variables outside of compiled stored procedures, I think the biggest use for them will be in compiled procedures, mainly because compiled procedures don’t support #temporary tables. So, everywhere folks use and abuse temp tables, they’ll be using memory-optimized table variables as a replacement. Be careful of this however, because memory-optimized table variables, like “ordinary” table variables, have no statistics and, absent of an OPTION RECOMPILE hint, other SQL statement always use an estimate of one row for them.

Memory-optimized table variables must be strongly typed, so you start by creating a table type with the CREATE TYPE DDL statement. You can’t use a durability specification in this statement, but that only makes sense, because memory-optimized table variables are non-durable by definition. They don’t participate in availability group failover. The compiled code module for them is created at CREATE TYPE time and uses a naming conversion for a ‘v’ in the name (e.g. xtp_v_15_277576027.dll), rather than ‘t’ for memory-optimized table modules or ‘p’ for compiled procedure modules. Because there is no data load at SQL Server startup, this module isn’t created after a instance restart until the first time its used to create a variable.

It’s important to realize that these table variables don’t live in tempdb; they live in the memory-optimized object space of the database in which they are created. This takes pressure off tempdb, but they can compete for memory with other memory-optimized objects in the same database. In a short test, as I was doing a bunch of inserts into a memory-optimized table, I instanciated a memory-optimized table variable and filled it with a large number of rows. The inserts into the table (not the table variable) started failing because the combination exceeded my memory allocation for optimized objects in that database. It’s also possible for a series of memory optimized variables that are active at the same time to exceed the memory allocation and fail due to lack of memory. Granted that because table variables are usually kept to a small number of rows and have a limited lifetime, this may not be an big issue; my concern was the proliferation that could result because they replace temp tables in compiled stored procedures.

There are a few more surprises. Individual memory-optimized table variables have no metadata like “ordinary” table variables do, so you can’t tell how many you have active at a time by consulting the metadata. They do appear in sys.dm_db_xtp_memory_consumers as memory_consumer_type of PGPOOL. This consumer is “used for all table variables and includes usage for serializable scans” according to the BOL description of that DMV. However, the pool doesn’t appear in sys.dm_db_xtp_table_memory_stats at all. This means that it doesn’t appear in the SSMS “Memory used by memory-optimized objects” report, even under the “system allocated memory” category. This is useful to know if you use the report or sys.dm_db_xtp_table_memory_stats to monitor usage.

Finally, about memory-optimized table variables and garbage collection. Although these variables may participate in garbage collection during their lifetime (e.g. for updates or deletes), when the variable goes out of scope, the memory it used is released in it’s entirety (sys.dm_db_xtp_memory_consumers/used_bytes goes to zero). There is a small amount of memory (up to ~62.5 mb or so, in my tests) that remains allocated in this pool after the last variable goes out of scope, but under low-memory conditions, even this is given back.

Bear in mind that this is all based upon observed behavior in SQL Server 2014 CTP2 and may change by RTM. If it does, I’ll update this post.

Cheers, @bobbeauch