|
| Hello,
I have a distributed query running on a SQL Server 2000 SP4 machine,
accessing Server of same. While logged into the prod server using Remote
Desktop, in Query Analyzer, query runs in aproximately 10 seconds. I have
the exact same linked server on the test machine and the query runs in 1
second. The developer says the performance started to degrade a couple days
ago. I'm unaware of any server changes that occurred to cause this slowdown.
The original query accesses local and remote tables, but for testing
purposes, I stripped it down to just access the remote server XXXX111.
The query:
select
H.actn_request_nbr as AR_NUMBER
, convert(nchar(8),H.request_date, 1) as AR_DATE
, A.assgn_org as RG
, H.problem_desc as AR_TITLE
from XXXX111.PIMS.dbo.ar_header H
inner join XXXX111.PIMS.dbo.ar_action_plan A
on H.actn_request_nbr = A.actn_request_nbr
where H.request_date = '02/09/2006'
order by H.actn_request_nbr ;
I also created a profiler trace for both the slow (prod) and fast (test)
runs and found the following for the slow run:
- sp_table_statistics_
rowset for the two query tables returns apx 10,000
more reads than the faster run
- sp_cursorfetch executed what looks like hundreds of times
The fast running query has no sp_cursorfetch call and seems to get the
results with a sp_prepexec with a select statement.
Note: I tried creating a linked server with the OLE DB SQL Server provider
and that didn't improve the performance. Also tried update statistics on the
two tables to no avail.
Any ideas as to what maybe causing this?
Thanks,
Dan
|
|