August 1, 2017 at 6:25 pm
Hi All
We have recently upgraded all our production SQL servers to 2016 Sp1-CU2
On some of the load balances servers, we have enabled query store with the below settings
Data Flush Interval - 25 min
Statistics Collection Interval - 15 min
Max Size - 6144
Next 2 properties are kept AUTO
and the retention is 14 days
We have 4-5 very busy databases on these servers. They are busy in terms of web servers hitting them and replication applying replicated commands on them
Specifically we have seen query store has too much impact on one of the databases in that it has caused connection time outs on these servers. Disabling query store on that database has resulted in no connectivity errors
Can some one please let me know how we can troubleshoot query store performance burden from SQL connections stand point ?
Also what are the ideal configuration settings for query store so that there are zero connectivity issues. After disabling query store on that 1 database brings the performance of the server normal and connections happen to the server as usual
Can someone please guide me in the right direction
August 2, 2017 at 5:42 am
First up, there are specific wait statistics assigned to query store. When you query sys.dm_os_wait_stats, if you are experiencing issues with Query Store, that's where you'll see the evidence of it. Focus here first. Ensure that you are getting a high amount of Query Store waits.
I've only heard about a very few systems who had a load so high that query store impacted the results. There are a few knobs you can tweak that might make a difference.
First up, you could change the capture mode from "All" to "Auto". That will mean that only queries that run over a certain value or that are called multiple times (3) get captured. You've changed the default values on some settings. You have a higher data flush interval and a lower collection interval. This means more stuff is going to remain in cache, using more memory, and you have to process what's on disk into aggregates more frequently. You might want to switch back to the defaults there. You may even want to have a lower Data Flush interval, although that can cause additional I/O, so if you're already I/O bound, this could make things worse.
On a side note, having just upgraded, what did you upgrade from? Was it prior to SQL Server 2014? If so, you might be additionally suffering from the change in the cardinality estimator which occurred then. Check the compatibility level on your databases and, where possible, compare the execution plans from before the upgrade to after to see if there are issues in performance. It's possible that this is the root of the problem, not Query Store (although on a very heavily used system, Query Store could make things worse).
"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
August 2, 2017 at 5:13 pm
kaleparag - Tuesday, August 1, 2017 6:25 PMCan some one please let me know how we can troubleshoot query store performance burden from SQL connections stand point ?
Hi kaleparag,
There are counters that can track the Query Store resource usage:
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-query-store-object
In our production OLTP environment we had to switch Query Store off soon after upgrading - it added between 5% and 15% CPU load depending on time of day. Initially we had enabled Query Store to help us address performance issues (cardinality-related, etc) but it turned out that Query Store was a big problem itself.
Disabling Query Store when under pressure generates temporary issues like query timeouts. So pick your time when your system is quiet.
We occasionally switch Query Store on for specific troubleshooting scenarios, but never over our peak processing hours.
Stephen.
August 3, 2017 at 9:04 pm
Thanks for the replies
Is seeing PAGEIOLATCH waits also associated with query store by any chance ?
August 3, 2017 at 10:04 pm
kaleparag - Thursday, August 3, 2017 9:04 PMThanks for the replies
Is seeing PAGEIOLATCH waits also associated with query store by any chance ?
We had one or two types of PAGELATCH. But if your system is more IO bound than CPU bound then yeah, I guess it's possible. We had a deep and meaningful chat with Microsoft during our post-upgrade problems and Microsoft quickly went from "Awesome, you're using Query Store to troubleshoot!!!" to "We're working on some improvements to Query Store with no ETA".
My guess is that in future they'll bring out bug fixes or additional features that help manage these edge cases.
August 4, 2017 at 3:28 am
kaleparag - Thursday, August 3, 2017 9:04 PMThanks for the replies
Is seeing PAGEIOLATCH waits also associated with query store by any chance ?
You might already be at the edge of having I/O issues, or you have I/O issues, and Query Store is adding to your I/O burden and killing it. I'd leave it off for the moment and focus on the system to ensure you're set up right there and have adequate hardware. Maybe after adjusting that, you can come back around to Query Store.
"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
August 4, 2017 at 3:29 am
Bleeping DBA - Thursday, August 3, 2017 10:04 PMWe had one or two types of PAGELATCH. But if your system is more IO bound than CPU bound then yeah, I guess it's possible. We had a deep and meaningful chat with Microsoft during our post-upgrade problems and Microsoft quickly went from "Awesome, you're using Query Store to troubleshoot!!!" to "We're working on some improvements to Query Store with no ETA".My guess is that in future they'll bring out bug fixes or additional features that help manage these edge cases.
Good to hear they're working on it. You're only about the 3rd person I've heard from that has had problems (and the OP is the 4th). It seems to mainly hit people who are working at the high end of things already. That's a shame because they're the ones most likely to benefit from what Query Store offers.
"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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply