Does Query Store Pre-Allocate Space

I love the questions I get while I’m presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I’m not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have […]

Read More

Database Clone

There are a bunch of ways you could create a database clone. Backup and restore is one method. Export/Import is another. There are even third party tools that will help with that. However, each of these has a problem. It’s moving all the data, not just once, but twice. You move the data when you […]

Read More

Query Data Store Data

The data in the Query Data Store is what makes all the magic happen. From collecting the aggregate performance metrics of a query to the various plans in use by that query to being able to force a plan, it’s all controlled by the data within the Query Data Store system tables. The Question When […]

Read More

Query Store and What Happened Earlier On Your Server

Here’s a great question I received: We had a problem at 9:02 AM this morning, but we’re not sure what happened. Can Query Store tell us? My first blush response is, no. Not really. Query Store keeps aggregate performance metrics about the queries on the database where Query Store is enabled. Aggregation means that we can’t [&helli...

Read More

Query Store and Plan Forcing: What Do You Use It For

If you’re working with Azure SQL Database or you’ve moved into SQL Server 2016, one of the biggest new tools is the Query Store. It provides a mechanism of capturing query performance over time and persisting it with the database. You also get the execution plans for those queries. Finally, you can choose to have […]

Read More

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. Plus, almost every time I teach it, I get a new question about the behavior that makes me delve into the Query Store just a little bit more, enabling me to better understand how it works. I […]

Read More

Query Store, Force Plan and Dropped Objects

I love the Query Store. Seriously. It’s a huge leap forward in the capabilities of Azure SQL Database and SQL Server in support of performance monitoring and query optimization. One of my favorite aspects of the Query Store is the ability to force plans. Frankly though, it’s also the scariest part of the Query Store. […]

Read More

Precedence Goes to Query Store or Plan Guide?

While presenting at SQLDay in Wroclaw, Poland, on the Query Store, I was asked a pretty simple question, which takes precedence, the Query Store or a Plan Guide? One of my favorite answers to questions is “I don’t know” because it gives me the opportunity to learn. Let’s figure this one out together. I’ll post [&hellip...

Read More

Query Store, Forced Plans, and New Plans

I love questions. I recently received one about new plans in the Query Store (available in Azure SQL Database now and in SQL Server 2016 after June 1). Let’s say you have selected a plan that you want to force. You set it up. Now, let’s say the plan ages out of cache or even goes through […]

Read More

Query Store and Optimize For Ad Hoc

I love presenting sessions because you get so many interesting questions. For example, what happens with Optimize for Ad Hoc when Query Store is enabled? Great question. I didn’t have the answer, so, on to testing. For those who don’t know, Optimize for Ad Hoc is a mechanism for dealing with lots and lots of […]

Read More