Service

SQL Server performance tuning

When a system slows down, the database is usually where the time goes. Month-end runs past the morning, screens freeze while a batch job is running, users see "transaction was deadlocked" errors, and somebody suggests a bigger server. A bigger server sometimes helps for a while. Finding the real cause helps for good.

We diagnose SQL Server performance problems from evidence, fix the causes in the schema, the queries and the application code, and leave you with the monitoring to keep it that way.

Typical symptoms

• Deadlocks that appear under load and disappear when you try to reproduce them.
• Blocking chains where one long transaction stalls dozens of users.
• Queries that were fast last year and now take minutes as the data has grown.
• Batch jobs and reports competing with the daytime workload.
• Plans that change from one day to the next, so performance is fast, then slow, then fast.
• An ORM generating queries nobody has looked at.

How we find the cause

We start by measuring, not guessing. Using Query Store, Extended Events, the deadlock graph, wait statistics and the plan cache, we find which statements actually consume the time, what they wait on, and which sessions block each other. Most systems have a handful of queries responsible for most of the pain, and the goal of the first days is to name them.

Deadlocks in particular nearly always come from a pattern: two code paths touching the same tables in a different order, a transaction that stays open while the application does other work, or an update that scans far more rows than it changes. Once the pattern is visible, the fix is usually clear.

How we fix it

• Queries and indexes. Rewrite the worst statements, add or replace indexes based on real usage, and remove the ones that only slow down writes.
• Schema. Where the design itself is the bottleneck, such as hot tables shared by unrelated workloads or wide rows updated constantly, we re-architect tables in small, reversible steps.
• Transactions and locking. Shorter transactions, a consistent lock order, the right isolation level, and row versioning where it fits.
• Application code. We fix the .NET side as well: Entity Framework and Dapper data access, batching instead of row-by-row loops, async calls that actually release threads, and connection handling.
• Workload separation. Moving reporting and batch work off the critical path, scheduling it sensibly, or pointing it at a readable secondary.

Every change is measured before and after on the same workload, so you see what each fix bought.

What an engagement looks like

1. Health check, about one week. We collect evidence from production or a realistic copy, identify the top problems and estimate the gain from fixing each. You get a short written report with the findings ranked by impact and effort.

2. Fix and verify, two to six weeks. We implement the fixes in your codebase and database projects, test them under load, and roll them out with you. The deliverables are working changes in your repository, not a list of recommendations.

3. Keep it fast. We set up the alerts and dashboards that catch regressions early, and document what we changed and why, so the next problem is found in hours rather than weeks.

A recent example

At a global investment bank, a system behind time-critical batch processing suffered from recurring deadlocks and growing latency. We led a performance initiative that re-architected the contended tables and refactored the application's data access. The deadlocks were eliminated, latency came down, and the system's scalability and reliability improved significantly.

Versions and environments

We work with SQL Server 2012 and later, on physical servers, virtual machines and containers, from small departmental databases to enterprise systems. We are comfortable working under the change-control and access rules of regulated environments, including working only through the tools and accounts your security team approves.