Monthly Archives: May 2019

Hekatonized Tempdb

1 Reply

At PASS Summit 2018, I attended a session led by Pam Lahoud (t) of the SQL Tiger Team , entitled “TempDB: The Good, The Bad, and The Ugly”. If you have access to the PASS recordings from 2018, I highly recommend watching this session.

It was a really fantastic presentation, detailing the full history of how the SQL Server engineering team has attempted to optimize TempDB in various ways. The two problems that busy servers can have with regard to TempDB are allocation page contention, and metadata contention, and the engineering team should be applauded for its clever approaches to solving these types of contention throughout the years. To be clear, all of the optimizations were related to temp table usage in stored procedures, not scripts.

However, none of those solutions for contention scaled – some only relocated the issue. As part of Pam’s presentation, she did a demo with a single TempDB metadata table that was “Hekatonized”  – actually using the In-Memory OLTP engine – and the difference in throughput was significant. She said that Microsoft intends to convert the remaining system tables in TempDB to be memory-optimized (you’ll need SQL 2019 CTP 3.0 or later to test).

So once you’ve got it installed or have started a container running it – how to you automagically convert TempDB system tables to be memory-optimized? With TSQL, of course:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Like other changes to TempDB, in order for the new memory-optimization to take effect a restart of the SQL Server service is required. Once the service is restarted, system tables in TempDB are now memory-optimized (it should be that way for RTM, but in CTP 3.0, it could be the case that not all system tables have been converted to Hekaton). You can reverse this setting with the following command, and again restarting the SQL Server service:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Unless your workload was truly hammering TempDB, you probably won’t see much difference in TempDB performance.

We need to be careful with this new In-Memory power, because depending on workload characteristics, we might need a whole lot more memory just to handle what’s going on in TempDB. Also, if you have scripts and/or monitoring that interrogate system tables in TempDB, you might be affected by some of the restrictions if TempDB system tables are memory-optimized. As the CTP release notes, state:

“A single transaction may not access memory-optimized tables in more than one database. This means that any transactions that involve a memory-optimized table in a user database will not be able to access TempDB system views in the same transaction.”

Another thing I want to make clear is that this new TempDB optimization only affects system tables in TempDB – not the tables you create; #table and ##table do not become memory-optimized as a result of this new feature.

After all, it’s name is MEMORY_OPTIMIZED_TEMPDB_METADATA

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.