Inconsistent Query Performance

Hello!
We are having a problem with one of the queries that an application runs.
This query uses some temp tables (subbed as table variables with no
performance change) and heavily uses some user defined functions. When run
on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
2000 Developer Editions), it returns within a second. When run against a
enterprise class server with 8 GB of RAM and at least four processors, maybe
eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
is nothing else going on with the large server. This will be the only
activity on the system.. I disabled awe and turned off /PAE switch with no
change. The timings are repeatable and the only improvement on the
enterprise class server is shortly after a reboot and even then the results
take 2-4 seconds vs, 1 second on the workstation. This performance quickly
deteriorates on the server. I have tried this with the database both on SAN
or on local disk on the server and there is no measurable difference.
Any ideas on what I should check next?
ThanksSteve,
> Any ideas on what I should check next?
Execution Plans
AMB
"Steve H" wrote:
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||First step is to check the execution plans. Same?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> Hello!
> We are having a problem with one of the queries that an application runs.
> This query uses some temp tables (subbed as table variables with no
> performance change) and heavily uses some user defined functions. When run
> on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> 2000 Developer Editions), it returns within a second. When run against a
> enterprise class server with 8 GB of RAM and at least four processors, maybe
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> is nothing else going on with the large server. This will be the only
> activity on the system.. I disabled awe and turned off /PAE switch with no
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the results
> take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> deteriorates on the server. I have tried this with the database both on SAN
> or on local disk on the server and there is no measurable difference.
> Any ideas on what I should check next?
> Thanks|||Yes, execution plans are very similar. Not exactly identical - but all
components are very similar in costs.
"Tibor Karaszi" wrote:
> First step is to check the execution plans. Same?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> > Hello!
> > We are having a problem with one of the queries that an application runs.
> > This query uses some temp tables (subbed as table variables with no
> > performance change) and heavily uses some user defined functions. When run
> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> > 2000 Developer Editions), it returns within a second. When run against a
> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> > is nothing else going on with the large server. This will be the only
> > activity on the system.. I disabled awe and turned off /PAE switch with no
> > change. The timings are repeatable and the only improvement on the
> > enterprise class server is shortly after a reboot and even then the results
> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> > deteriorates on the server. I have tried this with the database both on SAN
> > or on local disk on the server and there is no measurable difference.
> >
> > Any ideas on what I should check next?
> >
> > Thanks
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
inefficient result, and can be turned off using the MAXDOP hint (set to 1).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>> First step is to check the execution plans. Same?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> > Hello!
>> > We are having a problem with one of the queries that an application runs.
>> > This query uses some temp tables (subbed as table variables with no
>> > performance change) and heavily uses some user defined functions. When run
>> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> > 2000 Developer Editions), it returns within a second. When run against a
>> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> > is nothing else going on with the large server. This will be the only
>> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> > change. The timings are repeatable and the only improvement on the
>> > enterprise class server is shortly after a reboot and even then the results
>> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> > deteriorates on the server. I have tried this with the database both on SAN
>> > or on local disk on the server and there is no measurable difference.
>> >
>> > Any ideas on what I should check next?
>> >
>> > Thanks
>>|||Stats IO showed very small differences in number of logical reads. Physical
reads were 0 all around - well cached I guess. I had tried to set the server
to have SQL Server use only 1 processor which should have the same affect as
the MAXDOP setting = 1 (there is some looping and several queries involved
in this proc). Funny thing is that this behavior is repeatable on other
large servers and the quick performance occurs on other workstations.
"Tibor Karaszi" wrote:
> Similar <> same :-)
> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> > Yes, execution plans are very similar. Not exactly identical - but all
> > components are very similar in costs.
> >
> > "Tibor Karaszi" wrote:
> >
> >> First step is to check the execution plans. Same?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> > Hello!
> >> > We are having a problem with one of the queries that an application runs.
> >> > This query uses some temp tables (subbed as table variables with no
> >> > performance change) and heavily uses some user defined functions. When run
> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> > is nothing else going on with the large server. This will be the only
> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> > change. The timings are repeatable and the only improvement on the
> >> > enterprise class server is shortly after a reboot and even then the results
> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> > or on local disk on the server and there is no measurable difference.
> >> >
> >> > Any ideas on what I should check next?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
close), you tend to get the same performance (or a higher on the high-end machine). You could try
posting to the group -relationalserver.performance and see if you get some more attention there. Or
perhaps even time to open a ticket with MS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve H" <SteveH@.discussions.microsoft.com> wrote in message
news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> Stats IO showed very small differences in number of logical reads. Physical
> reads were 0 all around - well cached I guess. I had tried to set the server
> to have SQL Server use only 1 processor which should have the same affect as
> the MAXDOP setting = 1 (there is some looping and several queries involved
> in this proc). Funny thing is that this behavior is repeatable on other
> large servers and the quick performance occurs on other workstations.
> "Tibor Karaszi" wrote:
>> Similar <> same :-)
>> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
>> check
>> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
>> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
>> > Yes, execution plans are very similar. Not exactly identical - but all
>> > components are very similar in costs.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> First step is to check the execution plans. Same?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
>> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
>> >> > Hello!
>> >> > We are having a problem with one of the queries that an application runs.
>> >> > This query uses some temp tables (subbed as table variables with no
>> >> > performance change) and heavily uses some user defined functions. When run
>> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>> >> > 2000 Developer Editions), it returns within a second. When run against a
>> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
>> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>> >> > is nothing else going on with the large server. This will be the only
>> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
>> >> > change. The timings are repeatable and the only improvement on the
>> >> > enterprise class server is shortly after a reboot and even then the results
>> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>> >> > deteriorates on the server. I have tried this with the database both on SAN
>> >> > or on local disk on the server and there is no measurable difference.
>> >> >
>> >> > Any ideas on what I should check next?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is the same (or cost is very
> close), you tend to get the same performance (or a higher on the high-end machine). You could try
> posting to the group -relationalserver.performance and see if you get some more attention there. Or
> perhaps even time to open a ticket with MS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> news:A24BB7B3-D5EC-40E9-8739-3DB0EB4E9A2C@.microsoft.com...
> > Stats IO showed very small differences in number of logical reads. Physical
> > reads were 0 all around - well cached I guess. I had tried to set the server
> > to have SQL Server use only 1 processor which should have the same affect as
> > the MAXDOP setting = 1 (there is some looping and several queries involved
> > in this proc). Funny thing is that this behavior is repeatable on other
> > large servers and the quick performance occurs on other workstations.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Similar <> same :-)
> >> What does STATISTICS I/O say. I usually complement execution plans with statistics I/O. Also
> >> check
> >> for parallelism operators in the plan for the large machine. Sometimes parallelism is done with a
> >> inefficient result, and can be turned off using the MAXDOP hint (set to 1).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> news:348166BF-09E2-46F7-A42D-C16D337A8C4F@.microsoft.com...
> >> > Yes, execution plans are very similar. Not exactly identical - but all
> >> > components are very similar in costs.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> First step is to check the execution plans. Same?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Steve H" <SteveH@.discussions.microsoft.com> wrote in message
> >> >> news:2E38BDA4-E051-484B-BFF0-0BAEB1A9729E@.microsoft.com...
> >> >> > Hello!
> >> >> > We are having a problem with one of the queries that an application runs.
> >> >> > This query uses some temp tables (subbed as table variables with no
> >> >> > performance change) and heavily uses some user defined functions. When run
> >> >> > on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
> >> >> > 2000 Developer Editions), it returns within a second. When run against a
> >> >> > enterprise class server with 8 GB of RAM and at least four processors, maybe
> >> >> > eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
> >> >> > is nothing else going on with the large server. This will be the only
> >> >> > activity on the system.. I disabled awe and turned off /PAE switch with no
> >> >> > change. The timings are repeatable and the only improvement on the
> >> >> > enterprise class server is shortly after a reboot and even then the results
> >> >> > take 2-4 seconds vs, 1 second on the workstation. This performance quickly
> >> >> > deteriorates on the server. I have tried this with the database both on SAN
> >> >> > or on local disk on the server and there is no measurable difference.
> >> >> >
> >> >> > Any ideas on what I should check next?
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||On Wed, 17 May 2006 13:01:02 -0700, Steve H
<SteveH@.discussions.microsoft.com> wrote:
>We are having a problem with one of the queries that an application runs.
>This query uses some temp tables (subbed as table variables with no
>performance change) and heavily uses some user defined functions. When run
>on a personal workstation (SQL Server 2000 Personal or perhaps SQL Server
>2000 Developer Editions), it returns within a second. When run against a
>enterprise class server with 8 GB of RAM and at least four processors, maybe
>eight (can't tell if its hyperthreaded or not) it takes 30 seconds. There
>is nothing else going on with the large server. This will be the only
>activity on the system.. I disabled awe and turned off /PAE switch with no
>change. The timings are repeatable and the only improvement on the
>enterprise class server is shortly after a reboot and even then the results
>take 2-4 seconds vs, 1 second on the workstation. This performance quickly
>deteriorates on the server. I have tried this with the database both on SAN
>or on local disk on the server and there is no measurable difference.
>Any ideas on what I should check next?
Dunno, but I think I've got it too, or a close relative.
SP uses some permanent temp tables - tables that I truncate at start
of routine, then fill up with about a million records, perhaps
confusing the optimizer somewhat. Maybe I need to trigger more
recompiles or something? Also uses a simple UDF in the (newest
version) select clause only.
On same machine:
SP runs in ten minutes when it feels like it.
SP runs in twenty minutes other times.
SP runs in about 90 minutes other times.
Data is exactly the same.
Of course, production is seeing the longer times.
Not completely controlled for contending loads, but such as there is
does not explain the 20 to 90 jump in any case. It seems to go CPU
bound for extended periods. Stats IO doesn't show anything outrageous
except for the elapsed time - the time spent in the UDF seems shielded
from stats time, btw, thanks MSFT.
--
Unfortunately we're running this on SQL2K sp2, kind of retro, and
don't ask about the hardware!
But it's very frustrating to have this kind of poor performance and
variability for no apparent reason.
Josh