asked
Procedure Hangs from SQL Agent Job, Same Proc Runs fine in SSMS 2008 R2 Linked Servers
Hello, I have a Linked server to another SQL Server 2008 R2, and a stored procedure which executes queries utilizing both the local server and the linked server. When the procedure executes from a step in a SQL Agent Job, it intermittently hangs\slows down to a crawl (10-15 minutes to execute). When I stop the sql agent job and execute the procedure from SSMS, it executes just fine and normally under 30 seconds. Any ideas why this only happens from SQL Agent and how to fix this? Here is a sample of the batch sql executing from the procedure: --JWP Modified 4/3/14: Had client deploy truncate statement to stored proc Print 'Delete From RB_ImportShipmentBatches' Delete From [lnkServer1].AAD.dbo.RB_ImportShipmentBatches EXEC [lnkServer1].AAD.dbo.usp_import_RB_ImportShipmentBatches Declare @BatchCount INT Select @BatchCount = Count(*) From DXBatchQueue WHERE TableName = 't_al_host_shipment_master' AND DateProcessed Is Null PRINT 'Starting Unprocessed Batch Count = ' + Convert(Varchar,@BatchCount) If @BatchCount = 0 Begin PRINT 'Nothing to do' RETURN End Print 'Delete From ImportShipmentDetail Pass #1' Delete isd From ImportShipmentDetail isd JOIN ImportShipmentMaster ism ON isd.OrderNumber=ism.OrderNumber JOIN [lnkServer1].AAD.dbo.RB_ImportShipmentBatches tb ON ism.HostGroupID = tb.HostGroupID ...
It sounds like you are having problems with the statistics when you are executing the query via linked server. Basically the login used for the linked server does not have permissons to use stats on the remote servers so the queryplan produced is really crappy and will take forever like in your case. When you execute via SSMS you get a nice plan because you are able to access the stats on the remote server. **From BOL:** In order to view the statistics object, the user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role. So your linked server login needs above permissions for your query to be efficient. [Read this, top three linked server performance killers.][1] [1]:
http://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/
code formatting was off: Delete From [lnkServer1].AAD.dbo.RB_ImportShipmentBatches EXEC [lnkServer1].AAD.dbo.usp_import_RB_ImportShipmentBatches Declare @BatchCount INT Select @BatchCount = Count(*) From DXBatchQueue WHERE TableName = 't_al_host_shipment_master' AND DateProcessed Is Null PRINT 'Starting Unprocessed Batch Count = ' + Convert(Varchar,@BatchCount) If @BatchCount = 0 Begin PRINT 'Nothing to do' RETURN End Print 'Delete From ImportShipmentDetail Pass #1' Delete isd From ImportShipmentDetail isd JOIN ImportShipmentMaster ism ON isd.OrderNumber=ism.OrderNumber JOIN [lnkServer1].AAD.dbo.RB_ImportShipmentBatches tb ON ism.HostGroupID = tb.HostGroupID ...
15 People are following this question.
