Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mvandoni
Starting Member
12 Posts |
 Posted - 2010-11-13 : 09:39:41
|
| Hi all,last week we had to change the server we were using and we took the occasion to upgrade also the SQL Server from 2000 to 2008.We moved all our database detaching them from the old server and reattaching in the new one. We have some application, developed with access and connected to SQL via ODBC connection, to allow users to manage data using mostly passthrough commands.After migrating we are experiencing some problems which we didn't have with the old server.First of all data import from ascii datafile: we have a C++ program to perform this because all our files have the informations of one single record splitted in N rows on the datafile.This program read and parse the file and for each record run an INSERT INTO command to add data into SQL tables.The time required to perform this operation increased about 10 times from the old to the new server.Second problem with this PT query:SELECT TOP 100 PERCENT dbo.Datfiles.*, dbo.Comuni98.CodIstatAreaGeografica AS Area, dbo.Comuni98.CodIstatCom, dbo.CDC.Wave, dbo.CDC.CDC, dbo.TipoNome.Exp, dbo.AAnn.DataSped, dbo.AAnn.Controllata, dbo.AAnn.Annullata, dbo.AAnn.Sospesa, ....., AAnn.Sky_NoteFROM dbo.AAnn INNER JOIN dbo.CDC ON dbo.AAnn.IDCDC = dbo.CDC.IDCDC INNER JOIN dbo.Datfiles ON dbo.AAnn.Indice = dbo.Datfiles.Indice INNER JOIN dbo.Comuni98 ON dbo.CDC.CODISTAT = dbo.Comuni98.CodIstatCP INNER JOIN dbo.TipoNome ON dbo.AAnn.TipoNome = dbo.TipoNome.Cod LEFT OUTER JOIN dbo.Telefoni_Post_Controlli ON dbo.Datfiles.ID = dbo.Telefoni_Post_Controlli.ID LEFT OUTER JOIN dbo.Telefoni_Pre_Controlli ON dbo.Datfiles.ID = dbo.Telefoni_Pre_Controlli.ID WHERE (dbo.AAnn.ToEdp = 1) AND (dbo.AAnn.Annullata = 0) AND (dbo.AAnn.Sospesa = 0) AND (dbo.AAnn.Check_Ammin = 1) AND (dbo.AAnn.Check_Brief = 1) AND (dbo.AAnn.CkCom = 1) AND (dbo.AAnn.TipoNome <> '02') ORDER BY dbo.Datfiles.Indiceit had no problem on SQL 2000, yesterday we run it for the first time in SQL 2008 and it returned more or less 1 record each second taking more then one hour to perform a task that should require a couple minutes.After all the afternoon trying to change indexing or analizing the query execution plan I tried to eliminate the ORDER BY clause and the situation changed drastically (13 seconds to return around 23000 records).Is there some difference between the two system I don't know about which are causing these problems?Can anybody enlighten me?ThanksMichele |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-13 : 11:36:31
|
| Check that you have done all the steps outlined in the SQL2008 Upgrade Hints and Tips:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230You mentioned that you have been changing indexes, but if you have not rebuilt all indexes and all statistics then SQL2008 will most likely perform very badly.You should also consider changing the Compatibility ModeAnd, a bit late now, you should perform a full regression test.Having already moved off SQL2000 you have lost the opportunity to use the Upgrade Advisor, and to fix some types of database corruption which may now come to light - let's hope they are not present. |
 |
|
|
mvandoni
Starting Member
12 Posts |
Posted - 2010-11-13 : 11:55:12
|
The problem is that we didn't upgraded the system on the same server but we have installed sql 2008 on another newer server and then moved all DB there.quote:
You mentioned that you have been changing indexes, but if you have not rebuilt all indexes and all statistics then SQL2008 will most likely perform very badly.
I tryed also to rebuild indexes but nothing changed. And the difference in execution time between the query with and without ordering is really too big to be explained with a not rebuilt index.I'm speaking of tables which are not really big (max 25000 records) so I really cannot understand what can cause such a slow response. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-14 : 05:07:57
|
| "And the difference in execution time between the query with and without ordering is really too big to be explained with a not rebuilt index."Its not to do with a "rebuilt index", it is that the indexes and statistics will be in SQL2000 format, until they have been rebuilt - and then they will be in the native-format and SQL2008 needs.If you've got it on a different machine you can produce a query plan from the old server and the new one and compare them to see what the difference is.Its also possible that the I/O throughput on the new server is (for some reason) inadequate. Or it could be that memory is wrongly tuned. |
|
|
|
mvandoni
Starting Member
12 Posts |
Posted - 2010-11-16 : 10:09:55
|
| Ok probably we found where the problem is.IT people installed the new server with w2003Server 32bit so it is using only 4Gb RAM on 32 installed on the machine.This can explain why the new system is performing worst then the previous one.Now I asked them to reinstall it with 64bit version and then I'll have to reinstall SQL Server too.Do you think it is possible to backup the master db and restore it after reinstalling the server? Or is it better to script all objects and use scripts on the new installation (it will require a lot more time)?ThanksMichele |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-16 : 12:53:50
|
| Not sure about restoring MASTER. Have you got much that is in master? Just permissions perhaps? |
|
|
|
|
|
|
|
|
Problems after migrating form SQL Server 2000