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...
>
>