DEV Community: Arvind Toorpu

SQL Server In-Memory OLTP: When Memory-Optimized Tables Actually Pay Off

Arvind Toorpu — Mon, 26 Oct 2026 13:53:00 +0000

A client of mine had a session-state table taking 40,000 writes a minute during a flash sale, and every single one of them was queuing behind PAGELATCH_EX waits on the last page of a clustered index. Adding more CPU didn't help. Bigger disks didn't help. The bottleneck wasn't I/O, it was threads fighting over the same in-memory page structures. That's the exact scenario memory-optimized tables were built for, and it's also where I've seen people reach for them when the actual problem is a bad index design that In-Memory OLTP will just make expensive to fix.

What memory-optimized tables actually are

A memory-optimized table is a normal-looking CREATE TABLE with WITH (MEMORY_OPTIMIZED=ON) tacked on. Rows live in memory as the primary copy, not as a disk-page cache. There's no buffer pool, no page latching, no lock manager in the traditional sense. Concurrency is handled with row versioning and optimistic multi-version concurrency control, so readers never block writers and writers don't block each other on the same row unless they're actually touching the same row version.

You still get durability by default: a checkpoint file pair on disk and transaction log records mean a memory-optimized table survives a restart just like a regular one, unless you explicitly opt out with DURABILITY=SCHEMA_ONLY for things like staging tables or session state where losing the data on restart is fine.

The performance case according to Microsoft's own docs is specifically latch contention, lock contention, and CPU overhead from the interpreter, not "make my queries faster" in general. If your workload isn't hitting one of those three, this feature won't do much for you.

The how-to

Here's a durable memory-optimized table with a hash index for point lookups and a range index for the historical query pattern:

CREATE TABLE dbo.ShoppingCart (
    ShoppingCartId INT IDENTITY(1,1) NOT NULL
        PRIMARY KEY NONCLUSTERED,
    UserId INT NOT NULL
        INDEX ix_UserId NONCLUSTERED
        HASH WITH (BUCKET_COUNT = 1000000),
    CreatedDate DATETIME2 NOT NULL,
    TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

And a non-durable session table where you're fine losing rows on a restart, which is the classic ASP.NET session state use case:

CREATE TABLE dbo.UserSession (
    SessionId INT IDENTITY(1,1) NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 400000),
    UserId INT NOT NULL,
    CreatedDate DATETIME2 NOT NULL,
    ShoppingCartId INT
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Before you create the table, the database needs a memory-optimized filegroup:

ALTER DATABASE MyDb ADD FILEGROUP MyDb_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE MyDb ADD FILE (
    name = 'MyDb_mod1',
    filename = 'C:\data\MyDb_mod1'
) TO FILEGROUP MyDb_mod;

Where the real speedup comes from is pairing this with a natively compiled stored procedure. Interpreted T-SQL against a memory-optimized table is already faster than against a disk-based table, but it's still going through the interpreter. A natively compiled proc gets compiled to machine code at creation time:

CREATE PROCEDURE dbo.usp_AssignCart @SessionId INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    DECLARE @UserId INT, @ShoppingCartId INT;

    SELECT @UserId = UserId, @ShoppingCartId = ShoppingCartId
    FROM dbo.UserSession
    WHERE SessionId = @SessionId;

    IF @UserId IS NULL
        THROW 51000, N'Session or cart not found.', 1;

    UPDATE dbo.UserSession
    SET ShoppingCartId = @ShoppingCartId
    WHERE SessionId = @SessionId;
END;

Notice the BEGIN ATOMIC block and the mandatory isolation level. That's not optional boilerplate, it's how the engine decides transaction semantics for native code, and errors like arithmetic overflow or divide-by-zero get caught at creation time instead of runtime, which trips people up the first time a CREATE PROCEDURE fails on a syntax-looking issue that's actually a data-type problem.

On indexes: use a hash index only when every query against it is an equality predicate hitting every key column, point lookups by session ID or order ID. Use a nonclustered (range) index for anything with ORDER BY, BETWEEN, or inequality comparisons. Getting this backwards is one of the more common mistakes, people default to hash indexes because they sound faster, then wonder why a range scan on that column is doing a full index scan instead.

Where this requires care

Edition and version gating matters. In-Memory OLTP has been available in every edition since SQL Server 2016 SP1, but Standard Edition caps you at 32 GB of memory-optimized data per database, and Express caps at 352 MB. If you're still on 2014 or 2016 RTM, it's Enterprise/Developer/Evaluation only. Check your edition before you architect around this.

Bucket count is a one-way decision, mostly. Hash indexes need a bucket count set at creation, and getting it too low creates chain collisions, too high wastes memory permanently since you can't easily shrink it without rebuilding. As of more recent SQL Server versions you can ALTER TABLE ... ALTER INDEX ... REBUILD WITH (BUCKET_COUNT = n), but plan for this rather than guessing and hoping.

T-SQL support inside natively compiled procs is a real subset. No MERGE, limited join types in older versions, no cursors in the way you're used to. You will hit a wall porting an existing complex proc over verbatim. Budget time to rewrite, not just recompile.

Foreign keys and CHECK constraints referencing a disk-based table from a memory-optimized one aren't supported, and vice versa in some directions depending on version. If your memory-optimized table needs referential integrity against a regular table, you're enforcing it in application code or triggers, not the schema.

Optimistic concurrency assumes low conflict rates. If two sessions are frequently trying to update the same row, you get conflict errors back to the app instead of blocking, and now your app needs retry logic it probably doesn't have. This is the one that bites people hardest: they migrate a hot table expecting free concurrency and instead get a wave of 41302/41305 errors under contention they didn't have with lock-based blocking.

Always Encrypted with secure enclaves doesn't work on memory-optimized columns, and CLR code can't touch memory-optimized tables on the same connection. Check both before you commit to a migration if either is in your stack.

My take

In-Memory OLTP is a scalpel, not a general performance upgrade. If your profiler shows PAGELATCH or LCK_M waits stacking up on a specific hot table under real concurrency, and the table's working set actually fits in memory, this is one of the few SQL Server features that delivers the order-of-magnitude gains the marketing promises. If your problem is a missing index, a bad execution plan, or tempdb spills, move memory-optimized tables to the bottom of the list, they add operational complexity (memory sizing, checkpoint files, a genuinely different T-SQL subset) that isn't worth paying for a problem they don't solve. I've only reached for it maybe three times in production over the years, and every time it was because the wait stats told me to, not because a table was "slow."

Oracle GoldenGate 23ai: Real-Time Replication to the Cloud Without a Downtime Window

Arvind Toorpu — Thu, 15 Oct 2026 13:52:00 +0000

Last quarter I migrated a 4TB on-prem Oracle order-management database to OCI Autonomous Database with a change window of exactly zero minutes. No maintenance page, no "please don't place orders between 2 and 4 AM." The tool that made that possible was Oracle GoldenGate 23ai, and if you're still running the classic architecture from a few years ago, the upgrade path itself is worth knowing about before you touch a single extract.

Here's what actually changed, why it matters for cloud migrations specifically, and where I got bitten during setup.

What GoldenGate 23ai actually is

GoldenGate has always done one thing: capture committed changes from a source database's redo/transaction logs and apply them to a target, in near real time, without touching the source workload. 23ai (GA since April 2024, still shipping incremental releases under that name) adds a few things that matter if your target is a cloud database:

Microservices architecture is now the only architecture. Classic GGSCI-only deployments are on their way out. Everything runs as a set of REST-managed services (Administration Server, Distribution Server, Receiver Server) fronted by a browser console, managed through adminclient instead of the old ggsci shell alone.
AI vector data type support. GoldenGate can now replicate Oracle 23ai VECTOR columns in real time, including to non-Oracle vector stores like pgvector, MySQL HeatWave, and OpenSearch, as long as the embedding algorithm and dimensions match on both sides.
Data Streams. DDL and DML changes can be exposed as JSON CloudEvents over AsyncAPI, so a downstream microservice can subscribe to a table's changes without you writing a Kafka Connect pipeline for it.
OCI GoldenGate as a managed service. Fully managed deployments across 45+ OCI regions with elastic autoscaling, so you're not babysitting a VM running the hub.

For a straight database migration, the piece that matters most is still the boring one: extract, trail, replicat. Let's build it.

Setting up real-time replication to the cloud

Assume an on-prem Oracle 19c/23ai source and an OCI Autonomous Database (or Oracle on RDS, the pattern is nearly identical) as target. First, enable supplemental logging on the source. Do this at the PDB level, not the CDB root, unless you're specifically doing downstream mining, Oracle explicitly does not recommend root-level capture with a common user for normal replication.

-- Run inside the source PDB, connected as the GoldenGate admin user
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;

ALTER TABLE ORDERS.ORDER_HEADER ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Then, from the admin client (or GGSCI if you're still mid-upgrade), enable trandata and add the extract:

GGSCI> DBLOGIN USERID ggadmin@ORCLPDB1, PASSWORD ******
GGSCI> ADD TRANDATA ORDERS.ORDER_HEADER
GGSCI> ADD EXTRACT EXTORD, TRANLOG, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL /u01/ogg/dirdat/ab, EXTRACT EXTORD, MEGABYTES 200

The extract's parameter file is where you actually tell it what to capture:

EXTRACT EXTORD
USERID ggadmin@ORCLPDB1, PASSWORD ******
EXTTRAIL /u01/ogg/dirdat/ab
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
TABLE ORDERS.ORDER_HEADER;
TABLE ORDERS.ORDER_LINES;

On the target side (Autonomous Database, connecting over the wallet TNS alias), add the replicat against a checkpoint table:

GGSCI> DBLOGIN USERID ggadmin@ORCLADB_HIGH, PASSWORD ******
GGSCI> ADD CHECKPOINTTABLE ggadmin.ggchkpt
GGSCI> ADD REPLICAT REPORD, EXTTRAIL /u01/ogg/dirdat/ab, CHECKPOINTTABLE ggadmin.ggchkpt

And its parameter file:

REPLICAT REPORD
USERID ggadmin@ORCLADB_HIGH, PASSWORD ******
ASSUMETARGETDEFS
MAP ORDERS.ORDER_HEADER, TARGET ORDERS.ORDER_HEADER;
MAP ORDERS.ORDER_LINES, TARGET ORDERS.ORDER_LINES;

Start both, watch the lag, and once REPORD reports zero lag against a quiet source, you cut over the application's connection string. That's the whole trick to a zero-downtime migration: the cutover is a DNS/connection-string change, not a data-loading exercise.

GGSCI> START EXTRACT EXTORD
GGSCI> START REPLICAT REPORD
GGSCI> INFO REPLICAT REPORD, DETAIL

Where this requires care

A few things cost me real time on that migration, and I'd rather you skip them:

The classic-to-microservices jump isn't optional anymore. If you're on an older GoldenGate install, plan a separate upgrade step before the migration project, not during it. Trying to learn adminclient REST semantics on the same day you're cutting a production database over is a bad combination.

Autonomous Database needs the wallet, and the wallet needs network ACLs. GoldenGate connects to Autonomous Database over TLS using the downloaded wallet's TNS entry. If your GoldenGate host can't reach the ADB's public or private endpoint, or the ACL on the ADB instance hasn't been opened for that host, you'll get a connection failure that looks like a credentials problem and isn't.

Trail files will eat your disk if replicat lags. On a large initial sync, extract runs ahead of replicat easily. Watch trail directory growth, not just lag in seconds, especially if the target is on a slower network path like a cross-region OCI link.

Vector column replication has real constraints. It's not a magic heterogeneous conversion. Source and target vector dimensions and distance metric need to line up, or you're replicating garbage embeddings that happen to be the right data type.

Root-level capture with a common user works but isn't the recommended path. If you inherited a GoldenGate config that captures from the CDB root, budget time to migrate it to per-PDB extracts before you lean on it for a cloud migration you actually care about.

My take

GoldenGate 23ai's headline features (vector replication, Data Streams, the AI service integrations) are the interesting demo material, but the reason it's worth learning right now is much less flashy: it's the most reliable way I've found to move a live Oracle database to the cloud without asking the business for a maintenance window. The microservices architecture switch is a real cost if you're upgrading from an old classic install, but once it's running, the extract-trail-replicat pattern is the same discipline it's always been, just pointed at a cloud endpoint instead of a second on-prem box.

Amazon RDS Custom for Oracle: Root Access to Your DB Host Without Giving Up Managed Backups

Arvind Toorpu — Mon, 05 Oct 2026 13:51:00 +0000

A few years back I had a client running Oracle EE on regular RDS who needed a third-party monitoring agent installed at the OS level. Regular RDS for Oracle said no, flatly, there is no SSH, no root, no filesystem access, period. The workaround people usually reach for is "just run it on EC2 yourself" and eat the patching and backup burden forever. That tradeoff is exactly what RDS Custom for Oracle exists to remove, and if you have not looked at it since it launched, the details of how you actually get that access (and what it costs you operationally) are worth understanding before you commit a production workload to it.

What RDS Custom for Oracle actually is

It is not a third option between RDS and EC2, it is both at once. AWS still provisions and manages the underlying EC2 instance, still handles automated backups, patching orchestration, and monitoring, but it gives you SSH and root access to that instance and the ability to install agents, apply one-off patches, or tweak OS parameters that regular RDS locks away. The mechanism that makes this safe(ish) is called automation mode. When automation is in "full" mode, RDS Custom actively manages the instance and will overwrite or flag anything you change by hand. You pause it before you touch anything, do your work, then resume it.

The other piece that is different from stock RDS is the custom engine version (CEV). Instead of picking from AWS's list of Oracle versions, you build your own CEV from your own Oracle installation media and patch bundles, stored in S3. That is also how you get to bring specific PSU/RU levels or one-off patches that AWS has not pushed to the standard RDS for Oracle fleet yet.

Building a CEV and creating the instance

You need Oracle installation files and patches uploaded to an S3 bucket in the same region as the CEV, plus a manifest describing them:

{
  "mediaImportTemplateVersion": "2020-08-14",
  "databaseInstallationFileNames": ["V982063-01.zip"],
  "opatchFileNames": ["p6880880_190000_Linux-x86-64.zip"],
  "psuRuPatchFileNames": ["p32126828_190000_Linux-x86-64.zip"],
  "installationParameters": {
    "unixGroupName": "dba",
    "unixUname": "oracle",
    "oracleHome": "/home/oracle/oracle.19.0.0.0.ru-2020-04.rur-2020-04.r1.EE.1",
    "oracleBase": "/home/oracle/"
  }
}

Then create the CEV:

aws rds create-custom-db-engine-version \
    --engine custom-oracle-ee-cdb \
    --engine-version 19.cdb_cev1 \
    --database-installation-files-s3-bucket-name us-east-1-123456789012-custom-installation-files \
    --database-installation-files-s3-prefix 123456789012/cev1 \
    --kms-key-id arn:aws:kms:us-east-1:123456789012:key/abcd1234-ef56-7890-ab12-cd34ef567890 \
    --manifest file://manifest.json

Budget roughly two hours for this to finish, and know going in that a CEV's architecture is permanent once created. Get the manifest wrong and you delete and start over, you cannot patch it in place.

Once the CEV shows available, spin up the instance:

aws rds create-db-instance \
    --engine custom-oracle-ee-cdb \
    --db-instance-identifier my-cfo-instance \
    --engine-version 19.cdb_cev1 \
    --db-name MYPDB \
    --db-system-id MYCDB \
    --allocated-storage 250 \
    --db-instance-class db.m5.xlarge \
    --db-subnet-group mydbsubnetgroup \
    --master-username myuser \
    --master-user-password 'change-me-now' \
    --backup-retention-period 7 \
    --kms-key-id my-kms-key \
    --no-auto-minor-version-upgrade \
    --custom-iam-instance-profile AWSRDSCustomInstanceProfile-us-east-1

The IAM instance profile has to start with AWSRDSCustom, this is how RDS Custom's control plane distinguishes an instance it is allowed to manage.

Getting the SSH access you actually asked for

RDS Custom drops an SSH private key into Secrets Manager, named something like do-not-delete-rds-custom-ssh-privatekey-<resource-id>-<uuid>. Pull it down:

aws secretsmanager get-secret-value \
    --secret-id do-not-delete-rds-custom-ssh-privatekey-db-ABCDEFGHIJKLMNOPQRS0123456-0d726c \
    --query SecretString \
    --output text > cfo-instance.pem

chmod 400 cfo-instance.pem
ssh -i cfo-instance.pem ec2-user@ec2-12-345-678-901.us-east-2.compute.amazonaws.com

For a private instance without a public IP, tunnel through Systems Manager instead of exposing port 22:

# add to ~/.ssh/config
# Host i-* mi-*
#     ProxyCommand sh -c "aws ssm start-session --target %h --document-name AWS-StartSSHSession --parameters 'portNumber=%p'"

ssh -i cfo-instance.pem ec2-user@i-0bdc4219e66944afa

Once you are in as ec2-user, sudo su - rdsdb gets you the database owner account, and sudo su - gets you root. Before you do anything, pause automation:

aws rds modify-db-instance \
    --db-instance-identifier my-cfo-instance \
    --automation-mode all-paused \
    --resume-full-automation-mode-minutes 60

That last flag is your safety net, automation resumes on its own after 60 minutes even if you forget to flip it back manually.

Where this requires care

The end-of-support date matters more than most gotchas here. AWS has set March 31, 2027 as the end of support for RDS Custom for Oracle, with the migration window (to self-managed Oracle on EC2, via RMAN active duplication or Data Guard) running now through then. If you are standing up a new RDS Custom for Oracle workload today, plan the exit before you plan the entry.

A few operational ones too:

Standard Edition 2 on RDS Custom caps you at 16 vCPUs and 3 tenant databases per CDB, and Data Guard (so read replicas) is off the table on SE2 entirely.
Leaving automation paused longer than necessary means no automated backups, no patching, and no health monitoring during that window. I have seen a "quick 20 minute fix" turn into a half-day troubleshooting session with automation sitting paused the whole time, that is an outage waiting to happen if something else goes wrong concurrently.
A CEV manifest mistake is not a warning, it is a failed CEV you delete and rebuild, no patching in place.
You cannot reset the master password through the console's Modify action the way you can on regular RDS, it has to be an ALTER USER from inside the database.
Resource IDs, key names, and SSH secret names are all auto-generated and ugly. Script the lookups (describe-db-instances, describe-instances, get-secret-value) rather than hunting through the console every time you need to connect.

My take

RDS Custom for Oracle is the right answer for a narrow but real problem: you need root or SSH for something specific (an agent, a kernel parameter, a one-off patch) and you are not willing to give up managed backups and patch orchestration to get it. It is not a general-purpose upgrade from regular RDS for Oracle, and given the 2027 end-of-support date, I would not recommend it as the long-term home for a new mission-critical system without a migration plan already sketched out. Use it as a bridge, not a destination, and pause automation like you mean it.

Unlocking the Power of JSON Schema with Oracle Database 23ai

Arvind Toorpu — Mon, 21 Sep 2026 14:48:00 +0000

Unlocking the Power of JSON Schema with Oracle Database 23ai

Introduction

Staying current with the latest innovations in Oracle 23AI is critical for organizations seeking high performance, reliability, and automation from their cloud database solutions. AWS is rapidly evolving its managed database offerings, and Oracle Database 23ai introduces game-changing features that can transform how enterprises store, validate, and manage JSON data. One particularly exciting innovation is the introduction of JSON Schema support—a leap forward for developers building modern, data-driven applications on Oracle 23AI.

What is JSON Schema in Oracle Database 23ai AWS RDS?

JSON Schema provides a robust, standardized way to define, document, and validate the structure and constraints of JSON data in your Oracle databases. With Oracle Database 23ai now available , organizations can directly leverage JSON Schema validation to ensure their semi-structured data complies with expected formats—boosting data integrity and development agility.

Key Benefits

Cloud database automation: Automate data validation as JSON is ingested.
Stronger data consistency: Enforce standards for documents at the database layer.
Reduced application logic: Offload validation to the database, simplifying codebases.
Enhanced developer agility: Accelerate API and microservices development with reliable data models.
Improved compliance: Simplify auditing and data governance for regulatory standards.

How JSON Schema Works in Oracle Database 23ai RDS

Oracle Database 23ai natively integrates the JSON Schema standard (draft 2020-12), allowing you to:

Register JSON Schema definitions in the database
Associate schemas with specific JSON columns or tables
Automatically validate incoming JSON objects against defined schemas
Get detailed error reports for validation failures

Prerequisites

Oracle Database 23ai instance running on AWS RDS
RDS role privileges to create and manage JSON schemas
The JSON_SCHEMA package enabled in your schema

Enabling and Using JSON Schema in AWS RDS Oracle 23ai

Follow these steps to get started:

1. Define Your JSON Schema

Save your schema as a string or in a file. For example, a simple “person” schema:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Person",
  "type": "object",
  "properties": {
    "fullName": { "type": "string" },
    "age": { "type": "integer", "minimum": 0 }
  },
  "required": ["fullName", "age"]
}

2. Register the JSON Schema in Oracle

Use the new PL/SQL package DBMS_JSON_SCHEMA to register your schema:

BEGIN
  DBMS_JSON_SCHEMA.REGISTER_SCHEMA(
    schema_url  => 'https://mycorp.com/schemas/person',
    schema_doc  => '{ 
      "$schema": "https://json-schema.org/draft/2020-12/schema",
      "title": "Person",
      "type": "object",
      "properties": {
        "fullName": { "type": "string" },
        "age": { "type": "integer", "minimum": 0 }
      },
      "required": ["fullName", "age"]
    }'
  );
END;
/

3. Associate JSON Schema with a Table Column

Suppose you have a table users with a JSON column profile:

ALTER TABLE users MODIFY (profile CHECK (
  JSON_SCHEMA_VALID(
    profile, 
    'https://mycorp.com/schemas/person'
  )
));

4. Attempt to Insert Invalid Data

If an insert violates the schema, Oracle will reject it:

INSERT INTO users (id, profile) VALUES (
  1001, 
  '{"fullName": "Dana White", "age": -5}'  -- Invalid: age < 0
);
-- ERROR: fails validation due to schema violation

Real-World Use Case: Automated Data Quality for Customer Onboarding

Scenario

A financial services SaaS provider leverages multi-AZ Oracle RDS to power its customer onboarding application. Their microservices architecture sends JSON payloads with user profiles directly to a central “users” table in Oracle.

The JSON Schema Advantage

By registering a JSON Schema for user profiles, every inbound data record is automatically validated.
No more custom app-layer validation code; data stewards simply update the schema as policy evolves.
Compliance teams gain a clear audit trail for every rejected or malformed payload.

Results

Faster application rollouts due to simplified API validation.
Stronger compliance posture with built-in, granular data enforcement.
Reduced outages caused by malformed or incomplete data.

Conclusion

The support for JSON Schema in Oracle Database 23ai gives organizations unprecedented control over their semi-structured data. By elevating data validation to the database layer, teams can automate quality, improve governance, and accelerate development cycles. As data ecosystems become more complex, expect features like these to shape the future of cloud database automation and drive even greater enterprise agility.

Your AG Secondaries Aren't Load Balancing Because You Skipped One Step

Arvind Toorpu — Mon, 14 Sep 2026 13:46:00 +0000

A client paged me at 6am because their reporting dashboards were timing out. Nothing was down. The primary replica in their Always On AG was just getting hammered by every single read query in the environment, while two perfectly healthy secondary replicas sat there doing nothing but redo. They had "configured" read-scale months earlier: ALLOW_CONNECTIONS = READ_ONLY was set on both secondaries, the app connection string had ApplicationIntent=ReadOnly, and none of it mattered because nobody had built the read-only routing list on the primary. Connections with read intent were falling through to the primary because that's what happens by default when a routing list doesn't exist.

That's the whole point of this post. Making a secondary readable and getting traffic to actually route to it are two different configuration steps, and skipping the second one is the single most common mistake I see in AG read-scale setups.

What read-only routing actually is

Read-only routing is a primary-replica-side redirect. A client connects to the listener (or, in a clusterless read-scale AG, to a replica directly) with ApplicationIntent=ReadOnly in the connection string. If the instance it lands on is the primary, SQL Server checks whether a read-only routing list exists for that primary. If one does, it transparently redirects the connection to whichever secondary the list points to. No list, no redirect, the connection just runs read-write on the primary like normal.

Since SQL Server 2017 you don't even need a Windows Server Failover Cluster to get this: a read-scale availability group can exist without a cluster at all, purely to fan out read workload across replicas (Microsoft Learn). Be clear with yourself and your team about what that buys you, though: a cluster-less read-scale AG has no automatic failover. There's no cluster manager watching health and flipping the primary role for you. If you need that, you're building a normal Always On AG with WSFC (or Pacemaker on Linux) and layering read-only routing on top of it, which is the far more common production pattern.

Configuring it end to end

Here's the sequence that actually works, on a standard Windows-clustered AG with two readable secondaries.

First, mark each secondary as readable and give it a routing URL:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLNODE02'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
      READ_ONLY_ROUTING_URL = N'TCP://SQLNODE02.corp.local:1433'));

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLNODE03'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
      READ_ONLY_ROUTING_URL = N'TCP://SQLNODE03.corp.local:1433'));

This is the step people actually remember. Here's the one they don't: the routing list has to be defined on whichever replica is currently the primary, because that's the instance doing the redirecting.

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLNODE01'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('SQLNODE02','SQLNODE03'), 'SQLNODE01')));

That nested-parentheses syntax, (('SQLNODE02','SQLNODE03'), 'SQLNODE01'), is load-balanced round-robin routing across SQLNODE02 and SQLNODE03, with SQLNODE01 (the primary) as the fallback if both are unavailable. That's a SQL Server 2016+ feature; only one level of nesting is supported (Microsoft Learn).

Because any node can become primary during a failover, you need to repeat that PRIMARY_ROLE routing list definition on every replica, each one listing itself last as the fallback:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLNODE02'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('SQLNODE01','SQLNODE03'), 'SQLNODE02')));

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLNODE03'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('SQLNODE01','SQLNODE02'), 'SQLNODE03')));

On the client side, the connection string needs the intent flag and, if the listener spans subnets, multi-subnet failover:

Server=tcp:AG1-Listener,1433;Database=Sales;Application Intent=ReadOnly;MultiSubnetFailover=True;

For ad hoc testing from SSMS or sqlcmd, you can confirm which node you actually landed on:

SELECT @@SERVERNAME AS routed_to, sys.fn_hadr_is_primary_replica('Sales') AS is_primary;

And to check what's actually registered on the primary before you blame the app:

SELECT ag.name AS ag_name, ar.replica_server_name, rl.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
JOIN sys.availability_replicas ar ON rl.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

That query would have caught my client's problem in about ten seconds.

Where this requires care

The primary needs a routing URL too. If the routing list ever falls back to the primary (or a client connects to the primary directly with read intent and no list exists yet), and the primary itself has no READ_ONLY_ROUTING_URL, routing fails outright rather than silently running on the primary.

Load-balanced routing is round-robin per new connection, not per query. It's not smart about replica lag or current load. If SQLNODE03 is 40 seconds behind on redo because of a big index rebuild, round-robin will happily send a client there and that client will see stale data with no warning. If your reporting workload is lag-sensitive, monitor sys.dm_hadr_database_replica_states.secondary_lag_seconds and either alert on it or take that replica out of the routing list temporarily during heavy write windows.

Readable secondaries are not free connections. They're separate SQL Server instances and, outside a passive failover-only configuration, they need to be licensed like any other active SQL Server. This trips up teams who treat "we already pay for AG" as covering read-scale for free. Check your licensing terms, don't assume.

Basic Availability Groups on Standard Edition don't support readable secondaries at all. If you're on Standard Edition, you get one AG, one database, no read access to the secondary, full stop. Read-scale in the way this article describes needs Enterprise Edition (or SQL Server 2017+ read-scale-without-a-cluster, which has its own edition rules, worth double-checking against your specific version).

Application Intent is case-sensitive in spelling but the driver matters more than you'd think. Some older ODBC/JDBC driver versions silently drop or mishandle ApplicationIntent. If routing isn't happening and your T-SQL config checks out clean, check the actual driver version and connection string the app is sending, not just what you think you configured.

My take

Read-only routing is one of those Always On features that looks like a checkbox in the wizard and is actually two independent, un-obviously-linked configuration objects: the secondary's readability plus URL, and the primary's routing list. SSMS's Add Replica wizard doesn't force you through the second one clearly, and I've seen more environments get half-configured than fully configured. If you're rolling this out, script both steps together, verify with the DMV query above as part of your deployment, and don't trust the dashboard alone to tell you traffic is actually landing where you think it is.

S3 Express One Zone Got Cheaper and Gained Inventory Support. Time to Reconsider It

Arvind Toorpu — Thu, 10 Sep 2026 15:52:00 +0000

S3 Express One Zone launched back in 2023 with a pretty specific pitch: single-digit millisecond latency, up to 10x faster than S3 Standard, for workloads that actually need it. The catch was cost and a missing feature set that made it hard to run in production the same way you'd run Standard. Two 2026 updates change that calculus: a big price cut, and S3 Inventory support finally landing in April.

The pitch, quickly

S3 Express One Zone stores data in a single Availability Zone (hence "One Zone") in a directory bucket, not the usual S3 bucket structure. In exchange for giving up multi-AZ redundancy, you get consistent low-latency access, request costs up to 80% lower than Standard, and throughput that's genuinely built for latency-sensitive access patterns: ML training data loading, interactive analytics, key-value caching for AI search.

aws s3api create-bucket \
  --bucket my-express-bucket--use1-az4--x-s3 \
  --region us-east-1 \
  --create-bucket-configuration '{"Location":{"Type":"AvailabilityZone","Name":"use1-az4"},"Bucket":{"Type":"Directory","DataRedundancy":"SingleAvailabilityZone"}}'

Note the bucket naming convention: directory buckets need that --use1-az4--x-s3 suffix pattern, they're not just a regular bucket with a flag flipped.

The price cut

AWS announced price reductions of up to 85% for S3 Express One Zone. That's a meaningful shift in the cost-benefit calculation. At the original pricing, the latency win had to be worth a real premium to justify using it over Standard plus a caching layer. At the new pricing, that math looks a lot more favorable for a wider range of workloads, not just the most latency-obsessed ones.

S3 Inventory support (April 2026)

This is the operational gap that made Express One Zone hard to run seriously before. S3 Inventory generates scheduled reports (daily or weekly) listing every object in a bucket or prefix along with metadata like size, encryption status, and last modified time. Without it, if you wanted to know what was actually sitting in a directory bucket, you were stuck listing objects programmatically, which doesn't scale well for buckets with millions of objects.

Now you can set it up the same way you would for a Standard bucket:

aws s3api put-bucket-inventory-configuration \
  --bucket my-express-bucket--use1-az4--x-s3 \
  --id daily-inventory \
  --inventory-configuration '{
    "Destination": {
      "S3BucketDestination": {
        "Bucket": "arn:aws:s3:::my-inventory-reports",
        "Format": "Parquet"
      }
    },
    "IsEnabled": true,
    "Id": "daily-inventory",
    "IncludedObjectVersions": "Current",
    "Schedule": {"Frequency": "Daily"}
  }'

That output landing as Parquet in a regular bucket means you can query it directly with Athena, which is a much saner way to audit a large directory bucket than paging through ListObjectsV2 results.

Where it actually fits

The realistic use cases haven't changed, they've just gotten cheaper to justify:

ML training data staging. Loading training batches with consistent low latency, especially paired with SageMaker's optimized data channels, which now integrate natively with directory buckets.
Interactive analytics scratch space. Query engines that need to read and write intermediate results fast, where S3 Standard's latency profile becomes the bottleneck.
AI search key-value caching. Storing embeddings or lookup data where request latency directly affects user-facing response time.

Where it doesn't fit

Single-AZ means single-AZ. If the AZ your directory bucket lives in has an outage, your data is unavailable until that AZ recovers, full stop. This isn't a subtle tradeoff, it's the whole design point of the product, and it means Express One Zone is a poor fit for anything that's your durable source of truth. Treat it as a performance tier for data that either has a durable copy elsewhere (like the original training dataset in S3 Standard) or is inherently ephemeral (like intermediate compute results you can regenerate).

Trying it without commitment

Availability has been expanding steadily, Frankfurt joined the list of supported Regions in July 2026, bringing the total to eight. If your workload's home Region wasn't covered before, it's worth checking again.

aws s3api list-directory-buckets --region eu-central-1

A quick cost sanity check before you migrate anything

Before moving a workload over, it's worth running a small side-by-side test rather than trusting the headline "up to 85% cheaper" number blindly, since your actual savings depend heavily on your specific request pattern (lots of small GETs versus fewer large ones behaves very differently on request-based pricing). Stand up a directory bucket, mirror a day's worth of real traffic against it alongside your existing Standard bucket, and compare the actual bill delta rather than the marketing number. It's a couple hours of setup that turns "AWS says it's cheaper" into "here's what it actually costs for our workload."

Bottom line

If you evaluated S3 Express One Zone in 2023 or 2024 and decided the cost didn't justify the latency win for your workload, it's genuinely worth re-running that math now. Between the price cut and Inventory support closing the biggest operational gap, this moved from "niche latency tool" to "reasonable default for a wider set of performance-sensitive workloads" in about a year.

SnapStart Basically Kills Lambda Cold Starts for Java, Python, and .NET. Here's How to Actually Use It

Arvind Toorpu — Fri, 04 Sep 2026 12:49:00 +0000

Cold starts are the one Lambda complaint that never really goes away, especially if you're running Java or a heavier Python stack. SnapStart is AWS's answer, and it's now generally available for Java, Python 3.12+, and .NET 8+. If you haven't touched it yet, this is worth an afternoon.

What SnapStart actually does

Instead of initializing your function's runtime, dependencies, and execution environment from a cold start every time, SnapStart takes a snapshot of a fully initialized execution environment (memory and disk state, post-init) and caches it. When a new instance is needed, Lambda resumes from that snapshot instead of running your init code from scratch.

The practical result: init-phase latency drops by 70 to 90 percent for supported runtimes, according to AWS's own numbers. For a Java function with a heavy Spring context to boot, that's the difference between a multi-second cold start and something that barely registers.

Enabling it

For a supported runtime, it's a one-line change in your function configuration:

aws lambda update-function-configuration \
  --function-name my-java-function \
  --snap-start ApplyOn=PublishedVersions

Note the ApplyOn=PublishedVersions part. SnapStart only applies to published versions of your function, not $LATEST. That trips people up the first time, since testing against $LATEST in the console won't show any SnapStart benefit at all.

Via SAM or CDK, it looks like this (CDK, Python):

from aws_cdk import aws_lambda as lambda_

fn = lambda_.Function(
    self, "MyFunction",
    runtime=lambda_.Runtime.JAVA_21,
    handler="com.example.Handler::handleRequest",
    code=lambda_.Code.from_asset("build/function.zip"),
    snap_start=lambda_.SnapStartConf.ON_PUBLISHED_VERSIONS,
)

The gotcha nobody warns you about: uniqueness

Snapshots capture your execution environment's state, including anything you initialized before the handler runs, like a random seed, a cached secret, or a database connection. If your init code generates something that's supposed to be unique per execution environment (a UUID used as an instance identifier, for example) and you generate it once outside the handler, every resumed snapshot will have the exact same value, because they're all resuming from the identical frozen state.

AWS's guidance here is specific: anything that needs to be unique per invocation or per environment needs to be regenerated inside the handler, or you need to use the Lambda runtime hooks (beforeCheckpoint and afterRestore for Java) to refresh things like connections and credentials on resume rather than assuming init-time state stays valid forever.

// Java: refreshing a connection on restore using runtime hooks
import com.amazonaws.services.lambda.runtime.CoreLifecycleHooks;

public class Handler {
    private Connection dbConnection;

    public Handler() {
        CoreLifecycleHooks.registerBeforeCheckpoint(this::beforeCheckpoint);
        CoreLifecycleHooks.registerAfterRestore(this::afterRestore);
    }

    private void afterRestore() {
        // re-establish anything that shouldn't be shared across resumed snapshots
        this.dbConnection = createFreshConnection();
    }
}

Java 25 made this better

Java 25 on Lambda changed how tiered compilation interacts with SnapStart and Provisioned Concurrency. Previously, SnapStart would stop compilation optimization at tier C1. Starting with Java 25, that cap is gone, so functions get the benefit of full JIT optimization even when running from a SnapStart snapshot, instead of being stuck at a less-optimized compilation tier indefinitely.

If you're on an older Java version specifically because you assumed SnapStart's compilation ceiling was a permanent limitation, it's worth re-checking against Java 25.

What's NOT supported

Worth knowing before you go looking for it:

Container image functions don't support SnapStart, only zip-based deployments on supported managed runtimes.
Node.js and Ruby runtimes aren't supported as of this writing; only Java 11+, Python 3.12+, and .NET 8+.
OS-only (provided.al2/al2023) runtimes aren't supported either.

Pairing it with Graviton

SnapStart cuts init latency. Graviton (arm64) instances cut execution cost and generally improve throughput for CPU-bound workloads. They're independent levers, and stacking both together is a reasonable default for any new Java or Python Lambda function where cold start and cost both matter:

# SAM template snippet
Resources:
  MyFunction:
    Type: AWS::Serverless::Function
    Properties:
      Runtime: java21
      Architectures:
        - arm64
      SnapStart:
        ApplyOn: PublishedVersions

Should you turn this on everywhere

For Java functions specifically, yes, almost by default, given how much init-phase latency Java pays compared to something like Node. For Python and .NET, it's still worth it if you're seeing meaningful cold start impact on user-facing latency, but the win is smaller since those runtimes generally cold-start faster to begin with.

The one thing I'd actually test before flipping it on for a production function: run your function through a few resume cycles and check for any state that quietly stayed frozen from the snapshot when you expected it to be fresh. That's the failure mode that doesn't show up in a quick smoke test, only in production traffic days or weeks later.

Mastering Columnstore Indexes in SQL Server: New Features and Performance Enhancements in

Arvind Toorpu — Wed, 11 Jun 2025 15:29:28 +0000

Arvind Toorpu

Feb 18 '25

Mastering Columnstore Indexes in SQL Server: New Features and Performance Enhancements in 2022

