To Spoof the creation of SQL creating the CPU stats for the last 4 weeks I used some useful SQL routines, the full code is linked here but the relevant sections are explained in the blog post. These are a few great TSQL tips and routines that can be used in a variety of things, listed are ways to:-
- to create an N number of rows quickly
- create an incremental number (like an Identity column) but without identity or any ordered rows, would work with a table full of duplicates
- creating a random number in a result set
The output creates a result set that looks like
I declared some variables to control a change in CPU between specific hours and a threshold of activity, LOW, MED, HIGH
Spoofing the results of the CPU
DECLARE @StartDate DATETIME =GETDATE() DECLARE @BusinessStartHours TIME ='09:00:00' DECLARE @BusinessEndHours TIME ='18:00:00' DECLARE @ServerProfile CHAR(4) ='HIGH' --LOW, MED, HIGH
I used a simple join which creates a Cartesian product of sysobjects and syscolumns to create a large result set. This would create 2,347,149 rows on a SQL 2016 server. More than enough for what I need
FROM Master.dbo.sysobjects CROSS JOIN sys.columns
I then using a CTE (Common Table expression) and a really clever bit of SQL, of course I stole it somewhere at somepoint to generate a row number without having any row numbers or rows to order by.
ROW_NUMBER() OVER (PARTITION BY (SELECT 1) ORDER BY (SELECT 1)) AS ID
This generates sequential number from any result set.
I now have a table of rows from 1 to 2,347,149 rows if need. I then filtered by times [Number of hours] x [minutes in an hour] x [Days in a week] x [number of weeks]
WHERE ID <= ( SELECT 24 * 60 * 7 * 4 )
Combined with ( single line of code over two for clarity)
DATEADD(minute,ROW_NUMBER() OVER (PARTITION BY (SELECT 1)
ORDER BY (SELECT 1)),@StartDate) AS ReportDate
Which gave me data instantly from Now (Getdate()) to 4 weeks previous for every minute of every day.
The next thing I needed was some random CPU data. Rand is not the way to go with this as when combining with a result set you get the same random number
To generate a random number I used NewID, the following giving me a random number from 0-60
ABS(CHECKSUM(NewId())) % 60
Which give me a set of random numbers
Hopefully, some of these elements might save someone sometime