客户端应用程序中打开的 SqlConnections 与 SQL Server 中的进程之间有什么关系？
我只是尝试对 SQL Server 数据库中的表进行简单的架构更改（通过使用 SMSS 中的设计工具）。每当我尝试保存更改时，它总是超时。我想知道这是否是由于现有的连接“锁定”了表。
作为一个实验，我决定终止连接。我查询 master..sysprocesses 以获取该数据库的当前 spid，并一一杀死它们，直到我能够保存我的架构更改。 （不是很科学，但我远不是 SQL Server 专家）。果然，当我杀死所有 spid（除了我使用 SMSS 的那个）后，我能够保存架构更改。
我想问一下ADO.NET SqlConnections和spids之间的关系。例如，如果客户端应用程序在 SqlConnection 对象上调用 Open()，我是否应该在 master..sysprocesses 中看到另一个 spid？如果我在该 SqlConnection 上调用 Close() 会怎么样？ spid 应该消失吗？
我确信这不是那么简单，因为我知道存在连接池的概念，但是有人可以阐明这种关系是如何运作的吗？
谢谢
大卫
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.
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.
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?
Thank you
David
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果连接字符串中的 pooling=false
SqlConnection.Open()和Close()将与正在创建和销毁的 spid 完全相关。这会导致性能非常慢:)如果连接字符串中的 pooling=true
调用
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()等服务器终止。希望已经足够详细了，您还有什么想知道的吗？
If pooling=false in the connection string
SqlConnection.Open()andClose()will exactly correlate to spids being created and destroyed. This results in very slow performance :)If pooling=true in the connection string
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.Creating a new physical connection will create a new spid, which will show up as a new row in
sys.sysprocessesandsys.dm_exec_connections.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 forsp_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()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 askill @spidandSqlConnection.ClearAllPools().Hope that is enough detail, is there anything else you'd like to know?