All geek questions in one place

What is the relationship between open SqlConnections in a client application and processes on SQL Server?

I just tried to make a simple schema change to a table in a SQL Server database (using the Design tool in SMSS). Whenever I tried to save the changes, it went astray all the time. I wondered if this was due to existing joins that were “locking” the table.

I decided to kill the connection as an experiment. I requested master..sysprocesses to get the current spids for this database, and killed them one by one until I could save the schema change. (Not very scientific, but I'm far from an expert with SQL Server). Of course, when I killed all the spies (launched the one that used me using SMSS), I managed to save the change in the scheme.

I would like to ask about the relationship between ADO.NET SqlConnections and spids. For example, if a client application calls Open () in a SqlConnection object, should I see another spid in master..sysprocesses? What if I call Close () on this SqlConnection? Should the recession disappear?

I'm sure this is not so simple, as I understand that there is a concept of pooling, but can anyone shed some light on how this relationship works?

thank

David

+3
source share
1 answer

If pool = false in the connection string

SqlConnection.Open()and Close()will accurately correlate with the creation and destruction of spids. This leads to very poor performance :)

If pool = true in the connection string

SqlConnection.Open() , , .

spid, sys.sysprocesses sys.dm_exec_connections.

spid, SqlConnection.Open() . SQL Profiler XEvent, sp_reset_connection, , SqlClient, (, , ..).

SqlConnection.Close() , . , , , kill @spid SqlConnection.ClearAllPools().

, , - , ?

+1

Source: https://habr.com/ru/post/1759505/

More articles:


All Articles