Linked Server timeout - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Linked Server timeout

Author

Topic

rawilki
Starting Member

5 Posts

Posted - 2012-04-17 : 11:05:34

I am running a stored proc within a sql job. Code uses linked server in a SELECT statement. SQL on both servers is 2008 R2. Job receives error:
Executed as user: STAFF\<Acct>.
The OLE DB provider "SQLNCLI10" for linked server "xxx" reported an error.
Execution terminated by the provider because a resource limit was reached. [SQLSTATE 42000] (Error 7399)
Cannot fetch the rowset from OLE DB provider "SQLNCLI10" for linked server "xxx". . [SQLSTATE 42000] (Error 7421)
OLE DB provider "SQLNCLI10" for linked server "xxx" returned message "Query timeout expired".
[SQLSTATE 01000] (Error 7412).
The step failed.

I can run SELECT statement manually within SSMS just fine (11 minutes).
Does this relate to SQL Server Connections property of Remote Query Timeout ?
Any help is appreciated.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-17 : 12:16:49

Why is it taking 11 minutes ? that is too long. Can you give us more info such as table sizes, indexes used etc. what exactly are you doing?
is the linked server a production server?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-17 : 13:30:36

i would first focus on execution plan for procedure and see if i can optimise it before looking into linked server details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rawilki
Starting Member

5 Posts

Posted - 2012-04-17 : 15:02:46

Selecting from 7 joined tables. Table on linked server has 3,627,470 rows. It joins another table on linked server having 6,619,398 rows.
SET NOCOUNT ON
DECLARE @today DATETIME = GETDATE()
DECLARE @rowcounts TABLE
(mergeAction NVARCHAR(10))
DECLARE
@insertCount INT,
@updateCount INT,
@deleteCount INT

/***********************************************/
-- temporary table to hold data from staging
/***********************************************/
declare @LAS as table (
[CustomerID] [int] NOT NULL,
[PlantID] [int] NULL,
[CustomerLevelID] [char](2) NULL,
[AssessmentDate] [date] NOT NULL,
[AssessmentTypeID] [int] NOT NULL,
[SubjectArea] [varchar](50) NOT NULL,
[AchievementLevelID] [int] NULL,
[SchoolYearID] [int] NULL,
[AssessmentPeriod] [int] NULL,
[AssessmentKeyID] [int] NULL,
[RawScoreValue] [varchar](16) NULL,
[PctCorValue] [varchar](50) NULL,
[SubTestNum] [int] NULL,
[SubTestAchievementLevelID] [int] NULL,
[SubTestSubject] [varchar](50) NULL,
[SubTestRawScore] [varchar](16) NULL,
[SubTestPctCor] [varchar](16) NULL,
[D1Subject] [varchar](50) NULL,
[D1RawScore] [varchar](16) NULL,
[D1PctCor] [varchar](16) NULL,
[D2Subject] [varchar](50) NULL,
[D2RawScore] [varchar](16) NULL,
[D2PctCor] [varchar](16) NULL,
[D3Subject] [varchar](50) NULL,
[D3RawScore] [varchar](16) NULL,
[D3PctCor] [varchar](16) NULL,
[D4Subject] [varchar](50) NULL,
[D4RawScore] [varchar](16) NULL,
[D4PctCor] [varchar](16) NULL,
[D5Subject] [varchar](50) NULL,
[D5RawScore] [varchar](16) NULL,
[D5PctCor] [varchar](16) NULL,
[D6Subject] [varchar](50) NULL,
[D6RawScore] [varchar](16) NULL,
[D6PctCor] [varchar](16) NULL,
[D7Subject] [varchar](50) NULL,
[D7RawScore] [varchar](16) NULL,
[D7PctCor] [varchar](16) NULL,
[D8Subject] [varchar](50) NULL,
[D8RawScore] [varchar](16) NULL,
[D8PctCor] [varchar](16) NULL,
[D9Subject] [varchar](50) NULL,
[D9RawScore] [varchar](16) NULL,
[D9PctCor] [varchar](16) NULL)

