Category Archives: SQL Server

SQL 2019: PMEM, Enlightenment, and the Hybrid Buffer Pool

1 Reply

f you pay attention to hardware innovations as they relate to SQL Server, you’ve probably heard of PMEM, aka Persistent Memory, or Storage Class Memory. I blogged about PMEM in October of 2016, when HP Gen9 servers first started to support it (my post is here). If you are unfamiliar with PMEM, I suggest you read that post and watch the linked videos, before reading further.

With the impending release of SQL 2019, things are about to change in potentially big ways with regard to drastically reducing storage latency.

But first let’s reiterate how PMEM is supported on SQL Server 2016 running on Windows Server 2016.

SQL 2016 supports placing the “tail of the log” on a PMEM device (NVDIMM-N modules), which had the effect of reducing or eliminating WRITELOG waits. I was quite interested in that specific wait type, because of my keen interest in the In-Memory OLTP feature. If by deploying Hekaton you removed some of the bottlenecks associated with the traditional engine, usually the next bottleneck that appeared was the transaction log itself, in the form of WRITELOG waits. The log could keep up when the traditional engine was the bottleneck, but PMEM can bypass the storage stack, and it often eliminates or reduces transaction log latency. However, PMEM cannot do this alone — it must be used in conjunction with an application specifically written to leverage its capabilities (like SQL Server).

One issue for adopting PMEM for tail of the log on Windows Server 2016 is the fact that there is no support for TDE (but see the Hybrid Buffer Pool section below). Also, in the first release of PMEM from HP, there was limited capacity. This was not an issue because the tail of the log only consumes about 20MB per database (that’s not a typo, it’s megabytes), and the minimum size of a NVDIMM-N module was 8GB. For tail of the log on Windows Server 2016, you had way more PMEM capacity than required.

General restrictions/potential issues with deploying NVDIMM-N PMEM (not specific to any version of Windows Server or SQL Server) include:

  • it occupies memory slots that could otherwise be used by DRAM, thereby reducing the total memory capacity on the server

  • It’s not compatible with LRDIMMS

HP now makes NVDIMMs that operate at two different speeds. If using NVDIMMs that are slower than DRAM, all memory on the server will run at the slower speed. HP Gen10 servers support NVDIMMs that run at 2666Mhz, which matches the upper clock speed of RDIMMs (which they are compatible with). In that case, there should not be any server-level reduction in memory speed.

PMEM can be formatted as block storage – which will always navigate the storage stack – or as DAX (Direct Access) – which bypasses the storage stack. PMEM formatted as DAX can be considerably faster than when formatted as block storage (see section on TDE below, about DAX formatted PMEM still navigating the storage stack). Files placed on a DAX formatted volume become “memory-mapped”, and are byte-addressable.

Intel Optane DC Persistent Memory

Intel has recently joined the field, offering PMEM in the form of Intel Optane DC Persistent Memory, which has capacities large enough to host database files. It can operate in three modes:

  1. Memory mode – in this mode, Optane will not be persistent – data will be lost if there is a power failure or server shutdown/boot. Actual DRAM on the server is used as a hot tier with the memory capacity seen by the OS equivalent to the amount of Optane PMEM on the server.

  2. App Direct – this mode is used to persist data without navigating the storage stack, in conjunction with an application designed to take advantage of it (like SQL Server).

  3. Mixed mode – allocate some capacity to memory mode and some to App Direct mode.

At MS Ignite 2018, Cosmos Darwin demonstrated a reference architecture that used Intel Optane DC, and it achieved almost 14 million IOPS (the Ignite video can be seen here).

Anandtech reviewed a 512GB Intel Optane (Apache Pass) module here. Another thing to be aware of is that in order to use Intel Optane modules, your server must be running Cascade Lake CPUs (like Coffee Lake).

SQL version support for PMEM

SQL 2017 on Windows Server 2016 behaves the same as SQL 2016 on Windows Server 2016 – “tail of the log” is supported. However, there is no support for PMEM with SQL 2017 on supported Linux distributions (except as a traditional block store). Using PMEM with SQL 2019 on Linux supports what’s known as “enlightenment”, which allows us to place data and log files on DAX formatted volumes, thereby reducing latency considerably. SQL 2019 on Linux also support “tail of the log”.

To recap, for NVDIMM-N and Optane:

  • Windows Server 2016/Windows Server 2019 tail of the log: supported for SQL 2016, SQL 2017, and SQL 2019

  • Windows Server enlightenment: not available

  • Linux tail of the log: supported on SQL 2019

  • Linux enlightenment: supported for data and log files on SQL 2019

Hybrid Buffer Pool

As of SQL 2019 CTP 3.0, PMEM can be used with SQL Server 2019 running on Windows Server 2019 or supported Linux distributions to create what’s known as a Hybrid Buffer Pool. Think of it as a combination of traditional buffer pool memory (but for clean pages only), plus memory-mapped files. If the pages are clean, the engine will either reference the relevant pages in the traditional area of the hybrid buffer pool, or memory-mapped files on PMEM. This will be transparent to the user and/or application, but DBAs and application developers should be aware of the potential repercussions (the documentation for the Hybrid Buffer Pool can be found here).

Earlier in this post, I mentioned that TDE is not compatible with PMEM for Windows Server 2016, for which the only PMEM use case was tail of the log. However, if you use the Hybrid Buffer Pool, it’s now possible to use TDE if PMEM is installed (for both Windows Server 2019 and supported Linux distributions).

If we use enlightenment, data and log files would be memory-mapped, circumventing the storage stack – BUT – if you then enable TDE, even though the files are memory-mapped, they will be accessed as a block device, which requires navigating the storage stack. In this scenario, it might not make sense to have placed your data files on an enlightened device. But this should be verified though rigorous testing.

In addition to TDE, other features/settings that cause SQL Server to navigate the storage stack for DAX formatted PMEM are:

  • page verification of torn page

  • anything that uses the persistent version store (the only feature I’m aware of in SQL 2019 that uses PVS is Accelerated Database Recovery)

Some astute readers might be wondering why you’d want to use enlightenment on Linux, and not just use HBP from the start. Keep in mind that as stated above, the Hybrid Buffer Pool is only for clean pages that don’t exist in the buffer pool, so that’s a limitation you’ll have to consider.

Another way to think of it is that the Hybrid Buffer Pool is ideal for a workload that is very heavy on the read side (normally causing buffer pool thrashing or needing to access lots of pages outside the buffer pool), while enlightenment can optimize both reads and writes.

It’s not known at this time if the Hybrid Buffer Pool will be an Enterprise-only feature.

Syntax

Enabling the Hybrid Buffer Pool can be done at the instance or database level.

Instance-level syntax:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = OFF;

Database-level syntax:

ALTER DATABASE <databaseName> SET MEMORY_OPTIMIZED = ON;

ALTER DATABASE <databaseName> SET MEMORY_OPTIMIZED = OFF;

Despite the fact that “memory-optimized” appears in this syntax, it should be noted that enabling/disabling an instance or database for Hybrid Buffer Pool has nothing to do with the In-Memory OLTP feature, which the documentation may also refer to as “memory-optimized” databases.

Also potentially significant is the fact that the syntax for enabling/disabling Hybrid Buffer Pool may change before RTM.

Cost

If you check the pricing on the web, you can see that 128GB Optane PMEM costs around $2,000, 256GB costs almost $7,000, and 512GB costs over $21,000. And don’t forget, that does not  include the cost of Xeon Gen 2 CPUs, which are required.

Xeon Gen2 processors are listed here, with core counts from 4 to 56.

Supermicro supports Xeon Gen2 CPUs, and pricing for one of their servers can be seen here:

At that link, support for larger amounts of memory costs between $21,000 and $42,000 per cpu, and the fastest CPUs have a very high core-count (often 24 to 56 cores).

When you add up the cost of PMEM, the required Xeon Gen 2 CPUs, and SQL Server licensing, adoption of Optane Persistent memory may not be feasible for a lot of workloads that might benefit from its usage. You might start asking yourself if using NVMe storage and more memory would be a better fit budget-wise.

Availability Groups in general

For SQL Server workloads with extremely high throughput requirements that also require HA (and which workloads don’t?), it’s likely that PMEM will expose the fact that HA underpinnings can be a bottleneck, specifically for synchronous Availability Groups.. This may even prevent adoption of PMEM, until a suitable HA transport is available. To my knowledge, no database vendors currently have a solution to this problem; PMEM can be so blazingly fast, everything else in the chain is a bottleneck, including networking and HA transport. I have been advised that push mode RDMA might be a solution on the networking side of things, but I don’t believe that will be available for a while. Allan Hirt blogged about using RDMA for AGs here.

Due to the way the log transport mechanism is architected, PMEM usage with tail of the log for a database that belongs to an Availability Group is supported on secondary replicas only.

That means in order to achieve the same performance in case of failover, you would have to install PMEM on all replicas, in case they must operate as a secondary replica at some point. This would potentially drive up the price of adopting PMEM with Availability Groups and FCIs, especially if using Optane PMEM.

Availability Groups, enlightenment, and Hybrid Buffer Pool

On supported platforms (Linux), both enlightenment and Hybrid Buffer Pool are supported.

In-Memory OLTP and Enlightenment

As of this writing, enlightenment will not be supported for data/delta files (also known as checkpoint file pairs) for the In-Memory OLTP feature.

Gratitude

In November of 2018, PMEM – as it pertains to SQL Server – was owned by Argenis Fernandez (t) of the SQL Server Tiger Team, and I am greatly indebted to him for taking the time to explain the nuances of SQL Server interaction with PMEM, and how Microsoft envisions its usage in SQL 2019. I must also thank my friend Brian Carrig (t), who currently owns PMEM on the Tiger Team, for his patience in confirming what I believed to be correct, reorienting me when I was off-base, and resolving answers to my endless questions. Without either of these generous and brilliant technologists, this blog post doesn’t get written (thanks, gents!).

Finale

These are exciting times for DBAs, architects, and developers, and the evolution of PMEM provides choices that were not envisioned even just a few years ago. I look forward to seeing how this space evolves, as the technology matures, and applications are architected to leverage it. SQL Server integration with PMEM is a very forward looking move on the part of the SQL Server team.

Database Administration: A Point of Departure

2 Replies

In this post, I want to delve into one aspect of managing your career as a DBA that’s not often discussed: being a DBA is likely best used a point of departure for a different, but related role.

Don’t believe me?

Just look at some of the folks who used to be DBAs that have moved on:

Erin Stellato, now a consultant for SQL Skills
Glen Berry, now a consultant for SQL Skills
Jonathan Kehayias, now a consultant for SQL Skills
Brent Ozar, created a consulting and training company
Argenis Fernandez, worked for Pure Storage, now works on the SQL Server Tiger Team
Brian Carrig, now works on the SQL Server Tiger Team
Mike Fal, database manager at Rubrik
Denny Cherry, started his own consulting company
Joey D’Antoni, now a consultant for Denny Cherry and Company
Chris Adkin, pre-sales for Pure Storage
Thomas Grohser, architect for a hedge fund
Kendra Little, worked for Brent Ozar Unlimited, now works for Redgate

and the list goes on….

Why have all of these great technologists abandoned the DBA role? I’m guessing that there are several possible reasons:

  • who wants to do the same thing for 40 years?
  • more financial opportunity in sales and/or consulting
  • they move up, becoming database managers

Under specific circumstances, you might have a long career as a DBA, but if you’re not smart about it, your options can be limited.

Not mentioned so far is the fact that the likelihood of getting good/great roles as a DBA diminishes as you get older. Here in the USA, it’s not legal to ask a job applicant their age, but employers often get around that by asking you what year you graduated high school (why on earth would that be relevant, except to determine your age?).

How many older, gray-haired DBAs do you see in the field? Not too many, I’m guessing. An exception to this might be a DBA that has been at the same company for a very long time. Or someone who was hired specifically because they have decades of experience.

