March 7, 2021 at 5:59 pm
I'm working with a 260GB sized production-database here and working a little bit with QueryStore. However it's painfully slow, it takes several minutes to get the result I want to see (25 top resource consumers, last 24 hours, for example) sometimes it even gives me "can't connect to database" after awhile. Should it be like this with these amounts of data? The CPU goes way up when querying QueryStore aswell.
Querystore is configured to use 1000MB of space and state query threshold is 15 days.
Hints anyone? Thanks alot in advance.
March 8, 2021 at 1:22 pm
The reports are pretty chatty. Have you tried just running a query directly?
"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 8, 2021 at 1:58 pm
Nope. I'm a QueryStore newbie so I'm more comfortable with the UI at the moment. 🙂
March 8, 2021 at 2:03 pm
Try running a query or three to see what happens. The system tables are pretty straight forward to use. Let's figure out if it's Query Store itself that's a problem or just the reports.
"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 8, 2021 at 7:53 pm
I've tried doing some basic queries with the sys.query_* tables but well, don't notice anything special. There's lots of data in some tables and it takes a little while to get but well..
March 10, 2021 at 12:41 pm
Hmmm...
I'm not sure. Check your SSMS. Make sure you're on the latest version. Then, it may be simply that the load on your system is such that querying Query Store is too much. We might start adjusting the amount of stuff Query Store captures downwards. Here's a good article from Erin Stellato (who I trust very much, on this topic and others) for the settings you can, and should adjust to get better behavior.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply