SQL Server 2005 Linked Servers

microsoft.public.sqlserver.server

28 Feb 2007 9:36 AM

nnsoni

Hi,

I am using 2 servers with SQL Server 2005 installed on them. I have 1
Database (say the Corporate Database) on Server A and the other
databases (say the Department Database) on Server B. All the core
tables that are shared across all the departments are stored in the
Corporate Database (say Users, Groups, Roles, Categories etc) and all
the Department specific information is saved in the Department
Database (say Orders, Products etc).

I have created a linked server (Server Type as SQL Server) on Server
A
to connect to Server B. All my Stored Procedures are running from
Server A. My Stored Procedure looks like this

CREATE PROCEDURE [Company].[GetDocuments]
(
        @PolicyNo               varchar(50)
        ,@DocumentId    bigint = NULL
)

AS

SELECT
        doc.DocumentId
        ,doc.DocumentName
        ,doc.DocumentTypeId
        ,doc.PolicyNo
        ,doc.Title
        ,doc.FirstName
        ,doc.LastName
        ,doc.[FileName]
        ,dt.DocumentTypeName
        FROM Company.Documents doc
        INNER JOIN Corporate.DocumentTypes dt ON dt.DocumentTypeId =
doc.DocumentTypeId
WHERE
AND doc.PolicyNo like @PolicyNo
AND doc.DocumentId = COALESCE(@DocumentId, doc.DocumentId)

Here if you notice I have used 2 Synonyms a) Corporate which is local
to Server A ([Server A].[Database]....) and b) Company which is
equivalent to remote query across Server B ([Server B].
[Database]....)

When I check the execution plan of the above query - it shows the
heading for the remote query as "Remote Query" and does not even use
the indexes that are defined for the same (In the above example the
DocumentId field is defined as a Clustered Index).

Now my question is - if a cross database join is used across 2 SQL
Server 2005 databases; does the provider not take the advantage of
both databases being SQL Server (2005) and increase the performance
of
the query. Does it still try to add all the overhead of treating this
as a remote query rather than a specific SQL Server query

Thanks

Regards

Nitesh

28 Feb 2007 10:09 AM

Uri Dimant

Hi
If you use COALESCE or another function in the WHERE condition it is more
likely SQL Server with use index SCAN

(sql server 2005 sp2)

use demo

create table dbo.C (documentid int not null primary key)

go

insert into dbo.C values (1)

insert into dbo.C values (2)

insert into dbo.C values (3)

select documentid from dbo.C where documentid=2

---index seek CI

declare @d int

set @d=1

select documentid from dbo.C where documentid=coalesce(@d,documentid)

---scan ci

<nns***@gmail.com> wrote in message
Show quoteHide quote

news:1172655388.327193.43810@k78g2000cwa.googlegroups.com...
> Hi,
>
> I am using 2 servers with SQL Server 2005 installed on them. I have 1
> Database (say the Corporate Database) on Server A and the other
> databases (say the Department Database) on Server B. All the core
> tables that are shared across all the departments are stored in the
> Corporate Database (say Users, Groups, Roles, Categories etc) and all
> the Department specific information is saved in the Department
> Database (say Orders, Products etc).
>
>
> I have created a linked server (Server Type as SQL Server) on Server
> A
> to connect to Server B. All my Stored Procedures are running from
> Server A. My Stored Procedure looks like this
>
>
> CREATE PROCEDURE [Company].[GetDocuments]
> (
>        @PolicyNo               varchar(50)
>        ,@DocumentId    bigint = NULL
> )
>
>
> AS
>
>
> SELECT
>        doc.DocumentId
>        ,doc.DocumentName
>        ,doc.DocumentTypeId
>        ,doc.PolicyNo
>        ,doc.Title
>        ,doc.FirstName
>        ,doc.LastName
>        ,doc.[FileName]
>        ,dt.DocumentTypeName
>        FROM Company.Documents doc
>        INNER JOIN Corporate.DocumentTypes dt ON dt.DocumentTypeId =
> doc.DocumentTypeId
> WHERE
> AND doc.PolicyNo like @PolicyNo
> AND doc.DocumentId = COALESCE(@DocumentId, doc.DocumentId)
>
>
> Here if you notice I have used 2 Synonyms a) Corporate which is local
> to Server A ([Server A].[Database]....) and b) Company which is
> equivalent to remote query across Server B ([Server B].
> [Database]....)
>
>
> When I check the execution plan of the above query - it shows the
> heading for the remote query as "Remote Query" and does not even use
> the indexes that are defined for the same (In the above example the
> DocumentId field is defined as a Clustered Index).
>
>
> Now my question is - if a cross database join is used across 2 SQL
> Server 2005 databases; does the provider not take the advantage of
> both databases being SQL Server (2005) and increase the performance
> of
> the query. Does it still try to add all the overhead of treating this
> as a remote query rather than a specific SQL Server query
>
>
> Thanks
>
>
> Regards
>
>
> Nitesh
>

Are all your drivers up to date? click for free checkup

28 Feb 2007 12:05 PM

VIKING

On Feb 28, 2:36 pm, nns***@gmail.com wrote:
Show quoteHide quote

> Hi,
>
> I am using 2 servers with SQL Server 2005 installed on them. I have 1
> Database (say the Corporate Database) on Server A and the other
> databases (say the Department Database) on Server B. All the core
> tables that are shared across all the departments are stored in the
> Corporate Database (say Users, Groups, Roles, Categories etc) and all
> the Department specific information is saved in the Department
> Database (say Orders, Products etc).
>
> I have created a linked server (Server Type as SQL Server) on Server
> A
> to connect to Server B. All my Stored Procedures are running from
> Server A. My Stored Procedure looks like this
>
> CREATE PROCEDURE [Company].[GetDocuments]
> (
>         @PolicyNo               varchar(50)
>         ,@DocumentId    bigint = NULL
> )
>
> AS
>
> SELECT
>         doc.DocumentId
>         ,doc.DocumentName
>         ,doc.DocumentTypeId
>         ,doc.PolicyNo
>         ,doc.Title
>         ,doc.FirstName
>         ,doc.LastName
>         ,doc.[FileName]
>         ,dt.DocumentTypeName
>         FROM Company.Documents doc
>         INNER JOIN Corporate.DocumentTypes dt ON dt.DocumentTypeId =
> doc.DocumentTypeId
> WHERE
> AND doc.PolicyNo like @PolicyNo
> AND doc.DocumentId = COALESCE(@DocumentId, doc.DocumentId)
>
> Here if you notice I have used 2 Synonyms a) Corporate which is local
> to Server A ([Server A].[Database]....) and b) Company which is
> equivalent to remote query across Server B ([Server B].
> [Database]....)
>
> When I check the execution plan of the above query - it shows the
> heading for the remote query as "Remote Query" and does not even use
> the indexes that are defined for the same (In the above example the
> DocumentId field is defined as a Clustered Index).
>
> Now my question is - if a cross database join is used across 2 SQL
> Server 2005 databases; does the provider not take the advantage of
> both databases being SQL Server (2005) and increase the performance
> of
> the query. Does it still try to add all the overhead of treating this
> as a remote query rather than a specific SQL Server query
>
> Thanks
>
> Regards
>
> Nitesh

One of the solutions I can think of is writing an sp on Server B's
department database which would gather details of the input parameter
passed from the calling sp on Server A. This way you can cache the
execution plan, and utilize indexes to the best possible extent. This
is what we do at our shop.

Running SQL statements across linked servers had proved to be bad for
performance, forget alone use of indexes.

28 Feb 2007 1:36 PM

nnsoni

Show quote Hide quote

On 28 Feb, 12:05, "VIKING" <msrvik***@gmail.com> wrote:
> On Feb 28, 2:36 pm, nns***@gmail.com wrote:
>
>
>
>
>
> > Hi,
>
> > I am using 2 servers with SQL Server 2005 installed on them. I have 1
> > Database (say the Corporate Database) on Server A and the other
> > databases (say the Department Database) on Server B. All the core
> > tables that are shared across all the departments are stored in the
> > Corporate Database (say Users, Groups, Roles, Categories etc) and all
> > the Department specific information is saved in the Department
> > Database (say Orders, Products etc).
>
> > I have created a linked server (Server Type as SQL Server) on Server
> > A
> > to connect to Server B. All my Stored Procedures are running from
> > Server A. My Stored Procedure looks like this
>
> > CREATE PROCEDURE [Company].[GetDocuments]
> > (
> >         @PolicyNo               varchar(50)
> >         ,@DocumentId    bigint = NULL
> > )
>
> > AS
>
> > SELECT
> >         doc.DocumentId
> >         ,doc.DocumentName
> >         ,doc.DocumentTypeId
> >         ,doc.PolicyNo
> >         ,doc.Title
> >         ,doc.FirstName
> >         ,doc.LastName
> >         ,doc.[FileName]
> >         ,dt.DocumentTypeName
> >         FROM Company.Documents doc
> >         INNER JOIN Corporate.DocumentTypes dt ON dt.DocumentTypeId =
> > doc.DocumentTypeId
> > WHERE
> > AND doc.PolicyNo like @PolicyNo
> > AND doc.DocumentId = COALESCE(@DocumentId, doc.DocumentId)
>
> > Here if you notice I have used 2 Synonyms a) Corporate which is local
> > to Server A ([Server A].[Database]....) and b) Company which is
> > equivalent to remote query across Server B ([Server B].
> > [Database]....)
>
> > When I check the execution plan of the above query - it shows the
> > heading for the remote query as "Remote Query" and does not even use
> > the indexes that are defined for the same (In the above example the
> > DocumentId field is defined as a Clustered Index).
>
> > Now my question is - if a cross database join is used across 2 SQL
> > Server 2005 databases; does the provider not take the advantage of
> > both databases being SQL Server (2005) and increase the performance
> > of
> > the query. Does it still try to add all the overhead of treating this
> > as a remote query rather than a specific SQL Server query
>
> > Thanks
>
> > Regards
>
> > Nitesh
>
> One of the solutions I can think of is writing an sp on Server B's
> department database which would gather details of the input parameter
> passed from the calling sp on Server A. This way you can cache the
> execution plan, and utilize indexes to the best possible extent. This
> is what we do at our shop.
>
> Running SQL statements across linked servers had proved to be bad for
> performance, forget alone use of indexes.- Hide quoted text -
>
> - Show quoted text -

