Creating test data using TSQL- 4 weeks of CPU stats

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

CPU stats

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.

CPU stats-rownumber

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

CPU stats-random

Hopefully, some of these elements might save someone sometime

This entry was posted in DataScotland, Tips & Tricks, TSQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s