Quick answer: Most SQL Server performance problems in .NET apps come from a short list: missing or unused indexes, queries that scan too much data, N+1 database calls from ORMs, oversized result sets and blocking under load. Start by measuring (wait stats, Query Store or traces, application timings), fix the top queries first, then harden connection usage and caching. Guessing with random indexes rarely helps and can hurt writes.
Measure before you change anything
Tuning without a baseline wastes time. Capture:
- Which pages or API endpoints feel slow
- Server CPU, memory and disk latency during the pain
- Top queries by CPU, duration and logical reads
- Blocking sessions when users report freezes
On modern SQL Server versions available by early 2020, Query Store is one of the best built-in ways to see regressions after a release. Extended Events or historical Profiler traces still help in older environments. On the .NET side, Application Insights, custom timing logs or MiniProfiler-style tools show which repository calls dominate a request.
Compare .NET stack choices when you are still selecting a platform; for business line apps see .NET vs Node.js. Database hygiene tips in WordPress database optimization overlap conceptually even when the engine differs. Performance work below assumes SQL Server is already the store.
The usual suspects in .NET + SQL Server
N+1 queries
ORMs such as Entity Framework make it easy to lazy load related data in a loop: one query for parents, then one per child. Under a list page that becomes hundreds of round trips. Fix with eager loading (Include), explicit joins or a projection that selects only needed columns in one query. Watch the SQL log while you click through critical screens.
SELECT *
Pulling every column through an ORM materialises wide rows you never show. Prefer projections to DTOs. Less data over the wire means less memory and often better plan choices.
Missing indexes
Look at seeks vs scans on hot tables. A selective filter or join key without a supporting index forces scans. Add indexes that match real WHERE and JOIN patterns. Avoid indexing every column “just in case”; each index slows inserts and updates and consumes space.
Implicit conversions
Comparing a parameterized varchar to an nvarchar column (or mismatched types) can prevent index use. Align types in the schema and in ADO.NET / EF parameter types.
Parameter sniffing surprises
Cached plans that were great for one parameter set can be poor for another. Mitigations include option recompile on rare heavy queries, careful use of local variables or plan guides in specialist cases. Do not spray RECOMPILE everywhere.
Locking and blocking
Long transactions, wide updates and scans under READ COMMITTED can block readers. Keep transactions short, update fewer rows per batch and consider row versioning isolation (READ COMMITTED SNAPSHOT) with guidance from someone who understands the trade-offs for your app.
A practical tuning loop
- Identify the slowest user-facing action.
- Capture the SQL it runs (profiler, EF logging, Query Store).
- Run the heavy statements in SSMS with actual execution plans.
- Note high cost operators: scans, key lookups in a loop, sorts spilling to tempdb, parallel zones that surprise you.
- Fix data access shape first (fewer calls, narrower rows), then indexes, then query rewrites.
- Retest with realistic data volumes. Empty tables lie.
Indexing guidelines that stay useful
- Clustered index choice matters; often the primary key, but not always for heavy range patterns
- Covering indexes can remove key lookups for critical read paths
- Filtered indexes help for status columns with skewed values when used carefully
- Rebuild or reorganise based on fragmentation and maintenance windows; do not rebuild obsessively on tiny tables
- Review unused indexes periodically and drop those that only cost write time
Caching and application patterns
Not every read should hit SQL Server:
- Cache reference data (country lists, rare-changing settings) in memory with a clear expiry
- Use output caching for anonymous pages where freshness allows
- Avoid caching per-user data in ways that explode memory
- Prefer async database calls in ASP.NET to free threads under IO wait, but async does not fix a terrible query
Redis or similar external caches appear in larger systems; many business apps get far with in-process cache and better SQL first.
Connection pooling and timeouts
.NET connection pooling works when the connection string stays stable. Open late, close early (using blocks). Do not hold connections open across HTTP callouts. Set sensible command timeouts and log timeout victims rather than quietly retrying forever.
Watch for connection leaks under exceptions. A using statement around SqlConnection / DbContext scopes prevents many production incidents.
Reporting vs OLTP
Heavy reports on the same database as transactional traffic cause pain. Options include:
- Scheduled snapshot tables
- Read-only replica for reporting when licensing and setup allow
- Running large aggregates off-peak
- Pre-aggregating overnight
Do not let a director’s ad-hoc Excel extract lock the orders table at noon.
When the database is not the problem
Sometimes SQL is fine and the app is chatty with remote APIs, or the UI loads megabytes of JavaScript. Confirm with timings. Also check for DNS issues, failing Always On failovers or storage latency on the SQL host. Hardware and virtualisation noise is real on shared servers.
Reading an execution plan without fear
In SSMS, enable the actual execution plan and look for thick arrows (large row counts), cluster scans on big tables, and nested loops that multiply work. Hover operators for estimated vs actual rows; big mismatches hint at stale statistics or odd parameters. Key lookups after a noncovering index seek often mean you should include a few output columns in the index. Sorts that spill to tempdb show as warnings; sometimes a better index removes the sort entirely.
You do not need to memorise every icon. Follow the highest relative cost operators first and ask whether the query touches more rows than the feature needs.
Statistics and plan stability
SQL Server uses statistics to estimate cardinality. Auto-update helps but not always on large tables with gradual change. After bulk loads, updating statistics on hot tables prevents surprise plans. Avoid cargo-culting full scan sampling settings without measuring. When a release changes query shape, watch Query Store for regressions and force a prior plan only as a temporary bridge while you fix the root cause.
Tempdb and concurrency
Heavy sorts, hashes and snapshot isolation versioning stress tempdb. Multiple tempdb data files (within reason for your version and guidance), adequate storage throughput and avoiding unnecessary spills all help. Contention shows up as PAGELATCH waits. Application-side, fewer huge intermediate result sets beat endless server tuning.
Collaboration between developers and DBAs
Share a short performance budget for critical endpoints (for example p95 under two seconds on staging with production-sized data). Put slow query tickets next to feature work so they do not linger. A monthly top-10 query review prevents death by a thousand cuts.
FAQ
Should I add indexes suggested by the Database Engine Tuning Advisor blindly?
No. Use them as hints. Validate against write volume and overlapping existing indexes.
Is Entity Framework too slow for serious apps?
EF can perform well with careful queries and projections. Raw SQL or micro-ORMs help for hotspots. The ORM is rarely the whole story; query shape is.
How do I prove a fix worked?
Compare duration percentiles and logical reads for the same workload before and after, plus user-facing timings on staging with production-like data.
What about stored procedures vs ORM?
Either can be fast or slow. Procedures help encapsulate complex sets and plans; ORMs help velocity. Many teams mix both.
Make SQL visible to the team
SQL Server performance improves when developers see the queries their code produces and DBAs share wait stats in language the team understands. If you need a .NET application that stays responsive under real data, see our Custom development service.
