It only applies to incomplete outbound connections, where the client has sent a SYN packet but hasn't yet received a SYN-ACK packet from the server. This is the first thing the server's TCP stack does, before it even informs the server application that a connection is being made (i.e. accept() doesn't return yet).

It has a slight effect on BitTorrent as the peers change - appear and disappear - so often. When it starts downloading, it makes a lot of connections, which can cause the limit to be reached. When this happens you'll see Event 4226 appear in your System event log. The effect is that until some of the connection attempts either complete or time out, new connection attempts will be queued. So if you're browsing at the same time as downloading on BitTorrent, your browsing connections may be delayed.

People have produced 'patched' versions of the TCP/IP stack driver without this limit, but I would strongly recommend that you don't install them.

The reason for the limit is simple: to prevent simple SYN-flooding attacks.