Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ahmad Osama » View conenction and Locking Information

Add to briefcase

View conenction and Locking Information Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Sqlfrenzy
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: Saturday, June 15, 2013 3:37 AM
Points: 1,436, Visits: 1,561
Comments posted to this topic are about the item View conenction and Locking Information

Regards,
Sqlfrenzy
Post #747126
AndyJost
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
Sqlfrenzy
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: Saturday, June 15, 2013 3:37 AM
Points: 1,436, Visits: 1,561
great work done...i din't know that ping accepts parameters....thanks dude

Regards,
Sqlfrenzy
Post #751173
Dan Guzman - Not the MVP
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: Friday, April 26, 2013 11:57 AM
Points: 708, Visits: 660
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
AndyJost
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
Sqlfrenzy
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: Saturday, June 15, 2013 3:37 AM
Points: 1,436, Visits: 1,561
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
Dan Guzman - Not the MVP
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: Friday, April 26, 2013 11:57 AM
Points: 708, Visits: 660
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
Sqlfrenzy
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: Saturday, June 15, 2013 3:37 AM
Points: 1,436, Visits: 1,561
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
logicinside22
Posted Thursday, February 16, 2012 2:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:59 AM
Points: 280, Visits: 1,029
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.