SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving to Query Store


Moving to Query Store

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: Administrators
Points: 619994 Visits: 21261
Comments posted to this topic are about the item Moving to Query Store

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
call.copse
call.copse
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14731 Visits: 2462
Hi Steve - sounds interesting, in what way is the store different to using profiler - is it kind of like that but gives you statistics on everything ever?
taboggiano@gmail.com
taboggiano@gmail.com
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 58
We had quite a significant performance increase on 2017 at one job I had:
https://tracyboggiano.com/archive/2018/06/query-store-usage-and-adaptive-plan-tuning-usage/

I've already contacted Bob Ward about this and I'm currently using it on 2016 at my new job and have used to get better performance from certain queries by forcing plans. I do recommended being on the latest CU there were some fixes in one CU for systems with lots of tempdb usage. We were early adopters to 2016 and due to the tempdb issues we have to at one point turn Query Data Store off.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: Administrators
Points: 619994 Visits: 21261
The Query Store is a warehouse of query plans and stats, essentially capturing activity of plans that lets you analyze how well things perform. You can also force plans from here, which was possible in earlier versions, but not as easy.

Trace (Profiler) captures various types of metrics, but doesn't store these efficiently. Trace and Extended Events are more like logs of activity.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111690 Visits: 14932
I enabled Query Store on a 3 TB sized database that is bulk loaded nightly. During the day it receives a fairly high volume of queries (ie: 100 per second) at a steady pace between 9am - 6pm from a call center application and also sporadic ad-hoc (sometimes overly complicated) queries from the BI and other data analysts throughout the day and into the evening. We do use SentryOne montitoring, which is great for a lot of things, but this tool is configured to only capture execution plan with a runtime > 10 seconds. For historical usage statistics on high volume sub-second execution plans, like the ones coming from the call center, I turn to Query Store.

I wouldn't say it's the greatest thing to ever happen to SQL Server, data management views and plan guides were introduced back in 2005, but persisting the dmv data to a more star-schema data model is a big step forward and a foundation for building better query analysis and optimization tools.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Rod
Rod
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29333 Visits: 2780
Umm, not sure how to approach this. I read the article, but was confused in a few places. Some of this is due to the fact that I really don't know what the SQL Server Query Data Store is, but also part of it is, well I think something got past the editor. For example, the first paragraph ends with:

We were excited by the chance to actually gather some information on the .


On the what?

Then in the fourth paragraph, it starts by saying:

I don't expect that a lot of improvement at SQLServerCentral from changing this, as our third party forums and much of the internal code is batch SQL, and quite a bit generated on the fly. However, there are some stored procedures, and I might be very wrong.


I'm sorry, but I'm not following you here. So, you've got some stored procedures and how might that make you wrong? I'm probably being obtuse in my understanding, so please bear with me and help me by clearing up these two things. Thank you.

Kindest Regards,Rod
Connect with me on LinkedIn.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: Administrators
Points: 619994 Visits: 21261
Ugh, edit error. Capture data on the performance of the database queries.

Query Store captures metrics on queries that run often. It can determine which plans were used and help you prevent plan regressions, or better understand query processing.

It's less helpful if all your queries are ad hoc, dynamic SQL.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rod
Rod
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29333 Visits: 2780
Ah, thank you, Steve! OK, now I'm getting your drift, as to why Query Store is so useful.

Kindest Regards,Rod
Connect with me on LinkedIn.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111690 Visits: 14932
Query Store is to query performance metrics what Service Broker is to message queuing; it's just a standardized, optimized, and extensible way of doing something that many of us have been doing for years. It's good at what it does (archiving, aggregating, and visualizing query metrics and plans), and I don't see a need to deploy any of the equivalent in-house stuff myself and others have built in the past that do basically the same thing. What really great about getting everyone on Query Store is that developers can then create SSMS plugins that we all can share.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search