So, is being a production DBA the exclusive domain of younger technologists? I suppose that depends on where you draw the line between “young” and “mature”. For example, one of the all-time greatest DBAs was Robert Davis, aka @SQLSoldier. While he may have had a stint or two outside the DBA role, for almost all of his career, he worked as a DBA. Unfortunately, Robert passed away in early 2018, so we’ll never know if he would have stayed on that path. He did have consulting jobs on the side, but once he started to work at a hedge fund here in NYC, the hoops he had to jump through to get “approval” weren’t worth the hassle, so he no longer did outside work. 

Consulting shops are often the next stop in a DBA’s career. But there can be a lot of travel when working for a consulting shop, and that lifestyle isn’t for everyone (especially if you have young kids).

A superset of the DBA skill set would be that of an architect, which requires deep expertise in a variety of areas, such as storage, networking, HA/DR, perhaps Azure and/or AWS.

If you have less working years left than you’ve already worked, you might consider staying in the DBA role for your remaining working years. But that role is evolving,  and you’ll probably go the way of the dinosaurs, unless you also evolve.

If you’re a younger DBA – how sure are you that what you do on a daily basis will not be automated away by the cloud over the next decade?

Sharks must keep moving, or they’ll die. DBAs are pretty much the same, but have to be smarter than sharks about where they move, and what they move into.

What dedication and community engagement can do for your career

4 Replies

In July of 2012, I started a new role, but after a few months, I could see that there wouldn’t be much opportunity for me to learn there, and/or the pace of learning was simply too slow. The biggest problem I faced was that I had to move forward in the professional development realm on my own time. A brief overview of my life schedule looked like:

  • Monday to Friday: work from 10am to 6pm, get home and study SQL Server until 2am
  • Saturday and Sunday: study SQL Server from 10 am until 2am

Yeah, that’s not much of a life – or to be brutally honest, that’s no life at all, and I did this from 2012 until just last week. I’d say that at least 45 to 48 weeks of the year, I stuck to that schedule.

My work role was split between SQL development and DBA tasks, and it was a pretty small company. I was trying to get a dedicated DBA role, but that type of role usually exists at larger companies, and without recent large company experience, I was often not a good fit for the roles I was seeing. Add to that the fact that I have zero SSIS in my career (many roles require that), and we have a stumbling block to moving forward/upward.

SET PERSEVERANCE ON

In the interest of attaining advanced knowledge of SQL Server, I attended the following training and conferences since 2011:

  • 2011, SQL Skills Immersion Event (Performance Tuning)
  • 2013, SQL Cruise – on this trip I met Aaron Bertrand, Mike Fal, Stacia Varga, Brandon Leach, Buck Woody, Tim Ford, and others
  • 2014, Brent Ozar – Senior SQL DBA
  • 2015, Allan Hirt , Mission Critical SQL Server
  • 2016, Edwin Sarmiento online HA class
  • PASS Summit, 2013, 2015, 2016, 2017, 2018

I devoured blog posts from Brent, Jonathan Kehayias, Robert Davis, Paul Randal, Kimberly Tripp, Paul White, Aaron Bertrand, Kendra Little, Edwin Sarmiento, Allan Hirt, and many others.

As is often said, if you really want to learn something, you’ve got to teach it, and that’s why since 2016 I’ve been blogging and presenting at many SQL Saturdays across the USA.

I always believed that my next role would come from engagement with the SQL community – that someone out there would recognize my dedication, passion for learning, and desire to help others. I came close to getting a new role a few times, but nothing panned out, although during the initial phone screen for one of the positions I applied for, the interviewer told me that he had solved a production problem from reading one of my blog posts.

Not long ago I saw a post from a colleague on the NYC SQL user group message board about needing to fill a role for a strong DBA, and I’m thrilled to write that I’ve got a new dedicated DBA role at an international financial powerhouse. What struck me during the interview process was that I was not asked a single technical question about SQL Server – it seems my reputation had preceded me.

There are benefits to dedicating yourself to a life of learning, and helping others — you just never know when it might pay off.

 

Frameworks O How I Hate Thee

4 Replies

I’ve seen a lot of tech come and go in my time, but nothing I’ve seen vexes me more than “framework generated SQL”.  No doubt I’m ignorant about some aspects of it, but its usage continues to confound many a DBA.

To troubleshoot one of these bad boys, you might consider Google Glass, but it will fail you. The first issue is that these crappy frameworks generate a code tsunami that’s almost (or actually) unreadable by humans. The tables you know and love are aliased with names such as “Extent1” and the like. Multiple nestings of that, and it’s all gobbledygook aka spaghetti code.

Developers love frameworks, because they don’t have to spend time coding/maintaining SQL queries. I’m guessing it’s mostly used for UI, cause if it’s used for much more than that, performance is likely to absolutely suck. So you wicked smart developers theoretically save a bunch of money generating SQL for your UI, but then – because you have a totally crap schema – you have to pay expensive DBAs to drill down and resolve performance issues. And in the end, if your code and/or schema is bad enough, you relent, and convert it to a stored procedure call, which is exactly what your sharp DBA told you to do 20 billable hours ago.

A typical response to why developers use frameworks for databases is that they want their code to be “portable”. How many times have you seen a shop change database platforms? I could understand that argument if you used frameworks for all your code, reports, UI, everything. But if you use frameworks for the UI, and stored procedures for reporting, I guarantee that you’d have a heck of a time making that stored procedure code “generic”, such that it could be used against Oracle, Sybase, SQL Server, or DB2.

The more I think about it, I should totally love frameworks. I say that because if they were not in use, think of all the times I’d be stuck trying to improve performance, when now I can simply say: “Hey – that’s a framework query, and there’s absolutely nothing I can do about it – have a nice day….”

 

SQL 2019 In-Memory hotness

4 Replies

SQL 2019 is on track to become one of the most awesome releases – the product touches so many realms of the data platform, it’s truly mind boggling.

Since I have such a keen interest in Hekaton/In-Memory OLTP, when the CTPs are released for a new version of SQL Server, I look forward to any potential announcements about that feature.

So far, there’s been only one publicly announced enhancement for In-Memory OLTP in SQL 2019: system tables in TempDB will be “Hekatonized”. This will forever solve the issue of system table contention in TempDB, which is a fantastic use of Hekaton. I’m told it will be “opt in”, so you can use this enhancement if you want to, but you can also back out of it, which would require a restart of the SQL Server service.

But there’s at least one other enhancement that’s not been announced, although the details of its implementation are not yet known.

When you start to research the Hekaton feature, most are shocked to learn that CHECKDB does not verify anything about durable In-Memory tables: it silently ignores them.

That appears to have changed in SQL 2019, although either the informational message about what it does is misleading, or behind the scenes it does something different.

This is the output for DBCC CHECKDB of a memory-optimized database in SQL 2017:

Object ID 949578421 (object ‘inmem_table’): The operation is not
supported with memory optimized tables. This object has been skipped and will not be processed.

(the emphasis was added by me)

This is the output for DBCC CHECKDB of a memory-optimized database in SQL 2019:

DBCC results for ‘inmem_table’.
There are 101 rows in 3 pages for object “inmem_table”.

Why do I say the message is misleading?

Because durable data for memory-optimized tables is not stored in pages, but instead in a streaming fashion in files known as checkpoint file pairs (or data and delta files). Also, while it’s true that there are 101 rows in this table, the engine pre-creates a number of data and delta files, and it would make DBAs sleep a lot better at night, if all of those files were verified as being corruption free.

We’ll just have to stay tuned to the future CTPs and RTM of SQL 2019 to see how all of this shakes out.

Dangerous moves: Setting max size for In-Memory OLTP containers

5 Replies

I recently saw a thread on twitter, where the OP talked about setting the max size for an In-Memory OLTP container. I responded as I always do: it’s not possible to set a limit on anything having to do with storage for In-Memory OLTP.

Unfortunately, that’s not correct: through SSMS or TSQL, you can in fact set a max size for a container.

But you should not ever do that…..

Why?

Because if you do, and your checkpoint files exceed the max size of the container, your database can go into the In Recovery, Suspect, or OFFLINE state. The following code reproduces this issue:

Note that I’ve not yet found a way around this. The OP from that thread on twitter said he had to actually restart the SQL Server service to resolve the issue with that database, but I don’t see why that would make any difference (when I tried it, the database attempted recovery, but eventually went offline).

Setting a max size for the container is a really, really really bad idea, because it guarantees that the database will have some form of outage when you hit the threshold. The bottom line is that containers must be free to grow, period. That’s part of the capacity planning good DBAs will do before deploying the In-Memory OLTP feature.

Trials and tribulations of learning Linux

Leave a reply

Decades ago, before Microsoft SQL Server existed, I spent $500 (quite a hefty sum in those days) attempting to learn C language programming and Unix. It was the best $500 I ever spent, because it informed me that my brain simply does not work well with that technology (or at least, it didn’t back then). Fast forward to 2017, and voila!: SQL Server runs on Linux. But this time, there are some big differences. For one thing, Powershell can ease the burden of learning *nix commands. Also, Linux has the ability to install a desktop.

And so I’ve begun my deep dive into various aspects of running SQL Server on Linux, and Ubuntu was my distribution of choice.

Windows man

This life-long Windows SQL Server DBA depends on the ease with which one can copy and paste in either direction between a guest VM and the host, using RDP – it’s a huge time saver. Folks in the Linux world love to type stuff, and that’s ok with me, because I started in technology in the days before Windows existed, so I’m a seasoned MS-DOS/command line guy.

While researching various aspects of what’s possible on Linux, I read a lot of blog posts, and some of them had long lists of commands. While I could have collected those commands into a file on my Windows host and copied that file to the Linux guest, I simply wanted to copy and paste to and from my Ubuntu VM running on Hyper-V.

Alas, that was not to be.

If you search the web for “copy paste Ubuntu Hyper-V”, you’ll find loads of answers in forums, dispensing all types of advice that might have been good at that time. But now it’s 2018, and I tripped across this blog post from Craig Wilhite @ Microsoft:

Sneak Peek: Taking a Spin with Enhanced Linux VMs

There, it details how to go about setting up Enhanced Linux VMs, and so I downloaded Ubuntu Server 18.04, and got to work, following that blog post to the letter.

Denied

I spent the better part of a week after hours, trying to get this to work, plugging the error messages into search engines to see what came back.

After entering credentials into xrdp, I received the message: “Video remoting was disconnected”, and searching for that led me to this thread on github, which is related to Craig Wilhite’s post.

So clearly, others had experienced this issue, but there didn’t seem to be any resolution. I posted a message, asking for what next steps I might take, and followed recommendations, but nothing panned out. Finally, Craig suggested that perhaps the difference was due to the fact that I was using Ubuntu server, and he had verified the steps using Ubuntu desktop. I just finished testing with Ubuntu desktop, and hallelujah, Enhanced Session Linux VMs work with Ubuntu desktop.

But the entire reason I wanted to experiment with the server version was to investigate Kubernetes, and I wanted to use Ubuntu server for that.

As luck would have it, the next day I attended a webinar given by Argenis Fernandez (b| t) on using SQL Server on containers, and during the presentation, Argenis mentioned MobaXterm, which allows copy/paste, and has a free version. So I reinstalled Ubuntu server, installed MobaXterm, and lo and behold, I now have bidirectional copy/paste between host and guest.

That’s how it is when you learn any new, unfamiliar technology – you spin your wheels, make mistakes, fail, and if you push through and leave your mind open, you can be rewarded with expertise.

New kid on the block: sp_BlitzInMemoryOLTP

3 Replies

In-Memory OLTP has been included in the last three releases of SQL Server, starting with 2014 through 2017, and now runs on Linux, Windows, Azure SQL Database, and Azure Managed Instances. Additionally, since SQL 2016/SP1, the In-Memory OLTP feature has been available in non-enterprise editions.

What does this all mean?

It most likely means that it’s only a matter of time before a memory-optimized database lands on your doorstep, and you’ll probably have no idea how or why it’s different.

For a while now, I’ve been working on a script to evaluate a SQL Server environment for anything related to In-Memory OLTP, and I had help with testing, general suggestions, and final touches from Konstantin Taranov and Aleksey Nagorskiy; their assistance was invaluable. Konstantin suggested to Erik Darling and Brent Ozar that my script be included as part of their great Blitz series, and the the result is…..sp_BlitzInMemoryOLTP.

It is now part of the awesomeness known as the First Responder Kit, and the direct link to the script can be found here.

sp_BlitzInMemoryOLTP reports on two categories: instance level and database level.

First let’s discuss which parameters it sp_BlitzInMemoryOLTP accepts, and then we’ll break out the results, section by section.

@instanceLevelOnly BIT

This flag determines whether or not to simply report on the server-level environment (if applicable, i.e. there is no server-level environment for Azure SQL Database). With this parameter, memory-optimized databases are ignored. If you specify @instanceLevelOnly and a database name, the database name is ignored.

@dbName NVARCHAR(4000) = N’ALL’

If you don’t specify a database name, then sp_BlitzInMemoryOLTP reports on all memory-optimized databases within the instance that it executes in, or in the case of Azure SQL Database, the database that you provisioned. This is because the default for the @dbName parameter is N’ALL’.

Example:

It’s also possible to report on a specific database name.

Example:

The results of calling sp_BlitzInMemoryOLTP this way are explained later in this post.

@tableName NVARCHAR(4000) = NULL

Example:

If you only want to report on a specific memory-optimized table, you would supply a value for the @tableName parameter, and sp_BlitzInMemoryOLTP will search through all memory-optimized databases, looking for memory-optimized user tables that match. There is currently no wildcard matching for the @tableName parameter.

@debug BIT

Using the @debug =1 parameter tells sp_BlitzInMemoryOLTP to only print the TSQL statements that would have been executed. This allows you (or more likely, me) to resolve problems like missing quotes, or other potential issues that can occur when using dynamic SQL.

Example:

Supported platforms

This script has been tested on SQL 2014, SQL 2016, SQL 2017, and Azure SQL Database. It has not been tested against Azure Managed Instances

In the comments, please let me know other things about memory-optimized environments and/or databases you’d like to see included in the script.

How to interpret the results for sp_BlitzInMemoryOLTP

When you execute sp_BlitzInMemoryOLTP, it runs several queries that pertain to the In-Memory OLTP environment. It should be noted that if there are no results for a given query, i.e. no temporal memory-optimized tables, sp_BlitzInMemoryOLTP does not return an empty result set (this keeps the clutter to a minimum).

For example, it could be that a memory-optimized filegroup has been added to a database, but no memory-optimized objects have been created. Depending on the version of SQL Server, there might not be details about the containers or files within them, so sp_BlitzInMemoryOLTP won’t return information on that.

Instance level

Instance level evaluates the following:

  • the version/edition of SQL server
  • SQL Server ‘max memory’ setting
  • memory clerks
  • XTP memory consumers, aggregated
  • XTP memory consumers, detailed
  • the value of the committed_target_kb column from sys.dm_os_sys_info
  • whether or not instance-level collection of execution statistics has been enabled for all natively compiled stored procedures (because this can kill their performance….)
  • when running Enterprise, if there are any resource groups defined, and which memory-optimized databases are bound to them
  • XTP and buffer pool memory allocations, because In-Memory OLTP can affect on-disk workloads
  • summary of memory used by XTP

Section 1: version/edition of SQL server

Documentation here.

● Section 2: SQL Server ‘max memory’ setting

Documentation here.

● Section 3: memory clerks

Documentation here.

● Section 4: XTP memory consumers, aggregated

Documentation here.

● Section 5: XTP memory consumers, detailed

● Section 6: the value of the committed_target_kb column from sys.dm_os_sys_info. The amount of memory that SQL Server can use for the In-Memory OLTP feature is a percentage of the committed_target_kb value. But be forewarned, this value is not static. Details in my post here.

● Section 7: whether or not instance-level collection of execution statistics has been enabled for all natively compiled stored procedures. Enabling this on a production server could be considered drastic. More details can be found in my post here.

● Section 8: if running Enterprise, if there are any resource groups defined, and which memory-optimized databases are bound to them. Binding memory-optimized database to a Resource Pool (using Resource Governor) is considered a best practice, but unfortunately this capability is still Enterprise only. But if you’re on that edition, you should also be monitoring how close to the out of memory threshold you’re getting, and fire an alert when required. More details in my post here.

● Section 9: XTP and buffer pool memory allocations, because In-Memory OLTP can affect on-disk workloads

Database level

For a given memory-optimized database (or all memory-optimized databases), database level evaluates the following:

  • all memory-optimized tables
  • all indexes on all memory-optimized tables
  • the average chain length for HASH indexes (and informs you if the bucket count is too low)
  • the number of indexes per memory-optimized table
  • all natively compiled stored procedures
  • which native modules are loaded (stored procedures only, and this is not relevant for Azure SQL Database)
  • the number of natively compiled procedures
  • whether or not the collection of execution statistics is enabled for any natively compiled procedures
  • if using the temporal feature for memory-optimized tables, the amount of memory consumed by hidden temporal internal tables (which are memory-optimized)
  • memory structures for LOB columns (off-row)
  • all memory-optimized table types
  • database layout, which includes mdf, ldf, ndf, and containers, and the size in various formats (KB/MB/GB). The totalSizeMB column is the total for the entire database (uses a Window Function).

Three separate result sets that describe containers:

  • Container details by container name
  • Container details by fileType and fileState
  • Container file details by container_id, fileType and fileState

For Azure SQL Database, sp_BlitzInMemoryOLTP:

  • verifies if you are running on the Premium tier (that’s the only tier that supports In-Memory OLTP)
  • displays all records for xtp_storage_percent, in descending order (more info here)
  • displays the status of XTP_PROCEDURE_EXECUTION_STATISTICS and XTP_QUERY_EXECUTION_STATISTICS (more info here)

