SqlConnection vs Sql Session. Do their lifetimes coincide?

Question

I want to apply some sql-session level settings for certain processes in my c# app.

For instance, I want to set DEADLOCK_PRIORITY for some background processes to LOW.

The questions are:

1. If I open a new sql connection, does that start a new sql-session?

2. Will the sql-session live until the connection is closed? If I apply my settings right after the SqlConnection is opened, will they be valid for all queries executed in context of that same SqlConnection?

3. What about connection pooling? Is this possible that my SET DEADLOCK_PRIORITY LOW setting will be reused by other processes in my system (which I don't want to) because the SqlConnection is not actually closed ( asp.net connection pooling decides to reuse it).

Thank you!

Answer 1

ADO.NET executes sp_reset_connection when you take a SqlConnection from the pool (after having closed it so that it gets returned to the pool). According to What does "exec sp_reset_connection" mean in Sql Server Profiler? all SET options are being reset. That would include DEADLOCK_PRIORITY.

I would still suggest that you write a tiny test program to confirm this. ADO.NET session pooling is not perfect, for example it does not reset the ISOLATION LEVEL and does not rollback transactions when closing.