Default Query Store Settings

Erin Stellato talks about default query store settings:

For each database where I enable Query Store, I’d consider the workload and then look at the settings.  I tend to think that the default value of 100MB for MAX_STORAGE_SIZE_MB is really low, and I would be inclined to bump up STALE_QUERY_THRESHOLD_DAYS from 30 to something a bit higher.  I’d also probably drop  DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it.  This setting determines how often Query Store data is flushed to disk.  If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.  I’d also think about changing INTERVAL_LENGTH_MINUTES to a value smaller than 60 if I wanted to aggregate my query data over a smaller amount of time.  Sometimes interesting events happen within a 60 minute time frame, and they get can lost when data is aggregated across that window.  However, aggregating more frequently means I’m adding processing overhead to the system – there’s a trade-off there to figure out.

In our environment at least, 100 MB of query store data would last, oh, a couple hours?  Definitely tweak your settings and keep an eye on them early on.

Related Posts

Query Store On Azure SQL DW

Matt Usher announces Query Store is now available to all in Azure SQL Data Warehouse: Since our preview announcement, hundreds of customers have been enabling Query Store to provide insight on query performance. We’re excited to share the general availability of Query Store worldwide for Azure SQL Data Warehouse. Query Store automatically captures a history of queries, plans, and […]

Read More

Query Store Bugfixes

Erin Stellato has a roundup of Query Store bugfixes as of January 2019: The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade.  As of this writing, the current CU for […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031