Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pnpsql
Posting Yak Master
246 Posts |
Posted - 2013-03-13 : 10:37:50
|
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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-13 : 11:00:37
|
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 OptimizerTG |
|
|
|
|
|