View conenction and Locking Information

  • Comments posted to this topic are about the item View conenction and Locking Information

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • 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.

  • great work done...i din't know that ping accepts parameters....thanks dude 🙂

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • 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

  • 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

  • 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,
    [font="Verdana"]Sqlfrenzy[/font]

  • 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.

  • 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,
    [font="Verdana"]Sqlfrenzy[/font]

  • Awesome I added your script with SP and works fine.

    Just one concern about to use xp_cmdshell in production servers is good?

  • Thanks for the script.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply