SQL Server 2022 Optimization: 14 Techniques

Your ASP.NET application is, almost always, as fast as your SQL Server. CPU on the app server matters; query plans matter more. Here are fourteen optimizations we apply to production SQL Server 2022 instances on Adaptive's ASP.NET hosting, with the specific patterns Entity Framework Core 9 and 10 produce that benefit from each.

14 | Production Techniques
SQL 2022 | Standard / Enterprise
30-50% | Typical Throughput Gain (RCSI)
Query Store | Enabled by Default

!Database performance monitoring

1. Use the Query Store. Always.

SQL Server 2016+ ships with the Query Store — a built-in flight recorder for every query execution. It captures the plan, runtime, IO, and CPU. Without it, you're blind to regressions.

Enable on every database:

ALTER DATABASE [YourDb] SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
INTERVAL_LENGTH_MINUTES = 15,
MAX_STORAGE_SIZE_MB = 1024,
QUERY_CAPTURE_MODE = ALL
);

Then in SSMS: right-click the database → Reports → Query Store → Top Resource Consuming Queries. Three minutes of investigation usually finds the worst offender.

> ✅ On Adaptive plans: Query Store is enabled by default on every SQL Server 2022 database we provision. Reports are accessible via SSMS or Plesk.

2. Index What You Filter and Sort

The simplest, most-ignored optimization: indexes on columns used in WHERE, JOIN, and ORDER BY.

A query like:

context.Orders
.Where(o => o.CustomerId == 123 && o.Status == "Pending")
.OrderBy(o => o.CreatedAt)
.ToList();

Wants a composite index:

CREATE INDEX IX_Orders_Customer_Status_Created
ON Orders (CustomerId, Status, CreatedAt)
INCLUDE (Total, ProductId);

> 💡 Column order matters: (CustomerId, Status, CreatedAt) supports any prefix — (CustomerId), (CustomerId, Status), or the full set. Put the most-selective column first.

3. Watch Out for Parameter Sniffing

SQL Server compiles a query plan based on the first parameter values it sees. If a typical request has CustomerId = 123 (10 rows) but later asks for CustomerId = 999 (10,000 rows), the cached plan can be catastrophic for the second case.

Three mitigations:

| Mitigation | When to Use |

|---|---|

| OPTION (RECOMPILE) | Rare queries with wildly varied cardinality |

| OPTIMIZE FOR UNKNOWN | Compile for the average case (often best for general-purpose) |

| Query Store "Force Plan" | When you know one specific plan is the right one |

Entity Framework Core 9 added EF.Functions.QueryHint("RECOMPILE") for per-query control.

4. Cache Read-Heavy Queries

SQL Server 2022 supports a number of read-optimization patterns: indexed views, computed columns, and query plan caching. For heavily-read endpoints, layer in application-level caching (IMemoryCache, IDistributedCache, or Redis) to keep the database focused on writes.

public async Task<List<Product>> GetProductsAsync()
{
return await cache.GetOrCreateAsync("products:all", async entry =>
{
entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5);
return await context.Products.ToListAsync();
});
}

> 💡 For SQL Server scale-out: SQL Server 2022 supports Always On asynchronous replicas. Talk to our team if you need a custom configuration beyond the included plans.

5. Batch Operations with MERGE and ExecuteUpdate

For bulk upserts (insert-or-update), EF Core's SaveChanges runs one statement per row. With 10,000 rows, that's 10,000 round-trips.

MERGE INTO Inventory AS target
USING (VALUES (1, 50), (2, 75), (3, 30)) AS source (ProductId, Qty)
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET Qty = source.Qty
WHEN NOT MATCHED THEN INSERT (ProductId, Qty) VALUES (source.ProductId, source.Qty);

EF Core 9 added ExecuteUpdate and ExecuteDelete for similar bulk patterns:

context.Inventory
.Where(i => i.Qty == 0)
.ExecuteDelete();
context.Orders
.Where(o => o.Status == "Pending" && o.CreatedAt < cutoff)
.ExecuteUpdate(s => s.SetProperty(o => o.Status, "Expired"));

6. Use Ledger Tables for Audit Requirements

SQL Server 2022 introduced ledger tables — append-only, cryptographically verified storage. Great for audit logs, financial transactions, and any compliance-driven record-keeping.

CREATE TABLE AuditLog (
Id BIGINT IDENTITY PRIMARY KEY,
EntityType NVARCHAR(100),
EntityId BIGINT,
Action NVARCHAR(50),
PerformedAt DATETIME2 GENERATED ALWAYS AS ROW START,
PerformedBy NVARCHAR(100)
)
WITH (LEDGER = ON);

Tamper-evident, queryable like a normal table, integrates with Azure Confidential Ledger if you need external proof.

7. Encrypt Sensitive Columns with Always Encrypted

Always Encrypted keeps column data encrypted at the database — even DBAs can't read it without the client-side key. Combined with .NET 10's post-quantum support, this is the gold standard for sensitive data.

CREATE TABLE Customer (
Id INT PRIMARY KEY,
Email NVARCHAR(200) ENCRYPTED WITH (
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = MyKey
) NOT NULL,
SSN NCHAR(11) ENCRYPTED WITH (...) NOT NULL
);

Connection string enables it:

Server=...; Column Encryption Setting=enabled

8. Use TABLOCK for Bulk Inserts

When inserting millions of rows into an empty or staging table:

INSERT INTO StagingOrders WITH (TABLOCK)
SELECT * FROM SourceCsv;

Minimally-logged inserts run 5–10× faster than row-by-row. Requires the recovery model to be SIMPLE or BULK_LOGGED.

9. Tune Isolation Level

The default in EF Core is READ COMMITTED, which uses shared locks on reads. For high-read workloads, switch to READ COMMITTED SNAPSHOT:

ALTER DATABASE YourDb SET READ_COMMITTED_SNAPSHOT ON;

> 🚀 Now readers don't block writers (and vice versa). Most ASP.NET apps see throughput improvements of 30–50%, with no application code change. The cost is increased TempDB usage.

10. Compress Old Data

SQL Server's PAGE compression typically gets 50–70% space savings on text-heavy tables. For historical data:

ALTER TABLE OrderHistory REBUILD WITH (DATA_COMPRESSION = PAGE);

Smaller pages = more rows in memory = better cache hit rates.

11. Use Filtered Indexes for Hot Subsets

If 90% of queries filter on Status = 'Active', a filtered index is smaller and faster:

CREATE INDEX IX_Orders_ActiveOnly
ON Orders (CustomerId, CreatedAt)
INCLUDE (Total)
WHERE Status = 'Active';

12. Avoid SELECT * (And EF Core's Equivalent)

EF Core's default behavior loads all columns. For wide tables, that's wasted I/O. Use projection:

context.Customers
.Where(c => c.IsActive)
.Select(c => new { c.Id, c.Email, c.LastSeenAt })
.ToList();

Projection bypasses the change tracker and ships only the columns you need.

13. Profile EF Core Queries

EF Core 9 added richer query logging:

builder.Services.AddDbContext<AppDbContext>(opt =>
{
opt.UseSqlServer(connectionString);
opt.EnableSensitiveDataLogging(); // dev only
opt.LogTo(Console.WriteLine, LogLevel.Information);
});

> ⚠️ #1 cause of slow .NET pages: N+1 queries. In dev, watch the log for repetitive single-row SELECTs. Fix with .Include() for related entities or Select() projection.

14. Schedule Index Maintenance

Indexes fragment over time. Without maintenance, queries slow down imperceptibly until users complain. Set up an Agent job:

ALTER INDEX ALL ON YourTable REBUILD WITH (ONLINE = ON);

Or use Ola Hallengren's Maintenance Solution — the de-facto standard for SQL Server maintenance.

On Adaptive's hosting, we schedule maintenance during low-traffic windows automatically.

SQL Server Express: The Cost of Free

Most shared Windows hosts include "SQL Server Express." The limits hurt:

| Limit | Express | SQL Server 2022 (Adaptive) |

|---|---|---|

| Max DB size | 10 GB | Unlimited within plan storage |

| Max memory | 1.4 GB | Scales with plan |

| CPU utilization | Single core | All cores, parallel queries |

| Query Store | Not available | Enabled |

| Ledger tables | Not available | Yes |

| Always Encrypted | Limited | Full |

| SQL Agent | Not available | Yes |

| MariaDB alongside | Not available | Yes, included |

| Databases per plan | 1 | 2 / 5 / 10 by tier |

Every Adaptive plan includes real SQL Server 2022, not Express. You also get MariaDB databases alongside on every plan — useful if you're running a mix of .NET and PHP-based applications.

Frequently Asked Questions

Should I use EF Core or Dapper?

Both, often. EF Core for CRUD and complex graph navigation; Dapper for high-volume reporting queries where you want raw SQL with type-safe projection.

What's the right way to handle migrations in production?

Backward-compatible migrations only, run as a separate CI step (not on app startup), with tested down-migrations. Never deploy code that requires schema changes that haven't been applied yet.

When should I add application-level caching?

When reads exceed ~70% of total queries and the data tolerates short staleness windows. Wrap repeated SELECT calls in IMemoryCache or IDistributedCache with a 1–5 minute expiry. Reporting dashboards, navigation menus, configuration lookups, and product catalogs are obvious candidates.

Is SQL Server faster on Windows or Linux?

Marginally faster on Linux for raw query throughput on some workloads, but the operational tooling (SSMS, SQL Profiler, Agent, replication) is Windows-native. We deploy SQL Server 2022 on Windows Server 2022 for the management experience.

What about EF Core vs raw ADO.NET?

EF Core's overhead is real but small (1–3 ms per query) and worth it for 90% of CRUD code. For high-frequency hot paths, use Dapper or raw ADO.NET on critical queries.

title: Build on a SQL Server You Can Actually Tune
description: Real SQL Server 2022 (not Express). Query Store, ledger tables, Always Encrypted, MariaDB databases alongside — all on every plan. Starting at $9.49/mo.
cta-primary: Compare Hosting Plans | /asp-net-hosting-plans
cta-secondary: Talk to a Database Specialist | /contact