Sql-server – SQL Profiler Audit Logout duration 18446744073709548

SQL Server allows a maximum of 32767 connections by default. It can be altered using sp_configure. To view your current configuration for this setting, use the following query:

select * from sys.configurations
where name ='user connections'

By default, you should see a maximum of 32767, value_in_use equal to 0 (use the default settings). If this has been altered, you can reconfigure SQL Server to use other values as described in the link.

You should also review how many connections are actually being made, as there might be more activity outside your application (or your application is making more connections than you think). You will want to look at General Statistics -> Logical Connections in either perfmon or query the values in sys.dm_os_performance_counters (cntr_value will show the current point in time value):

select * from sys.dm_os_performance_counters
where counter_name ='User Connections'

Finally got some time to write a more detailed answer.

There are typically three main reasons a simple procedure like sp_reset_connection will take a long time to run.

1. You are waiting for CPU resources
2. You are blocked on a lock somewhere (perhaps as a result of DML or a competing transaction)
3. Your network is slow and it takes a long time to return the result back to the client

Ad 1) If you are waiting for CPU resources, this should show up as signal waits. Please see my comment on your question on how to diagnose if this is the issue

Ad 2) If you are waiting for a lock, this is best diagnosed by comparing two snapshots of sys.dm_os_wait_stats. See this article on how to do this:

• http://www.sqlsoldier.com/wp/sqlserver/sqludbaweekbeanefficientdba

If you see long waits for LCK_[Something], query sys.dm_tran_locks to track down which objects are being locked. In your case, I would expect to see some form of SCH-[Something]> locks blocking you.

Ad 3) The easiest way to diagnose network issues to first look for OLEDB and ASYNC_NETWORK_IO waits in step 2 (if you wait a long time for network, one of those show up). If those waits are high, use xperf -on latency or a network monitoring program like netmon or wireshark to check your latencies. If the network looks slow, this could also be caused by the calling application server not responding fast enough to the connection being recycled.