Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 server performance issue
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

246 Posts

Posted - 03/13/2013 :  10:37:50  Show Profile  Reply with Quote
Dear Team,

i have a big PROC CALLED "daily_check_tasks" IN SQL SERVER that runs SOME daily activity procs contained IN it
there ARE various activities ARE performed through these INNER CALLED procedures there is cursors also FOR iteration.

before a week ago the PROC "daily_check_tasks" is executed IN just 45 mins now FROM a week it is takin 3 HOURS TO ececute but there is NO huge DATA changes have been done. ONLY diif is previously it is riunning FOR 2 lacs records
AND now it is running FOR SOME 300 extra.

there ARE SERVER migratuion is done , we have moved TAKE the BACKUP AND restored IN new environment. How can i check that WHERE is the issue BY check environmental
AND cpu TIME analysis AND etc.

challenge everything

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 03/13/2013 :  11:00:37  Show Profile  Reply with Quote
first, please PICK a case and STICK to it preferably LOWER case.
next, do you have any logging of any kind statement by statement so that you can look at things like duration and rowcounts by statement? If not then that is the first thing you should do so you can see where the time is being spent and know where to focus your energy. It could be as simple as using SET NOCOUNT OFF, and before each statement: PRINT getdate(), then save all the output so you can review it and compare to previous runs.

The other path I would take is that because you moved to different hardware I would compare environments. Both the physical hardware as well as the all the sql server and database option configurations.

Be One with the Optimizer

Edited by - TG on 03/13/2013 11:03:41
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000