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.