Jul
12
2016

T-SQL Tuesday #80 – SQL Server 2016, using the Query Store for READ-ONLY databases

tsql2sday150x150

 
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

01_first_query

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

02_read_only_query

 
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

03_insert_error

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

04_read_only_db_query_store_read_write

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

 
 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit
3 comments
  1. Oddvar Eikli says:

    Great post – thank you for posting!

Leave a Reply

Your email address will not be published. Required fields are marked *