Enabling Query Store

, 2018-12-05

Performance tuning in SQL Server is one of the main jobs a DBA has.  Starting with SQL Sever 2016 a great tuning tool can be turned on for databases called Query Store.

Query Store is a product designed to help you tune queries based on performance and resource usage.  In order for Query Store to capture resource stats it has to be enabled and configured.

To configure and turn on Query Store follow the sql statements below (keep in mind this is highly customizable).  By Default Query Store operation mode is set to off.  Step 1 turns on query store and Step 2 does some simple configuration of query store.

  1. alter database [databasename] set query_store = on

    go

  2. alter database [databasename]

    set query_store (

    operation_mode = read_write,

    cleanup_policy = (stale_query_threshold_days = 30), — You can alter this to hold more historical information.

    data_flush_interval_seconds = 900,

    interval_length_minutes = 60, –This is the time window in which stats are captured

    max_storage_size_mb = 100, — Set this size based on the size of the database, keep in mind this adds to the overall size of the mdf files.

    query_capture_mode = Auto, –This can be set as All, Auto, None.  Auto will capture data just for high resource queries.

    size_based_cleanup_mode = auto,  — can be set to auto or off (I suggest auto as it cleans data as it reaches the max size)

    max_plans_per_query = 200);

This is a great tool to enable to tune problematic queries.  For more information regarding Query Store check out the links below.

Query Store Best Practices

Query Store Usage Scenarios 

Happy tuning folks!

The post Enabling Query Store appeared first on VitaminDBA.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads