Query Store Fundamentals: How does Query Store Work?

In the previous post, we learned about the basics of the Query Store. In this session, we will learn how Query Store works behind the scenes.

When a query is submitted to the SQL Server, it checks if there is already a plan available for the query in the procedure cache. If the plan is not found, Query goes through the compilation and optimization phases.

The query is checked for recompilation. During this phase, if the plan in the procedure cache is not similar to the forced plan for that query (If the plan has been forced for that query), the query goes through the recompilation and a similar forced plan is generated (i.e, the forced plan is applied just like the PLAN Hint). If the plan in the cache is the same as the forced plan, it will go ahead and use the same plan.

Before the query executes, the query text and the query plan are stored in the plan store which is in-memory. This information is stored asynchronously. Once the query executes, the run time stats information is captured in the runtime stats store which is in memory as well and stored asynchronously. The runtime stats are captured in memory and flushed out to the Query Store schema asynchronously as per the Data Flush Interval (Minutes) settings for the Query store. The query text and the query plan information are sent to the Query store schema with minimal latency (Source: Microsoft). If there is memory pressure, the runtime stats information can be sent to the disk before hitting the data flush interval.

Based on the resource: https://docs.microsoft.com/en-us/sql/relational-databases/performance/how-query-store-collects-data?view=sql-server-ver15

Know the Query store settings and what each setting actually mean:

When you enabled Query Store, you cannot always depend on the defaults. It is very important to know what each of the setting means and why it is important to set it right based on your workload.

DATA_FLUSH_INTERVAL_SECONDS (type: bigint) determines how often the data that has been collected in the memory is flushed out to the disk and stored on the primary data file of the database and this is depended on the interval we chose. By default, the interval is 15 minutes.

CLEANUP_POLICY determines how long you want the Query Store to save the data. The default is 30 days (STALE_QUERY_THRESHOLD_DAYS) which can be changed as per your needs.

MAX_STORAGE_SIZE_MB (bigint) determines the amount of space the Query Store can use. By default, it is 100MB for SQL Server 2016/2017. For SQL Server 2019, the default size is 1GB. Remember that, the size of the max storage size is checked when ever the data is flushed to disk based on the interval settings. If the size is full meanwhile, the query store will to be changed to read-only mode. If you chose the SIZE_BASED_CLEANUP_MODE is set then the cleanup mechanism is triggered automatically. Once the cleanup is completed, the Query Store will be change back to read-write (Source: Microsoft Docs)

INTERVAL_LENGTH_MINUTES is the interval where the runtime statistics are aggregated. If we capture the runtime stats for every query individually, that will fill up the Query store size pretty quickly. For that reason, the aggregate information is collected. For example, if you have a query being run 100 times in the interval of 15 minutes, there is only one entry and one row allocated for this query with the aggregated information between that interval. Lets say the interval number is 1. The next interval number 2 starts after 15minutes. For that interval number 2, if the same query runs again, it will get a single row entry no matter how many times it executes during that interval number 2.

SIZE_BASED_CLEANUP_MODE is AUTO by default. The cleanup process will be automatically triggered when the MAX size of the Query Store reaches 90%. This option helps Query store not to go to read only mode. This process cleans up the oldest queries and the least expensive data first. It is suggested not to turn off this option.


In this blog post, we learnt about how the Query Store actually works. We are going to see the advancements made to the Query store feature in SQL Server 2022, which we are going to learn in the coming blog posts.

It is important to know what and how we setup the options and settings for the Query store. For the Azure SQL database, once the defaults are applied and enabled it is difficult to change these configuration values without effecting the Query store operations. Remember, the Query store cannot be disabled on the Azure SQL database and elastic pool.

Thanks for reading!


  1. https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15
  2. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15
  3. https://docs.microsoft.com/en-us/sql/relational-databases/performance/how-query-store-collects-data?view=sql-server-ver15

Query Store Fundamentals Series: Know the basics

In this blog post, we will learn the basics of the Query Store feature in SQL Server.

Introduced in SQL Server 2016, the Query Store feature will store the Query plans, Queries, resource consumption information along with the run time stats information for your queries. This feature is not automatically enabled on your databases. It is a database-level option only. You need to enable the Query Store on the database to capture the queries and query information. You cannot enable the Query store on master or tempdb databases. The information captured by the Query Store is stored in the database you enabled the feature on, in the internal tables created in the PRIMARY filegroup and this cannot be changed. (Microsoft docs reference).

Query Store feature is enabled by default if you are using Azure SQL Database and disabled by default on Azure synapse analytical database.

So, what are the advantages if we enable the Query Store feature on your database?

  1. Very quickly identify the top resource consuming queries. Query Store captures CPU, IO, Memory, Duration, Logical Reads and Writes, Physical Reads, CLR Time, DOP, Row Count, Log Memory Used, Temp DB Memory Used, and Wait Time. You may say, I already have third party monitoring tool capturing the top resource consuming queries but look at the next several points to know Query store can offer.
  2. Query store captures the history of the execution plans for your queries. You can easily compare the difference between the plans by placing them side by side.
  3. If there are many execution plans generated for a query and you believe one of the plans is working better than others, you can force an execution plan of your choice with out changing any applications. This is very beneficial to quickly solve the query performance issues.
  4. Query Store feature can be used to capture the performance baselines. Personally, I used this feature when we upgraded the databases to new versions of SQL Server.
  5. We will be learning about the Automatic plan correction in the upcoming posts, available from SQL Server 2017 which depends on the Query Store feature that is enabled on the database. Automatic plan correction feature will monitor the regression between the execution plans and force the last good plan for your queries automatically.
  6. If you are in Azure, Query store hints is currently in preview. Just like the Query hints, you can use the Query Store hints to a Query by using its Query_id but without modifying the underlying code. Query hints can be used by using a stored procedure sys.sp_query_store_set_hints.

Plan cache will also capture the execution plans, right? So, what is the difference?

Plan cache Vs Query store

Plan cacheQuery Store
Flushed out on every server restartRetains the data (CLEANUP_POLICY)
Stores latest plansCollects plans over time
Evicted on memory pressureFlush out intervals
Difficult to know why the plan changedUseful when troubleshooting performance problems

How to enable Query Store?

You can enable it by using the GUI or using T-SQL.

Operation modes:

Read write: This mode will read the information from the Query store (reading for any forced plan information or reading information by automatic plan correction etc) and write the queries, related resource consumption information into the Query store.

Read only: This mode will only read the information from the Query store. Queries will not be captured and written to the Query Store.

Off: Query Store is in a turned-off state.

How to set what Queries can be captured?

We can set this policy by using the capture modes available:

All: Will capture every query including every execution of ad hoc queries. Remember, this can quickly fill up your query store max size if you have many ad-hoc queries running on the database. This is not a recommended capture mode.

Auto: Will does not capture the queries that are not worth consideration. The threshold of what queries are captured is decided by Microsoft based on the query execution count, compile, and runtime duration. This is the recommended capture mode from Microsoft.

None: No queries will be captured by the Query Store.

Custom: You can customize the capture policy. During the mentioned capture policy time threshold, the conditions are evaluated, and based upon that the query will be captured in the Query Store.

Conclusion: In this blog post, we have gone through the overview of what the Query store feature is and the benefits of it being enabled on your user databases, the difference between the plan cache and Query Store, different operation modes, and capture modes. In the coming part, we will learn about the query store options and how the query store works behind the scenes.

Thank you for reading!




T-SQL TUESDAY #143: Short code examples

Thank you John McCormack for hosting this month of T-SQL Tuesday.

For the past couple of years as a DBA, I migrated several databases and used many handy scripts that helped me made my work easier. These scripts may be simple but if you have a migration project involving several SQL Servers with some hundreds of databases, test and production database migrations becomes tedious. I would like to share some of then here which you might already known them very well.

  1. I used this script answered by AlexK (look for the script with most accepted answer) to kill all connections to a database. I used this script in Development/Test/QA/Production during database migrations. A very handy script. I used this script while migrating 200 servers from SQL Server 2000 to SQL Server 2016.
  2. Other Script that I have used for migrations are sp_help_revlogin to transfer the logins during migrations.
  3. When using transactional replication between the servers, I used to see several distribution job failures when a new snapshot generated or during the subscriber reinitialization. The snapshot generating the .sch files for replication with ANSI_PADDING turned OFF instead of ON. I found a very handy PowerShell script answered by Asaf Mohammad in Microsoft forum which will search the .sch file having SET ANSI_PADDING OFF in all the folders and subfolders of the snapshot and replace them with SET ANSI_PADDING ON. Thanks to Asaf for saving many hours. As per the recommendation from this forum, I used this script as a second step in my snapshot agent job so this setting is fixed within the snapshot agent job. After using this PowerShell script, my distribution job worked fine.

None of the scripts I mentioned above were written by me. It is the hard work of other professionals who created these scripts and freely shared the scripts to the world helping other professionals. Kudos to all of them. There are several other scripts that I use on regular basis but I wanted to keep this post short and only mention the ones which I used frequently since past couple of years.

I am looking forward to see what other SQL family members post about their favorite handy scripts!

T-SQL Tuesday #141 Work/Life Balance

I always thought I never had a work-life balance. I am a mother of two little kids. When someone asks me how I manage kids and work at the same time (especially during the pandemic), I mostly respond that it has been a hell of a lot of work when kids are at home and it’s been difficult to handle kids and work at the same time but I love to see my children in front of my eyes.

Whenever I hear about Work/Life balance, I get stressed out and get worried thinking I might doing something entirely wrong. I feel this way because I know I do not have a balance that I would like to have. I always dreamed of having that perfect family time every day but something comes up and that story goes on. As each day passed by, my guilt of not spending as much time with my family has built up doing nothing.

So I thought, what am I doing wrong here or why am I feeling guilty or being so stressed about it? Then I thought, let me think about it for a second. I am doing my job, taking care of my kids, doing household chores, and spending the rest of my time with my kids and family. All the things I do are important for me and my family. I am not wasting any time here. What if I can make little changes and mindfully think of where I am losing much time, I may make things better.

I spend many months thinking of that perfect time to spend with family or to have some self-time focusing on mind and body. I understood later that there will never be a perfect time. Stressing out or feeling guilty doesn’t help. We may have a busy work schedule. That is completely fine. It is really important of how happy we are once we are done with our work. It may be a couple of hours a day or even an hour a day. We always want to spend more time with our family because that’s our life.

I thought, instead of worrying about If I am being right in spending enough time with family, I tried making little changes in my schedule every day which slowly added up some free time. For example, starting my work early in the morning so I can log off a little bit early and can complete my chores a bit early so I can spend more time reading books with my kids and doing some artwork with them. I feel better as I make some changes to my schedule. I still have a lot of things to adjust and make changes to. It is a continuous process.

Everyone’s life is different. Everyone have their priorities. Nothing is right or wrong. Some people love working for more hours. That doesn’t mean they do not care their personal life. They just love their work. It’s all about what makes you happy. Some people love gardening. They find peace in it. I love having a cup of coffee and having some self-time 🙂

It is about finding happiness in the little things we do every day!

Little changes in our schedules can add a lot of time. Observe where you are spending your time and make changes accordingly.

Thanks, Tjay Belt for hosting this month of T-SQL Tuesday!

T-SQL Tuesday #139: Hybrid World!

With the invitation from Ben Weissman for the June month of T-SQL Tuesday, here are some of my thoughts about Hybrid world. Thanks Ben for hosting this month of T-SQL Tuesday!

With the increasing amount of data in our databases, handling the resources might get tough. Many shops currently have their databases spread across different environments like on-premises and on the cloud. While they plan to completely move their data into the cloud, process might be not easy if they are dealing with lot of data across different datacenters. That’s when the Azure Arc data services can really help manage all of these in a single secure location. We can manage SQL managed instances, Postgres SQL instances or in any other public cloud in one single panel. We can run this on our infrastructure or in any other public cloud.

Azure Arc data services in SQL

For the companies who are on-premises, Azure Arc provides the latest technologies like the evergreen SQL which means there will be no end of support for their old versions of databases. This technology provides continuous automatic updates and the features supporting databases. This will help in the capacity optimization and scale-out smoothly for the data workloads on-premises databases with no application downtime.

I did not yet had a chance to work with the companies using the hybrid environments. That doesn’t have to limit me from learning these amazing technologies Microsoft has to offer. I already started my learning journey with Azure Arc data services. It is a bit overwhelming to understand everything at once as there is so much to learn about this new technology. It is never too late to begin the learning. As many companies started moving to cloud, its never too late to improve the skills. With this T-SQL Tuesday invitation from Ben, I started learning about the Azure Arc data services. Here are the list of resources I am following and plan to follow to get started with Azure Arc data services, Kubernetes and Big data clusters:

  1. Big Data Clusters for the Absolute Beginner: Thank you Mohammad Darab for this amazing introduction session to Big Data Clusters. Am sure you are going to give a big kudos to Darab after watching this session. It’s that Amazing! Also, don’t forget to look at the playlist from Darab youtube channel focusing on Big Data clusters.
  2. Azure Arc-enabled Data Services: The Big Picture: This is an excellent 52 minute course by Ben Weissman which I definitely recommend to watch if you wanted to get the overview and start the journey with Azure Arc enabled data services learning. I watched this course yesterday and I love it.
  3. Microsoft Learn: Microsoft offers free training. Customize the path you wanted to learn and start the learning journey. Also, you can get free Azure account for free worth of $200 credit for a month to get hands on experience.
  4. Azure Arc-Enabled Data Services Revealed: Here is the link to the book written by Ben Weissman and Anthony E. Nocentino who are specialized in this area. Understand the core concepts of Kubernetes, fundamentals and architecture of Azure Arc-enabled data services and get in-depth knowledge on these concepts.
  5. Microsoft Azure: Youtube channel for free Microsoft Azure trainings

I hope these above resources will be helpful to you in starting your Hybrid learning journey 🙂

I would like to learn from other SQL professionals about their journey in the Hybrid world!

Thanks for reading!

T-SQL Tuesday: How Do You Respond When Technology Changes Under You?

With the invitation from AndyLeonard for the May month of T-SQL Tuesday, here are my thoughts on how to respond to technology changes.

As there is a saying, “When there is no Change, there is no Growth”

This is true in every aspect of life including technology. As the Technology changes, it is helping our lives to be more simpler and easier than before. With the continuous advancements in the technology, many professionals find themselves rushing in learning the new technologies. These advancements in the technologies adding up new features to previously existed technologies improve in performance and solve problems making our lives easier. The only question is how quickly can we cope with this change?

The only answer I believe is to train the employees with the new changes in the technologies. It is really important for the companies to not only bring the latest technologies to the companies for the use but also to mainly focus on the employee trainings to use the latest technologies efficiently.

Apart from the trainings, there are many employees who love and always look for the ways to learn new technologies. For every new technologies we have now, a free training platform may already be available to train the individuals so they have ease in using those technologies in their work. For example, Microsoft Azure. We do have a free training from Microsoft known as Microsoft learn. Microsoft also offers a free azure account so professionals can go ahead and get that hands on experience as they learn. This platform has helped many professionals across the world including me and helping in advancing our careers. Huge thanks to Microsoft for taking this initiative.

To keep ourselves updated with the latest evolving technologies, I follow several resources out there along with the technical documentation itself. There are several blogs related to specific technologies, communities helping each other to learn these new technologies and several technical newsletters and publications to keep us updated. I follow social media articles and posts from linkedin and twitter to be updated as much as possible with upcoming technologies.

Along with these, there are several youtube channels providing the concepts of the new technologies as they evolve. There are other training companies who can actually provide trainings especially from beginner to advance level along with hands on labs.

Keeping us up to date to know what is going on is not a tough job but investing time and interest in learning these technologies to effectively use in our daily work life is really important.

At the beginning, everything seems to be so confusing. If we see the number of technologies evolving at the same time, it is overwhelming for sure but if we can take one technology at a time and if we can focus on how the new technologies can actually simplify our daily work, we will be embracing the new technologies for the latest innovations they can offer.

Finally, it is very important to be ready for more and more advancements and to anticipate the changes. As the technology evolves, it becomes much easier to use. Keeping ourselves updated is the only way to keep growing!

Change is Good 🙂

T-SQL Tuesday #137: Jupyter Notebooks

With the invitation of Steve Jones for April month of T-SQL Tuesday, I am going to share some of my thoughts on using Jupyter notebooks.

Jupyter Notebook is an open-source web application that can serve us to create, share code and documents with others. It provides that beautiful way to document your code, text, images and query results. Jupyter notebooks in Azure data studio can run PySpark, Python 3, Spark R and Scala, as well as PowerShell. Run and visualize the data as you see the results without having to leave the environment. This is an excellent tool for documenting things. You do not have to write your long emails attaching bunch of screenshots any more. Once you start using the Jupyter notebooks, it will save you so much time and give you peace of mind. This tool can not only be used for documentation purposes but also can be used for your presentations for your demo purposes. Not only for the SQL code, this is a very nice tool for doing machine learning models, visualizing data and much more. Your code can be written in different cells which can be executed individually. This can help us to run specific part of the code in the project. This tool supports Python, R as well.

There are some magic commands that you can use to make your work easier. %clear, %autosave, %debug and %mkdir are some of the commands and these magic commands can be used in two ways. Linewise and cellwise. Linewise is when you wish to run a single command and cell-wise is when you wish to run an entire block of code.

There are also interactive dashboards in Jupyter notebooks.

When you wish to share these jupyter files and as they are in json format. You can save the notebook using different file options like a CSV, Excel, JSON or XML file . You can also convert your notebook into different formats by using the nbconvert option. For analytical queries, you can also build a chart for the data.

You can use the Jupyter notebooks for giving presentations to your customers and clients about your projects.

Jupyter notebooks is an amazing tool as you can document everything what you do. Once we start using Jupyter notebooks, you would definitely do not want to turn back to the previously used tools anymore. I recommend this tool to anyone who would like to explore and take advantage of many features the Jupyter notebooks has to offer.

T-SQL Tuesday #136: Least Favorite Data Type

With the Invitation of Brent Ozar for this month T-SQL Tuesday, I would like to share some thoughts on least favorite data type in SQL Server. Thank you Brent for hosting March month of T-SQL Tuesday.


Though VARCHAR(MAX) is suitable in situations with large strings of data, it has its own complications that we need to consider. In my career as a DBA, I at least saw couple of times SQL developers using VARCHAR(MAX) when they should not. Fixing the datatypes once in production is painful and causes risks.

When the data is stored in VARCHAR(n) datatype column, these values get stored in standard data page. VARCHAR(MAX) uses IN_ROW_Data up to 8000 bytes of data but if the data in more than 8000 bytes, it uses LOB_Data page and a pointer (reference) is stored in the IN_ROW_Data_page. We cannot create an Index on the VARCHAR(MAX) data type as a key column in the index. You can add the column of VARCHAR(MAX) as an included column but that wouldn’t be seekable and will duplicate the column completely which is lot of storage. We cannot compress the LOB data and the data retrieval from the LOB data is relatively slow. If the LOB data is stored as IN-ROW, it can be compressed but only when we compress the table with the PAGE level compression. LOB data cannot be compressed if you use ROW level compression and doesn’t depend if the LOB data is stored as in row or out of row.

Any variable you declare in VARCHAR(MAX) as the datatype will automatically spills to Tempdb which can cause performance problems. If you are using the VARCHAR(MAX) datatype column in your queries and sorting them, that requires so much memory to sort this data which may cause the memory issues. For selecting data having this datatype more than 8000 bytes can cause several off page I/O’s.


If you are running queries and searching on column, that column should always be used as a fixed length column instead of VARCHAR(MAX) data type. If your row size can be managed to be less than 8000 bytes, that will store the data IN_ROW_Data_page which can avoid any page splits and avoids locking or latching during the updates. Limiting the length of the strings is really important for designing any database. Using VARCHAR(n) is recommended over VARCHAR(MAX) when ever possible.

I am really looking forward to what other SQL family members have to say about their least and the most favorite data types!

T-SQL Tuesday #135: Tools of the Trade

With the Invitation of Mikey Bronowski for this month T-SQL Tuesday Tools of the Trade, I would like share some useful tools that make my job easier:

  1. OBS Studio: This is a free and open source software for video recording and live streaming. I mostly prerecord my sessions using OBS. I personally love this tool as we have pretty much good content on YouTube that teach us how to use this tool.
  2. SentryOne Plan Explorer: Plan explorer is an amazing tool to analyze your execution plan and tune your queries very quickly. Its completely free.
  3. SQL Search: It is a free search for databases. If you need to find any specific object/column or any string on any database on the server, this tool is very useful. I use SQL search regularly.
  4. SQL Compare: SQL compare tool is very useful to compare the database schema and data between environments. I use this tool regularly.
  5. SQLQueryStress: It is a lightweight performance testing tool, designed to load test individual queries. I personally use this tool for my demos and testing any queries for performance.
  6. Zoomit: ZoomIt is a free screen zoom and annotation tool for presentations. I use it regulary and I am sure most of the speakers already know about the tool.
  7. Diffchecker: A very useful too to compare code/documents.
  8. Adobe Lightroom: This is a photo editing tool which I personally liked. It is professional and used by many photographers across the world.
  9. Free Download Manager: I use this tool for my downloads. It is much faster to download files. I use this tool especially for YouTube downloads. If you need to download the playlists at a time in a single shot, this tool is really useful. It is completely free.
  10. Nimbus Screenshot: It is a chrome extension. This tool is really useful when you wanted to take the screenshot of entire page. You can select the area you wanted to take the screenshot by scrolling the page. Very useful.

These are some of the useful tools I use regularly. I would also like to see the list of the tools others feel useful. I would love to start using those tools as well.