In the last post in this series, we looked at how we can determine that our Connection pool was exhausted. In this post I'm going to go a little deeper into the Internal connection itself and how we can verify if this is a closed or active connection.
A connection object in the System.Data.SqlClient namespace consists of two parts:
The SqlConnection class has a pointer to a SqlInternalConnectionTds object if it’s open (_innerConnection member variable). The _innerConnection member variable is null if the connection is closed. Whenever a connection is closed by the code, the internal object gets disassociated from the external object and the ownership of the internal object transfers to the pool object. This relationship allows us to identify SqlConnection objects that have not been closed.
The SqlInternalConnectionTds object has a weak reference back to the owning SqlConnection object.
Since there are typically multiple pools and not all of them are full, we want to start with the internal objects that we know belong to a full pool.
Going back to the pool in question, lets dump out the items within this pool.
0:000> !do 012bbe80 Name: System.Data.ProviderBase.DbConnectionPool ... 00000000 400152b 40 0 instance 012bbfb4 _objectList 79102290 400152c 54 System.Int32 1 instance 100 _totalObjects ...
0:000> !do 012bbfb4 Name: System.Collections.Generic.List`1[[System.Data.ProviderBase.DbConnectionInternal, System.Data]] MethodTable: 654413c4 EEClass: 7912f680 Size: 24(0x18) bytes (C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) Fields: MT Field Offset Type VT Attr Value Name 7912d8f8 40009c7 4 System.Object[] 0 instance 012bbfcc _items 79102290 40009c8 c System.Int32 1 instance 100 _size 79102290 40009c9 10 System.Int32 1 instance 100 _version 790fd0f0 40009ca 8 System.Object 0 instance 00000000 _syncRoot 7912d8f8 40009cb 0 System.Object[] 0 shared static _emptyArray >> Domain:Value dynamic statics NYI 00155858:NotInit <<
0:000> !da 012bbfcc Name: System.Data.ProviderBase.DbConnectionInternal[] MethodTable: 7912d8f8 EEClass: 7912de6c Size: 416(0x1a0) bytes Array: Rank 1, Number of elements 100, Type CLASS Element Methodtable: 654009f0 [0] 012be414 [1] 012bf3e4 [2] 012bf008 ... [98] 0148114c [99] 01485fcc
At this point we want to save all these 100 internal connection addresses into a file and remove all the array indexes so that the file only contains:
012be414 012bf3e4 012bf008 ... 0148114c 01485fcc
Visual Studio is handy for this since we can select using alt + mouse to select the first 3-4 columns and delete them all, then save the file.
The goal at this point is to find any SqlConnection objects from these SqlInternalConnectionTds objects that are no longer referenced. If the SqlConnection still references the SqlInternalConnectionTds and cannot be reached through !gcroot, it has been abandoned by the code without closing it.
Using .foreach to dump out the connections is easiest since it avoid the manual work of processing each of the 100 connections in question:
.foreach /f ( place "c:\temp\InternalConnections.txt") { dd poi(poi( place +4)+4) l1} (32 bit)
or
.foreach /f ( place "c:\temp\InternalConnections.txt") { dq poi(poi( place +8)+8) l1} (64 bit)
Explanation of the .foreach command:
place – this is our placeholder, or variable name, that represents each of the addresses in the file dd – this would be dq in a 64-bit dump. It dumps out a double word, or the address place + 8 – the weak reference is at offset 8 from the SqlInternalConnectionTds(64 bit) or at offset 4(32 bit):
0:000> !do 012be414 Name: System.Data.SqlClient.SqlInternalConnectionTds MethodTable: 65404744 EEClass: 6544d9e0 Size: 140(0x8c) bytes (C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll) Fields: MT Field Offset Type VT Attr Value Name 79102290 4000f67 1c System.Int32 1 instance 4 _objectID ... 79104c38 4000f6d 4 System.WeakReference 0 instance 012be55c _owningObject ...
The WeakReference object has a handle at offset 8 that is the second +8 in the command (64 bit) or at offset 4 (32 bit):
0:000> !do 012be55c Name: System.WeakReference MethodTable: 79104c38 EEClass: 79104bd4 Size: 16(0x10) bytes (C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll) Fields: MT Field Offset Type VT Attr Value Name 791016bc 40005a9 4 System.IntPtr 1 instance 3f1268 m_handle 7910be50 40005aa 8 System.Boolean 1 instance 0 m_IsLongReference
The value at that location is the owning object if it exists.
Non-Null and has an owning object:
0:000> dd 3f1268 l1 003f1268 01575138
Null and no owning object:
0:000> dd 3f1268 l1 003f1268 00000000
Output of the foreach command:
0:000> .foreach /f ( place "c:\temp\InternalConnections.txt") { dd poi(poi( place +4)+4) l1} 003f1268 01575138 003f127c 0157336c 003f1290 0157136c 003f1298 0156f138 003f1244 015809fc ... 003f2d34 014ac514 003f2d2c 014acbf4 003f2d1c 014ac7d4 003f2d14 015817cc
As we can see, the internal connections have an owning SqlConnection object. This either means that they are actively being used by the code (not likely) or they have been abandoned (more likely).
Finding out if a connection is actively used
To find out if a SqlConnection is still being used by the code, we can run the !gcroot command. This command will tell us if the object is reachable by the .NET Framework and if it is not, it is ready to be collected.
0:000> !gcroot 0157336c Note: Roots found on stacks may be false positives. Run "!help gcroot" for more info. Scan Thread 0 OSTHread 590 DOMAIN(00155858):HANDLE(WeakSh):3f127c:Root:0157336c(System.Data.SqlClient.SqlConnection)
At this point in the application, we only have one thread running which is thread ID 0.
Here the output indicates that the object is reachable from thread 0. However, this can be a false positive because thread references can be old. We still have to verify that the object actually exists on that thread:
0:000> kL ChildEBP RetAddr 0012f31c 7739bf53 ntdll!KiFastSystemCallRet 0012f3b8 7b0831a5 user32!NtUserWaitMessage+0xc 0012f434 7b082fe3 System_Windows_Forms_ni+0xb31a5 0012f464 7b0692c2 System_Windows_Forms_ni+0xb2fe3 0012f490 79e7c6cc System_Windows_Forms_ni+0x992c2 0012f510 79e7c8e1 mscorwks!CallDescrWorkerWithHandler+0xa3 0012f64c 79e7c783 mscorwks!MethodDesc::CallDescr+0x19c 0012f668 79e7c90d mscorwks!MethodDesc::CallTargetWorker+0x1f 0012f67c 79eefb9e mscorwks!MethodDescCallSite::Call_RetArgSlot+0x18 0012f7e0 79eef830 mscorwks!ClassLoader::RunMain+0x263 0012fa48 79ef01da mscorwks!Assembly::ExecuteMainMethod+0xa6 0012ff18 79fb9793 mscorwks!SystemDomain::ExecuteMainMethod+0x43f 0012ff68 79fb96df mscorwks!ExecuteEXE+0x59 0012ffb0 7900b1b3 mscorwks!_CorExeMain+0x15c 0012ffc0 77e6f23b mscoree!_CorExeMain+0x2c 0012fff0 00000000 kernel32!BaseProcessStart+0x23
We can see that we have managed code on this thread. Let's look at what the managed stack looks like:
0:000> !clrstack OS Thread Id: 0x590 (0) ESP EIP 0012f32c 7c8285ec [InlinedCallFrame: 0012f32c] System.Windows.Forms.UnsafeNativeMethods.WaitMessage() 0012f328 7b08374f System.Windows.Forms.Application+ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32, Int32, Int32) 0012f3c8 7b0831a5 System.Windows.Forms.Application+ThreadContext.RunMessageLoopInner(Int32, System.Windows.Forms.ApplicationContext) 0012f440 7b082fe3 System.Windows.Forms.Application+ThreadContext.RunMessageLoop(Int32, System.Windows.Forms.ApplicationContext) 0012f470 7b0692c2 System.Windows.Forms.Application.Run(System.Windows.Forms.Form) 0012f480 00e70097 SqlConnectionLeakWin.Program.Main() 0012f69c 79e7c74b [GCFrame: 0012f69c]
Doesn't appear to be doing anything with SQL here. Let's look at the objects on the stack:
0:000> !dso OS Thread Id: 0x590 (0) ESP/REG Object Name ebx 01253384 System.Windows.Forms.Application+ThreadContext esi 015cc2e8 System.Collections.Hashtable+HashtableEnumerator 0012f354 01299fc4 System.Windows.Forms.NativeMethods+MSG[] 0012f358 01253384 System.Windows.Forms.Application+ThreadContext 0012f360 01299ad8 System.Windows.Forms.Application+ComponentManager 0012f3d8 01253384 System.Windows.Forms.Application+ThreadContext 0012f42c 01253384 System.Windows.Forms.Application+ThreadContext 0012f43c 01296b84 System.Windows.Forms.ApplicationContext 0012f444 0127fe4c System.ComponentModel.EventHandlerList 0012f458 01252a8c SqlConnectionLeakWin.Form1 0012f460 01253384 System.Windows.Forms.Application+ThreadContext 0012f474 01252a8c SqlConnectionLeakWin.Form1
We can conclude that this SqlConnection object is no longer being used and it has not been closed. This proves that the applications code did not close all connections and further code investigation needs to be performed to close all connections.
Reference:
Part 1
Adam W. Saxton | Microsoft SQL Server Escalation Services