SQL Server Performance

Using the Linked Server Provider Option "Nested Query" For Performance

Discussion in 'Performance Tuning for DBAs' started by DBADave, Aug 11, 2008.

  1. DBADave New Member

    We have a number of SQL 2000 linked servers that query SQL 2005 and SQL 2000 servers. I just determined that using the Provider Option "Nested Query" causes a big performance boost for queries against our SQL 2005 databases containing a nested SELECT statement in the FROM clause. My questions are:
    (1) should I always enable this provider option for any SQL 2000 to 2005 and 2000 to 2000 where a nested query is used?
    (2) will enabling this option cause overhead and possibly slow down performance where the query referencing a linked server is not nested?
    (3) In SQL 2005 should I turn this option ON for the OLE DB provider or could that negatively impact performance?
    Thanks, Dave
  2. satya Moderator

    I can see the other providers are SQL too, so switching on the Nested Query option should have similar kind of performance solution, for SQL 2005 linked queries it is better to use APPLY statement.
    This apply statement was created for the situation where you put multiple inline nested queries in the one statement.
  3. DBADave New Member

    I'm not sure I understand it completely so I will have to play around with it a bit so see how it impacts a linked server query.
    Thanks, Dave

Share This Page