Schema change limits aspnetcore session state in SQL

[PhilM-IT]

Summary

The schema generated by "Microsoft.Extensions.Caching.SqlConfig.Tools" for session state storage in SQL was adjusted to use a datetype incompatible with SQL memory-optimized tables. This significantly reduces performance potential using this solution compared to traditional .net framework.

Motivation and goals

the datetimeoffset data type is used in the ASPStateTempSessionsV2 table. This datatype is essentially datetime2 with timezone support.

Options:

1. Create a flag that allows for the use of schema and code that doesn't use datetimeoffset and replaces this with a supported equivalent type (https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/supported-data-types-for-in-memory-oltp?view=sql-server-ver16).

2. change entirely to supported datatime datatype. I can't be certain if the loss of timezone information will impact existing functionality.

3. Move Timezone or timezoneoffset value to a new column supported within memory optimized tables and handle any adjustments in code using multiple columns.

In scope

1. adjust SQL schema to support using memory-optimized tables again.

Out of scope

Forcing changes to the supported datatypes in SQL server for memory optimized tables (even SQL 2022 this isn't supported).

Risks / unknowns

lack of native timezone support?? unsure here.

Examples

Suggestions above. Not sure what else I can provide for examples.

Detailed design

I'm not sure I have enough detail/background as to why the schema was changed to begin with.
-->

[Pilchie]

@ajcvickers - any thoughts here? It doesn't look like this has changed since the code was added to the repo in 2016, so I'm not terribly inclined to change it.

[PhilM-IT]

@ajcvickers - any thoughts here? It doesn't look like this has changed since the code was added to the repo in 2016, so I'm not terribly inclined to change it.

We've manually adjusted the schema of the table to use datetime and the code seems to be working at the moment but we're not through testing. SQL will convert the values through an implicit type conversion. Just not sure if the session state provider actually leverages the timezone component of the datetimeoffset value. If not this should be a valid workaround/change.

It would help to clarify if the use of the timezone from this datatype is used by the code. If not this should be a harmless change. Again, not sure what drove the change from the previous schema that used datetime.

[ajcvickers]

@Pilchie I have very little context on this; it wasn't something my team was involved in. I do remember discussions on the ASP.NET team maybe four years ago that it was what it was and wasn't going to be moved forward in any way.

[sebastienros]

@PhilM-IT I would recommend you keep your current workaround which seems fine to me. The date time is stored and read as UTC so the offset part won't have any effect.

Duplicate of
#28378
#17640
dotnet/extensions#1894

For our own reference:

• the tool queries are here: https://github.com/dotnet/aspnetcore/blob/main/src/Tools/dotnet-sql-cache/src/SqlQueries.cs#L19
• the provider ones are here: https://github.com/dotnet/aspnetcore/blob/main/src/Caching/SqlServer/src/SqlQueries.cs#L30

Thanks for contacting us.

We're moving this issue to the .NET 8 Planning milestone for future evaluation / consideration. We would like to keep this around to collect more feedback, which can help us with prioritizing this work. We will re-evaluate this issue, during our next planning meeting(s).
If we later determine, that the issue has no community involvement, or it's very rare and low-impact issue, we will close it - so that the team can focus on more important and high impact issues.
To learn more about what to expect next and how this issue will be handled you can read more about our triage process here.

[adityamandaleeka]

Closing since we don't plan to work on this.