We're helping a client transition from on-prem services to Azure and as part of this, we're conducting load tests to choose the right SKU. The client wants to have Query Store enabled during these tests since they haven't used it on-prem and need to identify any potential impacts once they're on Azure SQL Managed Instance (Business Critical, with appropriate configuration). While running the tests using **ostress** for trace replay, we've observed a high number of **QDS_STMT** waits. Here's a breakdown of our Query Store configuration:
- Desired state: READ_WRITE
- Actual state: READ_WRITE
- Current max storage size: 7168 MB
- Current storage size: 0 MB
- Flush interval: 900 seconds
- Query capture mode: AUTO
Has anyone faced similar high **QDS_STMT** waits during heavy load? Any suggestions on how to tune or alleviate this issue without turning off Query Store completely?
2 Answers
Definitely make sure your configuration suits your workload! If there are a lot of insert queries with table parameters, that's where the issue might lie. You could tweak the capture policy to avoid capturing those inserts if they're not reflective of the typical workload. Just be wary of excluding important queries. Let me know if you need more specific settings!
It sounds like your application might be using a lot of dynamic SQL without parameters, which could be causing those waits. Have you considered switching to a custom capture policy? This would mean setting it up to capture queries only after they've been executed a certain number of times—a good move for dynamic SQL-heavy apps. For example, you could run something like this:
`ALTER DATABASE [QueryStoreDB]`
`SET QUERY_STORE = ON`
This way, you'd be capturing more relevant queries and reducing the QDS_STMT waits. Plus, managed instances really aren’t ideal for cost and performance compared to VMs, so keep that in mind too.
Related Questions
How To: Running Codex CLI on Windows with Azure OpenAI
Set Wordpress Featured Image Using Javascript
How To Fix PHP Random Being The Same
Why no WebP Support with Wordpress
Replace Wordpress Cron With Linux Cron
Customize Yoast Canonical URL Programmatically