Random run duration for a SQL job

Question

Post reply

Random run duration for a SQL job

elaelian.prelani

Valued Member

Points: 72
More actions
May 3, 2012 at 4:16 am

#392178

Hi,
I have an issue that I cannot understand anymore...
I have a server A (Windows Server 2003) hosting a database.
I have developed a DTS to save in this database some data for other databases from other servers.
This DTS is runned each morning with MS SQL Server (automatic execution).
I don't understand why every day, the run duration is random. Sometimes, the job spends 15 minutes to be done, sometimes, it spends 30 minutes...
Do you know please how I can begin me research to solve this problem?
Thans you in advance,
Elaelian

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

There can be a lot of reasons for this, mostly they fall into two broad categories

Resource contention

--Network

--Disk

--Processor

--Pretty much anywhere a bottleneck can occur.

Database contention

--table locking at the source

--table locking at the destination

--resource waits within the database

--Etc.

Since this is a DB forum, you can start by running some traces on the source and destination systems at the times when your job is running. You should also look for other jobs that may be running at the same time before running the trace.

A cheap way to try to fix this is to simply change the schedule of the job 15 minutes earlier or later to see if that makes any difference.

elaelian.prelani Valued Member Points: 72 More actions · Answer 2

I thought that locks can be the issue.

I want to use SQL Profiler to trace the server activities, especially locks.

Is it possible to run the trace automatically at 5am?

By the way, I tried to make start my job at 5.15 instead of 5.00. We'll see!

elaelian.prelani Valued Member Points: 72 More actions · Answer 3

Ok, so it doesn't make anything when I make my job started at 5.15am.

I've contacted the server administrator, he moved my virtual server to another physical server, where there is less traffic. But there's no changes...

I want to check if there's any lock while updating my database at 5am. Last time, I make SQL Profiler run on another pc since 8pm but it ended by itself at 2am... The best thing would be that I can make run SQL Profiler automatically at 5am... Any idea?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

What are you doing in the package?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

elaelian.prelani Valued Member Points: 72 More actions · Answer 5

Hi Koen,

In general, this package has to import some tables from several databases hosted by different server to my database hosted on my own server.

To be more precise, for each database, I began to backup my own database, to prevent error while importing the new data. Then I truncate my tables. Then I use the data flow tasks to import data from remote tables to my database. If all is ok, I delete backuped tables from my database and then I make the same steps for the other databases.

Here you can see a model of my package. Maybe it can help!

This night, I made SQL Profiler run on another pc but I slows down the job so I killed it...

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

elaelian.prelani (5/10/2012)
In general, this package has to import some tables from several databases hosted by different server to my database hosted on my own server.

Set up steps to record the start and end of each independent step. Then for any that vary, investigate what the above posters referred to on every server and device those steps use.

For logging pure SQL jobs, I use a simplistic copy/paste method - copy the post-statement lines, change the text description:

SET NOCOUNT ON;

DECLARE @dtPreviousDateTimeUTC AS DATETIME2(3); -- used for more advanced output.

DECLARE @msg VARCHAR(8000); -- used for debugging/timing/rowcount messages

SET @dtPreviousDateTimeUTC = GETUTCDATE()

select top 5000 * from sys.all_columns -- example SQL

SET @msg = RIGHT(' ' + CONVERT(VARCHAR(15),@@ROWCOUNT),15) + ' rows, ' + CONVERT(VARCHAR, GETUTCDATE(),21) + ', sec: ' + RIGHT(' ' + CONVERT(VARCHAR(15),CONVERT(DECIMAL(15,3),DATEDIFF(ms,@dtPreviousDateTimeUTC,GETUTCDATE())/1000.0)),15)

+ ', ' + ' YourFirstStep'

SET @dtPreviousDateTimeUTC = GETUTCDATE()

RAISERROR(@msg,0,1) WITH NOWAIT -- PRINT gets batched up. RAISERROR .. WITH NOWAIT does not, but RAISERROR doesn't like operations, and the legacy PRINT statements used operations.

select * from sys.databases -- example SQL

SET @msg = RIGHT(' ' + CONVERT(VARCHAR(15),@@ROWCOUNT),15) + ' rows, ' + CONVERT(VARCHAR, GETUTCDATE(),21) + ', sec: ' + RIGHT(' ' + CONVERT(VARCHAR(15),CONVERT(DECIMAL(15,3),DATEDIFF(ms,@dtPreviousDateTimeUTC,GETUTCDATE())/1000.0)),15)

+ ', ' + ' YourSecondStep'

SET @dtPreviousDateTimeUTC = GETUTCDATE()

RAISERROR(@msg,0,1) WITH NOWAIT -- PRINT gets batched up. RAISERROR .. WITH NOWAIT does not, but RAISERROR doesn't like operations, and the legacy PRINT statements used operations.