The Definitive Guide to SQL Connection Strings and App Performance

From defaults to tuned: practical patterns for SqlClient + EF Core + Dapper + SqlKata + GraphQL across APIs, Functions, Blazor, and Mobile

Written by Kraai Du Toit

1) When and where the options matter

• Per‑unit‑of‑work connections. APIs, Functions, background jobs—pooling turns most Open() calls into cheap checkouts.
• Bursty scale‑out. Azure Functions & autoscaled APIs benefit from a sensible Max Pool Size so one instance cannot overwhelm SQL.
• Cloud networks. Transient errors are normal; SqlClient retry logic cleans up the long tail without cluttering query code.

2) From default → enhanced (what changes and why)

3) App‑type playbook (API, Functions, Blazor, Mobile)

// Program.cs
builder.Services.AddDbContextPool<AppDb>(o => o.UseSqlServer(cs, sql => sql.EnableRetryOnFailure()));

// Startup once
SqlConnection.RetryLogicProvider = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
    new SqlRetryLogicOption { NumberOfTries=4, DeltaTime=TimeSpan.FromSeconds(2), MaxTimeInterval=TimeSpan.FromSeconds(10) }
);

4) Per‑tech guidance & code

4.1 Entity Framework Core

• Use AddDbContextPool (context pooling) plus ADO.NET pooling.
• Enable EF’s execution strategy (EnableRetryOnFailure) in addition to SqlClient retry.
• Prefer compiled queries for hot paths; project only needed columns.

// Program.cs
builder.Services.AddDbContextPool<AppDb>(o =>
    o.UseSqlServer(cs, sql => sql.EnableRetryOnFailure())
     .EnableSensitiveDataLogging(false));

// Compiled query example
public static readonly Func<AppDb,int,Task<Car?>> GetCarById =
    EF.CompileAsyncQuery((AppDb db, int id) =>
        db.Cars.AsNoTracking().Where(c => c.Id == id).FirstOrDefault());

4.2 Dapper (micro‑ORM)

• Open/close per request; keep statements parameterized.
• Use ExecuteAsync/QueryAsync with explicit commandTimeout.

await using var conn = new SqlConnection(cs);
await conn.OpenAsync(ct);
var rows = await conn.QueryAsync<CarDto>(
    "select Id, Make, Model from dbo.Cars where Status=@s",
    new { s = "Active" }, commandTimeout: 60);

4.3 SqlKata (query builder)

• Compose queries, compile to SQL+parameters, then execute with Dapper/ADO.
• Great for dynamic filters and conditional predicates.

var compiler = new SqlServerCompiler();
var q = new Query("Cars").Where("Status","Active").Select("Id","Make","Model");
var sql = compiler.Compile(q);
var cars = await conn.QueryAsync<CarDto>(sql.Sql, sql.NamedBindings, commandTimeout: 60);

4.4 GraphQL

• Use a DataLoader to collapse N requests into a single IN (@ids) query.
• Keep resolvers slim: build the SQL in a service, not inside the field resolver.

// Resolver (pseudo)
public Task<IReadOnlyList<Car>> GetCarsByIdsAsync(IReadOnlyList<int> ids) =>
    _dataLoader.LoadAsync(ids);

5) Connection‑string recipes

Managed Identity (recommended in Azure)

Server=tcp:<server>.database.windows.net,1433;
Database=<db>;
Authentication=Active Directory Managed Identity;
Encrypt=True;TrustServerCertificate=False;
Application Name=<app-name>;
MultipleActiveResultSets=True;
Pooling=True;Min Pool Size=10;Max Pool Size=100;
ConnectRetryCount=3;ConnectRetryInterval=5;
Command Timeout=300;

Developer workstation (AAD Default)

Server=tcp:<server>.database.windows.net,1433;
Database=<db>;
Authentication=Active Directory Default;
Encrypt=True;TrustServerCertificate=False;
Application Name=<app-name>;
Pooling=True;Min Pool Size=0;Max Pool Size=100;
Connect Timeout=30;Command Timeout=300;

Read‑only reporting pool (AG secondary)

