SQL Server 2019 – How to Enable Result Set Caching

sql serversql-server-2019

SQL Server 2019 CTP 2.3 added a few new columns to sys.databases, including is_result_set_caching_on:

It's not documented yet in the What's New in SQL Server 2019, nor in the ALTER DATABASE page.

I tried using the same syntax as Accelerated Database Recovery just for laughs:

ALTER DATABASE StackOverflow2013 SET RESULT_SET_CACHING ON;

But no luck:

Msg 5058, Level 16, State 12, Line 8
Option 'RESULT_SET_CACHING' cannot be set in database 'StackOverflow2013'.

Best Answer

This is a feature available only in Azure Synapse Analytics.

When you create your database using CREATE DATABASE DBName (EDITION = 'DataWarehouse'), you will be able to use ALTER DATABASE DBName SET RESULT_SET_CACHING ON.

Quote from Azure Synapse Analytics release notes:

When result set caching is enabled, query results are automatically cached in the user database for repetitive use. This allows subsequent query executions to get results directly from the persisted cache so recomputation is not needed. Result set caching improves query performance and reduces compute resource usage. In addition, queries using cached results set do not use any concurrency slots and thus do not count against existing concurrency limits. For security, users can only access the cached results if they have the same data access permissions as the users creating the cached results. For more information, see Performance tuning with result set caching. Applies to version 10.0.10783.0 or higher.

Here is the documentation page for ALTER DATABASE on Azure Synapse Analytics.

Related Solutions

Sql-server – Restoring a DB issues

You cannot set your database to single-user mode or offline because your database is already in a RESTORING state.

Second, your script tries to use the stored procedure sp_fixusers. This is a custom stored procedure to fix orphaned users. If you need to use this stored procedure, you need to re-create it first, using the script in the link above. But it is possible that your databases uses a different version and it will be better if you use the sp_fixusers from your server/database.

Sql-server – SQL Server DB restore fails

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'xyz' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

This error does not occur if YOU are the single-user in the database. It only occurs if someone else is in it AND it is ALREADY in single-user mode.

For example, try this:

create database test
--
use test
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Note: there are 4 separate batches, and the 4th one doesn't error. You're setting the mode while IN the database, and also setting it a 2nd time. No error.

Assuming you have been given the mandate to restore the DB regardless of what is happening, do an sp_who2 to look for the user who is connected, and KILL the spid. You can then proceed with the RESTORE.

Best Answer

Related Solutions

Sql-server – Restoring a DB issues

Sql-server – SQL Server DB restore fails

Related Question