April 17, 2003 at 6:30 am
Hi guys
We recently moved one of our critical databases from a SQL 6.5 (NT4) server to a SQL 2000 SP2 (Win2000) server. It was moved by scripting the database and then the data was BCP'd over.
Strangely enough some of our queries are now performing worse on the SQL 2000 server. Some now actually runs 4x longer! I did a test with simple "SELECT * FROM TABLE1" statements and I had the same problem.
Note that the Win2000 server has much better hardware specs than the old NT4 server.
Any ideas why this is happening?
Thanks
J
April 17, 2003 at 7:03 am
The obvious checks are whether you have got the indexes and statistics on the tables. Depending on how you scripted the database, you can sometimes leave behind the indexes.
This wouldn't explain why a select * from table runs more slowly but might explain why other queries would be slower.
Question - why did you migrate the database the way you did rather than restoring a backup?
Jeremy
April 17, 2003 at 7:35 am
Apply SP3 on 2000 machine. Update statistics, full text catalog if you use them and rebuild all the indexes. Should work fine.
April 17, 2003 at 9:20 am
To answer your question, Jeremy, a backup from SQL 6.5 cannot be restored in SQL 2000.
That's why we had to script the database.
Thanks Hirenk, I'll try your suggestion. Do you perhaps know if it has anything to do with the fact that SP_CONFIGURE 'network packet size' is not functional in either SQL 7 or SQL 2000 (< SP3), thus causing more overhead for packets smaller than 4096 bytes? According to TechNet, it actually defaults to 4096 bytes instead of 512 bytes.
April 17, 2003 at 11:35 am
All of the issues I ever saw with the upgrade were related to the stats changing. Run a full index rebuild (maintenance plan is easiest way to do it), then see.
Andy
April 17, 2003 at 12:10 pm
At the same time run some compares on couple of frequently used table for missing indexes. That does happen sometimes.
April 18, 2003 at 5:13 am
Also take your long queries and see if you can write other ways that are more optimized to the SQL 2K environment. It may be a engine differnece causing the query to react differently.
April 22, 2003 at 2:39 am
Thanks for all your input.
We did a rebuild of all the indexes and the queries seem to be running a bit faster.
I have a feeling that there might be some other processing happening on the SQL Server which is pulling the performance down. Would SQL Profiler be the best tool to monitor the impact of different databases and apps on the server?
April 22, 2003 at 8:09 am
Read this KB article. http://support.microsoft.com/default.aspx?scid=kb;en-us;297864
April 24, 2003 at 6:24 am
We have had the same behavior when we moved from 6.5 to 7.0.
The main raison explaining the poor performance we were experienced on 7.0 was that we were not using clustered indexes at all. On 6.5 this is not a problem but with 7.0 and 2000 the Optimizer doesn't seem to use non-clustered indexes as it should.
Hope it can help you.
Carl
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply