SQL Server performance tuning for web apps: find and fix the slow parts

Start by measuring, not guessing

When a web page feels slow, the database is blamed first, and sometimes unfairly. Before touching an index, confirm where the time goes. Application Insights or any decent profiler will show how much of a request is spent waiting on SQL versus rendering, calling other services or serialising a huge response. If the database is a minor part, tuning it will not help.

When SQL Server is the culprit, Query Store is the most useful built-in tool. It records query text, execution plans and runtime statistics over time, so you can sort by total duration, CPU or reads and see which statements deserve attention. Dynamic management views, such as those that report missing index suggestions and wait statistics, add context. Treat suggestions as hints, not orders, because the engine does not know your whole workload.

A good tuning session produces a short list: the five queries that cost the most in total, which is a mix of slow ones and fast ones called thousands of times. That list is your plan. If your team needs help building it, our SQL Server database development service includes performance reviews.

Indexes: the biggest lever and the easiest to abuse

Most dramatic improvements come from adding the right index, turning a full table scan into a seek. Look for scans on large tables inside execution plans, key lookups repeated for thousands of rows and warnings about missing indexes. A well-chosen composite index that matches your filter and sort columns, with included columns to cover the select list, can change a query from unusable to instant.

The abuse comes from adding every suggested index. Each index speeds some reads and slows every insert, update and delete, and consumes storage and backup time. On a busy transactional table that cost is real. Review unused indexes periodically, merge overlapping ones and keep the design intentional. A table with twenty indexes usually has a design problem, not a performance solution.

Statistics matter too. SQL Server uses them to estimate row counts and choose plans. Stale statistics on a fast-growing table can produce bad plans that look inexplicable. Regular maintenance, using sensible defaults and a tested schedule rather than a blind nightly rebuild of everything, keeps estimates healthy.

Query patterns that quietly hurt

Plenty of slowness is written into queries. Wrapping an indexed column in a function, such as converting a date column inside the where clause, stops the engine using the index. Leading wildcards in text searches force scans. Selecting every column when the page needs three wastes memory and network. Implicit conversions between mismatched data types, for example comparing a varchar column to an nvarchar parameter, can silently disable index seeks.

Parameter sniffing is the other classic. SQL Server builds a plan for the first parameter values it sees and reuses it. If those values were unusual, the plan suits few later calls. Remedies range from query hints to restructuring the query or using option clauses, but diagnose before you prescribe, since each has side effects.

Paging deserves a mention. Offset-based paging on a large result set gets slower as users go deeper, and sorting without a supporting index is costly. For feeds and admin lists, keyset paging or sensible limits are often better. Pair these fixes with the broader practices in our guide to building enterprise portals, where reporting screens are a common offender.

Tuning notes for Pakistani deployments

Many applications we inspect in Pakistan run on modest hardware or shared virtual servers, sometimes with SQL Server Standard or Express editions that have memory and size limits. Check the limits of your edition against your data growth, because hitting a ceiling looks exactly like a mysterious slowdown. Verify current edition limits in Microsoft's documentation.

Power and connectivity interruptions make backups and recovery drills more than a formality. Test that your database can be restored to a clean server, and keep copies off the same machine. Where the application serves customers in the Gulf or the UK from a Pakistani server, latency adds to every round trip, which makes chatty data access much more costly. Hosting in a nearby cloud region, as discussed in our Azure hosting article, often helps more than any query rewrite.

If you are stuck with an old application, tuning may be a bridge to modernisation rather than the destination. See our ASP.NET maintenance and support page for how we stabilise systems before larger changes.

Reading an execution plan without fear

Execution plans look intimidating but you only need a few habits. Read them right to left, because data flows from the leaf operations towards the final result. Look for the thickest arrows, which show where the most rows travel, and for operators with a high share of the estimated cost. A table scan or index scan on a big table is a prompt to ask whether a seek is possible. A key lookup executed many thousands of times suggests a covering index.

Then compare estimated rows with actual rows. When the two are far apart, the optimiser chose a plan based on wrong assumptions, and the cause is usually stale statistics, parameter sniffing or a predicate it cannot estimate. Warnings such as spills to tempdb, implicit conversions and missing statistics tell you precisely what to fix. A change that lowers logical reads is almost always an improvement, even when timing on a quiet development machine looks unchanged.

If you keep Entity Framework or another ORM in the picture, log the generated SQL for the slow page and run that exact statement. The query the ORM writes is often not the query the developer imagined. When the generated SQL is the problem, our ASP.NET Web API development team usually reshapes the data access rather than abandoning the ORM.

Maintenance habits that keep performance steady

Tuning is wasted if the database degrades again within months. Build a light maintenance routine: update statistics on a sensible schedule, rebuild or reorganise only fragmented indexes that matter, check for blocking after each release and keep an eye on file growth and tempdb. Run integrity checks and, above all, test restores. Review the top queries in Query Store after every major deployment, since new features often introduce new access patterns.

Archive old data when it no longer serves daily screens. A table holding years of logs or completed orders slows everything that touches it, while a separate archive table or partitioning strategy keeps the active set small. Agree retention rules with the business before deleting anything.