Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to analyze Concurrent Use Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 4, 2013 12:42 PM
Points: 7, Visits: 27
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.
Post #1454720
Posted Tuesday, May 21, 2013 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 7,098, Visits: 12,605
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
Post #1454953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse