Day: January 3, 2019

If you’re on SQL Server 2016 or above, maybe you’re thinking about using the Query Store. That’s good! It’s an incredibly useful tool for performance tuning, and it can help you scale to handle performance on many servers.

There’s one thing I think you should know. The max size limit for the Query Store is not a hard limit.

Controlling Query Store size

There’s a couple settings you can change to control the Query Store size. First, you want to set the Query Capture Mode to Auto, especially if you have an ad-hoc workload. There’s a great post by Erik Darling on BrentOzar.com about this setting. In summary, I recommend Auto to control the speed at which the Query Store grows.

Second, you can set the max size. You can do this in the database properties or with T-SQL, like so:

ALTER DATABASE [DBA]  
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 100);  

But the MAX_STORAGE_SIZE_MB is not a hard limit

I’ve seen this occur in production systems with heavy ad-hoc workloads. As the Query Store gathers data, there seems to be a point where the writes are queued up. When they are written to disk, the max size can be exceeded.

Anecdotally, I’ve seen a production systems where the max size has been vastly exceeded. But let me show you an example on a small scale on my demo machine.

Bad Query Store settings to reproduce this issue

To make this demo easy on my machine, I’m going to set the settings to the opposite of what I just recommended. Don’t use the settings below, this is just for the test.

And now I need some ad-hoc bad queries to write to the Query Store. Here’s what I came up with. I ran the output of this query, and pretty much instantly over-filled the Query Store.

SELECT top 10000 
	N'SELECT N''' + REPLACE(text,N'''',N'') 
	+ N''' FROM sys.messages WHERE severity > 1 and text = ''' 
	+ REPLACE(text,N'''',N'') + N'''' 
FROM sys.messages

Now let’s look at the Query Store properties.

Will this happen to my production system?

If you’re asking yourself if this will happen to you, I can’t answer that. First, make sure you have the right settings. Erin Stellato at SQL Skills has great advice on how to setup the Query Store.

I think this scenario is very unlikely in your system, unless you have very massive queries ad-hoc queries, and the Query Store setup incorrectly.