Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any idea
what is taking up all of the memory?
Thanks, Sean
> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
http://www.aspfaq.com/
(Reverse address to reply.)

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any idea
what is taking up all of the memory?
Thanks, Sean> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
--
http://www.aspfaq.com/
(Reverse address to reply.)

Performance Question

Newbie question on SQL, running 8 processors with 8GB of RAM. All but maybe
500MB of RAM is showing in use on Task Mgr with sql.exe only using
approximately 200MB. Nothing appears to be using the RAM, anyway or any ide
a
what is taking up all of the memory?
Thanks, Sean> Newbie question on SQL, running 8 processors with 8GB of RAM. All but
maybe
> 500MB of RAM is showing in use on Task Mgr with sql.exe only using
> approximately 200MB. Nothing appears to be using the RAM, anyway or any
idea
> what is taking up all of the memory?
Make sure you have checked "show processes from all users" and then click on
Mem Usage to make that column order...
http://www.aspfaq.com/
(Reverse address to reply.)

Performance problems, SP 3 and SP 4

We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedure
s
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLT
P
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries an
d
linked tables.
"Kevin3NF" wrote:

> Not what you want to hear, but this may be one of those times where hardwa
re
> is actually the answer. SQL Server is very heavily dependent on RAM and t
he
> disk subsystem (regardless of the service pack level). IMHO, the processo
r
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in
a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>sql

Performance problems, SP 3 and SP 4

We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedures
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLTP
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.
Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>
|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries and
linked tables.
"Kevin3NF" wrote:

> Not what you want to hear, but this may be one of those times where hardware
> is actually the answer. SQL Server is very heavily dependent on RAM and the
> disk subsystem (regardless of the service pack level). IMHO, the processor
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>

Performance problems, SP 3 and SP 4

We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedures
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLTP
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries and
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardware
> is actually the answer. SQL Server is very heavily dependent on RAM and the
> disk subsystem (regardless of the service pack level). IMHO, the processor
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> > We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> > The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> > 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> > a
> > little behind the current technology.
> >
> > The main application is a Dairy records processing system that was
> > converted
> > from mainframe COBOL to SQL Server. The data resides in 30 user
> > databases,
> > with hundreds of tables, some of them with 10 - 15 million rows.
> >
> > OLTP applications are a very small part of the system. The vast majority
> > is
> > large Transact SQL procedures, many of them 5 - 10,000 lines long with
> > 100's
> > of internal variables and multiple cursor driven processes. These
> > procedures
> > are executed by ACCESS 2000 client applications. The ultimate output is
> > packets of very complex statistical-type paper, pdf, and text file reports
> > that are mailed or emailed to individual dairy farmers and agribusiness
> > consultants.
> >
> > There are many hierarchical table relationships with cascading updates and
> > deletes and lots of smaller procedures inside update, delete, and
> > instead-of-update triggers. The entire system is basically batch driven
> > and
> > there is NO WAY that it could ever be re-written in an OLTP environment.
> >
> > That said, we started production in July of 2003 and had no problems
> > completing an entire days production between 7:00 a.m and 5:30 p.m. We
> > were
> > able to downsize our operations staff and have automated nightly jobs and
> > backups that run unattended overnight.
> >
> > In April of 2003 we installed SP 3 and things went to hell in a hand
> > basket.
> > We had to extend operations till 11:00 p.m to finish the daily production.
> > Faced with having to hire and train a third shift operator, we reverted
> > back
> > to SP 2 and all went well until we installed SP 4 3 weeks ago.
> >
> > I have to admit that SP 4 was a big improvement over SP 3, but it still
> > extended our operations day by 3 hours. I again reverted back to SP 2.
> >
> > I was told by MS technical support that each new version of SQL Server has
> > a
> > differently tuned query optimizer and that our site with it's heavy
> > reliance
> > on complex multi-table outer joins, cursors and procedural processing is
> > the
> > problem. It appears that each new version is being tuned more for small
> > OLTP
> > transactions.
> >
> > I was also told that large "traditional installations" have test-bed
> > systems that exactly duplicate production environments, and that each new
> > service pack is tested by a team of programmers who re-tune all SQL
> > statements to match the parameters of the new query optimizer.
> >
> > We are a small shop with very tight budget, very small staff, and a large
> > application that took us 4 years to convert from mainframe COBOL. We
> > cannot
> > afford to tear our procedures appart, rebuild them, and then re-validate
> > the
> > very complex data, every time a new service pack comes out.
> >
> > Short of buying new more powerful hardware (which we have budgeted for in
> > 2007) is there anything that we can do to make SP 4 as fast and efficient
> > as
> > SP 2?
> >
> > I would appreciate hearing from anyone who has experienced performance
> > problems with SP 3 and SP 4, and what if anything you were able to do
> > abouit
> > it.
> >
>
>

Performance Problems - HELP, PLEASE

One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>sql

Performance Problems - HELP, PLEASE

One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> One of the prime functions for this server is Report services.
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>> One of my SQL2000 sp3 servers has seems to be responding slower.
>> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual
>> processors.
>> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
>> on the C drive is the MASTER, there are no master files on the E drive.
>> One of the prime functions for this server is Report services.
>> I began looking around after one of the report writers mentioned that
>> things were responding slower then in the past, I run this query >
>> use master
>> select * From sysdatabases
>> I get this line for Master>
>> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
>> 00:00:00.000 0 80
>> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
>> If I use EM and look at Properties for the Master DB it shows the data
>> file where it should be>
>> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
>> I then ran
>> use master
>> dbcc checkdb ('master')
>> and there were no errors , the final line read like this>
>> "CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'master'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator."
>> Any and ALL recommendations and suggestions would be most appreciated!!!
>>
>