Moving to Query Store

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.

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.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.