SqlServerCache not emptying expired items
- Dominant language
- C#
- Stars
- 38.4k
- Forks
- 10.9k
- Avg merge
- 2d 10h
- Merged PRs (30d)
- 281
Description
### Describe the bug
I am seeing a scenario where the database table created for SqlServer-based DistributedCache is not being emptied properly. The number of items in the table seems to grow without bound.
### To Reproduce
We are using ASP.NET Core 3.1.1:
```xml
```
Unfortunately I don't know how we got into this situation as I observed it just now, and I don't have specific steps to reproduce.
We store items into the cache using `AbsoluteExpirationRelativeToNow` and there is no sliding expiration.
### Expected behavior
I expect the cache to be emptied consistently to avoid growing out of bounds.
### Screenshots
```sql
SELECT COUNT(*) Count, MIN(ExpiresAtTime) Oldest
FROM dbo.DistributedCache
WHERE GETDATE() > ExpiresAtTime;
```
returns
| Count | Oldest |
|-|-|
| 139962 | 2020-03-21 02:12:05.8058460 +00:00 |
### Additional context
I've looked at the code that does the delete: [src/Caching/SqlServer/src/SqlQueries.cs#L56](https://github.com/dotnet/extensions/blob/f4066026ca06984b07e90e61a6390ac38152ba93/src/Caching/SqlServer/src/SqlQueries.cs#L56). This seems a bit naive to me if there are many entries to be deleted, for some reason. If I run the code, our database seems to choke. I do see an index on `ExpiresAt` but it doesn't seem to help.
Contributor guide
Research direction
Start by inspecting src/Caching/SqlServer/src/SqlQueries.cs at line 56 and the expired-item deletion query. Use the reported DistributedCache table query to measure expired rows and investigate behavior with many entries; done means expired cache items are removed consistently without the database choking.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100