After server move a query doesn't work anymore

Question

I've moved an ASP+SQLServer application from an old server to a new one. The old one was a Windows 2000 server with MSDE, and the new one runs Windows 2008 with SQL 2008 Express.

Everything is ok, even a little faster, except just one function whose ASP page gives a time out. I've tryed the query within that page in a management query windows and it never ends. In the old server it took about 1 minute to be completed.

Here is the query:

SELECT DISTINCT TBL1.TBL1_ID, REPLACE(TBL1_TITOLO, CHAR(13)+CHAR(10), ’ ’), COALESCE(TBL1_DURATA, 0), TBL1_NUMERO, FLAG_AUDIO 
FROM SPOT AS TBL1 
INNER JOIN CROSS_SPOT AS CRS ON CRS.TBL1_ID=TBL1.TBL1_ID 
INNER JOIN DESTINATARI_SPOT AS DSP ON DSP.TBL1_ID = TBL1.TBL1_ID 
WHERE DSP.PTD_ID_PUNTO = 1044 
AND DSP.DSP_FLAG_OK = 1 AND TBL1.FLAG_AUDIO_TESTO=1 AND TBL1.FLAG_AUDIO_GRAFICO=’A’ AND CRS.CRS_STATO>2 
OR TBL1.TBL1_ID IN (SELECT ID FROM V_VIEW1 WHERE ID IS NOT NULL AND V_VIEW1.ID_MODULO = 403721) 
OR TBL1.TBL1_ID IN (SELECT TBL1_ID FROM V_VIEW2 WHERE V_VIEW2.ID_PUNTO = 1044)  
ORDER BY TBL1_NUMERO 

I've tried to transform the two views in the last lines into tables and the query works, even if it's a little slower than before. I've migrated the database with its backup/restore function. Could it be an index problem? Any other suggestions?

Answer 1

How big are the tables? Sounds like an index problem. Do you have the same indexes on both servers? Did you rebuild the indexes after restoring?

As TiCL suggested, the best way is to compare the execution plans of both servers.

Answer 2

I would also expect things to be faster on the new system, especially if you have also changed the hardware.

When migrating a database from older versions of SQL Server to newer versions of SQL Server, using either the backup-and-restore, detach-and-attach or upgrade-in-place methods, Microsoft recommends reindexing all of the tables in the database. Did you do that? This minimizes fragmentation in the indexes, but it also seems to update/improve the statistics for the tables in some way. Better statistics means better query optimization and (hopefully) shortened query durations.

Depending on how much data we are talking about, the "DISTINCT" and "ORDER BY" clauses may cause significant tempdb use. If your tempdb is somehow slower on the new system than it was on the old system, this could be your problem. It would be very noticeable if the other queries in the system don't use tempdb as much or at all.

"About 1 minute" also sounds suspiciously similar to the default timeout for queries from ASP pages. In other words, if the query used to run in 59 seconds and now it runs in an average of 1 minute and 1 second it may "suddenly" seem like it fails all of the time even though the running time has only gotten a little bit larger. The query time out is adjustable from your ASP code (the "query timeout" setting on the server pertains to linked servers, not client calls from IIS servers), but I would only suggest lengthening the query timeout as a last resort.

Answer 3

I agree with @jesusbolivar, probably with some index chnages you can fix the issue.

Be aware that SQL Server 2005+ has large performance improvements over SQL Server 2000 due to fairly significant changes in the database engine. These changes can bite you if you are not aware of them. A couple of examples:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/da725e78-b483-4894-81ff-4fdba94f500e/

Also http://social.msdn.microsoft.com/Forums/en/transactsql/thread/568bb212-5479-4a12-be83-bec08a894066

To mitigate the issues with these changes, fully test your application to ensure it behaves exactly the same on both versions. For instance, make sure it returns the same data in the same circumstances.