The new Query Store is one of the most exciting features coming with SQL Server 2016, but unfortunately comes with some limitations. Learn how we can overcome one of those
This post is part of the T-SQL Tuesday, which is a monthly blog party on the second Tuesday of each month. Everyone is welcome and have the chance to write about SQL Server.
This month’s topic is to give a [SQL] gift, so here is mine,
Making the Query Store to work in a READ_ONLY database.
Last week I explained why, from my point of view, we should treat the new Query Store as user data for several reasons.
One of the reasons is how the Store behaves when the database goes READ_ONLY, which was earlier explained by Kendra Little (b|t) here
This is the post I wanted to write first, but getting everyone on the same page first (with the previous one) seemed more appropriate, so I postponed it.
We have seen that in order to get the new Query Store, the database must be READ_WRITE, which might be a bit disappointing because people normally use READ_ONLY database for reporting, so we still might be interested in tune most frequent queries or those that consume lots of resources, so this new tool can help a lot to identify them.
Since that limitation comes together with the feature, the only we can do is to workaround it 🙂
Bypassing Read-Only database Query Store limitations
Disclaimer: This approach will not work if you have strong restrictions and your database must be read-only
Said that, we can start.
Although the query store’s information looks like is part of the databases’s metadata and it’s accessible using DMV’s in the [sys] schema, it behaves as it’s user data, so when the database goes READ_ONLY so does the Query Store.
First let’s see the normal behaviour when we create a new database
USE master GO IF DB_ID('read_only_db') IS NOT NULL BEGIN ALTER DATABASE read_only_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE read_only_db END GO CREATE DATABASE read_only_db GO ALTER DATABASE read_only_db SET QUERY_STORE = ON GO
Once we have our database with the Query Store enabled, we can start running queries
USE read_only_db GO CREATE TABLE dbo.t( c1 INT IDENTITY CONSTRAINT PK_t PRIMARY KEY ) GO SELECT * FROM dbo.t WHERE c1 = 26 GO 10 SELECT q.query_id, qt.query_sql_text, qr.last_execution_time, qr.count_executions FROM sys.query_store_query AS q LEFT JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id LEFT JOIN sys.query_store_plan AS qp ON qp.query_id = q.query_id CROSS APPLY (SELECT SUM(count_executions) AS count_executions, MAX(last_execution_time) AS last_execution_time FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id) AS qr WHERE qt.query_sql_text = '(@1 tinyint)SELECT * FROM [dbo].[t] WHERE [c1]=@1' GO
There are 10 executions for our query in the Query Store.
Now we can see how the counter stops when the database goes READ_ONLY.
ALTER DATABASE read_only_db SET READ_ONLY WITH ROLLBACK IMMEDIATE GO SELECT * FROM dbo.t WHERE c1 = 26 GO 10 SELECT q.query_id, qt.query_sql_text, qr.last_execution_time, qr.count_executions FROM sys.query_store_query AS q LEFT JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id LEFT JOIN sys.query_store_plan AS qp ON qp.query_id = q.query_id CROSS APPLY (SELECT SUM(count_executions) AS count_executions, MAX(last_execution_time) AS last_execution_time FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id) AS qr WHERE qt.query_sql_text = '(@1 tinyint)SELECT * FROM [dbo].[t] WHERE [c1]=@1' GO
Databases’ metadata resides in the PRIMARY filegroup and we can take advantage of that. By adding a new filegroup where to place user data, we can leave PRIMARY exclusively for metadata.
Which is another little proof of why database design matters.
We need to get the database back to READ_WRITE to be able to add new files or filegroups, so we’ll do that first. Then we will move our table to the new filegroup by recreating the clustered index there.
ALTER DATABASE read_only_db SET READ_WRITE WITH ROLLBACK IMMEDIATE GO ALTER DATABASE read_only_db ADD FILEGROUP [USER_DATA] GO ALTER DATABASE read_only_db MODIFY FILEGROUP [USER_DATA] DEFAULT GO ALTER DATABASE read_only_db ADD FILE (NAME='UserData', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\UserData.ndf') TO FILEGROUP [USER_DATA] GO CREATE UNIQUE CLUSTERED INDEX PK_t ON dbo.t (c1) WITH (DROP_EXISTING = ON) ON [USER_DATA]; GO ALTER DATABASE read_only_db MODIFY FILEGROUP [USER_DATA] READ_ONLY GO
After these changes, our database is still READ_WRITE, but our data is READ_ONLY because we have moved it to a READ_ONLY filegroup. Also note that I made the new filegroup as DEFAULT, so any attempt of creating a table (without specifying another READ_WRITE filegroup) will fail.
Trying to insert data in our table, will produce an error.
INSERT INTO dbo.t DEFAULT VALUES GO
But the Query store still works with no problem
SELECT * FROM dbo.t WHERE c1 = 26 GO 10 SELECT q.query_id, qt.query_sql_text, qr.last_execution_time, qr.count_executions FROM sys.query_store_query AS q LEFT JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id LEFT JOIN sys.query_store_plan AS qp ON qp.query_id = q.query_id CROSS APPLY (SELECT SUM(count_executions) AS count_executions, MAX(last_execution_time) AS last_execution_time FROM sys.query_store_runtime_stats WHERE plan_id = qp.plan_id) AS qr WHERE qt.query_sql_text = '(@1 tinyint)SELECT * FROM [dbo].[t] WHERE [c1]=@1' GO
You can see how again the query store keeps counting the executions of our query.
Conclusion
As I said before, you need the ability of leaving your database of READ_WRITE while your user data is READ_ONLY to accomplish it, but I am sure with this trick and a couple of triggers you can stop people messing with the data and at the same time enjoy this new feature, which I remind you it’s available for all editions, even Express (although there are some caveats like this)
And just to finish, I’ll leave you some links. Thanks for reading
- A bit old, but still applicable most of it, SQL Server Best Practices: Setting a Default Filegroup
- Move an Existing Index to a Different Filegroup
- ALTER DATABASE File and Filegroup Options
Great post – thank you for posting!