Query to analyze Concurrent Use

• Cole.Mietzner

  Old Hand

  Points: 307

  More actions

  May 20, 2013 at 1:53 pm

  #294166

  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.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 21, 2013 at 6:03 am

  #1616956

  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