15

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!

Artur Udod
  • 4,465
  • 1
  • 29
  • 58
  • From what I remember, a `session` will show external connections and internal processes, and a `connection` will only show external connections. – Brian May 17 '13 at 16:01

1 Answers1

3

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.

Community
  • 1
  • 1
usr
  • 168,620
  • 35
  • 240
  • 369
  • Thank you! That was rather useful. Could you please answer the first 2 questions as well? I guess the answer is "Yes" for both? – Artur Udod May 18 '13 at 20:38
  • 2
    The `SqlConnection` really represents a session for SQL Server. The physical connection is managed by ADO.NET. Whenever you `Open` a `SqlConnection` you create a new session, not a new connection. So: 1 = yes, 2 = yes, 3 = no (but beware of `ISOLATION LEVEL`! - I consider this to be a bug). – usr May 18 '13 at 21:11
  • 3
    Note: `sp_reset_connection` is (unfortunately?) called when *acquiring* a connection from a pool, not *releasing* a connection. This can lead to 'odd session stuff' between the two actions, including Session sp_getapplocks not being released :} – user2864740 Jan 26 '19 at 23:51