azure-cpu-bench.sql

-- setup
CREATE DATABASE test 
( EDITION = 'basic', MAXSIZE = 2 GB ); --SERVICE_OBJECTIVE = 'S0', 
WAITFOR DELAY '00:01';

ALTER DATABASE test  
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 1);  

USE test

CREATE TABLE [dbo].[Load]([Id] [int] NOT NULL) ON [PRIMARY]

declare @x int
set @x = 1
BEGIN TRAN
while (@x <= 1000)
BEGIN
	INSERT INTO [dbo].[Load] ([Id]) VALUES (@x)
	set @x = @x + 1
END
COMMIT

-- load test
ALTER DATABASE test 
MODIFY ( EDITION = 'basic', MAXSIZE = 2 GB ); --SERVICE_OBJECTIVE = 'S0', 
WAITFOR DELAY '00:01';
GO

ALTER DATABASE test SET QUERY_STORE CLEAR;
GO

select COUNT(1) from Load

declare @t int
declare @d datetime
set @d = DATEADD(second, 120, GETUTCDATE())
while (@d >= GETUTCDATE())
select top 1 @t = Id from Load order by newid()

WITH bucketizer as (
    SELECT
        rs.plan_id as plan_id,
        rs.execution_type as execution_type,
        SUM(rs.count_executions) as count_executions,
        DATEADD(mi, ((DATEDIFF(mi, 0, rs.last_execution_time))),0 ) as bucket_start,
        DATEADD(mi, (1 + (DATEDIFF(mi, 0, rs.last_execution_time))), 0) as bucket_end,
	ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) as total_cpu_time,
        ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) as total_duration
    FROM
        sys.query_store_runtime_stats rs
        JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    GROUP BY
        rs.plan_id,
        rs.execution_type,
        DATEDIFF(mi, 0, rs.last_execution_time)
    )
SELECT top 1 
	plan_id,
    	bucket_start,
    	bucket_end,
	total_cpu_time,
    	total_duration,
	count_executions
FROM bucketizer
where execution_type = 0
order by total_duration desc