February 26, 2016 at 3:22 pm
Hello,
I am trying to create a stored proc that will capture already harvested SQL Server Agent Job information. Some of the key data incldues SQL Server Agent Job start time, and duration in minutes. I am trying to create a stored proc that will add 1 minute to the SQL Server Agent Job Start Time for however many total minutes the job ran. So if a job ran for 120 minutes and the job start time was at 5:00PM. I would like 120 additional data points with 5:01, 5:02, 5:03, etc. as start times.
The reason I am trying to do this is to create a Tableau report that shows me a graph of all SQL Server Agent Jobs running at any given time at a server level, and enterprise level. Currently a job that runs for 2 hours and starts at 5pm will only show up once on my viz. Thus I am trying to create the additonal data points that will show the job running at each minute interval, for however long that job ran for.
I can easily generate the list of columns with the new date times like so:
Declare @CNT int
Declare @x int
Declare @z datetime
Create Table #DateSplit ( Cnt int, x int, z datetime )
Select
@x = [Duration_MIN],
@z = [Start_DTM]
From #TempTable
Set @CNT = 0;
while (@CNT < @x)
Begin
Insert Into #DateSplit Values (null, null, @z )
set @z = dateadd (minute,1 ,@z)
set @CNT = @CNT + 1
end
GO
Here is my Main Select Statement for reference:
SELECT Distinct
[STG_JobHistory].[History_ID] AS [History_ID],
[STG_JobHistory].[Job_NME] AS [Job_NME],
[STG_JobHistory].[Start_DTM] AS [Start_DTM],
[STG_JobHistory].[Duration_SEC] AS [Duration_SEC],
[STG_JobHistory].[Outcome_DSC] AS [Outcome_DSC],
[STG_JobHistory].[Run_ID] AS [Run_ID],
[STG_JobHistory].[Server_ID] AS [Server_ID],
[STG_JobHistory].[RunServer_ID] AS [RunServer_ID],
[STG_JobHistory].[LastModified_DTM] AS [LastModified_DTM],
[STG_Server].[Prod_TYP] AS [Prod_TYP],
[STG_Server].[Server_NME] AS [Server_NME],
[STG_Job].[Job_DSC] AS [Job_DSC],
[STG_Job].[JobCategory_DSC] AS [JobCategory_DSC],
[STG_Job].[JobOwner_NME] AS [JobOwner_NME],
Duration_SEC/60 AS [Duration_MIN]
--into #TempTable
FROM [dbo].[STG_JobHistory] [STG_JobHistory]
INNER JOIN [dbo].[STG_Server] [STG_Server] ON ([STG_JobHistory].[Server_ID] = [STG_Server].[Server_ID])
INNER JOIN [dbo].[STG_Job] [STG_Job] ON ([STG_JobHistory].[Server_ID] = [STG_Job].[Server_ID] and [STG_JobHistory].[Job_NME] = [STG_Job].[Job_NME])
INNER JOIN [dbo].[STG_JobSchedule] [STG_JobSchedule] ON ([STG_JobHistory].[Server_ID] = [STG_JobSchedule].[Server_ID] and [STG_JobHistory].[Job_NME] = [STG_JobSchedule].[Job_NME])
and [STG_JobHistory].[Start_DTM] > cast (@StartDate as varchar )
However, I am running into problems linking this back into my original data set tied to the same Agent Job ID.
Thank you in advance!
February 26, 2016 at 4:02 pm
I can give you a pretty easy example that will be easy for you to incorporate and that doesn't use any RBAR. My question is, what's in the "[STG_JobHistory].[Duration_SEC] AS [Duration_SEC]" column? It appears to be "SEConds" but I don't want to make that assumption.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2016 at 4:08 pm
Going to point you in a direction so that you can do some research over the weekend. Start with the link in my signature block about Tally Tables. Then read the following two articles as well:
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
After that, see what you can come up with. Any questions, post back here. If you solve your problem, post back here with your solution.
February 29, 2016 at 7:56 am
Hey Jeff- Correct. It is the duration of the SQL job in Seconds.
Lynn - I will check out the links and see what I can do!
Thanks to both for your time!
March 3, 2016 at 12:53 pm
Lynn,
I read the article on the tally tables and also worked though all of the examples. I do understand how tally tables are much faster than creating loops. However, there are over 430 servers x 10-40 agent jobs per server. Each server has at least one agent job that runs every minute. So this is going to be millions if not billions of rows(currently have 6M rows in STG_JobHistory and some rows will turn into 2000+ Start times). My issue is keeping all of the new start point times related to the correct Job.
However, I very well could not be understanding the concept fully. As this could very well be the answer. It definitely seems like the answer is close here; I just cannot connect the dots.
March 3, 2016 at 1:41 pm
GeoGym (3/3/2016)
Lynn,I read the article on the tally tables and also worked though all of the examples. I do understand how tally tables are much faster than creating loops. However, there are over 430 servers x 10-40 agent jobs per server. Each server has at least one agent job that runs every minute. So this is going to be millions if not billions of rows(currently have 6M rows in STG_JobHistory and some rows will turn into 2000+ Start times). My issue is keeping all of the new start point times related to the correct Job.
However, I very well could not be understanding the concept fully. As this could very well be the answer. It definitely seems like the answer is close here; I just cannot connect the dots.
Well, you said you wanted to split rows to have one row per minute of the job.
This is an (untested) example on how a tally table could work. I'm not using an actual table, instead, I'm generating one on the fly. I also cleaned your code a bit.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT Distinct
jh.History_ID AS History_ID,
jh.Job_NME AS Job_NME,
jh.Start_DTM AS Start_DTM,
jh.Duration_SEC AS Duration_SEC,
jh.Outcome_DSC AS Outcome_DSC,
jh.Run_ID AS Run_ID,
jh.Server_ID AS Server_ID,
jh.RunServer_ID AS RunServer_ID,
jh.LastModified_DTM AS LastModified_DTM,
s.Prod_TYP AS Prod_TYP,
s.Server_NME AS Server_NME,
j.Job_DSC AS Job_DSC,
j.JobCategory_DSC AS JobCategory_DSC,
j.JobOwner_NME AS JobOwner_NME,
jh.Duration_SEC/60 AS Duration_MIN
DATEADD( mi, t.n, jh.Start_DTM) AS Continuous_Start_DTM,
--into #TempTable
FROM dbo.STG_JobHistory jh
JOIN dbo.STG_Server s ON jh.Server_ID = s.Server_ID
JOIN dbo.STG_Job j ON jh.Server_ID = j.Server_ID and jh.Job_NME = j.Job_NME
JOIN dbo.STG_JobSchedule js ON jh.Server_ID = js.Server_ID and jh.Job_NME = js.Job_NME
JOIN cteTally t ON jh.Duration_SEC >= t.n * 60
WHERE jh.Start_DTM > cast (@StartDate as varchar );
EDIT: Correct the WHERE clause. Using CAST to convert a datetime to string is a bad idea. Use Convert to ensure the correct format and assign a length to the type.
March 3, 2016 at 2:41 pm
Very much appreciated. I did change the Cast to a convert. However the Continuous_Start_DTM doesn't appear to work as anticipated. I only included some of the columns in the below data for the first 10 records. I would expect to see 2 dates where duration_min = 2, and 7 dates where duration_min = 7, etc.
Apologize in advance for the formatting of the data.
History_IDStart_DTMDuration_SECLastModified_DTMDuration_MINContinuous_Start_DTM
63116042016-03-02 01:30:00.00000001182016-03-02 07:01:16.71012016-03-02 01:31:00.0000000
63751582016-03-03 01:30:00.00000001272016-03-03 07:01:10.71722016-03-03 01:31:00.0000000
63751582016-03-03 01:30:00.00000001272016-03-03 07:01:10.71722016-03-03 01:32:00.0000000
63754242016-03-03 02:00:00.0000000642016-03-03 07:01:10.71712016-03-03 02:01:00.0000000
63121852016-03-02 00:01:00.00000001222016-03-02 07:01:16.71022016-03-02 00:02:00.0000000
63121852016-03-02 00:01:00.00000001222016-03-02 07:01:16.71022016-03-02 00:03:00.0000000
63119242016-03-02 01:01:00.00000001222016-03-02 07:01:16.71022016-03-02 01:02:00.0000000
63119242016-03-02 01:01:00.00000001222016-03-02 07:01:16.71022016-03-02 01:03:00.0000000
63122462016-03-02 02:01:00.00000001222016-03-02 07:01:16.71022016-03-02 02:02:00.0000000
63122462016-03-02 02:01:00.00000001222016-03-02 07:01:16.71022016-03-02 02:03:00.0000000
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply