News

Optimising SQL Server Queries for High-Performance Web Apps

Optimizing SQL Server Queries for High-Performance Web Applications is less about finding one magical index and more about reducing unnecessary work at every stage of a request. A page that feels instant in a local development environment can become sluggish when it serves customers across Australia, competes for database resources, or processes thousands of concurrent requests.

SQL Server performance affects the entire web stack. Slow joins increase API latency, oversized result sets consume memory, and poorly planned writes can block customer-facing reads. The symptoms may appear in Angular, .NET, or a reverse proxy, while the underlying cause is an inefficient execution plan.

Australian applications also have practical geographic considerations. A database hosted in Sydney may serve users in Melbourne quickly but introduce noticeable latency for customers in Perth, while an international data centre can add delay across every database round trip. Mobile users on variable NBN, 4G, and 5G connections make compact responses especially valuable.

Effective query tuning combines measurement, sound schema design, appropriate indexing, and code that asks SQL Server for precisely the data it needs. The following techniques apply to online shops, booking systems, internal business portals, and high-traffic APIs.

Measure The Real Bottleneck

Start with evidence rather than rewriting queries based on intuition. Capture request duration, database duration, CPU time, logical reads, physical reads, returned rows, and wait statistics. Application Performance Monitoring tools can show whether a slow endpoint spends its time in SQL Server or elsewhere in the request pipeline.

In SQL Server Management Studio, SET STATISTICS IO ON and SET STATISTICS TIME ON provide useful local measurements. The Actual Execution Plan reveals operators, estimated row counts, scans, lookups, sorts, and memory grants. Query Store is particularly valuable in production because it records query history and helps identify regressions after a deployment or statistics update.

A query that runs in 20 milliseconds on a developer laptop may behave differently against a busy production database. Test with representative data volumes, realistic parameters, and concurrent traffic. A Sydney-based retailer preparing for a Boxing Day promotion should benchmark its catalogue and checkout queries under a load pattern that resembles the event, rather than relying on a handful of manual requests.

Pay attention to percentile latency, not just averages. An average response time of 150 milliseconds can conceal a poor 95th or 99th percentile caused by blocking, plan compilation, or occasional large scans. Customers experience those slow outliers as a page that stalls.

Return Less Data

The fastest rows for SQL Server to retrieve are the rows the application never requests. Avoid SELECT * in web-facing queries, particularly when tables contain large descriptions, JSON documents, binary data, or audit columns. Specify the fields required by the view model and leave unused data in the database.

Pagination should be deliberate. A simple OFFSET ... FETCH query is convenient, but deep pages can become expensive because SQL Server still has to locate and discard preceding rows. For feeds, transaction histories, and large product lists, keyset pagination is often more efficient:

SELECT TOP (25)
    OrderId,
    CreatedAt,
    TotalAmount,
    Status
FROM dbo.Orders
WHERE CreatedAt < @LastSeenCreatedAt
ORDER BY CreatedAt DESC;

For reliable ordering, use a unique tie-breaker such as OrderId alongside CreatedAt. This prevents records from moving between pages when several rows share the same timestamp.

Filtering should happen in SQL Server rather than in C# or JavaScript. Loading 50,000 records into an ASP.NET service and filtering them in memory increases database transfer, application memory, garbage collection, and response time. A narrow query also reduces bandwidth for users browsing from regional towns or using a congested mobile connection.

Build Indexes Around Access Patterns

An index should support a real query pattern, not simply exist because a column is frequently mentioned in application code. Begin with columns used in WHERE, JOIN, and ORDER BY clauses. The best key order depends on selectivity, filtering behaviour, and how the query sorts or groups results.

For example, an orders screen might commonly filter by customer and status, then sort by creation date:

CREATE INDEX IX_Orders_Customer_Status_Created
ON dbo.Orders (CustomerId, Status, CreatedAt DESC)
INCLUDE (OrderId, TotalAmount);

The included columns can allow SQL Server to satisfy the query from the index alone, avoiding extra key lookups. Covering indexes often improve read-heavy endpoints, but they increase storage and write costs. Every insert, update, and delete must maintain each affected index.

Avoid creating separate indexes for every column without reviewing their value. Too many overlapping indexes slow writes, consume memory, and complicate maintenance. Check usage statistics and execution plans, then remove redundant structures cautiously after observing production workloads.

Filtered indexes are useful when an application repeatedly accesses a small subset, such as active subscriptions or unprocessed jobs:

CREATE INDEX IX_Jobs_Ready
ON dbo.Jobs (Priority, CreatedAt)
WHERE ProcessedAt IS NULL;

The query predicate must align with the filter for SQL Server to use the index. Index design should evolve with the application’s most important access paths rather than follow generic rules.

Keep Predicates Searchable

A searchable, or SARGable, predicate allows SQL Server to use an index efficiently. Applying a function to an indexed column often prevents a seek. For example, WHERE YEAR(OrderDate) = 2025 makes it harder to use an index on OrderDate than a range condition:

WHERE OrderDate >= '20250101'
  AND OrderDate <  '20260101'

The same principle applies to conversions, calculations, and leading wildcard searches. LIKE '%tablet%' generally cannot use a normal B-tree index effectively, while LIKE 'tablet%' can often use one. For genuine word search, consider Full-Text Search or a dedicated search service.

Implicit conversions are another common source of poor plans. If a web API sends a string parameter for an integer or date column, SQL Server may convert every row during comparison. Match parameter types to the database schema in C#, and use strongly typed parameters rather than concatenated SQL.

Avoid constructing SQL with string interpolation. Parameterised queries protect against injection and help SQL Server reuse execution plans. In .NET, use SqlParameter, a well-configured micro-ORM, or Entity Framework parameterisation. Dynamic SQL can still be appropriate when safely generated with sp_executesql and validated identifiers.

Understand Joins And Execution Plans

Execution plans show how SQL Server intends to obtain data. A clustered index scan is not automatically a problem; scanning a small table may be cheaper than seeking through an index. The important questions are whether the chosen operation matches the table size, whether estimates are accurate, and how much work the operator performs.

Join performance depends on useful indexes and compatible data types. Foreign key columns should usually be indexed when they participate in joins, especially on large child tables. A query joining Orders to OrderLines can multiply rows quickly, so return only the columns and records required by the endpoint.

Large discrepancies between estimated and actual row counts often indicate stale statistics, skewed data, or parameters that produce very different result sizes. Updating statistics may help, but it does not solve every problem. Parameter-sensitive queries sometimes need a revised query shape, OPTION (RECOMPILE), OPTIMIZE FOR, or a different indexing strategy.

Be cautious with hints. FORCESEEK, join hints, and forced plans can stabilise a known regression, but they can also preserve an assumption that becomes wrong as data changes. Treat hints as targeted interventions, document them, and review them through Query Store.

Control Blocking And Transaction Scope

A fast query can still wait behind another transaction. Long-running updates, uncommitted changes, and excessive transaction scopes create blocking that appears to application users as random slowness. Capture blocking chains and identify the session holding the lock, not only the session waiting for it.

Keep transactions short and focused. Do not open a transaction before making external HTTP calls, rendering a complex response, or waiting for user input. In an ASP.NET service, perform validation and remote work outside the database transaction whenever the business rules allow it.

The default READ COMMITTED isolation level may be suitable for many operations, but row-versioning options such as READ_COMMITTED_SNAPSHOT can reduce reader-writer blocking. Enable such changes only after assessing tempdb capacity, transaction duration, and consistency requirements.

For a booking system serving customers in Brisbane and Adelaide, avoiding double allocation matters more than shaving a few milliseconds from a read. Use the isolation level and locking behaviour that protect the business invariant, then tune the query within those correctness boundaries.

Improve The Application-Database Boundary

Database optimisation is often limited by how the application calls SQL Server. Reduce chatty data access patterns such as issuing one query per item in a loop. This N+1 pattern can be hidden inside an ORM and become costly when an endpoint returns hundreds of entities.

Use projection to select only the fields required by the response. In Entity Framework Core, a projection with Select is generally preferable to loading full entities and related graphs. Apply AsNoTracking() for read-only queries where change tracking offers no benefit, and inspect generated SQL rather than assuming the ORM produced an efficient statement.

Connection pooling helps reuse established connections, but it does not make an overloaded database faster. Dispose connections promptly, avoid holding them while performing non-database work, and set sensible command timeouts. A timeout should expose an operational problem, not conceal it indefinitely.

Caching can remove repeated database work for stable data such as product categories, configuration, and public content. Use an appropriate cache lifetime and invalidate data when changes matter. A Melbourne ticketing service may cache event metadata safely while keeping seat availability strongly consistent.

Practical Query Tuning Checklist

Use the following recommendations when reviewing a slow endpoint or planning a new SQL Server feature:

Treat this checklist as part of code review and operational monitoring rather than a once-only database exercise. Query performance changes as tables grow, customer behaviour shifts, and new features introduce different access patterns.

A good review also records the reason for an index, a query rewrite, or a transaction setting. That context helps the next developer understand which workload the change supports and prevents well-intended maintenance from undoing a performance fix.

Fast SQL Server applications are built through small, measurable decisions: retrieve fewer rows, make access paths predictable, keep transactions controlled, and verify every assumption against production-like data. For an Australian web product, that discipline can reduce latency across Melbourne, Perth, Sydney, and regional users while lowering infrastructure costs.

Start with the slowest measured endpoint, capture its actual plan, and make one controlled change at a time. Record the before-and-after metrics in source control or your engineering notes, then keep Query Store and application telemetry in the deployment pipeline so performance remains a feature of the product rather than a last-minute repair.