May 20, 2013 at 1:53 pm
First off, I dont even know where to start with this. I do not expect anyone to do my work for me, but a push in the right direction would be great.
I have a table that lists every task run through our application. It logs a StartTimeDate and an EndTimeDate. I would like to see what tasks are running concurrently to help better our performance. I would like the results in 5min increments and limited to a week at a time. I honestly have no clue how to even start to write a statement to perform this as I am a QA engineer with moderate SQL skills.
I have created a sample DB at: http://sqlfiddle.com/#!3/beb7a/1
Any help would be greatly appreciated. Or if there is a program out there that does this already that would be awesome too.
May 21, 2013 at 6:03 am
First approach that came to mind:
1. Setup a tally table in your environment.
SET NOCOUNT ON ;
GO
-- Credit: http://www.sqlservercentral.com/articles/T-SQL/62867/
--DROP TABLE dbo.tally
--=============================================================================
-- Create and populate a tally table
--=============================================================================
IF NOT EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.tally')
AND type IN (N'U') )
BEGIN
--===== Create and populate the tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY( INT,1,1 ) AS n
INTO dbo.tally
FROM master.dbo.SysColumns sc1,
master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (n) WITH FILLFACTOR = 100
--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.tally TO PUBLIC
PRINT 'Created tally table.'
END
GO
2. Use the tally table to create a time-table with a start and end time that spans 5 minute increments:
SELECT DATEADD(minute, n - 5, CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AS StartTime,
DATEADD(millisecond, n * 60000 - 3, CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AS EndTime
FROM DBA.dbo.tally
WHERE n > 0
AND n % 5 = 0
AND n <= 1440;
3. Use the time-table created in step 2 to join to the Watson table to group events into 5 minute buckets.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply