Behavior of SPs from SQL 2000 to SQL 2005

Last Post 26 Mar 2008 01:45 AM by SQLUSA. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
eolivera
New Member
New Member

--
08 Oct 2007 03:08 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 inmediately, 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 inmediately 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 inmediately. 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 inmediately. 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 inmediately 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
eolivera
New Member
New Member

--
08 Oct 2007 08:20 AM
Both were restored from the same backup.
Ed
Lee
New Member
New Member

--
26 Mar 2008 01:16 AM
You need to run DBCC CHECKDB, DBCC UPDATEUSAGE, rebuild all of the indexes, and update stats when moving to a new version, instance, or server. Parameter sniffing has been greatly curtailed in SQL 2005; if you want to test this simply top and restart the service and then execute it several times with common parameters.

Lee
BLOG :: www.texastoo.com
SQLUSA
New Member
New Member

--
26 Mar 2008 01:45 AM
Make sure to

rebuild dynamic table indexes with FILL FACTOR 70
average table with FILL FACTOR 90
static tables with FILL FACTOR 100

Dynamic tables should be reindexed nightly, every other day or weekly depending on response needs and deterioration (fragmentation).

Kalman Toth, Business Intelligence Architect
SQL 2005 Business Intelligence Workshop NYC APR 21-24: http://www.sqlusa.com
jorge.rivera
New Member
New Member

--
09 Apr 2008 03:30 PM
I think we have some ways to try fix the problem,

first, as all people says, the maintenance plans are the first step

Second, in the old sql 2005 where the query ran ok, review indexes and statistics created and compare to sql 2005 where the performance is bad.

third, execution plans changed from sql2000 to sql2005, in the old sql2005 where the test run same as sql2000, trace again using profiler and include in the capture, execution plan and compare it to sql 2005 where the querys take a long time, use the same profiler.

fourth, problem in middle tier, if you can, try using sql 2000 in the server where sql2005 takes a long time to return rows, just to verify isn't a network or hardware problem, if you have the same problem as sql2005, may be the hardware need to be tuned, if the response is good, then your hardware is ok and we need check the .NET driver, may be there is some issue.

as gunneyk it may be is a problem in the how sql 2005 complie and re-compilation SPs works, but it think the middle tier

Expect it can help you to find error..
You are not authorized to post a reply.

Acceptable Use Policy