Total time jobs run on a day

Question

Total time jobs run on a day

river1

SSC-Insane

Points: 24300
More actions
February 4, 2016 at 11:29 am

#324008

Hi,
I have some performance issues so one of the measures that I would like to take is see what whose the average of time that jobs took to run on some dates.
For exemple:
01.02.2016 - 200 minutes
01.03.2016 - 220 minutes
01.02.2016 - 100 minutes
How can I do this using t-sql can someone help me with a query that retrieves this results?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You know the deal. Post DDL, sample data as INSERT statements and expected results based on that sample data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 2

Create a table for storing the results.

For each stored procedure you want to time, add a variable @StartTime (or choose any other name, especially if this name is already used in the stored procedure). At the start of the stored procedure, set it to CURRENT_TIMESTAMP. At the end of the stored procedure, add a row to your table with the name of the stored procedure, the start time and the end time. You can compute the duration by using DATEDIFF. (You can even add this as a computed column in the table).

This will not store durations when the stored procedure rolls back. If you need that as well, it will become a lot harder.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

can you clarify your understanding of "average"

here is a starter doc for your further thoughts

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/it-s-hard-to-be/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

river1 SSC-Insane Points: 24300 More actions · Answer 4

Hello,

Thank you for the replies.

I would like to get the time that each job took per day to execute. This cam be done on a month analysis.

Suppose I have 7 jobs running on my server on a daily basis. So me run once per day, some run twice and some run several times per day

I would like a t-sql that would retrieve per job, the number of times it was executed during the day, how many times it failed and the total amount of time it was executing.

Suppose that I have one job that runs 5 times per day. I would like to know how many times it run, for how much time and with of the times were not successfully.

So I would like to have a list within one month range that show my this information per day.

Can someone help me?

Thank you very much

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

What have you tried?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

river1 (2/4/2016)
Hello,
Thank you for the replies.
I would like to get the time that each [font="Arial Black"]job [/font]took per day to execute. This cam be done on a month analysis.
Suppose I have 7 [font="Arial Black"]jobs [/font]running on my server on a daily basis. So me run once per day, some run twice and some run several times per day
I would like a t-sql that would retrieve per [font="Arial Black"]job[/font], the number of times it was executed during the day, how many times it failed and the total amount of time it was executing.
Suppose that I have one [font="Arial Black"]job [/font]that runs 5 times per day. I would like to know how many times it run, for how much time and with of the times were not successfully.
So I would like to have a list within one month range that show my this information per day.
Can someone help me?
Thank you very much

Lookup the "sys[font="Arial Black"]job[/font]history" table in Books Online. Follow your nose from there. No need to modify any stored procedures for such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

Jeff's right, of course. SQL Server already tracks job history. The page he's referring to it https://msdn.microsoft.com/en-us/library/ms174997%28v=sql.100%29.aspx. If you run into problems, post back what you're tried and we'll see where it goes from there.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions