Performance difference between SQL 6.5 and SQL 2000 server

Hello,
I've migrated my database from SQL 6.5 to SQL 2000 server.
After migration, some stored procedures are slower in SQL 2000 server
than SQL 6.5 server. The difference in time is very high - sometimes
as high as twice or thrice the time taken in 6.5 environment. The
client once mentioned to us that cursors are slow in SQL 2000 server.
We need a quick solution for this problem. A vast majority of the
procedures create 4-5 temporary tables and do an insert or update on
the temporart tables. Then there are some select statements from the
temporary tables.
I've rebuilt the indexes, updated the statistics, created tempdb device
on a separate drive. But no use.
Any help will be greatly appreciated.Ravi,
I would look at the settings for the database as well. If you have Auto
Close, Auto Shrink, Auto Create Stats and other settings checked that could
be some of your problem.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138108502.952649.250460@.z14g2000cwz.googlegroups.com...
> Hello,
> I've migrated my database from SQL 6.5 to SQL 2000 server.
> After migration, some stored procedures are slower in SQL 2000 server
> than SQL 6.5 server. The difference in time is very high - sometimes
> as high as twice or thrice the time taken in 6.5 environment. The
> client once mentioned to us that cursors are slow in SQL 2000 server.
>
> We need a quick solution for this problem. A vast majority of the
> procedures create 4-5 temporary tables and do an insert or update on
> the temporart tables. Then there are some select statements from the
> temporary tables.
> I've rebuilt the indexes, updated the statistics, created tempdb device
> on a separate drive. But no use.
> Any help will be greatly appreciated.
>|||Also ensure that in SQL 2000 Server:
Size of the Tempdb is large enough
Tempdb it is set to Autogrow by considerable size
There is sufficient Free Space in your application's database
Your application's DB is set to Autogrow by considerable size
Please let us know if this helped you.
"Chris Wood" wrote:

> Ravi,
> I would look at the settings for the database as well. If you have Auto
> Close, Auto Shrink, Auto Create Stats and other settings checked that coul
d
> be some of your problem.
> Chris
> <Ravi.Meenakshi@.gmail.com> wrote in message
> news:1138108502.952649.250460@.z14g2000cwz.googlegroups.com...
>
>|||The settings of the tempdb is same in 6.5 and 2000. There's enough
space in the DB. We've even moved tempdb device on a different drive.
With all these, still the performance is very bad in 2000. The question
is, with all the other factors and configuration options remaining the
same between 6.5 and 2000, why is there such a big difference in
performance?|||When we went from 6.5 to 7.0 we found that SQL needed bigger and faster
hardware. SQL 7's requirements are higher than 6.5. Did you migrate in place
or upgrade to a different server? SQL2000 is better than SQL 7 but has the
same sort of hardware requirements.
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138196134.975962.278020@.g43g2000cwa.googlegroups.com...
> The settings of the tempdb is same in 6.5 and 2000. There's enough
> space in the DB. We've even moved tempdb device on a different drive.
> With all these, still the performance is very bad in 2000. The question
> is, with all the other factors and configuration options remaining the
> same between 6.5 and 2000, why is there such a big difference in
> performance?
>|||We've migrated to a different server, but the hardware configuration of
6.5 server and 2000 server are the same. Does this mean that SQL 2000
server needs better hardware than 6.5?|||Can you post an example? Include the Inout & Output tables definitions
if you can.
-- Barry Young|||What are the hardware specs for the 2 servers?
Chris
<Ravi.Meenakshi@.gmail.com> wrote in message
news:1138343333.576107.101060@.o13g2000cwo.googlegroups.com...
> We've migrated to a different server, but the hardware configuration of
> 6.5 server and 2000 server are the same. Does this mean that SQL 2000
> server needs better hardware than 6.5?
>|||I got a few pointers, but not sure how to take this forward.
In all the slow procesures, 5-6 temporary tables are created. A lot of
insert, update and select operations are happening on the temporary
tables. When I ran the profiler to capture the SP:Recompile event I
found that it's happening quite frequently. I read an article in MS
site that for temporary tables SP is recompiled for every 6 rows of
updation to temp tables. Moreover, the SP is very big and internally
calls 3-4 procedures in a cursor loop. I believe all of this will
warrant re-compilation. My question is:
1. Is there a way to turn off re-compilation of procedures?
2. Is there a way to increase the threshold for tempdb data updation -
from 6 rows to 500 or more?
It appears as though the procedure invocation is taking more time in
2000 than 6.5
Could it be because of recompilation? Please see the following
articles:
http://support.microsoft.com/defaul...b;en-us;Q243586
http://support.microsoft.com/defaul...b;EN-US;q195565
queryrecompilation.asp" target="_blank">http://msdn.microsoft.com/library/d...compilation.asp
Any help / suggestions would be greatly appreciated.|||If recompiles caused by Temporary Tables is in fact your problem, then
the last article that you reference has a number of suggestions to
address it:
-- Use Table vairables instead (my preference)
-- Use Temporary tables with [keep plan]
-- Use Temporary tables with [keepfixed plan]
-- Barry Young