July 18, 2010 at 10:34 pm
Hi,
I am facing an issue with the report running really slow after the migartion of the DB from SQL 2000 to SQL 2005.Cursors are being used for genarting the report.When running the reports againts the SQL 2K instance its hardly taking 30 seconds where as when ran against the new SQL 2005 instance its taking 22 minutes.
I checked for blockings while running the report but could not find any.The memory utilization seems to be fine (the Total server memory is always less than taget server memory).The buffer cash hit ratio is 99%.
Please find the sever configuration for the old and the new Servers
Old
Total server memory: 4 GB
ProcessorCount :4
Server : windows 2003 32 bit
new
Total server memory: 32 GB
ProcessorCount :8
Server : windows 2003 64 bit
I have set the compatibility to 80 on new sql 2005 instance.
I ran the traces while running the report against sql 2K and SQL 2005.When analysed the traces i found that the cursor statemnts used for genearting report are getting executed in no time on old sql 2K but they are taking some time when pointed to new SQL 2005 instance.This issue has been bugging us since quiet some time...Please some one help me..
July 18, 2010 at 11:56 pm
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic954585-65-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2010 at 11:57 pm
Since u have migrated the database, You should do rebuilding indexes as well as Update the statistics. This will definitely help in reducing response time.
July 19, 2010 at 4:46 am
Yes I did rebuild the Indexes...ran update usage,update statistics....but still the issue remains 🙁
July 19, 2010 at 11:37 pm
Check execution plan for cursor-qry as well as the queries participating inside the cursor structure... that might help......
July 20, 2010 at 2:55 am
Generally, using cursors is slowing down performance.
As fas as reports are concerned I'd assume that you can rebuild the query to a set-operation.
It might help, if you'd post the code.
July 20, 2010 at 2:43 pm
Please post the cursor query to help you better.
SKC
July 21, 2010 at 3:19 pm
compare the query execution plans of the cursors from your old and new...im sure there should be some difference in the plan...
July 22, 2010 at 12:09 am
Also if you haven't done so, try having the reports call a stored procedure and pass paramaters as opposed to building the query in the report. THis reduces network traffic and caches the query on the server.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply