MDW: ServerActivity upload causes several thousand connections to MDW server - by ToddSQL

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 668397 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 5/13/2011 11:03:46 AM
Access Restriction Public


While running the upload job for the Server Activity data collector, a large number of connections are made to the MDW database host and quickly dropped.  On a server with a large number of processors, running processes, disks and SQL instances (and therefore, several instances of LogicalDisk, Processor, Process and MSSQL* performance counters) this can cause several thousand connections to occur, using up all the client ports available and causing problems with any outbound connection, including DNS lookups and Windows authentication.

This is caused by the Lookup task “LKU – Lookup into performance_counter_instances to obtain performance_counter_id for all counter paths that get inserted” under “DFT – Bulk Insert Collected data into MDW” in the PerfCountersUpload package.  The task is set to partial cache, which results in the package logging into the MDW host, running a SELECT performance_counter_id from snapshots.performance_counter_instances where path=x, and logging out for every performance counter in the current upload cache file.  Even though the connection is quickly made and dropped, the port numbers are not immediately released as they remain in a TIME_WAIT status for some time before releasing.  Once the server runs out of ports, everything from DNS lookups to Kerebos authentication stop working until a port is available.

A workaround is to edit the PerfCountersUpload package and switch the cache to full.  This caches the entire performance_counter_instances table, but it is generally very small.
Sign in to post a comment.
Posted by Microsoft on 6/28/2011 at 4:50 PM

Just want to update you that the issue was resolved and it will be available in next SQL 2008 service pack (SP3) and in next major release. For SQL 2008R2 the fix will need to wait till next Service Pack (SP2).


Posted by Microsoft on 6/13/2011 at 12:14 PM
Thank you for reporting this issue, as Sethu mentioned we already have this issue in our system and we do plan to address it our next SQL Server 2008 Service Pack. We may also deliver it before that as a downloadable hotfix.


Alex Grach
Posted by ToddSQL on 5/14/2011 at 8:26 AM

We are running SQL 2008 R2 RTM x64.
Posted by Sethu Srinivasan on 5/13/2011 at 6:13 PM
We are working on fix for this issue. We shall keep you posted when the fix is available
Please let us know the SQL Server version you are using

Sethu Srinivasan[MSFT]
SQL Server