DEV Community: Arvind Toorpu

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.

Auditing SQL Server Database User Activity in AWS RDS: A Step-by-Step Guide

Arvind Toorpu — Wed, 12 Mar 2025 14:47:23 +0000

Auditing SQL Server Database User Activity in AWS RDS: A Step-by-Step Guide

Auditing user activity in SQL Server on AWS RDS involves leveraging AWS-native tools combined with SQL Server's built-in features. in this article I provide a detailed guide for setting up and managing auditing:

Step 1: Enable SQL Server Audit in AWS RDS

SQL Server Audit is supported on RDS and can track user activity. Here's how to enable and configure it:

1.1 Configure an Audit Parameter Group

Log in to the AWS Management Console.
Navigate to RDS > Parameter Groups.
Create a new parameter group for your SQL Server instance:
- Choose Parameter group family matching your SQL Server version.
- Set the name, e.g., sqlserver-audit-group.
Edit the parameter group:
- Search for the parameter rds.sqlserver_audit and set it to 1 (enabled).
- Save the changes.
Associate the parameter group with your RDS instance:
- Go to RDS Instances and select your SQL Server instance.
- Modify the instance and change the parameter group to the new one.
- Apply changes (you may need to reboot the instance for changes to take effect).

Step 2: Set Up SQL Server Audit

Once the audit feature is enabled, configure it at the database level.

2.1 Create an Audit Object

This defines where the audit logs will be stored.

USE master;
GO
CREATE SERVER AUDIT AuditToFile
TO FILE (FILEPATH = 'D:\rdsdbdata\SQLAudit\');
GO

2.2 Create an Audit Specification

Define the events to capture in the audit.

CREATE SERVER AUDIT SPECIFICATION AuditUserLogins
FOR SERVER AUDIT AuditToFile
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP);
GO

2.3 Enable the Audit and Specification

ALTER SERVER AUDIT AuditToFile WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION AuditUserLogins WITH (STATE = ON);
GO

Step 3: Access and Review Audit Logs

Audit logs for RDS SQL Server are stored in the default directory (D:\rdsdbdata\SQLAudit\) and can be accessed via the AWS Management Console.

Navigate to RDS > Your Instance > Logs and Events.
Locate logs with the prefix SQL_AUDIT_LOG.
Download the logs to review them locally.

Alternatively, query the logs directly using the SQL Server function:

SELECT * 
FROM sys.fn_get_audit_file ('D:\rdsdbdata\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT);

Step 4: Use CloudWatch for Enhanced Monitoring

Integrate SQL Server activity logs with AWS CloudWatch for centralized monitoring and alerting.

4.1 Enable Enhanced Monitoring

In the RDS Console, go to your SQL Server instance.
Enable Enhanced Monitoring and set the monitoring interval.

4.2 Stream Audit Logs to CloudWatch

Navigate to RDS > Log Exports.
Enable SQL Server Audit Logs for export to CloudWatch.
In CloudWatch, create a log group and associate the logs with it.

4.3 Set Up CloudWatch Alerts

Create a metric filter for specific events (e.g., failed logins).
Configure an alarm to notify you when thresholds are breached.

Step 5: Query User Activity with Dynamic Management Views (DMVs)

Leverage SQL Server DMVs to query real-time user activity.

5.1 Track Active Sessions

SELECT session_id, login_name, host_name, program_name, database_id
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

5.2 Review Recent Logins

SELECT login_time, session_id, login_name, client_net_address
FROM sys.dm_exec_connections
JOIN sys.dm_exec_sessions
ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id;

5.3 Monitor Query Activity

SELECT r.session_id, s.login_name, s.host_name, t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id;

Step 6: Automate Alerts and Notifications

6.1 Use Event Notifications

Set up event notifications for specific actions, such as failed logins or schema changes.

CREATE EVENT NOTIFICATION FailedLoginAlert
ON SERVER
FOR FAILED_LOGIN
TO SERVICE 'MyService'
GO

6.2 Configure Alerts in AWS RDS

Use the AWS EventBridge to trigger actions (e.g., email notifications) for specific RDS events.

Step 7: Best Practices for RDS SQL Server Auditing

Minimize Audit Overhead:
- Audit only the necessary events to reduce performance impact.
Secure Audit Logs:
- Restrict access to audit logs in RDS and CloudWatch.
Regularly Review Logs:
- Analyze audit logs periodically for anomalies or suspicious activity.
Automate Responses:
- Use AWS automation tools to handle critical events like repeated failed logins.
Enable Encryption:
- Ensure audit logs and database communications are encrypted.

Conclusion

Auditing user activity in SQL Server on AWS RDS combines SQL Server’s robust auditing features with AWS's monitoring and alerting capabilities. By following this step-by-step guide, you can ensure a secure, compliant, and well-monitored SQL Server environment. Regular audits help mitigate risks, detect anomalies, and maintain database integrity, which is essential for modern, data-driven organizations.

Oracle Database Migration from Windows to Linux Using RMAN Transportable Tablespace

Arvind Toorpu — Tue, 04 Mar 2025 16:26:55 +0000

Oracle Database Migration from Windows to Linux Using RMAN Transportable Tablespace

Migrating an Oracle database between different operating systems can often feel daunting. However, with the right tools and steps, this process can be more manageable than it seems. In this article, we will walk through migrating an Oracle database from Windows to Linux using the RMAN Transportable Tablespace feature - one of the most efficient methods for such a task.

Prerequisites

Oracle Database installed on both Windows and Linux servers. Network connectivity between the two servers. Sufficient disk space on both servers. Basic understanding of RMAN and Oracle database administration.

Step-by-Step Migration
Prepare the Source Database (Windows)

First, we need to ensure the source database is in READ ONLY mode. This step is crucial to prevent any changes during the migration process.

SQL> ALTER DATABASE OPEN READ ONLY;

Identify the Tablespaces to be Transported

Identify the tablespaces that you want to transport. For this example, we will transport the sales_data tablespace.

SQL> SELECT tablespace_name FROM dba_tablespaces;

Generate the Transportable Tablespace Set
Use RMAN to create the transportable tablespace set, including metadata files and datafiles.

rman target / 

RMAN> TRANSPORT TABLESPACE sales_data  
TABLESPACE DESTINATION '/tmp/transport_tbs'  
EXPORT LOG '/tmp/transport_tbs/tts_export.log';

This command creates the datafiles and a transportable tablespace set in the specified destination.

Transfer Files to the Destination Server (Linux)
Using a secure copy tool like scp, transfer the files to the target Linux server.

scp /tmp/transport_tbs/* oracle@linux_server:/tmp/transport_tbs/

Prepare the Target Database (Linux)
Create the necessary directories and ensure the target database is up and running.


SQL> CREATE TABLESPACE sales_data DATAFILE '/u01/app/oracle/oradata/sales_data01.dbf' SIZE 100M;

Import Metadata
Use Data Pump to import the metadata into the target database.

impdp system/password DIRECTORY=dpump_dir1 DUMPFILE=sales_data.dmp 
TRANSPORT_DATAFILES='/u01/app/oracle/oradata/sales_data01.dbf';

Make the Tablespaces Read/Write
Once the import is complete, make the tablespace read/write.

SQL> ALTER TABLESPACE sales_data READ WRITE;

Verify the Migration
Confirm the tablespace and data have been transported correctly by querying the objects.

SQL> SELECT * FROM dba_tablespaces WHERE tablespace_name='SALES_DATA'; 

SQL> SELECT * FROM sales.orders WHERE ROWNUM  EXEC DBMS_TTS.TRANSPORT_SET_CHECK('sales_data', TRUE);

Convert the Endianness (if required)
Use RMAN to convert the data files if the source and target platforms have different endian formats.

    RMAN> CONVERT DATAFILE '/tmp/transport_tbs/sales_data01.dbf'
    TO PLATFORM="Linux x86 64-bit"
    FROM PLATFORM="Windows NT (32-bit)"
    DB_FILE_NAME_CONVERT ('/tmp/transport_tbs', '/u01/app/oracle/oradata');

Import Metadata as detailed above
Following these steps, you can efficiently migrate your Oracle database from Windows to Linux using the RMAN Transportable Tablespace feature, ensuring minimal downtime and data integrity.

Conclusion:
Database migration might seem a strenuous process, but with Oracle RMAN Transportable Tablespace, it becomes structured and manageable. Adapting this guide to your specific scenarios will help in achieving this transition smoothly. If you encounter any issues, Oracle's documentation and community forums are excellent resources for troubleshooting and additional guidance.

SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations

Arvind Toorpu — Wed, 26 Feb 2025 19:40:30 +0000

Query 1: Identify tempdb Usage by Sessions That Could Be Released by Disconnecting

This query identifies sessions that are holding tempdb resources and could release them if disconnected. It focuses on sessions that are idle (sleeping) and still have allocated tempdb space.

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;

Key Outputs:

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.
session_id: The ID of the session holding the resources.
status: Indicates if the session is idle (sleeping).

Action:

If these sessions are no longer needed, you can disconnect them using the following command:

KILL <session_id>;

Query 2: Estimate tempdb Disk Space That Can Be Reclaimed by Running SHRINKFILE

This query estimates the amount of tempdb space that can be reclaimed by running SHRINKFILE. It uses the sys.dm_db_file_space_usage DMV to identify unused space in tempdb.

SELECT 
    name AS file_name,
    size * 8 / 1024.0 AS current_size_mb,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024.0 AS used_space_mb,
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024.0 AS free_space_mb
FROM 
    sys.master_files
WHERE 
    database_id = DB_ID('tempdb')
    AND type = 0; -- 0 = Data file, 1 = Log file

Key Outputs:

current_size_mb: Current size of the tempdb data file in MB.
used_space_mb: Space actively used in the tempdb data file in MB.
free_space_mb: Free space that can potentially be reclaimed by shrinking the tempdb data file.

Action:

If there is significant free space, you can reclaim it by running the following command:

USE tempdb;
DBCC SHRINKFILE ('tempdev', target_size_in_mb); -- Replace 'tempdev' with the logical name of your tempdb data file

Important Notes:

Disconnecting Sessions:
- Disconnecting sessions should be done cautiously, as it may disrupt active processes or users.
- Ensure the sessions are truly idle and no longer needed before killing them.
Shrinking tempdb:
- Shrinking tempdb is generally not recommended unless absolutely necessary, as it can lead to fragmentation and performance overhead.
- If tempdb grows frequently, consider increasing its initial size to avoid frequent auto-growth events.
Monitoring:
- Regularly monitor tempdb usage using the provided queries to proactively manage resources and avoid contention.

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

Arvind Toorpu — Tue, 18 Feb 2025 01:10:12 +0000

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

Columnstore indexes in SQL Server have become a cornerstone for improving performance in analytical workloads, particularly for large datasets. With the latest enhancements in SQL Server 2022, these indexes are more powerful, versatile, and capable of boosting performance across diverse scenarios. This article explores these enhancements with practical examples to help you understand and implement them effectively.

1. Ordered Columnstore Indexes

SQL Server 2022 introduces the ability to create ordered columnstore indexes. Sorting the data while building a columnstore index improves compression efficiency and enables faster queries for range-based filters.

Example

Suppose we have a sales table:

CREATE TABLE SalesData (
    SalesID INT PRIMARY KEY,
    SalesDate DATE NOT NULL,
    SalesAmount DECIMAL(10, 2)
);

To create an ordered columnstore index by SalesDate:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesData
ON SalesData
ORDER BY SalesDate;

Explanation

Before Ordering: Querying a range of dates, e.g., sales data for a specific year, would involve scanning unsorted row groups.
After Ordering: The query benefits from improved compression and faster access since data is sorted by SalesDate. This is particularly useful for time-series queries such as WHERE SalesDate BETWEEN '2025-01-01' AND '2025-12-31'.

2. Batch Mode on Rowstore

Batch mode execution, traditionally available only for columnstore indexes, is now extended to rowstore tables in SQL Server 2022. This feature allows analytical workloads on rowstore tables to benefit from batch processing, improving query performance.

Example

Consider a Products table stored as a rowstore:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Category NVARCHAR(50),
    Price DECIMAL(10, 2)
);

Query using batch mode:

SELECT Category, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category
OPTION (USE HINT ('ENABLE_BATCH_MODE'));

Explanation

Without Batch Mode: The query processes rows one by one, which is slower for analytical tasks.
With Batch Mode: Data is processed in batches, leading to significant performance gains by minimizing CPU and memory usage.

3. Enhanced Support in Always On Availability Groups

Columnstore indexes now support querying on readable secondary replicas in Always On Availability Groups. This enhancement allows organizations to offload analytical workloads to secondary replicas.

Example

Assume an Always On configuration with a primary and a secondary replica. On the secondary replica:

SELECT SalesDate, SUM(SalesAmount) AS TotalSales
FROM SalesData
GROUP BY SalesDate
HAVING SUM(SalesAmount) > 1000;

Explanation

Primary Replica: Reserved for transactional operations.
Secondary Replica: Processes read-intensive queries involving columnstore indexes, reducing the workload on the primary replica while maintaining high availability.

4. Improved Diagnostic Capabilities

The new DMV sys.dm_db_column_store_row_group_physical_stats provides detailed insights into the health and efficiency of row groups in columnstore indexes.

Example

To inspect a columnstore index:

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesData');

Explanation

Insights Gained: The DMV reveals metrics such as the number of rows per row group, compression status, and whether segments are being pushed to disk or remain in memory.
Use Case: Identifying poorly compressed or fragmented row groups to decide whether to rebuild or reorganize the columnstore index.

5. Batch Mode for Single-Threaded Queries

SQL Server now supports batch mode processing even for single-threaded queries, which was previously limited to multi-threaded scenarios.

Example

Query with a single-threaded operation:

SELECT SUM(SalesAmount) AS TotalSales
FROM SalesData
WHERE SalesDate > '2025-01-01';

Explanation

Before: Single-threaded queries executed row by row, consuming more resources.
After: Batch mode optimizes the execution by processing data in chunks, resulting in faster queries even without parallelism.

6. Batch Mode for the SORT Operator

The SORT operator in SQL Server now supports batch mode execution when working with columnstore indexes.

Example

Suppose you want to rank sales records by SalesAmount:

SELECT SalesID, SalesAmount,
       RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM SalesData;

Explanation

Without Batch Mode: Sorting large datasets row by row is resource-intensive.
With Batch Mode: Sorting is performed in batches, reducing the CPU and memory overhead while improving the overall efficiency.

Performance Comparison Using Benchmark

Let’s quantify the impact of columnstore indexes using a benchmark experiment with a table containing 20 million rows.

Setup

Create the table and populate it with 20 million rows:

CREATE TABLE SalesData (
    SalesID INT PRIMARY KEY,
    SalesDate DATE NOT NULL,
    SalesAmount DECIMAL(10, 2)
);

-- Insert 20 million rows
INSERT INTO SalesData
SELECT TOP (20000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SalesID,
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2024-01-01') AS SalesDate,
    RAND(CHECKSUM(NEWID())) * 1000 AS SalesAmount
FROM master.dbo.spt_values v1, master.dbo.spt_values v2;

Query Performance Without Columnstore Index

SELECT SalesDate, SUM(SalesAmount) AS TotalSales
FROM SalesData
GROUP BY SalesDate
HAVING SUM(SalesAmount) > 5000;

Execution Time: ~50 seconds
Rows Processed: 20 million

Add Columnstore Index

CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesData
ON SalesData;

Query Performance With Columnstore Index

SELECT SalesDate, SUM(SalesAmount) AS TotalSales
FROM SalesData
GROUP BY SalesDate
HAVING SUM(SalesAmount) > 5000;

Execution Time: ~2 seconds
Rows Processed: ~1 million (compressed row groups)

Conclusion

The latest advancements in columnstore indexes—such as ordered indexes, batch mode on rowstore, and extended diagnostic capabilities—have made SQL Server 2022 a powerful platform for handling analytical workloads. By leveraging these features, organizations can achieve remarkable performance improvements, especially for large datasets, making columnstore indexes an indispensable tool in modern database management.

DEV Community: Arvind Toorpu

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

Best Practices for Setting MAXDOP

Important Considerations

How to Validate MAXDOP Settings

Final Thoughts

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

Query to Find tempdb Disk Space Usage by Idle Sessions:

Explanation of the Query:

Example Output:

Key Notes:

Auditing SQL Server Database User Activity in AWS RDS: A Step-by-Step Guide

Auditing SQL Server Database User Activity in AWS RDS: A Step-by-Step Guide

Step 1: Enable SQL Server Audit in AWS RDS

1.1 Configure an Audit Parameter Group

Step 2: Set Up SQL Server Audit

2.1 Create an Audit Object

2.2 Create an Audit Specification

2.3 Enable the Audit and Specification

Step 3: Access and Review Audit Logs

Step 4: Use CloudWatch for Enhanced Monitoring

4.1 Enable Enhanced Monitoring

4.2 Stream Audit Logs to CloudWatch

4.3 Set Up CloudWatch Alerts

Step 5: Query User Activity with Dynamic Management Views (DMVs)

5.1 Track Active Sessions

5.2 Review Recent Logins

5.3 Monitor Query Activity

Step 6: Automate Alerts and Notifications

6.1 Use Event Notifications

6.2 Configure Alerts in AWS RDS

Step 7: Best Practices for RDS SQL Server Auditing

Conclusion

Oracle Database Migration from Windows to Linux Using RMAN Transportable Tablespace

Oracle Database Migration from Windows to Linux Using RMAN Transportable Tablespace

SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations

Query 1: Identify tempdb Usage by Sessions That Could Be Released by Disconnecting

Key Outputs: