July 5, 2009 at 5:54 am
HI
HOQ CAN I MEASURE THE PERFORMANCE OF A JOB>?
I HAVE A JOB WITH ONE STEP AND IN THIS JOB I LOAP TO THE ROWS IN THE TABLE AND checking the time of each row if it is more than a value so i will delete this row and update a field in other table
i want to find a way to check the performance of my job for 100000 record and to know when this record will be deleted and the delay which it take the job to be deleted
I WANT A WAY TO KNOW HOW TO MEASURE PERFORMANCE I USE NOW SQL SERVER 2008 WHICH TYPE OF THE ALERT WILL HELP ME
AND IF I WANT TO CREATE A JOB DUNAMICALLY HOW CAN I DO IT
July 5, 2009 at 6:37 am
I will add the obvious:
As you spend more time here, you'll see a lot of posts that help people get away from "procedural" Row By Agonizing Row Processing to Set Based Processing. There is no way to manipulate data faster than set based processing.
Since you are concerned with performance of a specific procedure, and since you mentioned that it is doing something row by row, I'm pretty sure if you post your procedure here, we can point out ways to make it orders of magnitude faster.
As far as actually analyzing this procs performance, AFTER you've change dto a more set based approach,you'd want to look at the estimated and actual executed plans, and see if there is something that can benefit from an additional index, or modifying an existing index to INCLUDE columns that might be causing a table scan.
you can try setting things like SET STATISTICS TIME ON and SET STATISTICS IO ON to se some additional information about the proc when you run it;
and you could also declare a datetime variable at the top of your procedure, and return the difference in milliseconds at the end of the proc using DATEDIFF; so you can see how long the procedure says it is taking.
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply