October 8, 2007 at 6:15 am
The middle tier development group has a set of unit tests that they run against the database. The one I am concerned with, the one that does not return data immediately, inserts the foreign key entries prior to inserting three rows in the table that is going to be queried. In SQL Server 2000, the stored procedure used to retrieve the test rows returns immediately with the correct number of rows. But in SQL 2005, the stored procedure does not return anything after the .Net unit test completes. Developers copied the stored procedure call to a management studio window and it takes over a minute for that data to be returned while adhoc queries return immediately. What's more confusing, an older SQL 2005 installed on an old run down server, the play server, behaves as SQL 2000 and returns data immediately. None of the three servers that we have created for the migration do. I traced the entire unit test to reproduce it from management studio and the data is returned immediately on the server where it's taking more than a minute from the .Net middle tier. I checked the configuration of both SQL 2005, the new 2005 and the old tired sever and they appear to be identical. Both servers have SP1. If anyone has experienced this issue, I would appreciate if he shares the solution with me.
Regards,
Ed
October 8, 2007 at 6:25 am
Did you compare the execution plans? If that is the issue, you could copy the execution plan from the fast executing pre-service pack version.
Regards,
Andras
October 8, 2007 at 6:36 am
Hi
How did you port your sql2000 db to sql 2005.
are the statistics and indexes updated.
Also check connection settings from the middle tier.
If you have a test server you can upgrade to the latest service pack and try it out.
"Keep Trying"
October 8, 2007 at 6:40 am
No, I didn't do that. If they are different, how can I copy an execution plan from one server to another?
Thanks,
Ed
October 8, 2007 at 6:44 am
Eduardo Olivera (10/8/2007)
No, I didn't do that. If they are different, how can I copy an execution plan from one server to another?Thanks,
Ed
Ed,
Have a look at http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
But, before you read it, please look at the execution plans, see if they are indeed the reason for the performance difference, make sure your statistics are up to date. Forcing execution plans should really really be the last solution 🙂
Regards,
Andras
October 8, 2007 at 6:45 am
Hi Chirag,
I moved backups from the 2000 server and restored the databases to the 2005 server. I did reindex and ran update statistics. I don't know about the .net connections because I don't deal with that but I could ask.
Thanks,
Ed
October 8, 2007 at 6:50 am
Thanks, Andras, I will look into this option.
Ed
October 10, 2007 at 7:14 am
Thank you all, the problem we were having was due to a difference of about a minute between the middle tier server and sql server. They were running a minute ahead so the timeout was set from the very moment they submitted the procedure. Apparently they are still having other issues but, since I haven't heard from them, they haven't pinned on me.;)
Regards,
Ed
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply