SQL Server DatabasesSQL Server is one of those platforms that you can run “out of the box” for years without issues — until one day it isn’t. When performance problems strike, businesses feel it in real dollars: payroll delayed because a query is blocked, reports timing out during a board meeting, or customer transactions failing at peak load.
That’s when the database team gets the call.
My name is Jamaurice Holt, and as a Senior Database Architect I’ve managed hundreds of SQL Server instances across multiple data centers and cloud providers. From MySQL migrations to Always On Availability Groups, I’ve seen firsthand what separates environments that survive from those that thrive. And like Brent Ozar famously says:
“Your SQL Server doesn’t care about your feelings — it cares about waits, bottlenecks, and bad queries.”
The Reality of Enterprise SQL Server: More Than Just Backups and Patches
In one of my largest roles, I managed 300+ SQL Server databases across three data centers and two cloud platforms. The complexity wasn’t just in scale — it was in the variety of workloads:
- OLTP workloads demanding millisecond response times.
- Analytics queries chewing through billions of rows.
- Legacy vendor databases running SQL 2012 side-by-side with 2019 clusters. Every day was about balancing performance, cost, and uptime. And every week surfaced new lessons.
Indexing in the SSD Era: Stop Fighting Yesterday’s Battles
One of the biggest misconceptions I still see is the obsession with index fragmentation.
In the spindle-disk era, nightly reindex jobs were critical. Fragmentation meant the disk head jumped all over the platters, killing performance. But today, with SSDs and cloud-backed storage, there are no spindles to defragment. Fragmentation simply doesn’t matter the way it once did.
Here’s how I approach it now:
- Focus on statistics: Accurate stats drive good query plans.
- Avoid blind reindexing: Rebuilding indexes “just because” wastes resources and bloats transaction logs.
- Use Ola Hallengren’s maintenance scripts: Ola’s framework is community-standard for handling index rebuilds, statistics updates, and integrity checks. But the defaults need tuning. For example, don’t rebuild every index nightly; target only those with high fragmentation and frequent use.
- Know your workload: Sometimes, leaving an index fragmented is less harmful than triggering massive log growth with a rebuild. As Brent Ozar often points out, “Stop treating index maintenance as a religion — treat it as a business decision.”
Execution Plans: The Story Behind Every Query
Ask any experienced DBA: execution plans don’t lie. They show you exactly how SQL Server thinks your query should run.
Some of the most impactful fixes I’ve implemented came from digging into plans that looked “fine” at first glance but told a deeper story:
- A nested loop join ballooning into billions of lookups.
- Scalar UDFs tanking performance until SQL Server 2019’s UDF inlining saved the day.
- Queries that flipped from good to bad because of parameter sniffing — running perfectly for one input, then grinding to a halt for another. I’ve made it a habit to teach teams: “Don’t argue opinions — read the plan cache.”
Wait Statistics: The Server’s Cry for Help
When a server is slow, DBAs often dive straight into CPU, memory, or disk. But the truth lives in wait stats.
Waits are how SQL Server tells you what it’s waiting on:
- CXPACKET and CXCONSUMER waits reveal parallelism pain.
- PAGEIOLATCH tells you queries are bottlenecked on I/O.
- ASYNC_NETWORK_IO means the app can’t consume data fast enough. The trick is separating signal from noise. Every SQL Server will show waits — but tuning for the wrong ones wastes time. Brent Ozar’s advice here is gold: “Tune the waits you can change, not the ones you can’t.”
Real-World War Stories
A few lessons that stick with me:
- TempDB contention: On one system, TempDB had a single data file on a SAN. Under load, it became a bottleneck. Adding additional data files and moving TempDB to SSDs cut blocking incidents by 80%.
- Over-indexing disaster: A vendor insisted on creating an index for nearly every column. The database swelled to terabytes, backups dragged, and inserts slowed to a crawl. After auditing usage, we dropped dozens of indexes — instantly improving performance.
- Parameter sniffing meltdown: A report query ran in 3 seconds one day and 30 minutes the next. The culprit? SQL Server cached a “bad plan” from a skewed parameter. Fix: forced plan guides and, later, OPTIMIZE FOR UNKNOWN hints. These aren’t academic problems — they’re the daily battles DBAs fight to keep businesses online.
SQL Server in the Cloud Era
Cloud has changed how we think about tuning. Running SQL Server in AWS RDS or Azure SQL Managed Instance introduces new rules:
- Limited access to the OS and storage means you can’t always “fix it with hardware.”
- Cloud billing models mean bad queries aren’t just slow — they’re expensive.
- Availability Groups behave differently in managed services, with failover quirks DBAs must account for. Performance tuning in the cloud requires not just query knowledge but also cost-awareness and architecture planning.
Why Curiosity Matters More Than Scripts
Tools like Ola Hallengren’s scripts and Brent Ozar’s First Responder Kit are invaluable. But scripts can only take you so far. The real skill is curiosity — digging beyond the symptom to uncover the cause.
Being a great DBA means asking questions like:
- Why is this query slow only on Tuesdays?
- Why did the optimizer suddenly choose a different plan?
- What changed in the environment that no one told us about? It’s detective work. And it’s what makes the role exciting.
Final Thoughts
I don’t claim to have all the answers — DBA work is part science, part art. But after years managing enterprise-scale SQL Server environments, I can say this:
- Index strategies must evolve in the SSD/cloud era.
- Execution plans and wait stats are your truth-tellers.
- Community tools from experts like Brent Ozar and Ola Hallengren are game-changers — but they work best in the hands of a curious, adaptable DBA. My name is Jamaurice Holt, and I’m committed to sharing real-world lessons in SQL Server performance, cloud migrations, and database resilience. If you’re a fellow DBA or engineer, let’s keep the conversation going — because in this field, we all get better when we learn from each other.
#SQLServer #DatabaseAdministration #PerformanceTuning #DBA #SQLServerPerformance #SQLServerTips #QueryTuning #IndexOptimization #SQLCommunity #BrentOzar #OlaHallengren #CloudDatabases #SQLServerMigration #AzureSQL #AWSRDS #DatabaseResilience #TechLeadership #JamauriceHolt
Originally published on Medium