Hello,
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MB
Just use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the desired size. The
specified file size will be persisted and used upon following startups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup script for Alter tempdb
> database.
> Thanks in advance.
> MB
>
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Friday, March 30, 2012
Increase Default Tlog size of Tempdb
Hello,
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MBJust use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the de
sired size. The
specified file size will be persisted and used upon following startups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...en">
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But
the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup sc
ript for Alter tempdb
> database.
> Thanks in advance.
> MB
>
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MBJust use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the de
sired size. The
specified file size will be persisted and used upon following startups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...en">
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But
the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup sc
ript for Alter tempdb
> database.
> Thanks in advance.
> MB
>
Increase Default Tlog size of Tempdb
Hello,
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MBJust use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the desired size. The
specified file size will be persisted and used upon following startups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup script for Alter tempdb
> database.
> Thanks in advance.
> MB
>
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MBJust use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the desired size. The
specified file size will be persisted and used upon following startups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup script for Alter tempdb
> database.
> Thanks in advance.
> MB
>
Monday, March 12, 2012
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 ru
n
> 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, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> 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 n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> 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 ru
n
> 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, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> 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 n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> 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...
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with stat
istics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes paral
lelism 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...[vbcol=seagreen]
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>|||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 serve
r
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 st
atistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes par
allelism 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...
>
>|||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 ma
chine). You could try
posting to the group -relationalserver.performance and see if you get some m
ore 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...[vbcol=seagreen]
> Stats IO showed very small differences in number of logical reads. Physic
al
> reads were 0 all around - well cached I guess. I had tried to set the ser
ver
> 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 involve
d
> 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:
>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is th
e 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...
>
>|||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, mayb
e
>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 SA
N
>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
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 ru
n
> 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, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> 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 n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> 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 ru
n
> 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, may
be
> eight (can't tell if its hyperthreaded or not) it takes 30 seconds. Ther
e
> 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 n
o
> change. The timings are repeatable and the only improvement on the
> enterprise class server is shortly after a reboot and even then the result
s
> take 2-4 seconds vs, 1 second on the workstation. This performance quickl
y
> deteriorates on the server. I have tried this with the database both on S
AN
> 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...
>
>|||Similar <> same :-)
What does STATISTICS I/O say. I usually complement execution plans with stat
istics I/O. Also check
for parallelism operators in the plan for the large machine. Sometimes paral
lelism 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...[vbcol=seagreen]
> Yes, execution plans are very similar. Not exactly identical - but all
> components are very similar in costs.
> "Tibor Karaszi" wrote:
>|||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 serve
r
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 st
atistics I/O. Also check
> for parallelism operators in the plan for the large machine. Sometimes par
allelism 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...
>
>|||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 ma
chine). You could try
posting to the group -relationalserver.performance and see if you get some m
ore 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...[vbcol=seagreen]
> Stats IO showed very small differences in number of logical reads. Physic
al
> reads were 0 all around - well cached I guess. I had tried to set the ser
ver
> 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 involve
d
> 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:
>|||Thank you for your help.
"Tibor Karaszi" wrote:
> Hmm, I'm out of ideas, I'm afraid. In general, if the execution plan is th
e 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...
>
>|||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, mayb
e
>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 SA
N
>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
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
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
Subscribe to:
Posts (Atom)