SQL Server Performance

query runs faster when connected locally using IP as opposed to hostname

Discussion in 'General DBA Questions' started by rajah, May 9, 2011.

  1. rajah New Member

    HiI have a strange problem that a query is returning in 11 sec when query analyser is connected locally using the hostname, and in 1 second when it is connected locally using the IP address.
    This is a new server being built by a different team, and im trying to get them to investigate networking issues. But im at a loss to explain this reality.
    The query actually is a select, joining a few tables, and is also returning an ntext column. there are only about 800 rows returned. noticeably, the issue disappears when the ntext column is removed from the query, but im assuming that will be due to less data having to be returned to the screen masking the slowness of the query.the new box is setup to use a dns, although im sure the dns is not being configured correctly. A test to add the hostname to resolve to the local IP address in the HOSTS file, also did not resolve the issue.pinging and tracert to the IP and hostname are identical, and don't reveal any slowness.
    however, the issue is that with all things completely identical, other than using the IP address instead of the hostname to connect, the query takes 1 sec using the IP, and 11 seconds using the hostname.
    any ideas?
    regards
    rajah
  2. satya Moderator

    Welcome to the forums.
    That is a strage behaviour, as long as the connection is attempted the result should be similar. I suspect this could be related to - WINS/ name resolution on a network; have a discussion with your network team to find if there are any delay/dropped packets on network.
  3. Adriaan New Member

    Two things come to mind,
    (1) multiple network cards on the SQL Server machine
    (2) something I remember as "reverse IP lookup" - try a search
  4. Adriaan New Member

    Also, have you tried (HOSTNAME) ?
  5. Adriaan New Member

    or rather
    (localhost)
  6. rajah New Member

    Hi
    Sorry about the delay getting back to you. Yes, i have tried logging in using IP, hostname, (local) and . (dot)
    Only the IP returns immediately. All the others return in 11-12 seconds.
    There were issues in the network, the ports were not working at the correct speeds, 100Mbit/Half when it should be 1000Mbit/Full. These issues were fixed.
    There are two network cards in the box. one is for NAS connections on a separate subnet, but this is not currently being used, the disks are a local raid array.
    There is a DNS being used, and im sure it hasn't been setup correctly. Nonetheless, when we bypassed the dns by adding the hostname/IP mapping in the HOSTS file, the issue did not go away. It almost seemed like the results when logged in via the hostname were still going through the network card and back to the client running on the same box as the database. I say that because, when i take out the ntext field from the select, the query returns immediately. Often when you select larger amounts of data over a network you see some delay in comparison to selecting the data locally. hence im thinking the data is still hitting the network card route in some way.
    Any more details around the reverse lookup? what should i be checking for?
  7. satya Moderator

    What speed and DUPLEX set for the ports?
    In general it should be 1GBPS or greater (compatibility of NIC). FOr sure the problems are there for naming/DNS. As you got 2 NICs it is ideal to use one port for SQL SErver alone and divert all other traffic to remaining port.
    You can use lmhosts to enter details for reverse lookup,
    To work around this problem, either provide some reliable means for performing a reverse lookup of the SQL Server server's IP address, use the SQL Server machine name instead of the IP address, or apply this hotfix.

    To use the HOSTS file or LMHOSTS, put the TCP/IP address & server name of the SQL Server server at the start of the line followed by a few spaces followed by the machine name of the SQL Server server. For example:
    123.123.123.123 myservername
    The HOSTS or LMHOSTS file belongs in the System32DriversEtc directory for client computers running Microsoft Windows NT and Microsoft Windows 2000, or in the Windows directory for clients running Microsoft Windows ME, Microsoft Windows 98, or Microsoft Windows 95.
  8. rajah New Member

    Hi Satya
    I found the same info just now on the reverse IP lookup, http://support.microsoft.com/kb/300420
    When Microsoft Data Access Components (MDAC) version 2.6 is installed, each attempt to connect to a SQL Server 7.0 or SQL Server 2000 database using an IP address (rather than the server name) may take longer than anticipated. "In certain reported cases, a "Timeout expired" error is reported to the client application and the connection attempt fails.

    Be aware that if you are using a server alias which maps to a TCP/IP address, this problem can still occur.


    This problem does not occur with MDAC 2.5 or 2.1 installed.

    However, i believe my problem is exactly the opposite to this. Im having problems when connecting using the hostname, and not the IP. And ive also used the HOSTS file mapping and the problem is still there. We also have SP4 applied, and this KB issue was there with SP2.
    The network speed should be 1gb/full duplex, and i believe that is the case now. Im not really a networking guy, but we have one i can send instructions/queries to. Ill ask him to look into what you said about "use one port for SQL SErver alone and divert all other traffic to remaining port.", unless this can be done via a setting on the database?
  9. satya Moderator

    Yeah the use of NIC should be enabled from Operating system not from SQL Server. And also you need to change the default port that is used by SQL SErver, use a named port.
  10. rajah New Member

    when you say "use a named port" you mean use a port other than the defualt port? Is that something that will help with the current hostname/IP issue or something that you are mentioning as being best practice?
  11. rajah New Member

    Ive managed to fix the issue. i opened the client network utility tool installed on the database server, and removed the "shared memory network protocol". now the query analyser on the local database server is returning data as fast using hostname as it is when using IP. it's a strange thing, but my headache is gone.
    the issues i had with remote connection performance, should be dealt with whent they have fixed the port connection speeds to use full 1gb/duplex.
    many thanks for your help.
  12. satya Moderator

    Ah good old problem of Shared Memory protocol...glad it is solved and appreciate your feedback.
  13. rajah New Member

    Just some more advice on this issue. since i won't actually be running local queries when this box is in production, is it recommended to leave the shared memory protocol disabled? will it have any adverse effects on the local services/jobs etc on the production server if it is disabled?

Share This Page