Job runs slow every seven days...why?
Tom Dacon
updates a table from the preceding day's OLTP traffic on a set of similar
databases. Depending on the size of the database the job steps take various
amounts of time, from ten seconds up to about an hour and a quarter. The
whole job of seven steps takes on average an hour and a half. Except when
the following happens:
For six days out of every seven, the job steps take more or less the same
amount of time, but on the seventh day they take from three to six times as
long. For instance the job step that takes about an hour and a quarter on
six days in a row takes about ten hours on the seventh day.
Each of the job steps has one day out of seven when it's slow, but it's not
the same day for all seven jobsteps.
During the nominal time span of an hour and a half for the whole job no
other time-consuming processes are running that I can identify (this is a
reporting server, not an online OLTP server).
Any suggestions?
Thanks,
Tom
Erland Sommarskog
about what's in the job etc.
I think you should study the pattern more closely. Does step 1 always run
slow on Mondays, step 2 always slow on Tuesday etc? Or is "every seven
days" only approxamite?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Tom Dacon
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D68264A...@127.0.0.1...
> Tom Dacon (tda...@community.nospam) writes:
>
> Interesting. But of course it is impossible to tell with no knowledge
> about what's in the job etc.
>
> I think you should study the pattern more closely. Does step 1 always run
> slow on Mondays, step 2 always slow on Tuesday etc? Or is "every seven
> days" only approxamite?
>
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9D68264A...@127.0.0.1...
> Tom Dacon (tda...@community.nospam) writes:
>
> Interesting. But of course it is impossible to tell with no knowledge
> about what's in the job etc.
>
> I think you should study the pattern more closely. Does step 1 always run
> slow on Mondays, step 2 always slow on Tuesday etc? Or is "every seven
> days" only approxamite?
Thanks for your interest, Erland. I didn't try to include any details of
what the job actually does, because from my study of the symptoms I'm
thinking that there's something systemic going on that does not depend on
the actual details of the SQL. I'm thinking that it might somehow have to do
with recalculation of statistics or something that SQL Server is doing to
the indexes on a periodic basis - the seven-day cycle. Hence my query: I'm
hoping that someone like yourself who knows a lot about what SQL Server does
under the hood might recognize this symptom and have an explanation for it.
The pattern is exact. All of the databases have the same schema and differ
only in their contents and quantities of data. Each job step runs on a
different database, and runs slowly exactly every seventh day, and as I said
the job steps aren't necessarily on the same cycle, although as it happens
they tend to cluster around Tuesday and Thursday. The slowdown factor
seems to be proportional to the size of the databases: the smallest
databases
slow down by around a factor of two, the largest by up to a factor of six or
more.
I haven't identified a competing process but I'm still looking.
In general, the job steps run on a reporting server, on replications of
e-commerce
OLTP databases, which are full of orders and order items and their related
tables
of funds transactions, accounts receivable records, and so forth. The
process
maintains a reporting table which encapsulates the lifetime performance of
each of the order items, in terms of its received revenue, refunds,
delinquencies, accounts receivable, and so forth. Each day a job step
inspects all order items in a database, identifying new orders and any whose
status
has changed since the previous run and bringing up to date the order item's
performance measures. The SQL defeats parameter-sniffing by copying input
parameters to local variables before processing, so I don't think that's
involved in this issue.
I'm continuing to study the data. Any ideas would be welcome.
Tom
Erland Sommarskog
> Thanks for your interest, Erland. I didn't try to include any details of
> what the job actually does, because from my study of the symptoms I'm
> thinking that there's something systemic going on that does not depend
> on the actual details of the SQL. I'm thinking that it might somehow
> have to do with recalculation of statistics or something that SQL Server
> is doing to the indexes on a periodic basis - the seven-day cycle. Hence
> my query: I'm hoping that someone like yourself who knows a lot about
> what SQL Server does under the hood might recognize this symptom and
> have an explanation for it.
No, SQL Server does not have any autonomous processes that are scheduled
to run weekly or somesuch.
> The pattern is exact. All of the databases have the same schema and differ
> only in their contents and quantities of data. Each job step runs on a
> different database, and runs slowly exactly every seventh day,
It was not clear from your original post that the steps were running
in different databases. Am I right to guess that each job runs the
same code, just in different databases?
From what you say now, my guess would be that someone has scheduled
reindexing or similar maintenance jobs to run weekly in each database, but
spreading the jobs, so that different database are maintained on different
weekdays - but at the same time as your job run.
It seems a good idea to investigate the schedule for the server.
Tom Dacon
>
> It was not clear from your original post that the steps were running
> in different databases. Am I right to guess that each job runs the
> same code, just in different databases?
Yes, that's true.
>
> From what you say now, my guess would be that someone has scheduled
> reindexing or similar maintenance jobs to run weekly in each database, but
> spreading the jobs, so that different database are maintained on different
> weekdays - but at the same time as your job run.
>
> It seems a good idea to investigate the schedule for the server.
Thanks, Erland. That'll be my next task.
Tom