Thanks for the Prompt reply......It does make sens to write the stored
procedure in Server B BUT only when the procedure is independant. What
is the best way to go about when you need to join 2 tables in the same
procedure and these tables exist on different databases. One way is
possibly filter out all the data (from the Server B Tables) and return
the same from a stored procedure and dump the records in a TABLE
variable and join with the same. The other possibility is using a view
on server B and joining the same with a table on Server A - but how
would this be different - from the above example

1 Mar 2007 4:10 AM

VIKING

On Feb 28, 6:36 pm, nns***@gmail.com wrote:
Show quoteHide quote

> On 28 Feb, 12:05, "VIKING" <msrvik***@gmail.com> wrote:
>
>
>
> > On Feb 28, 2:36 pm, nns***@gmail.com wrote:
>
> > > Hi,
>
> > > I am using 2 servers with SQL Server 2005 installed on them. I have 1
> > > Database (say the Corporate Database) on Server A and the other
> > > databases (say the Department Database) on Server B. All the core
> > > tables that are shared across all the departments are stored in the
> > > Corporate Database (say Users, Groups, Roles, Categories etc) and all
> > > the Department specific information is saved in the Department
> > > Database (say Orders, Products etc).
>
> > > I have created a linked server (Server Type as SQL Server) on Server
> > > A
> > > to connect to Server B. All my Stored Procedures are running from
> > > Server A. My Stored Procedure looks like this
>
> > > CREATE PROCEDURE [Company].[GetDocuments]
> > > (
> > >         @PolicyNo               varchar(50)
> > >         ,@DocumentId    bigint = NULL
> > > )
>
> > > AS
>
> > > SELECT
> > >         doc.DocumentId
> > >         ,doc.DocumentName
> > >         ,doc.DocumentTypeId
> > >         ,doc.PolicyNo
> > >         ,doc.Title
> > >         ,doc.FirstName
> > >         ,doc.LastName
> > >         ,doc.[FileName]
> > >         ,dt.DocumentTypeName
> > >         FROM Company.Documents doc
> > >         INNER JOIN Corporate.DocumentTypes dt ON dt.DocumentTypeId =
> > > doc.DocumentTypeId
> > > WHERE
> > > AND doc.PolicyNo like @PolicyNo
> > > AND doc.DocumentId = COALESCE(@DocumentId, doc.DocumentId)
>
> > > Here if you notice I have used 2 Synonyms a) Corporate which is local
> > > to Server A ([Server A].[Database]....) and b) Company which is
> > > equivalent to remote query across Server B ([Server B].
> > > [Database]....)
>
> > > When I check the execution plan of the above query - it shows the
> > > heading for the remote query as "Remote Query" and does not even use
> > > the indexes that are defined for the same (In the above example the
> > > DocumentId field is defined as a Clustered Index).
>
> > > Now my question is - if a cross database join is used across 2 SQL
> > > Server 2005 databases; does the provider not take the advantage of
> > > both databases being SQL Server (2005) and increase the performance
> > > of
> > > the query. Does it still try to add all the overhead of treating this
> > > as a remote query rather than a specific SQL Server query
>
> > > Thanks
>
> > > Regards
>
> > > Nitesh
>
> > One of the solutions I can think of is writing an sp on Server B's
> > department database which would gather details of the input parameter
> > passed from the calling sp on Server A. This way you can cache the
> > execution plan, and utilize indexes to the best possible extent. This
> > is what we do at our shop.
>
> > Running SQL statements across linked servers had proved to be bad for
> > performance, forget alone use of indexes.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for the Prompt reply......It does make sens to write the stored
> procedure in Server B BUT only when the procedure is independant. What
> is the best way to go about when you need to join 2 tables in the same
> procedure and these tables exist on different databases. One way is
> possibly filter out all the data (from the Server B Tables) and return
> the same from a stored procedure and dump the records in a TABLE
> variable and join with the same. The other possibility is using a view
> on server B and joining the same with a table on Server A - but how
> would this be different - from the above example

I did mention earlier, you let the sql server cache the plan and your
indexes will be useful when you are retrieving data from server
B.database. Querying thro' a view is much slower than querying from
the base tables.

Thread options