2

I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure

USE [xxxx]
GO
/****** Object:  StoredProcedure [dbo].[MARK_ACTIVE]    Script Date: 4/20/2015 5:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[xxxx]
AS
SET ANSI_NULLS ON
INSERT INTO [123.456.7.890].[DBASE1].[dbo].[TABLE]      (smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX)
SELECT TOP 3 smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX
FROM [098.765.4.321].[[DBASE2].[dbo].[TABLE2] cr
WHERE
NOT EXISTS (SELECT  id,smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX          FROM [123.456.7.890].[DBASE1].[dbo].[TABLE]  c
  WHERE cr.smsdt = c.smsdt)

and here's my job settings

My General enter image description here

My Steps enter image description here

My advance steps enter image description here

My Scheduler enter image description here

When I run the stored proc it executes for less than 10 seconds but when i try to call that stored proc using the job this is what happen

enter image description here

it just keep on in progress state but nothing happens, looks like it got stuck in that state.

6
  • 2
    What are the waits associated - run sp_whoisactive while you run sql agent job ? May be blocking ? How big is the table ? Also, why are you using TOP * without an order by clause ? – Kin Shah Apr 21 '15 at 2:36
  • How large is your msdb.dbo.sysjobhistory table? – user507 Apr 21 '15 at 2:58
  • Now it is not stuck in in progress....it shows executed as user:NT service\sqlserveragent. login failed for user 'nt authority\anonymous logon'.[SQLSTATE 28000](Error 18456). The step Failed when using sql server agent – CheAnne Apr 21 '15 at 2:59
  • 1
    Are you able to run other Agent Jobs or is it just this one that's bombing? – Kris Gruttemeyer Apr 21 '15 at 3:00
  • its the only one that having this error. – CheAnne Apr 21 '15 at 3:08
1

If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure.

Can you please post the linked server configuration for further information?

0

Your code for the query is using two servers, identified by IP address as shown below. Where is the server [123.456.7.890]? And where is the server [098.765.4.321]?

My guess is that [098.765.4.321] is the [RemoteServer] and [123.456.7.890] is the [LocalServer].

First of all, the slowness of the code is not SQL Agent's fault. Code run from SQL Agent is pretty much as efficient or inefficient as code run directly from SSMS. The problem is that you are moving much more data across the linked server than you might think. Here is the outline of your code:

INSERT ... INTO [LocalServer].[DBASE1].[dbo].[TABLE] 
   SELECT TOP 3 ... FROM [RemoteServer].[DBASE2].[dbo].[TABLE2]
   WHERE NOT EXISTS 
         (SELECT ... FROM [LocalServer].[DBASE1].[dbo].[TABLE])

If I have interpreted this correctly, then likely most of the data in [RemoteServer].[DBASE1].[dbo].[TABLE] has to be dragged over to the [LocalServer] and stored in a worktable in tempdb.

It is extremely unlikely that the WHERE NOT EXISTS is processing to find the first 3 matching rows that come across the linked server. Instead it likely waits for the data to be present locally, before processing the worktable data to filtered by the WHERE NOT EXISTS and then choose the TOP 3 rows.

The slowness is due to how much data is being moved between servers and by the likelihood that the [LocalServer] does not have access to the [RemoteServer]'s statistics.

You might also read the Guidelines for Using Distributed Queries at: http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx In part, the document says: "To create the best query plans ..., the query processor must have data distribution statistics from the linked server."

Using OPENQUERY to get the results from a remote server will often result in better performance. When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server. (You will notice that in OPENQUERY the query uses a three-part name, since it runs directly on the remote server.)

Likewise, at times creating a stored procedure to run on [RemoteServer] would allow processing to happen on that server first, then return a more limited data set. After that data is returned, the code on the [LocalServer] can complete the process.

Movement of data is costly to overall performance and moving data between servers is even more costly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.