SQL 连接池和审计登录/注销 [英] SQL Connection Pooling and Audit Login/Logout

问题描述

当我使用 SQL Server Profiler 分析我的应用程序时，我看到很多关于连接到同一数据库的Audit Login 和Audit Logout 消息.我想知道，这是否表明我的连接池有问题?我问的原因是因为我在 MSDN 文档中找到了有关连接池的内容:

When I profile my application using SQL Server Profiler, I am seeing lots of Audit Login and Audit Logout messages for connections to the same database. I am wondering, does this indicate that something is wrong with my connection pooling? The reason I ask, is because I found this in the MSDN documentation in regards to connection pooling:

登录和注销事件将不会连接时在服务器上引发从或返回到连接池.这是因为当连接实际上没有关闭时它返回到连接池.有关更多信息，请参阅审核登录事件类和审计注销事件SQL Server 联机丛书中的课程.

Login and logout events will not be raised on the server when a connection is fetched from or returned to the connection pool. This is because the connection is not actually closed when it is returned to the connection pool. For more information, see Audit Login Event Class and Audit Logout Event Class in SQL Server Books Online.

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

此外，有没有人有任何提示可以确定连接池对给定 SQL 服务器的有效性?我在一台服务器上有很多数据库，我知道这会产生巨大的影响，但我想知道是否有一种简单的方法来获取有关连接池有效性的指标.提前致谢！

Also, does anyone have any tips for determining how effective the connection pooling is for a given SQL server? I have lots of databases on a single server and I know this can have a huge impact, but I am wondering if there is an easy way to obtain metrics on the effectiveness of my connection pooling. Thanks in advance!

推荐答案

请记住，每个连接字符串都会对连接进行池化.如果您有许多数据库并使用许多连接字符串进行连接，则当不存在具有正确连接字符串的连接时，您的应用程序将创建一个新连接.然后它将池化该连接，如果池已满，则碰撞现有连接.默认最大池大小为 100连接，因此，如果您经常在 100 多个数据库之间跳来跳去，您将一直关闭和打开连接.

Remember that connections are pooled per connectionstring. If you have many databases and connect using many connectionstrings, your app will create a new connection when none exist with the correct connectionstring. Then it will pool that connection and, if the pool is full, bump an existing connection. The default Max Pool Size is 100 connections, so if you're routinely bouncing through more than 100 databases, you'll close and open connections all the time.

这并不理想，但是您可以通过始终连接到单个数据库(一个连接字符串)然后切换数据库上下文USE [DBName]"来解决问题.有缺点:

It's not ideal, but you can solve the problem by always connecting to a single database (one connection string) and then switch db context 'USE [DBName]'. There are drawbacks:

• 您无法为每个连接字符串指定用户/密码(您的应用用户需要对所有数据库的权限).
• 您的 SQL 变得更加复杂(尤其是当您使用开箱即用的 ORM 或存储过程时).

如果您的数据库数量不是很大，您可以尝试增加最大池大小.否则，如果某些数据库经常使用而其他数据库不常用，您可以在不常用的数据库上关闭池.这两个项目都是通过 connectionstring 配置的.

You could experiment with increasing the Max Pool Size if your database count isn't huge. Otherwise, if some databases are used frequently while others aren't, you could turn pooling off on the infrequent dbs. Both items are configured via connectionstring.

就指标而言，监控 SQL Server 上的登录和注销事件是一个好的开始.如果您的应用能够很好地汇集在一起​​，那么您应该不会看到很多.

As far as metrics, monitoring the login and logout events on SQL Server is a good start. If your app is pooling nicely you shouldn't see a lot of them.

这篇关于SQL 连接池和审计登录/注销的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！