Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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'.
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.
Currently, in CTP 2.3, in so far as I can tell - you cannot enable this, even under DAC.