The output in the photos that follow was returned from executing sp_BlitzInMemoryOLTP, for a database named OOM-DB. You can get information on all memory-optimized databases if you don’t supply a database name when calling sp_BlitzInMemoryOLTP.

● Section 1: Listing of memory-optimized databases on this instance of SQL Server

· Section 2: memory-optimized tables, including row counts

● Section 3: indexes on memory-optimized tables. It’s helpful to know how many, and what type of indexes there are.

● Section 4: average chain length for HASH indexes (if any). When a HASH index is created for a memory-optimized table, a value must be supplied for what’s known as the “bucket count”. But it doesn’t get adjusted automatically, and as a result, it can cause performance problems. More details here.

● Section 5: Number of indexes per memory-optimized table. SQL 2014 and SQL 2016 have a limit of 8 nonclustered (RANGE) indexes per memory-optimized table. That ceiling was lifted in SQL 2017, and I’ve tested creating several hundred indexes on a single memory-optimized table (but please don’t do that in production!).

clip_image028

Sections 6 through 8:

  • natively compiled stored procedures
  • which natively compiled stored procedures are currently loaded
  • how many natively compiled stored procedures there are

● Section 9: if using the temporal feature for memory-optimized tables, the amount of memory consumed by hidden temporal internal tables (which are memory-optimized). For temporal tables, there’s a difference between how things are handled if the temporal table is memory-optimized. I’ve written about that in this post.

● Section 10: memory structures for LOB columns (off-row). For memory-optimized tables, LOB columns are actually stored as separate tables, and this can lead to performance problems. MCM Dimitri Korotkovitch has a great post on it here.

● Section 11: memory-optimized table types. Yes, tables and table types can be memory-optimized, and you’ll want to be aware of the potential gotchas with those memory-optimized types, as detailed in my post.

● Section 12: all database files, including the name, size, and location for each container.

Sections 13 through 15 pertain to the amount of storage consumed by durable memory-optimized tables. The files that persist durable data to storage go through several state changes over time. As a result, the storage footprint for memory-optimized databases that contain durable data can be surprisingly large, relative to the amount of data that’s stored in memory (Microsoft suggest 4x memory-optimized data size as a starting point). So it’s a good idea to keep an eye on the storage footprint.

● Section 13: Container details by container name

One row per container, listing the aggregated size of all files within that container, as well as how many files per container

● Section 14: Container details by fileType and fileState

Here, the breakdown is a bit different, taking into account the type of file.

For each type of file, i.e. DATA or DELTA, aggregate the storage consumed and number of files for each file type, across ALL containers for this database. For example, there are a total of 11 files of fileType DATA with a fileState of ACTIVE, across all containers for this memory-optimized database. (Note that SQL 2014 has file types that don’t exist in later versions of SQL Server)

● Section 15: Container file details by container_id, fileType and fileState

For each type of file, i.e. DATA or DELTA, aggregated the storage consumed and number of files for each file type, PER CONTAINER.

In the prior example, we saw that there were a total of 11 files of fileType DATA with a fileState of ACTIVE, across all containers for this memory-optimized databases.

This result shows the breakdown of each fileType and fileState PER CONTAINER. The container named InMemDB_inmem1 has 3 files that have a fileType of DATA and a fileState of ACTIVE. So we expect to see 8 more files with this type and state, in the remaining containers. Sure enough, we see that the container named InMemDB_inmem2 has an additional 8 files with a fileType of DATA and a fileState of ACTIVE.

Understanding how In-Memory OLTP works (with all of its various gotchas) can only be addressed by putting in the required time. If you read the documentation, and then study the real-world deployment concepts detailed in my extensive blog post series on In-Memory OLTP, you’ll be on the right path. Once you begin to wrap your brain around In-Memory OLTP, you’ll need some help evaluating memory-optimized environments and/or databases, and that’s where sp_BlitzInMemoryOLTP can help.

In-Memory OLTP Resources, Part 4: OOM, the most feared acronym in all of In-Memory OLTP

1 Reply

Earlier parts of this series can be found here:

Part 1: The Foundation

Part 2: Checkpoint File Pairs

Part 3: OOS (Out of Storage)

This post will cover memory requirements and usage, and what happens if you run actually reach OOM, also known as ”Out Of Memory”, a condition that strikes fear in the hearts of DBAs supporting memory-optimized databases. We’ll also cover CPU-bound conditions.

How memory is allocated to the In-Memory OLTP engine

At a high level, the memory that’s allocated to the In-Memory OLTP engine comes from the SQL Server ‘max memory’ setting, as does everything else within SQL Server. But beneath that level, we need to be aware of memory pools.

image

The pool that can be used for allocating memory to the In-Memory OLTP engine depends on which edition you are running:

  1. if you are running Enterprise Edition, you can use Resource Governor to configure a Resource Pool. Memory-optimized databases can be bound to separate pools, or multiple databases can be bound to a single pool. If you don’t bind a memory-optimized database to a pool created with Resource Governor, then all memory allocations for In-Memory OLTP for that database comes from the Default pool.
  2. if you are NOT running Enterprise Edition, all memory for In-Memory OLTP is allocated from the Default pool.

If using the Default pool, then as a result of deploying the In-Memory OLTP feature, there can be performance issues with on-disk workloads.

The following image shows that as we add rows to memory-optimized tables – and put pressure on the buffer pool – the buffer pool responds by shrinking, and that can affect disk-based workloads. If we then delete rows from memory-optimized tables, the buffer pool can expand. But what if we don’t delete rows from memory-optimized tables? Then the buffer pool will stay in its reduced state (or shrink even more), and that can cause problems due to buffer churn (continually having to do physical I/Os to retrieve pages from storage, for disk-based workloads).

image

Astute readers will consider using Buffer Pool Extensions (BPE), which is available in Standard Edition only. Yes, you could do that, but BPE retrieves a single 8K page at a time, and can actually make performance worse. And in case you’re wondering, no, it’s not possible to compress memory-optimized data that’s stored in memory. Think Windows will actually page out any of the memory allocated to In-Memory OLTP? That’s simply not possible.

Resource Governor

If you are running Enterprise Edition, then this problem gets solved by creating a resource pool. Now, to be clear, that doesn’t mean you can’t run out of memory for memory-optimized objects. It only means that your In-Memory workload can’t affect the on-disk workload, unless of course you configure the resource pool incorrectly. I’ve got a blog post on how to monitor resource pools here.

Let’s create a resource pool, with an artificially low upper bound, and insert rows until we hit the limit.

On my server, I was able to INSERT 305 rows before the pool ran out of memory, and receiving error 41805:

image

Causes of OOM

What can cause a memory-optimized database to run out of memory? It could be that resource consumption (memory) exceeded:

  • the relevant percentage of committed_target_kb from the sys.dm_os_sys_info DMV (explained in a moment)
  • MAX_MEMORY_PERCENT value of a Resource Pool that the database is bound to (if running Enterprise Edition and using Resource Governor)

or:

  • garbage collection is not operational (the purpose of GC is to reclaim memory consumed by stale row versions)
  • updates to memory-optimized table variables caused row versions to be created, and because GC does not operate on table variables, you ran out of memory (for table variables that have a very large amount of rows)

The only thing that can prevent GC from working is a long running transaction.

committed_target_kb

We are supposed to base our belief of how much memory is available for our memory-optimized databases, upon committed_target_kb from the sys.dm_os_sys_info DMV. Memory available for In-Memory OLTP is expressed as a percentage of committed_target_kb, based on total system memory, which is detailed here. Prior to SQL 2016/SP1, the In-Memory OLTP feature was only supported on Enterprise Edition, and the amount of memory allocated to SQL Server was limited to what the operating system supported.

But in a post-SQL 2016/SP1 world, things are different, because the In-Memory OLTP feature is now supported on non-enterprise editions. This means that people will start deploying In-Memory OLTP on servers with a lot less memory than is possible with Enterprise, and therein lies a potential issue.

The problem is that committed_target_kb is a moving target. 

From the documentation:

Applies to: SQL Server 2012 through SQL Server 2017.
Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:
– the current state of the system including its load
– the memory requested by current processes
– the amount of memory installed on the computer
– configuration parameters
If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory.

Those parts about “the current state of the system including its load” and “the memory requested by current processes” concern me. If there is x amount of memory available on a server, and you check the value of committed_target_kb when the server is “at rest”, then under load there might in fact be much less memory available. I believe this is one of the main causes of OOM for memory-optimized workloads, especially when people do a POC on under-provisioned machines (like laptops).

Database restore and recovery

The process of recovering a database is different for databases with durable memory-optimized data.

Step 1: the backup file is read, and the various types of of files are created. For example, all MD/NDF/LDF and data and delta files are created.

Step 2: data is copied from the backup into the files created in Step 1. If you restore a database WITH NORECOVERY, you have completed both Step 1 and Step 2

Step 3: For databases with durable memory-optimized data, there is one additional step, and that’s to stream data from the Checkpoint File Pairs (data/delta files) back into memory

It should be noted that if the backup contains both on-disk and memory-optimized tables, none of the on-disk data is available until all of the memory-optimized data has finished streaming. When restoring a backup – whether the database has memory-optimized data or not – the process short-circuits if there isn’t enough free space to create the files in Step 1. Unfortunately, no such validation of available memory is done for Step 3. That means you can spend a long time creating files on disk, then spend an additional lengthy amount of time streaming data to memory, only to find that you don’t have enough memory. If you think Microsoft should change this, please upvote my Connect item.

When data is streamed into memory, the wait type will be WAIT_XTP_RECOVERY.

The unwary DBA would logically think that the only time you can see WAIT_XTP_RECOVERY is when actually restoring a database with memory-optimized data, but unfortunately that’s not correct. The Microsoft documentation doesn’t list all of the possible “recovery events” that can cause restreaming, but through my own testing, I’ve come with the following list:

setting a database:

  • OFFLINE
  • READ_ONLY when it was READ_WRITE
  • READ_WRITE when it was READ_ONLY

Also, setting Read Committed Snapshot Isolation ON or OFF, will cause restreaming.

Additionally, the speed of restreaming is directly influenced by the number of volumes that you have created containers on, and the IOPS available from those volumes.

Potential solutions to OOM

  1. Open a DAC (Dedicated Admin Connection). Then delete rows, and/or move data from memory to disk.
  2. Increase system memory
  3. If Garbage Collection for row versions is not operational (due to long running transactions), clear up those long-running transactions so that GC can proceed

If you attempt to move data from memory-optimized tables to disk-based tables, i.e. using SELECT INTO, please note that it’s possible to create schema for memory-optimized tables that you can’t simply migrate to disk.

For example, the following CREATE TABLE is perfectly legal for memory-optimized tables, but will fail for disk-based tables (and also fails if using SELECT * INTO on-disktable FROM in-memtable):

The ability to create tables like this is detailed at this link, with the relevant section being:

“…you can have a memory-optimized table with a row size > 8060 bytes, even when no column in the table uses a LOB type. There is no run-time limitation on the size of rows or the data in individual columns; this is part of the table definition.”

What happens if you hit OOM

So how does hitting OOM affect workloads for memory-optimized databases?

SELECT still works, and also DELETE and DROP, but of course INSERT and UPDATE will fail.

CPU bound

Last but not least, I wanted to touch on potential CPU issues for memory-optimized databases. Database recovery can be CPU bound under the following circumstances:

  • many indexes on large memory-optimized tables (2014, 2016)
  • too many LOB columns (2016+)
  • incorrect bucket count set for HASH indexes (2014, 2016, 2017)

The first item in this list, “many indexes on large memory-optimized tables (2014, 2016)” has supposedly been addressed in SQL 2017.

LOB columns are actually stored as separate memory-optimized tables, and as noted by Dmitri Korotkevitch (blog) in this post, can impact performance.

The “incorrect bucket size for HASH indexes” issue persists to this day. If the bucket count is too low, there will be many sets of key columns that hash to the same value, increasing the chain length, and having not only a terrible effect on performance in general, but database recovery in particular.

Wrapping up

