View conenction and Locking Information

  • Ahmad Osama

    SSCertifiable

    Points: 7676

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

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

  • AndyJost

    Ten Centuries

    Points: 1010

    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.

  • Ahmad Osama

    SSCertifiable

    Points: 7676

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

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

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    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

  • AndyJost

    Ten Centuries

    Points: 1010

    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

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    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]

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    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.

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    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]

  • logicinside22

    SSCrazy Eights

    Points: 9617

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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