4 min read

Make the most of the SQL Server Max DOP parameter to enhance your query run time by embracing parallelism!

Arvind Toorpu — Wed, 11 Jun 2025 15:22:33 +0000

Arvind Toorpu

May 7 '25

Optimizing SQL Server Index Rebuilds with MAXDOP

#aws #sqlserver #database #dba

4 min read

[Boost]

Arvind Toorpu — Fri, 23 May 2025 18:43:24 +0000

Arvind Toorpu

Apr 25 '25

Troubleshooting tempdb Growth: Identifying Idle Sessions Holding Temporary Resources in SQL Server

#aws #database #sqlserver #dba

2 min read

Optimizing SQL Server Index Rebuilds with MAXDOP

Arvind Toorpu — Wed, 07 May 2025 11:30:00 +0000

Optimizing SQL Server Index Rebuilds with MAXDOP: A Practical Guide

Keeping your SQL Server databases running at peak performance requires regular index maintenance. One critical but often overlooked aspect is how parallelism, controlled through the MAXDOP setting, can significantly impact the speed and efficiency of index rebuilds. In this guide, we'll walk through how you can optimize your SQL Server index maintenance by properly configuring MAXDOP.

Why Index Maintenance Matters

Index fragmentation is an inevitable side effect of everyday database operations like inserts, updates, and deletes. Over time, fragmented indexes can lead to slower queries and increased I/O, affecting the overall performance of your applications.

Performing regular index maintenance — through rebuilds or reorganizations — is essential to:

Reduce query response times
Improve system throughput
Maintain efficient storage usage

Learn more about index maintenance best practices from SQL Server Docs.

Harnessing the Power of Parallelism with MAXDOP

SQL Server can utilize multiple processors when performing index operations. The Maximum Degree of Parallelism (MAXDOP) setting controls how many CPU cores SQL Server can use for these operations.

Setting MAXDOP During Index Rebuilds

By default, index operations follow the instance-level MAXDOP setting. However, you can explicitly override this for specific rebuilds:

ALTER INDEX [IX_Sales_OrderDate] ON [Sales].[Orders]
REBUILD WITH (MAXDOP = 4);

This command instructs SQL Server to use 4 processor cores for rebuilding the index, regardless of the server’s default MAXDOP setting.

Reference: Reduce Time for SQL Server Index Rebuilds and Update Statistics

Benchmark Results: How MAXDOP Affects Rebuild Times

To illustrate the real-world impact, I ran tests on a large table containing 15 million rows (~60GB) using different MAXDOP values.

Test Setup:

SQL Server: 2022 Enterprise Edition
Cores: 16 logical processors
Memory: 128GB RAM
Storage: NVMe SSD
Table: SalesTransactions (60GB)
Index: IX_SalesTransactions_TransactionDate (Non-clustered)

Performance Results:

MAXDOP Setting	Rebuild Duration (seconds)	CPU Utilization (%)	Memory Usage (GB)
1	420	12	2.5
2	240	25	4.2
4	135	48	7.6
8	74	92	12.3
12	73	95	12.6
16	72	96	12.8

Key Takeaway:

Significant performance improvements were observed up to MAXDOP = 8.
Beyond 8, performance gains were marginal.

This aligns with Microsoft's guidance to limit MAXDOP to 8 or fewer for most workloads.

Practical MAXDOP Configuration Scenarios

Scenario 1: 24x7 Production System

In environments with continuous user activity, balance is crucial.

ALTER INDEX [IX_CustomerTransactions_TransactionDate] ON [Sales].[CustomerTransactions]
REBUILD WITH (MAXDOP = 4, ONLINE = ON);

Moderate parallelism ensures faster rebuilds without heavily impacting user workloads.
ONLINE = ON allows queries to continue during the rebuild.

Scenario 2: Dedicated Maintenance Window

If you have a maintenance window with minimal load:

ALTER INDEX ALL ON [Sales].[OrderDetails]
REBUILD WITH (MAXDOP = 8);

Higher parallelism maximizes rebuild speed.
Ideal when users are offline or during planned downtimes.

Scenario 3: Resource-Constrained Systems

On systems with limited CPU or concurrent heavy usage:

ALTER INDEX [IX_Inventory_ProductID] ON [Inventory].[Products]
REORGANIZE;

ALTER INDEX [PK_Inventory_Products] ON [Inventory].[Products]
REBUILD WITH (MAXDOP = 2);

Prefer reorganization over rebuild when possible.
Limit MAXDOP for minimal disruption.

Note: Reorganize operations are single-threaded by design.

Best Practices for Setting MAXDOP

When fine-tuning MAXDOP for index operations:

Start with MAXDOP = 0.5 × (physical cores) (up to 8).
Monitor system waits and resource usage during rebuilds.
For OLTP systems, restrict MAXDOP to 4 or fewer during peak hours.
Evaluate your storage subsystem — parallelism increases I/O!

Example configuration for a 16-core server:

ALTER INDEX [IX_FactInternetSales_OrderDate] ON [dbo].[FactInternetSales]
REBUILD WITH (MAXDOP = 8, SORT_IN_TEMPDB = ON);

SORT_IN_TEMPDB = ON offloads sorting operations, reducing contention in user databases.

Important Considerations

Enterprise Edition is required for parallel index rebuilds. Standard Edition operations remain single-threaded.
Reorganize operations do not use multiple threads.
Online rebuilds with ALLOW_PAGE_LOCKS = OFF and MAXDOP > 1 can lead to increased fragmentation.
If MAXDOP exceeds available CPUs, SQL Server uses the maximum available automatically.

How to Validate MAXDOP Settings

You can confirm your MAXDOP usage by capturing execution plans:

CREATE EVENT SESSION [CapturePlans] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\temp\CapturePlans.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS, STARTUP_STATE=OFF);

ALTER EVENT SESSION [CapturePlans] ON SERVER STATE=START;

-- Execute your index rebuild

ALTER INDEX [IX_Sales_CustomerID] ON [Sales].[Orders]
REBUILD WITH (MAXDOP = 4);

ALTER EVENT SESSION [CapturePlans] ON SERVER STATE=STOP;

Analyze the captured event file to review the degree of parallelism utilized.

Final Thoughts

Tuning MAXDOP for index rebuilds is a powerful way to optimize SQL Server maintenance without sacrificing user experience. With careful configuration based on your environment's needs, you can dramatically reduce maintenance windows and keep your databases performing at their best.

Troubleshooting tempdb Growth: Identifying Idle Sessions Holding Temporary Resources in SQL Server

Arvind Toorpu — Fri, 25 Apr 2025 17:00:40 +0000

To find out how much tempdb disk space is occupied by each session that is connected and is idle in a SQL Server database, you can use the following query. This query leverages Dynamic Management Views (DMVs) like sys.dm_db_session_space_usage and sys.dm_exec_sessions to identify idle sessions and their tempdb usage.

Query to Find tempdb Disk Space Usage by Idle Sessions:

SELECT 
    es.session_id,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    su.user_objects_alloc_page_count * 8 / 1024.0 AS user_objects_alloc_mb,
    su.user_objects_dealloc_page_count * 8 / 1024.0 AS user_objects_dealloc_mb,
    su.internal_objects_alloc_page_count * 8 / 1024.0 AS internal_objects_alloc_mb,
    su.internal_objects_dealloc_page_count * 8 / 1024.0 AS internal_objects_dealloc_mb,
    (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024.0 AS user_objects_net_mb,
    (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024.0 AS internal_objects_net_mb
FROM 
    sys.dm_db_session_space_usage su
JOIN 
    sys.dm_exec_sessions es
    ON su.session_id = es.session_id
WHERE 
    es.status = 'sleeping' -- Filter for idle sessions
    AND (su.user_objects_alloc_page_count > 0 OR su.internal_objects_alloc_page_count > 0) -- Filter for sessions using tempdb
ORDER BY 
    user_objects_net_mb DESC, internal_objects_net_mb DESC;

Explanation of the Query:

sys.dm_db_session_space_usage:
- Tracks tempdb space usage for each session.
- Columns like user_objects_alloc_page_count and internal_objects_alloc_page_count show the number of 8 KB pages allocated for user objects (e.g., temporary tables) and internal objects (e.g., worktables).
sys.dm_exec_sessions:
- Provides session-level information, such as session_id, login_name, host_name, program_name, and status.
- The status column is used to filter for idle sessions (status = 'sleeping').
Calculations:
- Multiply page counts by 8 / 1024.0 to convert pages to megabytes (MB).
- Calculate the net space usage by subtracting deallocated pages from allocated pages.
Filters:
- es.status = 'sleeping': Filters for sessions that are idle (not actively running queries).
- su.user_objects_alloc_page_count > 0 OR su.internal_objects_alloc_page_count > 0: Ensures only sessions using tempdb are included.
Output:
- user_objects_net_mb: Net space used by user objects (e.g., temporary tables) in MB.
- internal_objects_net_mb: Net space used by internal objects (e.g., worktables) in MB.

Example Output:

session_id	login_name	host_name	program_name	status	user_objects_alloc_mb	user_objects_dealloc_mb	internal_objects_alloc_mb	internal_objects_dealloc_mb	user_objects_net_mb	internal_objects_net_mb
52	sa	SQLHost01	SQLServerManagement	sleeping	50.25	10.00	20.50	5.00	40.25	15.50
67	app_user	AppServer01	MyApp.exe	sleeping	30.00	30.00	0.00	0.00	0.00	0.00

Key Notes:

Idle sessions (status = 'sleeping') may still hold tempdb resources if they have not explicitly dropped temporary objects or if their scope has not ended.
If you notice high tempdb usage by idle sessions, investigate whether temporary objects are being properly cleaned up or if long-running transactions are holding resources.

This query is a great way to monitor and troubleshoot tempdb usage, especially in environments with high concurrency or large temporary object usage.

DEV Community: Arvind Toorpu

SQL Server In-Memory OLTP: When Memory-Optimized Tables Actually Pay Off

What memory-optimized tables actually are

The how-to

Where this requires care

My take

Further reading

Oracle GoldenGate 23ai: Real-Time Replication to the Cloud Without a Downtime Window

What GoldenGate 23ai actually is

Setting up real-time replication to the cloud

Where this requires care

My take

Further reading

Amazon RDS Custom for Oracle: Root Access to Your DB Host Without Giving Up Managed Backups

What RDS Custom for Oracle actually is

Building a CEV and creating the instance

Getting the SSH access you actually asked for

Where this requires care

My take

Further reading

Unlocking the Power of JSON Schema with Oracle Database 23ai

Unlocking the Power of JSON Schema with Oracle Database 23ai

Introduction

What is JSON Schema in Oracle Database 23ai AWS RDS?

Key Benefits

How JSON Schema Works in Oracle Database 23ai RDS

Prerequisites

Enabling and Using JSON Schema in AWS RDS Oracle 23ai

1. Define Your JSON Schema

2. Register the JSON Schema in Oracle

3. Associate JSON Schema with a Table Column

4. Attempt to Insert Invalid Data

Real-World Use Case: Automated Data Quality for Customer Onboarding

Scenario

The JSON Schema Advantage

Results

Conclusion

Your AG Secondaries Aren't Load Balancing Because You Skipped One Step

What read-only routing actually is

Configuring it end to end

Where this requires care

My take

Further reading

S3 Express One Zone Got Cheaper and Gained Inventory Support. Time to Reconsider It

The pitch, quickly

The price cut

S3 Inventory support (April 2026)

Where it actually fits

Where it doesn't fit

Trying it without commitment

A quick cost sanity check before you migrate anything

Bottom line

Further reading

SnapStart Basically Kills Lambda Cold Starts for Java, Python, and .NET. Here's How to Actually Use It

What SnapStart actually does

Enabling it

The gotcha nobody warns you about: uniqueness

Java 25 made this better

What's NOT supported

Pairing it with Graviton

Should you turn this on everywhere

Further reading

Mastering Columnstore Indexes in SQL Server: New Features and Performance Enhancements in

Mastering Columnstore Indexes in SQL Server: New Features and Performance Enhancements in 2022

Make the most of the SQL Server Max DOP parameter to enhance your query run time by embracing parallelism!

Optimizing SQL Server Index Rebuilds with MAXDOP

[Boost]

Troubleshooting tempdb Growth: Identifying Idle Sessions Holding Temporary Resources in SQL Server

Optimizing SQL Server Index Rebuilds with MAXDOP

Optimizing SQL Server Index Rebuilds with MAXDOP: A Practical Guide

Why Index Maintenance Matters

Harnessing the Power of Parallelism with MAXDOP

Setting MAXDOP During Index Rebuilds

Benchmark Results: How MAXDOP Affects Rebuild Times

Test Setup:

Performance Results:

Practical MAXDOP Configuration Scenarios

Scenario 1: 24x7 Production System

Scenario 2: Dedicated Maintenance Window

Scenario 3: Resource-Constrained Systems