March 2, 2022 at 4:32 pm
Hi
Looking at implementing Query Store in one of our automation testing environments. So far the only potential obstacle I can see is that presumably when you restore from backup (as we do prior to each test) you then remove all accumulated query store data and this negates the the whole point of having it .
Is there anyway query store can be not overwritten when a backup is restored ? Or perhaps copied to a separate copy of a dba and then "re-added" ?
many thanks
Simon
March 3, 2022 at 2:47 pm
Query Store is not overwritten during a restore. In fact, it remains completely intact. All Query Store information is in system tables, stored with each database and will get backed up and restored, automatically. No extra steps needed to ensure that data is intact.
In fact, what you need to consider is whether or not, when you restore a database, you need to go in and clear out the Query Store information. Depending on the data, you could be violating some laws letting Query Store information remain intact in non-production environments.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2022 at 10:06 am
Hi Grant
thank you for your reply but thats not the behaviour I seeing . If I run this simple test I can see query store vanishes after he restore (which is kinda what I'd expect)
use master
go
create database QS_Restore
go
USE QS_Restore
GO
BACKUP DATABASE [QS_Restore] TO DISK = N'C:\SQLBackup\QS_Restore.bak' WITH NOFORMAT, NOINIT, NAME = N'QS_Restore-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
ALTER DATABASE [QS_Restore] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL,
MAX_STORAGE_SIZE_MB = 2000,INTERVAL_LENGTH_MINUTES = 30
)
GO
create table test
(id int identity (1,1) not null ,
descript varchar(10)
)
insert test values ('test')
select * from test order by 1
select * from sys.query_store_query_text;
SELECT actual_state_desc FROM sys.database_query_store_options;
USE [master]
go
RESTORE DATABASE [QS_Restore] FROM DISK = N'C:\SQLBackup\QS_Restore.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
select * from sys.query_store_query_text;
SELECT actual_state_desc FROM sys.database_query_store_options;
March 4, 2022 at 10:44 am
So your referring the the query store data which you have accumulated prior to restoration?
Database is running happy, collecting query store data.
I restore database with database100
Query store data for database is lost
Query store data for database100 is put in place.
March 4, 2022 at 10:46 am
Your sample script you backup the database prior to enabling Query Store, so yes you restore that backup you will have no query store data.
use master
go
create database QS_Restore
go
USE QS_Restore
GO
ALTER DATABASE [QS_Restore] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL,
MAX_STORAGE_SIZE_MB = 2000,INTERVAL_LENGTH_MINUTES = 30
)
GO
create table test
(id int identity (1,1) not null ,
descript varchar(10)
);
insert test values ('test')
select * from test order by 1;
select * from sys.query_store_query_text;
SELECT actual_state_desc FROM sys.database_query_store_options;
GO
BACKUP DATABASE [QS_Restore] TO DISK = N'C:\SQLBackup\QS_Restore.bak' WITH NOFORMAT, INIT, NAME = N'QS_Restore-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
USE [master]
go
RESTORE DATABASE [QS_Restore] FROM DISK = N'C:\SQLBackup\QS_Restore.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;
GO
select * from sys.query_store_query_text;
SELECT actual_state_desc FROM sys.database_query_store_options;
March 4, 2022 at 11:10 am
perhaps I haven't explained well enough. After each automation run we restore the db from backup. Thus it would effectively nuke all query store data . I want to be able to maintain a cumulative state of query store info so that I can restore said databases multiple times and have all the historical query store data
March 4, 2022 at 11:29 am
There is no import/export for query store data on its own.
Someone did raise this for MS to investigate, you can upvote that here
Export Query Store tables separately from the database tables · Community (azure.com)
The only option you have is to do a DBCC CLONEDATABASE at the end of the automation run to a new clone database which has the structure and Query Store data. But that again will be a snapshot at the time the clone was taken, its not going to be a cumulative store of query store data so you would have to run your analysis over multiple databases still.
March 4, 2022 at 12:54 pm
I feared as much. Thank you for the advice. Much appreciated.
March 4, 2022 at 1:56 pm
Oh!
yeah, the query store data is stored with the database. If you back it up, which you are absolutely NOT doing, when you restore, you will get that data back. This is totally different and yeah, you'd need to manually export that data. You could do that though and you could automate it. It's just going to require you to build it.
HOWEVER,
There is no import mechanism that lets you load it up. You'd have to put it in separate tables. Again, you could do that. It's just going to require you to build it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2022 at 2:17 pm
"There is no import mechanism that lets you load it up. You'd have to put it in separate tables. Again, you could do that. It's just going to require you to build it."
So theory I could create a separate db and put together some sort of ETL script to manually build a central repository of duplicate dmvs etc
I guess then Id be missing all the nice gui features for reporting ...
March 4, 2022 at 3:10 pm
Yeah, absolutely.
Although, I have good news. There is an open source software that does a lot of this, OpenQueryStore. I saw this when it was an in-house private bit of code and it was impressive. I think the open source one is improved. Worth checking out anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply