Linked server performance issue?

Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- HowardCan you show us the query that you are using over the linked server?
Have you tried using OPENQUERY?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"H Lee" <hlee@.prodigy.net> wrote in message
news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
Two SQL Server 2000 SP3a boxes are configured in a linked server
relationship. When performing a Profiler trace on one of them, the results
spit out a whole slew of what appears to be system stored procedures being
called, each having Profiler Logical Disk Reads showing values ranging from
2500 - 50000-- instant targets for possible optimization.
The biggest performance offending stored procs are:
"sp_table_statistics_rowset;2", "sp_columns_rowset", and
"sp_indexes_rowset."
The "ApplicationName" associated with the Profiler rows that contain these
sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
servers in a linked server relationship, and the "ServerName" value is the
other server. These stored procedures are executed many times over and over
again.
We're wondering if some component or aspect of the linked server
relationship, or MSDTC, may be causing these stored procedures to be
executed repeatedly without an end in sight. If linked server relationships
or MSDTC is in fact a very likely source of these stored procedures, then
are they essential for the functionality of linked servers or MSDTC? If
anyone has experienced this behavior or know of it, what might be some other
standard causes, other than 3rd-party application calls, that is commonly
known to execute these sprocs? And, given that the cause is indeed linked
servers, or MSDTC, or some other functionality built into SQL Server, is
there any chance that the frequency of these calls can be lessened or
eliminated?
Thanks,
- Howard|||Vyas, thank you for your suggestions.
Would OPENQUERY eliminate these calls?
We have many queries executing over the linked servers. One of them is the
following:
SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
INNER JOIN
"dbo"."Purge_Tbl" pct
ON
Tbl1001.Appnumber = pct.Phase_Number
where
pct.set_id = 1 and copy_f = 1
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> Can you show us the query that you are using over the linked server?
> Have you tried using OPENQUERY?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "H Lee" <hlee@.prodigy.net> wrote in message
> news:OcM6agMsEHA.2196@.TK2MSFTNGP14.phx.gbl...
> Two SQL Server 2000 SP3a boxes are configured in a linked server
> relationship. When performing a Profiler trace on one of them, the
results
> spit out a whole slew of what appears to be system stored procedures being
> called, each having Profiler Logical Disk Reads showing values ranging
from
> 2500 - 50000-- instant targets for possible optimization.
> The biggest performance offending stored procs are:
> "sp_table_statistics_rowset;2", "sp_columns_rowset", and
> "sp_indexes_rowset."
> The "ApplicationName" associated with the Profiler rows that contain
these
> sproc calls is "Microsoft SQL Server." The "HostName" value is one of the
> servers in a linked server relationship, and the "ServerName" value is the
> other server. These stored procedures are executed many times over and
over
> again.
> We're wondering if some component or aspect of the linked server
> relationship, or MSDTC, may be causing these stored procedures to be
> executed repeatedly without an end in sight. If linked server
relationships
> or MSDTC is in fact a very likely source of these stored procedures, then
> are they essential for the functionality of linked servers or MSDTC? If
> anyone has experienced this behavior or know of it, what might be some
other
> standard causes, other than 3rd-party application calls, that is commonly
> known to execute these sprocs? And, given that the cause is indeed linked
> servers, or MSDTC, or some other functionality built into SQL Server, is
> there any chance that the frequency of these calls can be lessened or
> eliminated?
> Thanks,
> - Howard
>
>|||When doing a normal query as shown below, you are really running the query
on your local machine rather than on the linked server.
OpenQuery allows you to do SQL Passthrough queries that will then execute on
the remote server rather than the local server.
Try it both ways and take a look at the Execution plans..
Rick Sawtell
MCT, MCSD, MCDBA
"H Lee" <hlee@.prodigy.net> wrote in message
news:ufOIfNWsEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Vyas, thank you for your suggestions.
> Would OPENQUERY eliminate these calls?
> We have many queries executing over the linked servers. One of them is
the
> following:
>
> SELECT * FROM "A_Prod"."dbo"."CustomApplicants" Tbl1001
> INNER JOIN
> "dbo"."Purge_Tbl" pct
> ON
> Tbl1001.Appnumber = pct.Phase_Number
> where
> pct.set_id = 1 and copy_f = 1
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:uf97JwSsEHA.2456@.TK2MSFTNGP10.phx.gbl...
> results
being[vbcol=seagreen]
> from
> these
the[vbcol=seagreen]
the[vbcol=seagreen]
> over
> relationships
then[vbcol=seagreen]
> other
commonly[vbcol=seagreen]
linked[vbcol=seagreen]
>sql