SQL Server Performance

Linked Servers

Discussion in 'Performance Tuning for DBAs' started by Chappy, Mar 5, 2003.

  1. Chappy New Member

    Are there any performance caveats for using linked servers ?

    We have an sql server storing small bits of dynamic data for our corporate website... session guids etc. Also, the website needs to access our live subscription system to show what products a user is subscribed to etc.

    I decided I did not want the website code (asp.net, could be many as yet undisclosed exploits) directly hitting our live subs server in anyway, and so I added the subs server to the website sql server as a linked server. The website then only ever calls stored procedures on the website sql server, which in turn calls across to the linked subs server using a heavily restricted login.

    Anyone have any comments about this architecture ?
    Sometimes the website appears to stall and then times out. I have not yet ascertained whether this refers to the http connection to the web service, or the database connection from the web service to the website sql server.

    Thanks for any comments.


  2. Chappy New Member

    Btw, obviously my nexts steps are to profile the activity. I will do this tommorrow, but since I am short of time, I decided I would ask for any general advice on the design in the meantime. Cheers!
  3. satya Moderator

    Did you get any issues in the test bed for this setup?
    Try starting PROFILER and see the activity where its lacking and getting timeouts.
    For me as it seems nothing problematic about the setup.

    Satya SKJ
  4. Chappy New Member

    No, there were not timeouts on the same frequency as is happening on the live setup. The dev server is an inferior machine also.

    However, admittedly the dev setup was not totally representative. One point I meant to mention was that on eht live setup, the subs server is 2000, and the server from which it is linked is sql 7.
    On the dev setup, both were sql 2000. Perhaps this could be a factor, I need to investigate more.
    But its reassuring to hear nothing is blatantly wrong with the architecture, thanks.
  5. satya Moderator

    Also take into account for the netlibs and authentication mode between these 2 servers.

    HTH

    Satya SKJ
  6. Argyle New Member

    I recently did some work with linked servers between sql 2000 and sql 7. I wrote down some issues I had at the time:

    -------------------------------------------

    Issue 1:
    You often can#%92t run SELECT queries from a SQL Server 2000 via linked servers to views on a SQL Server 7.0. Might only affect views on the SQL 7.0 server that in turn reference another SQL 7.0 Server.
    BUG: Error 7356 from a Distributed Query (Q255097)
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q255097

    Solution:
    One has to rearrange the internal representation of the column order on the affected tables on the SQL Server 7 server. Use the script in this article:
    http://www.sqlservercentral.com/columnists/jtravis/changingcolumn.asp

    -------------------------------------------

    Issue 2:
    Slow transfer or “general network error messages”

    Solution:
    Set the “Default Network Library” to TCP/IP instead of Named Pipes on the involved SQL Servers. Use “cliconfg.exe”.

    -------------------------------------------

    Issue 3:
    Extremely slow performance between linked servers. Especially when working with large amount of data.

    Solution:
    Be sure to know how you code your queries. There are many issues to look out for when working with linked servers. Doing selects or updates trough a linked server reference can cause you to fetch the entire table data via slow cursors even if you only want one row.
    Use the command OPENQUERY if possible.

    Example:
    The following query will use a very slow cursor and fetch data row by row (look for sp_cursoropen in profiler)
    INSERT INTO [myDB].[dbo].[myTable]([myID], [myInfo])
    SELECT * FROM myLinkedServer.myOtherDB.dbo.myOtherTable

    Rewrite it like this and all rows will be sent over in a single batch:
    INSERT INTO [myDB].[dbo].[myTable]([myID], [myInfo])
    SELECT * FROM OPENQUERY(myLinkedServer, 'SELECT * FROM myOtherDB.dbo.myOtherTable')

    -------------------------------------------

    Issue 4:
    The MSDTC service has to be able to do name lookups on the involved SQL Server.

    Solution:
    If DNS or WINS are miss-configured or traffic is blocked in routers or something then add name and IP address entries in “lmhosts” and “hosts” files on the involved SQL servers.

    -------------------------------------------

    Issue 5:
    A bit complicated to configure firewalls to allow MSDTC traffic since MSDTC uses dynamic ports.
    INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (Q250367)
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q250367

    Solution:
    Alternative 1:
    Open up for all ports between the involved SQL server IP addresses.
    Alternative 2:
    Open up for all ports above 1024 between the involved SQL Server IP addresses. Also open up for port 135 between the servers.
    Alternative 3:
    Edit the registry on both SQL servers to force the MSDTC service to use a specific range of ports, for example 5000 - 5020. Then open up traffic for these ports in the firewall. Also open up for port 135 between the servers.

    -------------------------------------------

    Issue 6:
    Cannot run DTS transfer tasks between two databases on the same SQL Server.
    BUG: Error 3910, "Transaction Context in Use by Another Session" (Q279857)
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q279857

    Solution:
    Write custom SQL scripts instead of using DTS transfer tasks.

    -------------------------------------------

    /Argyle
  7. satya Moderator

    Handful information by Argyle, keep it up Pal.

    Satya SKJ
  8. Chappy New Member

    Forgot to say thanks, argyle, that was a very informative reply.<br /><br />Today I noticed there was a fair amount of paging going on. Then looking at page file usage %, this would go up from around 20% at a uniform rate, until it hit a certain limit (not 100% though, oddly enough), at which point the usage would not increase further. It would hold at this plateau for around 15 minutes, at which point it would appear to give up, page file usage would drop back down, and sql activity would resume as normal. <br /><br />During this plateau, the server would suffer from network timeouts, dropped connections and no sql activity occurring in profiler.<br />As soon as the plateau ended, page file usage dropped back to 20% again and the increase started immediately again at the same rate, in such a way I could predict when the server would next go down.<br /><br />I *think* what was happening is that the page file was needing to grow beyond its current size, but was struggling to do this efficiently (Im not sure if 2k even allows a fragmented page file, or if it simply tries to avoid it), and so ensued a massive amount of disk thrashing while it tried to grow the page file.<br /><br />Next step was to fix the paging file to a set size of a gig, and not let windows dynamically grow and shrink it. As soon as I did this, and rebooted, the problem appears solved. I dont know why the pagefile struggled to grow so badly, but I think fragmentation may have been a factor. Defragging the drive overnight...<br /><br />So a fun day, I learnt something new <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />
  9. Argyle New Member

    I'll add that as issue 7 on the list [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />/Argyle

Share This Page