No tags yet.

SEARCH BY TAGS: 

RECENT POSTS: 

FOLLOW ME:

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

Query Store Overview


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.

#SQLServer2017 #sqlserver #PerformanceTuning #microsoft

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