Hopefully this mini-series about resource consumption for memory-optimized workloads has given you a clear understanding of why Microsoft recommends the following:

  • 2x data set in memory for starting memory allocation (only for In-Memory, does not include memory for on-disk workload)
  • 3x workload IOPS from disks where containers are stored (handles operational workload plus read/write File Merge workload)
  • 4x durable memory-optimized data size for initial storage footprint

These are rough guides, but should be observed at first, and then tuned as required.

This concludes the series on resources issues for In-Memory OLTP.

In-Memory OLTP Resources, Part 3: OOS (Out of Storage)

3 Replies

Zero free space

This is a continuation of Part 1 and Part 2 of this blog post series, related to resource issues/requirements for memory-optimized databases.

In this post, we’ll continue with simulating what happens to a memory-optimized database when all volumes run out of free space.

In my lab, I’m running Windows Server 2012. Let’s use Powershell to install the File System Resource Manager, which will allow us to create a quota for the relevant folder:

add-windowsfeature –name fs-resource-manager –includemanagementtools

After installing the Windows feature we can set the quota for the folder, but we shouldn’t enable it just yet, because first we have to verify the current size of the folder.

On my server, I created a quota of 1.5GB, and then enabled it.

Now let’s INSERT rows into the table, in batches of 1000, until we reach the limit (the INSERT script is listed in Part 2, I’m trying to keep this post from getting too long).

Once the quota has been reached, we receive the dreaded 41822 error – this is what you’ll see when all of the volumes where your containers reside run out of free space (if even one of the volumes has free space, your workload can still execute).

image_thumb[389]_thumb

Just out of curiosity, we’ll verify how many rows actually got inserted. On my server, I’ve got 4,639 rows in that table, and the folder consumes 1.44GB. So theoretically, there was enough space on the drive to create more checkpoint files, but it seems as though the engine won’t just create what it can to fit in the available space. It’s more likely that the engine attempts to precreate a set of files, and it either succeeds or fails all at once, but I’ve not confirmed that.

I disabled the quota, executed a manual CHECKPOINT, and ran the diagnostic queries again:

image_thumb[26]

File Merge

Data files persist rows that reside in durable memory-optimized tables, and delta files store references to logically deleted rows. As more and more rows become logically deleted across different sets of CFPs, two things happen:

  1. the storage footprint increases (imagine that all data files have 50% of their rows logically deleted)
  2. query performance gets worse, because result sets must be filtered by entries in the delta files, which are increasing in size

Microsoft killed both of these birds with one stone: File Merge (aka Garbage Collection for data/delta files)

In the background – while your workload is running – the File Merge process attempts to combine adjacent sets of CFPs, and this is where we get to one of the file states that we didn’t cover in Part 1: MERGE TARGET

A file that has the fileType of MERGE TARGET is the new set of combined data/delta files from the File Merge process. Once the merge has completed, the MERGE TARGET transitions to ACTIVE, and as we stated earlier in this series, ACTIVE files can no longer be populated.

But what about the source files that the MERGE TARGET is derived from? After a CHECKPOINT, these files transition to WAITING FOR LOG TRUNCATION, and can be removed. It should be noted that it can take several checkpoints and transaction log backups for CFPs to transition to a state where they can actually be removed. That’s why Microsoft recommends 4x durable memory-optimized data size for the initial storage footprint.

In the images that follow, we can see that the formerly distinct transaction ranges of 101 to 200, and 201 to 300, have been combined into a single CFP, which has the range of 101 to 300.

image_thumb[29]

image_thumb[31]

image_thumb[33]

Effect on backup size

File Merge – and the requisite file state changes that CFPs must go through – explain why backups for memory-optimized databases can be considerably larger than the amount of data stored in memory. Until CFPs go through the required state changes, they must be included in backups.

IOPS

The File Merge process requires both storage and IOPS, as it reads from both sets of CFPs, and writes to a new set. Let’s say your workload requires 500 IOPS to perform well. We’ve just added another 1,000 IOPS as a requirement for your workload to maintain the same level of performance: 500 IOPS each for the read and write components of File Merge. That’s why Microsoft recommends 3x workload IOPS for your memory-optimized storage.

Potential remedies, real and imagined

What happens to your memory-optimized database when all volumes run out of free space?

In my testing of inserts that breached the quota for the folder, I saw no affect on database status. However, if I created the database, set the quota to a much lower value, and then created a memory-optimized table, the database status became SUSPECT. In a real-world situation, with hundreds of gigabytes or more of memory-optimized data, the last thing you want to do is a database restore in order to return your database to a usable state.

I was able to set the database OFFLINE, and then ONLINE, and that cleared the SUSPECT status. But keep in mind, that setting the database OFFLINE/ONLINE will restream all your data, so there will be a delay in database recovery due to that.

What can you do if your volumes run out of free space?

Well, in SQL 2014, your database went into “SUSPENDED” mode (not suspect), and it was offline, until perhaps you added more space and restarted the database (not sure, I didn’t test that). In SQL 2016+, the database goes into what’s known as “delete-only mode”, where you can still SELECT data, but modifying data is limited to deleting rows and/or dropping indexes/tables. Of course, SELECT, DELETE, and DROP to nothing to solve your problem: you need more free space.

When a database transitions to delete-only mode, that fact is written to the SQL errorlog:

[WARNING] Database ID: [9]. Checkpoint hit an error code 0x8300000a. Database is now in DeleteOnlyMode

You might think that you can issue CHECKPOINT manually, and do transaction log backups, hoping that File Merge will kick in. Or you could manually execute File Merge, with this uber-long thing:

EXEC sys.sp_xtp_checkpoint_force_garbage_collection <dbname>

But keep in mind that if there was no additional free space on the volumes to precreate CFPs, then it’s not likely that there will be enough free space to write a new set of CFPs for DBA-initiated File Merge.

The only thing you can do to remedy this situation is to either free up some space on the existing volumes, or create a new container on a new volume that has free space.

In Part 4, we’ll discuss memory in the same ways we’ve discussed storage – how it’s allocated, and what happens to your memory-optimized workload when you run out of it.