October 2, 2020 at 9:15 pm
I do development and do not have access too client servers or our own production servers. When there are performance issues we will take a bak local and restore it. Some DBs restore with the query store information while others look empty. Is there a way to ensure that data gets in there. I can't seem to google to figure out how. I just can find that the data for query store is stored in the primary file group.
As for the settings in the properties for query store just assume they are default mostly. Nothing special in the settings. I can see data in the management views
Lastly these servers are 2016 and 2017
October 3, 2020 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 5, 2020 at 11:47 am
If Query Store is enabled on a given database, when that database gets backed up, the information within Query Store will also be backed up. No extra steps are necessary to make this happen. It's just a part of the database, like any other table, procedure or view. The page-by-page copy that a BACKUP database command does, includes the Query Store data.
Now, if you're not seeing Query Store data, it's likely one of three things. First, you're not actually using a BACKUP/RESTORE process to move the databases around, but instead are using some type of import/export or something else. Second, Query Store is not enabled on that database, so there is no Query Store data in it. Third, someone is running the command to clear the Query Store data either before the backup is taken, or after the restore is processed.
Those are the mostly likely causes from the info provided.
"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
October 5, 2020 at 3:15 pm
Thanks for the reply. This must just be one of those strange behaviors. All three of those reasons I thought about and none of them are this case. The client resolved it by giving me access to the server so I could do the research necessary.
October 5, 2020 at 3:55 pm
Hmmm... Sorry. I can't think of anything else it could be. A backup includes Query Store and there's no step you have to do or command or anything. It's just built right in.
Ah, maybe, unlikely, but possible, the dates on the systems are different so Query Store thought it had to do a cleanup? Yeah, probably not, but I'm trying to come up with all the reasons why the data might be gone.
"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
October 5, 2020 at 7:19 pm
The only other things I can thing of would be the Edition of the local instance or the compatibility mode.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply