SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

pnpsql
Posting Yak Master

India
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

TG
Flowing Fount of Yak Knowledge

USA
6062 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.

EDIT:
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
TG

Edited by - TG on 03/13/2013 11:03:41
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.03 seconds. Powered By: Snitz Forums 2000