Query Store Central Repository – Part 1

I’ve worked for SaaS companies for the last 6 years or so.  So our queries are largely the same across our system and by default Query Store is per database.  So it would be handy to have a central repository to help you determine which queries across your whole server are your worse performing queries.  Hence comes my idea to build a central repository.  I believe I put in connect item before it got moved to the new platform for this but never put a new ticket.  So this is the beginning of building something along those lines.  So it will be a work in progress so to speak.  My current company I care about queries that are taking a long time to run.  So I’m going to store the top 50 queries in total duration into a database handily called DBA because that’s where I store all the DBA stuff.  To do this, I have some none client related databases I don’t care about so I create a table to tell which databases to collect the data from.  Then a table to put the information into and job to run every day at midnight and sum up the data.  Now the data is stored in UTC time so the data will be off by whatever timezone difference you are in but with most people being 24×7 shops as SaaS companies that shouldn’t matter and if it does you can edit the query.

Hopefully, next up will be some reporting on this data.  But I need to enough data first to see what I can report on and see what is best to write a report in.

If you have any suggestions on what you think a central repository should look like, feel free to DM me on email.

--Create tables
USE DBA
GO
CREATE TABLE [dbo].[QueryStore_Databases](
	[DatabaseID] [int] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_QueryStore_Databases] PRIMARY KEY CLUSTERED 
(
	[DatabaseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 
GO

CREATE TABLE [dbo].[QueryStore_Stats](
	[DatabaseID] [int] NOT NULL,
	[query_id] [bigint] NOT NULL,
	[object_id] [bigint] NOT NULL,
	[object_name] [nvarchar](128) NULL,
	[query_sql_text] [nvarchar](max) NOT NULL,
	[total_duration] [float] NOT NULL,
	[count_executions] [bigint] NOT NULL,
	[num_plans] [float] NOT NULL,
	[query_hash] [binary](8) NOT NULL,
	[collection_date] [date] NOT NULL,
 CONSTRAINT [PK_QueryStore_Stats] PRIMARY KEY CLUSTERED 
(
	[DatabaseID] ASC,
	[query_id] ASC,
        [collection_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
GO

--Create job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Capture Query Store Stats', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Collect Query Store Stats', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'USE DBA
DECLARE @SQL NVARCHAR(MAX) = ''''

SELECT @SQL += REPLACE(REPLACE(''
	USE {{DBName}}
	INSERT INTO DBA.dbo.QueryStore_Stats 
		(
		DatabaseID, 
		query_id, 
		object_id,
		object_name, 
		query_sql_text, 
		total_duration, 
		count_executions, 
		num_plans, 
		query_hash, 
		collection_date
		)
	SELECT TOP 50
		{{DatabaseID}} DatabaseID,
		p.query_id query_id,
		q.object_id object_id,
		ISNULL(OBJECT_NAME(q.object_id), NULL) object_name,
		qt.query_sql_text query_sql_text,
		ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) *0.001 , 2) total_duration,
		SUM(rs.count_executions) count_executions,
		COUNT(distinct p.plan_id) num_plans,
		query_hash,
		CAST(CONVERT(varchar, getdate() - 1, 1) AS DATE) as CollectionDate
	FROM sys.query_store_runtime_stats rs
		JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
		JOIN sys.query_store_query q ON q.query_id = p.query_id
		JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
	WHERE NOT (rs.first_execution_time > CAST(CONVERT(varchar, getdate(), 1) AS DATE) 
					OR rs.last_execution_time < CAST(CONVERT(varchar, getdate() - 1, 1) AS DATE))
	GROUP BY p.query_id, 
		qt.query_sql_text, 
		q.object_id, 
		query_hash
	HAVING COUNT(distinct p.plan_id) >= 1
	ORDER BY total_duration DESC ''
	, ''{{DBName}}'', DatabaseName)
	, ''{{DatabaseID}}'', DatabaseID)
FROM dbo.QueryStore_Databases

EXEC (@SQL)


', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20190220, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'18fc7723-7e23-4939-a7f3-77a11f3d72a5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO