SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Enabling Query Store

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.

vitamindba

VitaminDBA.com is a source of SQL Server News and Tips coming from a SQL Server sr. database analyst with 10 years of IT experience ranging from Windows Server, Network, and SQL Server administration. I upload articles on the blog on a fairly consistent basis with the intention of at least 1 new article a week.

Comments

Leave a comment on the original post [vitamindba.com, opens in a new window]

Loading comments...