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.