/****************************************************************************/
-- Load data from staging on server xxx to the temp table
/****************************************************************************/
INSERT INTO @LAS
SELECT
C.CustomerID,
SCH.PlantID,
case when G.CustomerLevelID IS NOT NULL then G.CustomerLevelID
when CH.CustomerLevelID IS NOT NULL then CH.CustomerLevelID
else 'UK' end,
C.AssessmentDate,
C.AssessmentType,
C.SubjectArea,
C.AchievementLevelID,
C.SchoolYearID,
C.AssessmentPeriod,
K.AssessmentKeyID,
convert(varchar(16),C.RawScrValue),
convert(varchar(16),C.PctCorValue),
SubTestNum,
SubTestAchievementLevelID,
SubTestSubject,
convert(varchar(16),SubTestRawScr),
convert(varchar(16),SubTestPctCor),
D1Subject,
convert(varchar(16),D1RawScr),
convert(varchar(16),D1PctCor),
D2Subject,
convert(varchar(16),D2RawScr),
convert(varchar(16),D2PctCor),
D3Subject,
convert(varchar(16),D3RawScr),
convert(varchar(16),D3PctCor),
D4Subject,
convert(varchar(16),D4RawScr),
convert(varchar(16),D4PctCor),
D5Subject,
convert(varchar(16),D5RawScr),
convert(varchar(16),D5PctCor),
D6Subject,
convert(varchar(16),D6RawScr),
convert(varchar(16),D6PctCor),
D7Subject,
convert(varchar(16),D7RawScr),
convert(varchar(16),D7PctCor),
D8Subject,
convert(varchar(16),D8RawScr),
convert(varchar(16),D8PctCor),
D9Subject,
convert(varchar(16),D9RawScr),
convert(varchar(16),D9PctCor)
FROM [xxx].ABC.dbo.AssmtStagingFor_LAS C
INNER JOIN SIS.dbo.Customer S
ON S.CustomerID=C.CustomerID
AND C.AssessmentDate>'1/1/2000'
INNER JOIN SIS.dbo.AssessmentKey K
ON K.AssessmentTypeID=C.AssessmentType
AND K.AssessmentNumber=C.AssessmentNumber
LEFT OUTER JOIN SIS.dbo.School SCH
ON SCH.PlantID=C.PlantID
LEFT OUTER JOIN [xxx].ABC.dbo.AssmtStagingFor_LAS_Detail D
ON D.CustomerID=C.CustomerID
AND D.AssessmentDate=C.AssessmentDate
AND D.AssessmentType=C.AssessmentType
AND D.SubjectArea=C.SubjectArea
LEFT OUTER JOIN SIS.dbo.CustomerLevelType G
ON G.CustomerLevelID=C.CustomerLevelID
LEFT OUTER JOIN SIS.dbo.StudentCohort CH
ON CH.CustomerID=C.CustomerID
AND C.AssessmentDate BETWEEN CH.EffectiveStartDate and CH.EffectiveEndDate

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-17 : 18:06:41

1. @LAS this is not a temp table but a table variable which will kill your performance
Either create a temp table #LAS or a real table you will be truncating or dropping
Make sure you add good indexes on this temp table since I am sure you will be using it with something else
2. Do you have Index on all the fields you are joining on. If so which ones if not which ones

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-17 : 18:12:56

You pretty much might need index on all of these tables


SIS.dbo.Customer							CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS			CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS			AssessmentTypeID
[xxx].ABC.dbo.AssmtStagingFor_LAS			AssessmentNumber
[xxx].ABC.dbo.AssmtStagingFor_LAS			CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS			PlantID

[xxx].ABC.dbo.AssmtStagingFor_LAS			CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS			CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS			AssessmentDate
[xxx].ABC.dbo.AssmtStagingFor_LAS			AssessmentType
[xxx].ABC.dbo.AssmtStagingFor_LAS			SubjectArea
[xxx].ABC.dbo.AssmtStagingFor_LAS			CustomerLevelID

SIS.dbo.AssessmentKey						AssessmentTypeID
SIS.dbo.AssessmentKey						AssessmentNumber
SIS.dbo.School								PlantID
[xxx].ABC.dbo.AssmtStagingFor_LAS_Detail	CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS_Detail	CustomerID
[xxx].ABC.dbo.AssmtStagingFor_LAS_Detail	AssessmentDate
[xxx].ABC.dbo.AssmtStagingFor_LAS_Detail    AssessmentType
[xxx].ABC.dbo.AssmtStagingFor_LAS_Detail    SubjectArea

SIS.dbo.CustomerLevelType					CustomerLevelID
SIS.dbo.StudentCohort						CustomerID
SIS.dbo.StudentCohort						EffectiveStartDate

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

rawilki
Starting Member

5 Posts

Posted - 2012-04-17 : 18:40:18

OK. Thanks for the help!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-17 : 19:15:32

well not done yet. add those indexes and that temp table for LAS with indexes and let's see what happens.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources