Great troubleshooting query.

As you point out, the use of Ping to retrieve the IP address is very slow. How about the following alternatives to speed it up:

1. Add parameters to the Ping command to reduce the number of requests to send from the default 4 down to 1; and reduce the Time to Live to 1 to stop the ping request traveling through routers and slow networks. The line to set the Ping command would then change to:

SET @cmd = 'ping -n 1 -i 1 ' + @HostName

2. As the query is only interested in the IP address and does not need to test if the client machine is available on the network, we could use nslookup to query DNS. This requires changing a few lines as the results need parsing slightly differently:

SET @cmd = 'nslookup ' + @HostName

INSERT INTO @Results

EXECUTE master..xp_cmdshell @cmd

SELECT @ClientIPAddress = LTrim(Replace(Results, 'Address:', ''))

FROM @Results

WHERE Results LIKE 'Address:%'

On a fairly slow server with only 7 connections, option 1 reduces the query time from 1 min 8 secs to 49 secs. And option 2 reduces the query time down to 2 seconds :-D.