The client operates a core banking platform supporting real-time transaction processing, including NEFT, RTGS, UPI, and loan servicing workloads, running on a large-scale SQL Server environment with high evening peak transaction activity.

Business Challenges

The environment had scaled to a high-throughput, always-on transaction workload without a corresponding revisit of patching, indexing, and memory configuration.

Outdated patch level and disabled optimizer fixes

The instance was running several cumulative updates behind current, with query optimizer hotfixes and other performance-related trace flags left disabled.

Heavy index fragmentation

Over 300 indexes were running at 50% or higher fragmentation, with no automated maintenance strategy in place to keep pace with transaction volume.

Unclustered heap tables slowing core workloads

More than 50 high-traffic tables had no clustered index, forcing heap forwarding and degraded I/O on transaction-heavy workloads.

Index coverage gaps

Duplicate and unused indexes were adding unnecessary write overhead, alongside 18 missing indexes on high-seek columns, several with a substantial estimated query impact.

Memory and concurrency pressure at peak hours

Evening transaction volumes pushed connection counts well above 1,000, with memory utilization near capacity and no tuned memory, tempdb, or NUMA configuration to absorb the load.

Project Objectives

Geopits scoped the engagement to give the client's engineering team a prioritized, sequenced path to closing performance gaps without disrupting a live, transaction-critical banking workload.

Key Goals:

Bring the SQL Server instance current on patching and enable optimizer and performance-related trace flags
Eliminate fragmentation debt and rebuild high-traffic heap tables with proper clustered indexes
Close index coverage gaps and tune memory, tempdb, and parallelism settings for peak evening load

Solution Provided by Geopits

Geopits ran the assessment as a structured, four-part engagement covering configuration, indexing, capacity, and workload patterns.

Instance & Configuration Audit

Reviewed patch level, trace flag configuration, memory allocation, NUMA topology, and parallelism settings against current Microsoft guidance.

Index & Table Health Analysis

Assessed fragmentation, duplicate and unused indexes, missing index opportunities, and heap tables lacking clustered indexes across the core transaction schema.

Wait Stats & Capacity Review

Analyzed top wait types, disk I/O latency, and connection patterns to isolate where evening peak load was creating contention.

Prioritized Tuning Roadmap

Delivered 20 sequenced recommendations spanning patching, trace flags, indexing, memory, tempdb, and partitioning strategy.

Capabilities

Before

After

SQL Server Patch Level

Several cumulative updates behind the latest release

Apply the latest cumulative update after validation in a non-production environment.

Cost Threshold for Parallelism

Default value of 5

Increase to 50 to reduce unnecessary parallel query plans.

Index Fragmentation

300+ indexes with over 50% fragmentation

Schedule index REBUILD and REORGANIZE maintenance, prioritized by fragmentation severity.

Max Server Memory

Unconfigured; no upper limit defined

Configure to approximately 90% of physical memory to balance SQL Server and operating system requirements.

TempDB Configuration

4 TempDB data files on a high-core-count server

Increase to 8 data files to minimize allocation contention and improve concurrency.

Key outcomes

Prioritized Recommendations Delivered

Recommendations spanning patching, trace flags, indexing, memory, TempDB, and partitioning strategy, sequenced for phased rollout on the live transaction system.

Fragmented Indexes Identified

Proposed scheduled REBUILD and REORGANIZE maintenance using industry-standard scripts, prioritized by fragmentation severity.

Heap Tables Flagged for Clustered Index Rebuild

Convert heap tables to clustered indexes to reduce forwarded record overhead and improve performance on high-traffic transaction tables.

Missing Indexes Identified

Several missing indexes showed an estimated query impact of 90%+, targeting the workload's highest seek columns for maximum performance improvement.

Conclusion

The assessment gave the client's engineering team a clear, prioritized view of where its SQL Server environment stood: where patching had fallen behind, where fragmentation and missing indexes were adding load, and where memory and concurrency settings needed to catch up with evening peak volume. Geopits then worked with the team to roll these changes out in a sequence that protects a live, transaction-critical banking workload throughout.

Ready to Transform Your Data?

Geopits works alongside your team as a strategic partner, starting with stabilizing your current databases, then modernizing your data infrastructure, and ultimately helping you unlock the full potential of AI.

Book a Demo Learn More

170

Happy Clients so far

2100+

Databases Managed

142+

Successful Migrations

At Geopits, our mission is to empower businesses to achieve reliable, secure and high-performing database environments and to derive meaningful business insights.