In our testing environment, opening a SqlConnection takes around 5 seconds. I would like to investigate why it is taking so long, but I am not sure where to look at. Until the connection is open there are no entries in the SqlProfiler. The sql server (ver 2012) is running on a separate machine and we are connecting to it from another computer in the same network. We see the 5 second freeze in console applications as well as from the IIS (ASP.NET). The communication itself is fast.
在我们的测试环境中，打开SqlConnection大约需要5秒。我想调查一下为什么要花这么长时间，但我不知道该看哪儿。在连接打开之前，SqlProfiler中没有条目。sql server (ver2012)运行在一台单独的机器上，我们从同一网络中的另一台计算机连接到它。在控制台应用程序和IIS (ASP.NET)中，我们看到了5秒钟的冻结。交流本身很快。
Here's a screenshot from dotTrace:
这是dotTrace的截图:
What can I do to investigate what exactly is taking those 5 seconds? Thanks.
我能做什么来调查这5秒到底花了什么?谢谢。
Update: Thanks everyone for great comments. It was the authentication - once we switched from integrated to sql authentication, the slowdowns were gone.
更新:谢谢大家的精彩评论。这是身份验证——一旦我们从集成身份验证切换到sql身份验证，这种放缓就消失了。
1 个解决方案
#1
3
Here go the usual best bets:
下面是通常的最佳选择:
- Change the connection string from SSPI to SQL Authentication. If SQL authentication is faster > focus on authentication providers
- 将连接字符串从SSPI更改为SQL身份验证。如果SQL身份验证加快了>对身份验证提供者的关注。
- Change the connectivity from tcp/ip to named pipes. If named pipes are faster > focus on networking stack
- 将连接从tcp/ip更改为指定的管道。如果命名管道更快，>将重点放在网络堆栈上
- Change connection string from using host name to fully qualified host name. If faster > fix DNS/WINS properties. See also Initial connection to SQL Server Connection Is Slow. Why?.
- 将连接字符串从使用主机名更改为完全限定的主机名。如果更快的>固定DNS/赢得属性。还请参见到SQL Server的初始连接是缓慢的。为什么?。
- Change connection string from using host name to IP address. If faster, fix DNS lookup. See also https://social.msdn.microsoft.com/Forums/en-US/807748eb-51c8-4702-b7e9-fa32b7028747/sqlconnectionopen-takes-up-to-5-seconds-first-time-its-called-why-is-it-so-slow-using-net-35?forum=sqldataaccess
- 更改连接字符串从使用主机名到IP地址。如果更快，请修复DNS查询。参见https://social.msdn.microsoft.com/forums/en us/807748eb - 51 - c8 - 4702 - b7e9 fa32b7028747/sqlconnectionopen - - - 5秒——第一次————————————这是为什么——慢——使用——净- 35 ?forum=sqldataaccess
#1
3
Here go the usual best bets:
下面是通常的最佳选择:
- Change the connection string from SSPI to SQL Authentication. If SQL authentication is faster > focus on authentication providers
- 将连接字符串从SSPI更改为SQL身份验证。如果SQL身份验证加快了>对身份验证提供者的关注。
- Change the connectivity from tcp/ip to named pipes. If named pipes are faster > focus on networking stack
- 将连接从tcp/ip更改为指定的管道。如果命名管道更快，>将重点放在网络堆栈上
- Change connection string from using host name to fully qualified host name. If faster > fix DNS/WINS properties. See also Initial connection to SQL Server Connection Is Slow. Why?.
- 将连接字符串从使用主机名更改为完全限定的主机名。如果更快的>固定DNS/赢得属性。还请参见到SQL Server的初始连接是缓慢的。为什么?。
- Change connection string from using host name to IP address. If faster, fix DNS lookup. See also https://social.msdn.microsoft.com/Forums/en-US/807748eb-51c8-4702-b7e9-fa32b7028747/sqlconnectionopen-takes-up-to-5-seconds-first-time-its-called-why-is-it-so-slow-using-net-35?forum=sqldataaccess
- 更改连接字符串从使用主机名到IP地址。如果更快，请修复DNS查询。参见https://social.msdn.microsoft.com/forums/en us/807748eb - 51 - c8 - 4702 - b7e9 fa32b7028747/sqlconnectionopen - - - 5秒——第一次————————————这是为什么——慢——使用——净- 35 ?forum=sqldataaccess