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

Moving to Query Store

By Steve Jones,

In SQL Server 2016, Microsoft introduced the Query Data Store (QDS) as a tool that would capture data about the execution of queries inside of your database. This was a project that had been in the works for a number of years, and one that many of us that were bound by NDA agreements had been following. We were excited by the chance to actually gather some information on the performance of our database queries. 

Are you using Query Store? You should be, as this tool will become more valuable over time. I know that there are potential overhead issues (3-5% for most people, but possibly larger). I would argue that the potential for better performance and understanding of our systems outweighs the overhead. After all, if we're unwilling to devote some resources to measuring our systems, how do we really know what to improve?

We upgraded the SQLServerCentral servers to SQL Server 2017 this year (2018), and I've been wanting to enable the Query Store. I've been slightly hesitant with over 75,000 air miles and 5,000 driving miles on the road since the upgrade. Being distracted and out of my routine isn't the best way to document and carefully observe the effects of a change. Not to mention concerns over data leakage for a company bound by the GDPR. After a little discussion and debate, and my schedule slowing, I'm looking to change that soon. 

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. While we're over provisioned with resources to avoid any performance problems, I do expect that we'll learn a few things. I hope we find places to better tune code, and with some documentation of the process, hopefully some of you out there might spot things our team doesn't.

If you've got stories of the QDS working well or not well, let us know. Certainly let Microsoft know as well. The QDS is a major part of the SQL Server platform improving in the future and there are enhancements in SQL Server 2019. While I don't know that the QDS and some of the automatic tuning features remove the need for a data professional to watch a system, I'd like to think they do provide opportunities and insight for how we might better structure and develop applications, as well as help us find better patterns that are useful in our initial database coding.

If you've got stories, Erin Stellato wants to know (and she has a few in the post). If you're concerned about overhead, read her other post. If you're confused, we're working on some articles to help you learn more. Give the QDS a try, especially if you've got some less critical systems. Part of our job is learning how to use new tools, and this is one that ought to be on most DBAs ToDo list.

Total article views: 56 | Views in the last 30 days: 3
Related Articles

Querying System Tables

It is not recommended, but there is quite a bit of valuable information stored in the SQL Server 200...


Need better Query !!!!!!!!

Need better Query !!!!!!!!


A Better English Query

Someone is trying to build a better query engine for the web, something beyond a search engine. Stev...


How to modify 'system stored procedure' SQL 2008?

SQL Server 2008 modify alter system stored procedure


Query Store, Force Plan and “Better” Plans

I am endlessly fascinated by how the Query Store works. I love teaching it at every opportunity too....