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