on November 1, 2016
A couple of questions on SQL Server 2016’s new Query Store feature came up in my recent pre-conference session on index tuning in SQL Server. I wasn’t 100% sure of the answer offhand, so I promised to follow up in a blog post.
Here are the questions:
Can you change the filegroup where Query Store keeps its data?
I thought there might be a trick to use a different filegroup for Query Store by using the default filegroup setting in SQL Server before enabling it, but NOPE!
Please vote for this to be improved in this Connect Item.
(It only has 6 votes at the time of this writing! It needs more. And thanks to Derek Bell for emailing me the link to the Connect item.)
What happens if you’re using ‘Optimize for Adhoc Workloads’ and Query Store?
I was pretty sure I’d read a post from Grant Fritchey about this, and sure enough, he covers the behavior here.
Spoiler: Enabling Optimize for Adhoc doesn’t prevent the plan from being stored in Query Store on first run.
Grant has a bunch of great posts on Query Store. Check them all out here.
REGISTER