Another SSIS Package Validation Question

We are going to be running a package repeatedly 24/7. The same package against the same data store, filtered using a "stageFlag" so as not to read rows previously processed. We have various timing statistics and have yet to fine tune; but on the surface it appears that it takes approximately three minutes to validate and another three minutes to run. If we have no additional data on the second run it still takes three minutes to complete - to do nothing but skip rows already processed.

Is it possible to set this up to run repeatedly without the validation on each iteration?

Any ideas as to how this would be accomplished would be greatly appreciated.

David,

Try setting DelayValidation=TRUE.

-Jamie

|||I thought this only delayed validation until later - that it still had to be validated before execution. I want it to validate once and then run forever. I will check it out again; but if you've understood my request and could respond that would be great.|||

No this is not possible. We always validate before an execution because the package doesn't "know" that nothing has been changed externally.

Matt

|||

Matt,

A small question. If DelayValidation=FALSE will a task get validated twice. i.e. once at package startup and once immediately prior to the task firing? Or is it one or the other?

I only ask because I'm sure I've seen situations where setting DelayValidation=TRUE on a task causes the whole package to run quicker. Perhaps I was mistaken...

Thanks

Jamie

|||

Hi Jamie,

Setting DelayValidation to false does cause an extra validation to occur because we always validate right before we execute but if delay validation is false then we also validate it up front. You might wonder why we do this since it does slow package execution. The answer is so we catch problems before the package runs for a long time. If you had a package that took 8 hours to run then it would be very useful to catch the error in the first validation pass than to wait until 7 hours had passed before getting to the task that would fail validation, but we give the option to turn it off as needed.

Matt

|||

Dear Matt,

I also have a problem about Pre-Execution Phase that it takes very long time. (Some packages have been taken around 2 minutes, but some is less, and some package could not start execute phase, so I disable it)

Could I have any solution to solve this?

Thank you very much for your help

Nop

Another SSIS Package Validation Question

We are going to be running a package repeatedly 24/7. The same package against the same data store, filtered using a "stageFlag" so as not to read rows previously processed. We have various timing statistics and have yet to fine tune; but on the surface it appears that it takes approximately three minutes to validate and another three minutes to run. If we have no additional data on the second run it still takes three minutes to complete - to do nothing but skip rows already processed.

Is it possible to set this up to run repeatedly without the validation on each iteration?

Any ideas as to how this would be accomplished would be greatly appreciated.

David,

Try setting DelayValidation=TRUE.

-Jamie

|||I thought this only delayed validation until later - that it still had to be validated before execution. I want it to validate once and then run forever. I will check it out again; but if you've understood my request and could respond that would be great.|||

No this is not possible. We always validate before an execution because the package doesn't "know" that nothing has been changed externally.

Matt

|||

Matt,

A small question. If DelayValidation=FALSE will a task get validated twice. i.e. once at package startup and once immediately prior to the task firing? Or is it one or the other?

I only ask because I'm sure I've seen situations where setting DelayValidation=TRUE on a task causes the whole package to run quicker. Perhaps I was mistaken...

Thanks

Jamie

|||

Hi Jamie,

Setting DelayValidation to false does cause an extra validation to occur because we always validate right before we execute but if delay validation is false then we also validate it up front. You might wonder why we do this since it does slow package execution. The answer is so we catch problems before the package runs for a long time. If you had a package that took 8 hours to run then it would be very useful to catch the error in the first validation pass than to wait until 7 hours had passed before getting to the task that would fail validation, but we give the option to turn it off as needed.

Matt

|||

Dear Matt,

I also have a problem about Pre-Execution Phase that it takes very long time. (Some packages have been taken around 2 minutes, but some is less, and some package could not start execute phase, so I disable it)

Could I have any solution to solve this?

Thank you very much for your help

Nop

Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
Josh
Hi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:

> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>
|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.
|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:

> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>
|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:[vbcol=seagreen]
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poor
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most common
causes of intermittently slow queries.
John
"JXStern" wrote:

> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>

Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
JoshHi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:

> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:

> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:[vbcol=seagreen]
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
>|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poo
r
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most commo
n
causes of intermittently slow queries.
John
"JXStern" wrote:

> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>

Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
JoshHi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:
> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:
> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Have you read the section on parameter sniffing in Ken Henderson's
> >"The Guru's Guide to SQL Server Architecture and Internals" ISBN
> >0-201-70047-6
> >also check out the post http://tinyurl.com/983uf
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
>> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
>> <jbellnewsposts@.hotmail.com> wrote:
>> >Have you read the section on parameter sniffing in Ken Henderson's
>> >"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>> >0-201-70047-6
>> >also check out the post http://tinyurl.com/983uf
>> I'm aware of sniffing, but I don't see how it can be that when the
>> same parameters run later give (very!) different runtimes.
>> (benchmark process does not show or validate results).
>> J.
>>|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poor
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most common
causes of intermittently slow queries.
John
"JXStern" wrote:
> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Have you looked at the locking events in SQL Profiler?
> >
> >John
> >
> >"jxstern" wrote:
> >
> >> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> >> <jbellnewsposts@.hotmail.com> wrote:
> >> >Have you read the section on parameter sniffing in Ken Henderson's
> >> >"The Guru's Guide to SQL Server Architecture and Internals" ISBN
> >> >0-201-70047-6
> >> >also check out the post http://tinyurl.com/983uf
> >>
> >> I'm aware of sniffing, but I don't see how it can be that when the
> >> same parameters run later give (very!) different runtimes.
> >>
> >> (benchmark process does not show or validate results).
> >>
> >> J.
> >>
> >>
>