Very Computer

After upgrading from SQL 6.5 to SQL 7 performance dropped dramatically.
Queries that used to take 1 minute in 6.5 now have to be killed after an
hour.
The strange thing is that almost no memory is used although 400MB was
dedicated to SQL Server. The CPU usage in SQL 7 is about 50%, it used to
be about 90% in 6.5. The hard disk activity stops after about 30 seconds
of querying. After that nothing seems to happen.
To avoid the influence of network stuff we ran all client processes on
the server.

So SQL 7 does not use the available resources and we don't know the
reason. Does anybody have a clue?

additional data:
SQL Server 7.0 service pack 1
Windows NT 4.0 Service Pack 5
Server: Compaq Proliant

Have you traced the query with profiler, and checked the execution
plans?  What kind of indexes are you using?  Are they fragmented at
all?

--
Shane M Ryan
MCSE+I, MCSD, MCDBA

Sent via Deja.com http://www.deja.com/
Before you buy.

Maybe the optimizer index selection has changed for your queries. We had no
problems when we upgraded to SQL7.
Check the ShowPlan and compare it with the SQL 6.5. At times if a
non-clustered index is selected instead of a clustered index it migt degrade
performance.

We haven't upgraded our systems to SQL7 yet, but I expect nothing
but hard work ahead. I expect queries to break or produce incorrect
results, and I expect performance hogs to appear. And I expect that
we have to deal with the problems one by one.

In a situation like the one above, you should check for locks and
blocks with sp_who and sp_lock. If one process is blocking the other
ones, the latter won't do any work. If if the blocking process has
gone to lunch, the server will simply be sleeping.

And why would a process go to lunch? Well, when studying a customer's
database, one of the bottlenecks I found was a seemingly innocent function
where the client code failed to somehow consume a result set, leading to
that the transaction started in that SP kept* around. And as
the client proceeded to other functions, it acquired more and more
locks, and when it created temp tables, quite a few processes got
hung.

--

This is an incomplete mess.

The database compatibility was changed a few times. It did not affect
performance.
The problems occur when a report writer (Business Objects) fires a SQL query to
SQL 7. There are no hard coded creations of temp tables or SELECT INTO
statements in these queries.
I do not know how the queries are translated within SQL 7. The temp database is
growing to extreme proportions during the query. (1 GB)
How can we influence temporaty table stuff? The problem is that the queries are
generated automatically, so we can not influence them. So if possible we would
like to take care of the problem by changing some preferences in SQL. Is this
possible?
Yes, we have updated the statistics and rebuilt all indexes. DBCC was all fine

BoB

We haven't upgraded our systems to SQL7 yet, but I expect nothing
but hard work ahead. I expect queries to break or produce incorrect
results, and I expect performance hogs to appear. And I expect that
we have to deal with the problems one by one.

In a situation like the one above, you should check for locks and
blocks with sp_who and sp_lock. If one process is blocking the other
ones, the latter won't do any work. If if the blocking process has
gone to lunch, the server will simply be sleeping.

And why would a process go to lunch? Well, when studying a customer's
database, one of the bottlenecks I found was a seemingly innocent function
where the client code failed to somehow consume a result set, leading to
that the transaction started in that SP kept* around. And as
the client proceeded to other functions, it acquired more and more
locks, and when it created temp tables, quite a few processes got
hung.

--

This is an incomplete mess.