Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View conenction and Locking Information Expand / Collapse
Author
Message
Posted Friday, July 03, 2009 4:15 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
Comments posted to this topic are about the item View conenction and Locking Information

Regards,
Sqlfrenzy

Post #747126
Posted Friday, July 10, 2009 3:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 04, 2011 12:14 PM
Points: 974, Visits: 126
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 .



Post #750905
Posted Friday, July 10, 2009 9:22 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
great work done...i din't know that ping accepts parameters....thanks dude

Regards,
Sqlfrenzy

Post #751173
Posted Friday, July 10, 2009 9:25 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 30, 2013 9:43 AM
Points: 710, Visits: 664
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?

it sounds helpful, but I'm a little stuck.

Dan
Post #751175
Posted Friday, July 10, 2009 9:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 04, 2011 12:14 PM
Points: 974, Visits: 126
There are some strange blank characters at the beginning of the lines in the original post. You can use Find and Replace in SSMS to fix them:
1. Paste the SQL into a new SSMS query window
2. Highlight the single character immediately before the first Declare word
3. Press Ctrl-H This will open the Find and Replace window and copy the selected character in the Find What field
4. Enter a single space into the Replace With field and click Replace All

This should replace about 528 occurances.

You should then be able to run the query to create the stored proc.

Andy



Post #751198
Posted Friday, July 10, 2009 11:59 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
Dan Guzman (7/10/2009)
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?

it sounds helpful, but I'm a little stuck.

Dan


it can be used with sql 2005


Regards,
Sqlfrenzy

Post #751305
Posted Friday, July 10, 2009 12:08 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 30, 2013 9:43 AM
Points: 710, Visits: 664
Sqlfrenzy (7/10/2009)
Dan Guzman (7/10/2009)
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?

it sounds helpful, but I'm a little stuck.

Dan


it can be used with sql 2005



That doesn't help with any of the other questions.
Post #751308
Posted Friday, July 10, 2009 12:11 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 3:10 AM
Points: 1,475, Visits: 1,623
Dan Guzman (7/10/2009)
Sqlfrenzy (7/10/2009)
Dan Guzman (7/10/2009)
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?

it sounds helpful, but I'm a little stuck.

Dan


it can be used with sql 2005



That doesn't help with any of the other questions.


those were already answered by Andy


Regards,
Sqlfrenzy

Post #751312
Posted Thursday, February 16, 2012 2:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 304, Visits: 1,170
Awesome I added your script with SP and works fine.
Just one concern about to use xp_cmdshell in production servers is good?


Aim to inspire rather than to teach.
SQL Server DBA
Post #1253440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse