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
Showing posts with label sp3a. Show all posts
Showing posts with label sp3a. Show all posts
Monday, March 26, 2012
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,
- Howard
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
|||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[vbcol=seagreen]
> 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
>
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
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
|||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[vbcol=seagreen]
> 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
>
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...
> > 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
> >
> >
> >
>
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...
> > 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
> >
> >
> >
>
Linked server performance
SQL Server 2000 Standard, SP3a. I have a linked server
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe
defined pointing to DB2, using their DB2 Connect driver.
When I execute a single table query from SQL Server
against the DB2 linked server with a highly selective
where clause using four part naming, it appears as if the
query retrieves all rows from the remote DB2 table into
SQL Server, and then applies the filter. Is there any way
to force the filtering to occur on the remote database?
examples:
runs fast:
select * from OPENQUERY(DB2Server, 'select COL1, COL2
from SCH.TABLE where COL1 = ''4''')
runs slow:
select COL1, COL2 from DB2Server..SCH.TABLE
where COL1 = '4'
Thanks
Vern RabeI don't understand your question. You seemed to have answered it yourself.
If you use OPENQUERY() it will just pass the query through to the remote
server and return the results. If you do not use OPENQUERY it may return
lots of rows due to the way the linked server and the optimizer works. You
obviously found that to be the case so what is wrong with using the first
method?
--
Andrew J. Kelly SQL MVP
"Vern Rabe" <vern@.NO_rabe_SPAM.com> wrote in message
news:0adf01c53c93$fce71970$a501280a@.phx.gbl...
> SQL Server 2000 Standard, SP3a. I have a linked server
> defined pointing to DB2, using their DB2 Connect driver.
> When I execute a single table query from SQL Server
> against the DB2 linked server with a highly selective
> where clause using four part naming, it appears as if the
> query retrieves all rows from the remote DB2 table into
> SQL Server, and then applies the filter. Is there any way
> to force the filtering to occur on the remote database?
> examples:
> runs fast:
> select * from OPENQUERY(DB2Server, 'select COL1, COL2
> from SCH.TABLE where COL1 = ''4''')
> runs slow:
> select COL1, COL2 from DB2Server..SCH.TABLE
> where COL1 = '4'
> Thanks
> Vern Rabe
Monday, March 19, 2012
Linked Server Error
SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?
> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server
.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I c
an
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's usef
ul
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?
> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server
.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I c
an
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's usef
ul
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)
Monday, March 12, 2012
Linked Server Error
SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?
> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I can
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's useful
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?
> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I can
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's useful
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)
Linked Server Error
SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I can
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's useful
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)
using MS OLE DB Provider for DB2, which comes with Host Integration Server.
I have just started to get an error on this LS when trying to use it,
although previously (before Sunday!) it worked fine:
ERROR 7303: Could not initialise data source object of OLE DB provider
'DB2OLEDB'
SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
I have bounced the server and checked the event log but it gives me no
additional information. Logon credentials are OK for linked server and I can
ping the box. Nothing new has been installed on the server.
I've got a stack sump generated when the exception occurred if that's useful
to anyone...?> SQL 2000, Sp3a on Windows 2000. I have a linked server set up to an AS400
> using MS OLE DB Provider for DB2, which comes with Host Integration Server.
> I have just started to get an error on this LS when trying to use it,
> although previously (before Sunday!) it worked fine:
> ERROR 7303: Could not initialise data source object of OLE DB provider
> 'DB2OLEDB'
> SqlDumpExceptionHandler: Process 54 generated fatal exception c000005
> EXCEPTION ACCESS VIOLATION.SQL Server is terminating this process.
> I have bounced the server and checked the event log but it gives me no
> additional information. Logon credentials are OK for linked server and I can
> ping the box. Nothing new has been installed on the server.
> I've got a stack sump generated when the exception occurred if that's useful
> to anyone...?
User submitted from AEWNET (http://www.aewnet.com/)
Subscribe to:
Posts (Atom)