• Facebook - Black Circle
  • Twitter Round
  • Instagram Black Round
  • LinkedIn - Black Circle

SEARCH BY TAGS: 

Please reload

RECENT POSTS: 

FOLLOW ME:

  • Facebook - Black Circle
  • Twitter - Black Circle
  • Instagram - Black Circle
  • LinkedIn - Black Circle

Query Store Overview

June 25, 2018

 

 

 

 

It's amazing that we never had a built in feature that logged historical record of queries. Sure we had the plan cache, but that was cleared with a service restart or reboot. How many times have you walked into an environment and they would say the server was slow yesterday, or at 2:00am last night, what happened?

 

Well, it's tough to pin point if you didn't create some means of logging (hint: try logging the output of sp_whoisactive periodically!) or a third party tool.

 

What is it?

As of SQL Server 2016, Microsoft has introduced a feature called "Query Store".  Query store provides a persistent means of viewing the history of executed queries along with a bunch of statistics as well as execution plans. 

 

Is this feature turned on by default? No, but it should be! Until then, feel free to come up with a custom solution to enable it by default (think, modelDB).

 

Why do I need it?

You can identify expensive queries based on CPU, I/O, Memory.

Historical reference for Queries (so you know if any over night process was running slow! or if it was executed a number of times)

Possibly resolve parameter sniffing issues by forcing execution plans. 

 

How do I set it up?

Right click on your database, go to properties and select query store.

 

 

I typically set the operation mode to Read/Write mode as it will collect query plans and runtime statistics. 

 

Statistics collection interval will determine how often query information will be collected. it's up to you to figure out a balance between disk space and granularity. 

 

Max size determines the maximum size of the query store. Bear in mind that this feature doesn't autogrow like a database. upon reaching the maximum size it will switch to what is known as read only mode and no longer collect plans and statistics. 

 

 

 

 

Share on Facebook
Share on Twitter
Please reload