Waaay more connections than necessary

  • Artigo

We had a dump where we got a .NET OutofMemory exception.   The ASP.NET folks looked at the dump and saw a large number of rooted TDS objects.  I wasn't sure where those were coming from, so pulled up the dump to evaluate this.

0:033> !dumpheap -stat
...
0x65400574   11,959    1,339,408 System.Data.SqlClient.SqlConnectionString
0x654049c0   11,959    1,482,916 System.Data.SqlClient.TdsParser
0x65405fdc   11,958    1,530,624 System.Data.SqlClient._SqlMetaData
0x65404744   11,959    1,674,260 System.Data.SqlClient.SqlInternalConnectionTds
0x79101fe4   36,452    2,041,312 System.Collections.Hashtable
0x65404dec   11,959    2,248,292 System.Data.SqlClient.TdsParserStateObject
0x7910be50  206,356    2,476,272 System.Boolean
0x65400808  233,910    4,678,200 System.Data.Common.NameValuePermission
0x65400b64  221,950    5,326,800 System.Data.Common.NameValuePair
0x7912d8f8  257,690    5,741,720 System.Object[]
0x000df590      175   10,314,064      Free
0x7912d9bc   36,461   13,194,048 System.Collections.Hashtable+bucket[]
0x790fd8c4  493,301   34,033,216 System.String
0x7912dae8   48,478  192,431,300 System.Byte[]
Total 1,994,862 objects, Total size: 290,427,832

Notice any pattern?  11959 seems to be pretty repetitive.  I  especially don't understand why we have almost 12000 internal connections unless....

Yep - customer is not pooling!!

First, dump out the method table for the System.Data.SqlClient.SqlConnectionString

0:033> !dumpheap -mt 0x65400574

This produces a ridiculously large (11,959 members!) list of SqlConnectionStrings.  So, let's dump out one at random:

0:033>  !do 0x2e016064
Name: System.Data.SqlClient.SqlConnectionString
MethodTable: 65400574
EEClass: 6544c6c0
Size: 112(0x70) bytes
GC Generation: 2
(C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll)
Fields:
      MT    Field   Offset                 Type VT     Attr    Value Name
790fd8c4  4000bce        4        System.String  0 instance 2e015e50 _usersConnectionString

And then keep drilling into him...

0:033> !do 2e015e50
Name: System.String

String: Data Source=cpsrv;Initial Catalog=Changepoint;Integrated Security=False;User ID=xxxxx;Password=yyyyy;Enlist=False;Pooling=False;Asynchronous Processing=True;Application Name=SqlQueryNotificationService-ac370dde-d9c4-4cbe-9d82-7d7a8df159c6

Let's do another one, too

0:033> !do 0x2c5b9aa8
Name: System.Data.SqlClient.SqlConnectionString
MethodTable: 65400574
EEClass: 6544c6c0
Size: 112(0x70) bytes
GC Generation: 2
(C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll)
Fields:
      MT    Field   Offset                 Type VT     Attr    Value Name
790fd8c4  4000bce        4        System.String  0 instance 2c5b9894 _usersConnectionString

0:033> !do 2c5b9894
Name: System.String
MethodTable: 790fd8c4
EEClass: 790fd824
Size: 530(0x212) bytes
GC Generation: 2
(C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll)
String: Data Source=cpsrv;Initial Catalog=Changepoint;Integrated Security=False;User ID=xxxx;Password=yyyy;Enlist=False;Pooling=False;Asynchronous Processing=True;Application Name=SqlQueryNotificationService-78b81d9b-ecd9-400d-8435-6e745d449c96

Note that in both cases, the connection string has Pooling=False and an application name that has a GUID.  Either one of these will prevent good use of connection pooling. 

The customer needs to turn pooling on, plus eliminate the GUID from the Application Name.

Comments