With AddDistributedSqlServerCache in ASP.NET Core, how do you create the cache table, and how are expired rows hidden from reads and cleaned up?
answer
- dotnet sql-cache create or script
- Id nvarchar(449), Value varbinary(MAX)
- reads filter on ExpiresAtTime
- ExpiredItemsDeletionInterval 30 min, minimum 5
- DefaultSlidingExpiration 20 minutes
basics
~20 sCreate the table with the dotnet sql-cache tool, then set ConnectionString, SchemaName and TableName. Reads filter on ExpiresAtTime, so expired rows are never returned; a delete of expired rows runs at most every ExpiredItemsDeletionInterval, 30 minutes by default.
solid answer
~40 s`AddDistributedSqlServerCache` (package `Microsoft.Extensions.Caching.SqlServer`) needs an existing table and `ConnectionString`, `SchemaName` and `TableName` in `SqlServerCacheOptions`; the constructor throws if any is empty. The `dotnet-sql-cache` tool creates it: `dotnet sql-cache create "<connection string>" dbo BasketCache`, or `dotnet sql-cache script dbo BasketCache -o cache.sql --idempotent` to put the SQL in a migration. The table holds `Id`, `Value`, `ExpiresAtTime`, `SlidingExpirationInSeconds` and `AbsoluteExpiration`, indexed on `ExpiresAtTime`. Every read filters `@UtcNow <= ExpiresAtTime`, so an expired row is never returned, and a sliding read first updates `ExpiresAtTime`. Physical cleanup is lazy: any cache operation checks whether `ExpiredItemsDeletionInterval` (default 30 minutes, minimum 5, smaller throws `ArgumentException`) has passed and then runs a background `DELETE` of expired rows. Entries written without options get the 20-minute `DefaultSlidingExpiration`.
code
bash · 2 linesdotnet tool install --global dotnet-sql-cache
dotnet sql-cache script dbo BasketCache --output BasketCache.sql --idempotentgo deeper
Recall that the SQL Server store needs a table created with the dotnet sql-cache tool and a connection string, schema and table name.
Explain the table's columns, that reads filter on ExpiresAtTime, and the 20-minute DefaultSlidingExpiration and 30-minute deletion interval defaults.
Explain lazy cleanup driven by cache traffic, the five-minute minimum, per-instance deletes, and keeping cache churn away from transactional tables.
Weigh reusing an existing SQL Server against adding Redis: operational simplicity versus database load, latency and cleanup behaviour.
## When the SQL Server store fits `AddDistributedSqlServerCache` backs `IDistributedCache` with a table in SQL Server. A fashion retailer whose regional storefront already runs on SQL Server, and has no Redis in that region, can share basket snapshots across its instances this way, at the cost of database round trips and write load on the same server as its orders. ## Creating the table The cache does not create its table. The `dotnet-sql-cache` package is a .NET tool with two commands: 1. **`dotnet sql-cache create <connectionString> <schemaName> <tableName>`** connects and creates the table and its index. 2. **`dotnet sql-cache script <schemaName> <tableName>`** prints the SQL instead; `-o|--output` writes it to a file and `-i|--idempotent` produces a script that can run against a database that already has the table, which suits a deployment pipeline. The created table: | Column | Type | Holds | |---|---|---| | `Id` | `nvarchar(449)`, case-sensitive collation, primary key | the cache key | | `Value` | `varbinary(MAX)` | the value bytes | | `ExpiresAtTime` | `datetimeoffset` | the effective expiry, after any sliding | | `SlidingExpirationInSeconds` | `bigint NULL` | the sliding window | | `AbsoluteExpiration` | `datetimeoffset NULL` | the absolute deadline | A nonclustered index `Index_ExpiresAtTime` supports the cleanup query. Keys are compared case-sensitively, so `Basket:42` and `basket:42` are different rows. ## Registering it ```csharp builder.Services.AddDistributedSqlServerCache(o => { o.ConnectionString = builder.Configuration.GetConnectionString("cacheDb"); o.SchemaName = "dbo"; o.TableName = "BasketCache"; o.ExpiredItemsDeletionInterval = TimeSpan.FromMinutes(10); }); ``` `SqlServerCacheOptions` holds: - **`ConnectionString`, `SchemaName`, `TableName`**: required; the `SqlServerCache` constructor throws when any is null or empty. - **`DefaultSlidingExpiration`**: 20 minutes; applied when a write carries no expiration at all. - **`ExpiredItemsDeletionInterval`**: `null` by default, meaning 30 minutes; a value below 5 minutes throws `ArgumentException`. ## Hiding expired rows Expiry is enforced in the queries, not by a job: - **Reads** select `Value` only `WHERE Id = @Id AND @UtcNow <= ExpiresAtTime`. A row past its time is invisible even if it is still in the table. - **Sliding reads** first run an `UPDATE` that moves `ExpiresAtTime` forward by the window, or to `AbsoluteExpiration` when less time than the window remains, and only for rows that have not yet expired. - **Writes** compute `ExpiresAtTime` from the sliding window when there is one, otherwise from the absolute expiration, then `UPDATE` the row or `INSERT` it. ## Cleaning up Nothing deletes rows on a timer. Every cache operation calls a check: if more than `ExpiredItemsDeletionInterval` has passed since the last cleanup, it starts `DELETE FROM <table> WHERE @UtcNow > ExpiresAtTime` with `Task.Run` and records the time. Consequences: 1. **An idle app never cleans up**, so abandoned baskets stay in the table until traffic returns. 2. **Each instance runs its own cleanup**; four storefront instances may each issue the delete within one interval. 3. **The delete competes with order traffic** if the cache table shares a database with it; a dedicated database or schema keeps cache churn away from transactional tables. ## Trade-offs against Redis | | SQL Server store | Redis store | |---|---|---| | Expiry | query filter plus lazy bulk delete | server-side TTL per key | | No options given | 20-minute sliding default | no TTL | | Setup | table via `dotnet sql-cache` | none beyond a server | | Read cost | `UPDATE` + `SELECT` with sliding | hash read + `EXPIRE` with sliding | It works, and it adds no new infrastructure, but each cache read becomes database work. ## Mistakes to avoid - **Forgetting the table in a new environment.** Without it every cache call fails with a SQL error; generate the script with `--idempotent` and run it in the deployment pipeline. - **Setting an aggressive cleanup interval.** Anything under five minutes throws when the cache is constructed, and short intervals multiply the delete across instances anyway. - **Assuming case-insensitive keys.** The `Id` column uses a case-sensitive collation, so key construction must be consistent. - **Sharing the orders database without thought.** Cache writes and bulk deletes add log and lock activity to the same server that takes orders. For the retailer, the SQL Server store is a reasonable bridge until Redis exists in that region, not a long-term replacement for it.
- An app sets ExpiredItemsDeletionInterval to two minutes to keep the table small; what happens?The `SqlServerCache` constructor throws `ArgumentException`, because the interval may not be less than five minutes. The singleton is constructed when it is first resolved, so the failure appears then, typically on the first request that needs the cache, not when `AddDistributedSqlServerCache` runs.
- Why can a row that expired an hour ago still be in the table, and does that risk serving stale data?Cleanup only runs when a cache operation finds the deletion interval has passed, so an idle period leaves expired rows behind. They are never served: every read filters on `@UtcNow <= ExpiresAtTime`. The cost is table size, not correctness.
- AddDistributedSqlServerCache creates its cache table automatically on first use
- A SQL Agent job deletes expired rows at the exact moment they expire
- Expired rows are returned until the cleanup delete has removed them
- ExpiredItemsDeletionInterval can be set to one minute for a tidier table
- Rows written without options never expire in the SQL Server store