Server=tcp:<listener>,1433;
Database=<db>;
Authentication=Active Directory Managed Identity;
Encrypt=True;TrustServerCertificate=False;
Application Name=<app-name>;
Application Intent=ReadOnly;MultiSubnetFailover=True;
Pooling=True;Min Pool Size=5;Max Pool Size=80;
Connect Timeout=15;Command Timeout=120;

6) Practical insights from the field (extractions)

• Use Microsoft.Data.SqlClient over System.Data.SqlClient. New features and better .NET Core support land here first. (Waqas Kamal)
• Explicit pooling knobs matter under load. Consider Persist Security Info=false, Connection Timeout=30, Pooling=true, and Min/Max Pool Size sized to your traffic; watch for pool fragmentation if max is reached. (Waqas Kamal)
• Conceptual model for pooling: connections cycle between active and idle in the pool; when the pool is full, new requests queue. (Stack Overflow blog)
• Why connection strings matter: the right options make apps faster, more secure, and easier to diagnose; set Application Name for observability. (Aireforge)
• High‑throughput ADO.NET tips: prefer async I/O, batch writes, stream with SqlDataReader, and use SqlBulkCopy for large loads. (Reintech)

7) Troubleshooting quick hits

8) Two helpful paraphrases to include in your docs

9) References (primary & supporting)

• SqlConnection.ConnectionString (reference): MS Docs
• ADO.NET SQL Server connection pooling: MS Docs
• ADO.NET connection pooling overview: MS Docs
• Manage connections in Azure Functions: MS Docs
• SqlClient configurable retry logic: MS Docs · Config file
• “Configurations that significantly improves your app performance…” (Medium): medium.com/@dewanwaqas
• “Improve database performance with connection pooling” (Stack Overflow blog): stackoverflow.blog
• “Why You Should Love Your Connection Strings” (Aireforge): aireforge.com
• Optimizing ADO.NET performance (Reintech): reintech.io

10) The mental model: what really happens on Open()

A SqlConnection session negotiates TLS/TDS, authenticates, and allocates server resources. ADO.NET keeps a per-connection-string pool of idle sessions so most Open() calls are O(1) checkouts. Close/Dispose returns to the pool. Open late, close early.

• Pool key is the exact string. Even whitespace or reordered keys create a new pool.
• Max Pool Size bounds concurrent sessions per process; opening beyond that queues.
• Azure Functions scale-out multiplies pools; size caps and reuse to avoid storms.

11) Defaults → Enhanced: concrete improvements

12) Resilience: SqlClient configurable retry

using Microsoft.Data.SqlClient;
SqlConnection.RetryLogicProvider =
  SqlConfigurableRetryFactory.CreateExponentialRetryProvider(
    new SqlRetryLogicOption { NumberOfTries = 4, DeltaTime = TimeSpan.FromSeconds(2), MaxTimeInterval = TimeSpan.FromSeconds(10) });

13) Copy-paste connection strings (ready to ship)

Managed Identity (Azure)

Server=tcp:<server>.database.windows.net,1433;
Database=<db>;
Authentication=Active Directory Managed Identity;
Encrypt=True;TrustServerCertificate=False;
Application Name=<app>;
Pooling=True;Min Pool Size=10;Max Pool Size=100;
ConnectRetryCount=3;ConnectRetryInterval=5;
Command Timeout=300;

Dev (AAD Default)

Server=tcp:<server>.database.windows.net,1433;
Database=<db>;
Authentication=Active Directory Default;
Encrypt=True;TrustServerCertificate=False;
Application Name=<app>;
Pooling=True;Min=0;Max=100;Connect Timeout=30;Command Timeout=300;

SQL Login (fallback)

Server=tcp:<server>.database.windows.net,1433;Database=<db>;
User ID=<user>;Password=<secret>;Encrypt=True;TrustServerCertificate=False;
Application Name=<app>;Pooling=True;Min Pool Size=10;Max Pool Size=100;
ConnectRetryCount=3;ConnectRetryInterval=5;Command Timeout=300;

