客户端应用程序中的打开SqlConnections与SQL Server中的进程之间是什么关系？ [英] What is the relationship between open SqlConnections in the client app and processes in SQL Server?

问题描述

我只是尝试对SQL Server数据库中的表进行简单的架构更改（通过使用SMSS中的设计工具）。每当我尝试保存更改时，它都会一直超时。我想知道这是否是由于现有的连接锁定了桌子造成的。

I just tried to make a simple schema change to a table in a SQL Server database (by using the Design tool in SMSS). Whenever I tried to save the change, it kept timing out. I wondered whether this was due to existing connnections which were 'locking' the table.

我决定通过实验取消连接。我查询master..sysprocesses以获取该数据库的当前spid，并一一杀死它们，直到能够保存架构更改。 （不是很科学，但是我离SQL Server专家还很远）。果然，当我杀死了所有的间谍（除了使用SMSS的间谍），我就能够保存架构更改。

I decided to kill connections as an experiment. I queried master..sysprocesses to get the current spids for that database, and killed them one by one until I was able to save my schema change. (Not very scientific, but I'm far from an expert with SQL Server). Sure enough, when I had killed all the spids (bar the one which was me using SMSS), I was able to save the schema change.

我想问一下关于ADO.NET SqlConnections和spid之间的关系。例如，如果客户端应用程序在SqlConnection对象上调用Open（），我是否应该在master..sysprocesses中看到另一个spid？如果我在该SqlConnection上调用Close（）怎么办？该spid是否应该消失？

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

我敢肯定它不是那么简单，因为我了解连接池的概念，但是有人可以阐明这一点吗？

I'm sure it's not quite that simple, since I understand there is a notion of connection pooling, but can someone shed any light on how this relationship works?

谢谢

David

推荐答案

如果连接字符串中的pooling = false

SqlConnection.Open （）和 Close（）会与创建和销毁的spid完全相关。这会导致性能非常慢：）

SqlConnection.Open() and Close() will exactly correlate to spids being created and destroyed. This results in very slow performance :)

如果连接字符串中pooling = true

调用 SqlConnection.Open（）将使用池中的现有物理连接，或者如果池中没有可用的物理连接，则创建一个新的物理连接。

Calling SqlConnection.Open() will either use an existing physical connection from the pool, or create a new one if none are available in the pool.

创建新的物理连接将创建一个新的spid，它将在 sys.sysprocesses 和 sys.dm_exec_connections 。

Creating a new physical connection will create a new spid, which will show up as a new row in sys.sysprocesses and sys.dm_exec_connections.

重用现有的池物理连接只会重用现有的spid，所以您 SqlConnection.Open（）不会服务器端这些表中的任何可见更改。但是，可以使用SQL Profiler或XEvent通过查找 sp_reset_connection 来检测到它，这是SqlClient调用的存储过程，它告诉服务器清除连接状态（例如，确保

Reusing an existing pooled physical connection will just reuse an existing spid so you SqlConnection.Open() will not make any visible change in those tables on the server side. However it can be detected by using SQL Profiler or XEvent by looking for sp_reset_connection, which is a stored procedure called by SqlClient that tells the server to clear the connection state (e.g. make sure there is no transaction, etc).

SqlConnection.Close（）通常将物理连接返回到池中。

SqlConnection.Close() will usually return the physical connection to the pool, so it will not disappear from the server. Physical connections are actually closed in various ways under the hood, such as by being killed by the server such as kill @spid and SqlConnection.ClearAllPools().

希望有足够的细节，还有什么您想知道的吗？

Hope that is enough detail, is there anything else you'd like to know?

这篇关于客户端应用程序中的打开SqlConnections与SQL Server中的进程之间是什么关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！