{"id":250,"date":"2016-07-12T10:54:49","date_gmt":"2016-07-12T09:54:49","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=250"},"modified":"2022-12-25T10:13:04","modified_gmt":"2022-12-25T10:13:04","slug":"sql-server-2016-using-the-query-store-for-read-only-databases","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2016\/07\/12\/sql-server-2016-using-the-query-store-for-read-only-databases\/","title":{"rendered":"T-SQL Tuesday #80 – SQL Server 2016, using the Query Store for READ-ONLY databases"},"content":{"rendered":"

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 \"tsql2sday150x150\"<\/a><\/p>\n

 
\nThis post is part of the T-SQL Tuesday<\/a>, 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.
\nThis month’s topic is to give a [SQL] gift<\/a>, so here is mine,
\nMaking the Query Store to work in a READ_ONLY database.<\/p>\n

 
\n 
\nLast week I explained why, from my point of view, we should treat the new Query Store as user data for several reasons<\/a>.<\/p>\n

One of the reasons is how the Store behaves when the database goes READ_ONLY, which was earlier explained by Kendra Little (b<\/a>|t<\/a>) here<\/a><\/p>\n

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.<\/p>\n

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.<\/p>\n

Since that limitation comes together with the feature, the only we can do is to workaround it \ud83d\ude42<\/p>\n

 
\nBypassing Read-Only database Query Store limitations<\/strong><\/p>\n

Disclaimer: This approach will not work if you have strong restrictions and your database must be read-only<\/strong><\/p>\n

Said that, we can start.<\/p>\n

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<\/a>, so when the database goes READ_ONLY so does the Query Store.<\/p>\n

First let’s see the normal behaviour when we create a new database<\/p>\n

\r\nUSE master\r\nGO\r\nIF DB_ID('read_only_db') IS NOT NULL BEGIN\r\n\tALTER DATABASE read_only_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n\tDROP DATABASE read_only_db\r\nEND\r\nGO\r\n\r\nCREATE DATABASE read_only_db\r\nGO\r\n\r\nALTER DATABASE read_only_db SET QUERY_STORE = ON\r\nGO\r\n<\/pre>\n
Once we have our database with the Query Store enabled, we can start running queries<\/p>\n
\r\nUSE read_only_db\r\nGO\r\n\r\nCREATE TABLE dbo.t(\r\nc1 INT IDENTITY CONSTRAINT PK_t PRIMARY KEY\r\n)\r\nGO\r\n\r\nSELECT * FROM dbo.t WHERE c1 = 26\r\nGO 10\r\n\r\nSELECT q.query_id, qt.query_sql_text, qr.last_execution_time, qr.count_executions\r\n\tFROM sys.query_store_query AS q\r\n\t\tLEFT JOIN sys.query_store_query_text AS qt\r\n\t\t\tON qt.query_text_id = q.query_text_id\r\n\t\tLEFT JOIN sys.query_store_plan AS qp\r\n\t\t\tON qp.query_id = q.query_id\r\n\t\tCROSS 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\r\n\tWHERE qt.query_sql_text = '(@1 tinyint)SELECT * FROM [dbo].[t] WHERE [c1]=@1'\r\nGO\r\n<\/pre>\n
\"01_first_query\"<\/a><\/p>\n
There are 10 executions for our query in the Query Store.<\/p>\n
Now we can see how the counter stops when the database goes READ_ONLY.<\/p>\n
\r\nALTER DATABASE read_only_db SET READ_ONLY WITH ROLLBACK IMMEDIATE\r\nGO\r\n\r\nSELECT * FROM dbo.t WHERE c1 = 26\r\nGO 10\r\n\r\nSELECT q.query_id, qt.query_sql_text, qr.last_execution_time, qr.count_executions\r\n\tFROM sys.query_store_query AS q\r\n\t\tLEFT JOIN sys.query_store_query_text AS qt\r\n\t\t\tON qt.query_text_id = q.query_text_id\r\n\t\tLEFT JOIN sys.query_store_plan AS qp\r\n\t\t\tON qp.query_id = q.query_id\r\n\t\tCROSS 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\r\n\tWHERE qt.query_sql_text = '(@1 tinyint)SELECT * FROM [dbo].[t] WHERE [c1]=@1'\r\n\r\nGO\r\n<\/pre>\n
\"02_read_only_query\"<\/a><\/p>\n
 
\nDatabases’ 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.
\nWhich is another little proof of why database design matters.<\/p>\n
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.<\/p>\n
\r\nALTER DATABASE read_only_db SET READ_WRITE WITH ROLLBACK IMMEDIATE\r\nGO\r\n\r\nALTER DATABASE read_only_db ADD FILEGROUP [USER_DATA]\r\nGO\r\n\r\nALTER DATABASE read_only_db MODIFY FILEGROUP [USER_DATA] DEFAULT\r\nGO\r\n\r\nALTER 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]\r\nGO\r\n\r\nCREATE UNIQUE CLUSTERED INDEX PK_t ON dbo.t (c1) WITH (DROP_EXISTING = ON) ON [USER_DATA];  \r\nGO  \r\n\r\nALTER DATABASE read_only_db MODIFY FILEGROUP [USER_DATA] READ_ONLY\r\nGO\r\n<\/pre>\n
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.<\/p>\n
Trying to insert data in our table, will produce an error.<\/p>\n
\r\nINSERT INTO dbo.t DEFAULT VALUES\r\nGO\r\n<\/pre>\n
\"03_insert_error\"<\/a><\/p>\n
But the Query store still works with no problem<\/p>\n
\r\nSELECT * FROM dbo.t WHERE c1 = 26\r\nGO 10\r\n\r\nSELECT q.query_id, qt.query_sql_text, qr.last_execution_time, qr.count_executions\r\n\tFROM sys.query_store_query AS q\r\n\t\tLEFT JOIN sys.query_store_query_text AS qt\r\n\t\t\tON qt.query_text_id = q.query_text_id\r\n\t\tLEFT JOIN sys.query_store_plan AS qp\r\n\t\t\tON qp.query_id = q.query_id\r\n\t\tCROSS 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\r\n\tWHERE qt.query_sql_text = '(@1 tinyint)SELECT * FROM [dbo].[t] WHERE [c1]=@1'\r\n\r\nGO\r\n<\/pre>\n
\"04_read_only_db_query_store_read_write\"<\/a><\/p>\n
You can see how again the query store keeps counting the executions of our query.<\/p>\n
 
\nConclusion<\/strong><\/p>\n
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<\/a>)<\/p>\n
And just to finish, I’ll leave you some links. Thanks for reading<\/p>\n