14) Azure Functions: patterns that survive scale

• Open/Close per invocation; no global shared SqlConnection.
• Set Command Timeout higher for ETL/Timer jobs.
• Consider NAT Gateway if bursts cause sporadic connect failures (SNAT exhaustion).

15) API & Blazor & Mobile

ASP.NET Core API

• Scoped context/connection per request; AddDbContextPool for EF; set Application Name.

Blazor Server

Per-operation context; never share DbContext across concurrent circuit calls.

Blazor WASM & Mobile

Do not connect directly to SQL; use an API. Optionally add offline with SQLite + sync.

16) EF Core vs Dapper vs SqlKata

EF Core

Context pooling + compiled queries; AsNoTracking() for reads; EF EnableRetryOnFailure.

Dapper

Parameterize; explicit commandTimeout; use SqlBulkCopy for big inserts.

SqlKata

Compose → compile to SQL+parameters → execute with Dapper.

17) GraphQL without N+1

Batch with DataLoader and run a single IN (@ids) query. Consider a read-only pool for list queries.

18) Observability: App Insights + DMVs

Set Application Name. Track Open() and command durations; correlate with DMVs.

SELECT program_name, COUNT(*) AS sessions FROM sys.dm_exec_sessions WHERE is_user_process=1 GROUP BY program_name;
SELECT session_id, command, wait_type, cpu_time, reads FROM sys.dm_exec_requests WHERE session_id <> @@SPID;

19) Advanced pool tuning

Prevent fragmentation

Canonicalize strings; differences create new pools.

Sizing math

concurrency ≈ RPS_instance × DB_ops_per_request × p95_DB_seconds

Load Balance Timeout

Retire elderly connections on return; start 300–600s if needed.

Read/Write split pools

...; Application Intent=ReadWrite; ...

...; Application Intent=ReadOnly; MultiSubnetFailover=True; ...

MARS

Default off; enable only for overlapping readers on one connection.

20) Deep knobs & Azure networking

TransparentNetworkIPResolution

Races multiple IPs; measure Open() p95.

Packet Size

8192 is fine for most; only change for giant LOB/bulk with measurement.

SNAT & NAT Gateway

Pooling minimizes new sockets; NAT Gateway provides more ephemeral ports & stable egress.

21) Throughput that moves the needle

SqlBulkCopy

using var bulk = new SqlBulkCopy(conn) { DestinationTableName = "dbo.Staging" };

TVPs

var tvp = new DataTable(); /* add rows */

Multi-row INSERT (Dapper)

// INSERT ... VALUES (...),(...),(...) with parameters

22) Timeouts

• Connect: 15–30s (fix DNS/TLS if slow).
• Command: APIs 30–60s; batch/reporting up to 300s.

23) Production checklists

APIs: scoped connection per request; Functions: per-invocation open/close; Blazor/Mobile: no direct SQL.

24) Golden strings (by style)

EF Core (API)

...; Authentication=Active Directory Managed Identity; Pooling=True; Min Pool Size=10; Max Pool Size=100; Connect Timeout=30; Command Timeout=60; Application Name=My.Api;

Dapper (microservices)

...; Pooling=True; Min Pool Size=10; Max Pool Size=80; ConnectRetryCount=3; ConnectRetryInterval=5; Command Timeout=60; Application Name=My.Dapper.Api;

GraphQL (read-heavy)

...; Application Intent=ReadOnly; MultiSubnetFailover=True; Pooling=True; Min=5; Max=80; Connect Timeout=15; Command Timeout=60; Application Name=My.GraphQL;

25) Anti-patterns

• Global shared SqlConnection
• Fragmented strings
• TrustServerCertificate=True in prod
• MARS as a band-aid
• No retry in cloud

26) What you should observe

• Open() p95 → single-digit ms
• Stable sessions near but under Max Pool Size
• Reduced intermittent timeouts/SNAT issues

27) References (consolidated)

• Microsoft Docs (SqlConnection, pooling, Functions, SqlClient retry)
• Stack Overflow blog
• Aireforge
• Waqas Kamal
• Reintech