linked server: environment issues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

linked server: environment issues

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2004-06-10 : 06:27:40

Hi,
We are facing performance issues in a new environment...

Background:

we are extracting the data from remote database using link server thru remote Proc call. In our SP, we call the remote SP

insert into #table
exec link.db.owner.SP p1, p2, p3

which returns the recordset. this recordset is captured in #table. SP execution used to take 5- 10 min

Current Scenario:

remote database is moved to new server and we changed the underlying link to point to the new server, and al other link server config properties remain same.

Problem:

Test case 1: testing the connectivity

Exec linkserver.db.owner.SP P1, P2, P3

This works fine on old link server and new link server

Test case 2: testing the connectivity by putting into # table

INSERT #TABLE
Exec linkserver.db.owner.SP P1, P2, P3

This works fine on old link server, BUT IT IS BEEN LONG RUNNING QUERY IN NEW ENVIRONMENT , IT IS BEEN RUNNING FOR HOURS.

Changes in Environments:

On calling side:
No change in any code except point to new server for underlying link server.
No changes in tempdb configuration.

On Remote side:

Old server was single instance server , where as nw server has multiple instances

nw old

processors : 4 4
speed: 2Ghzs 500mega
no of instances 4 1

any light on this?

regards,
Praveen

test script follows:
declare @mmid varchar(10)
declare @from datetime
declare @to datetime

set @mmid ='844923'
set @from = '2004-05-20'
set @to = '2004-05-26'
/*
Create Table #Ship (
SN NVarChar(60) Not Null,
Dlv_Doc_Id NvarChar(10) Null,
fcst_prd_Nm NvarChar(15) Null,
mat_id NVarChar(8) Null ,
lineitem NVarChar(10) Null ,
shipsite NVarChar(5) Null ,
shipdate DateTime Null ,
cst_id NVarChar(22) Null ,
cst_nm NVarChar(35) Null ,
cst_srch_nm NVarChar(11) Null ,
st_hse_nbr NVarChar(35) Null ,
addr_2_nm NVarChar(35) Null ,
addr_cty NVarChar(35) Null ,
rgn_cd NVarChar(6) Null ,
post_cd NVarChar(10) Null ,
sls_ord_id NvarChar(10) Null,
sls_ord_itm_nbr NVarChar(10) Null
)

Insert Into #Ship (sn,dlv_doc_id,fcst_prd_nm,mat_id,lineitem,shipsite,shipdate,cst_id,cst_nm,cst_srch_nm,st_hse_nbr,addr_2_nm,addr_cty,rgn_cd,post_cd,sls_ord_id,sls_ord_itm_nbr)
*/
Execute SNTrax.SNTrax_Public.dbo.SP_Rufus_Ship
@from,--@Last_Run_Date,
@to,--@Now,
@mmid

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-10 : 13:10:12

Have you run SQL Profiler to see what is going on each server? How about Performance Monitor to see if you have any hardware bottlenecks?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources