20

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

sys.databases

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'.
3
  • 2
    This was mentioned in one of the SQL Bits presentations in the context of polybase I think Mar 3, 2019 at 21:38
  • 4
    You're going to need some windbg skills there, bud.
    – DBArgenis
    Mar 4, 2019 at 16:30
  • 3
    @DBArgenis You'd need 11,907 WinDbg skills I'd reckon
    – Paul White
    Jun 8, 2019 at 19:03

2 Answers 2

2

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.

10

Currently, in CTP 2.3, in so far as I can tell - you cannot enable this, even under DAC.

3
  • 11
    You can, it's just, uh...I'm... (loosens collar)...looking for someone willing to go on record with it publicly. :-)
    – Brent Ozar
    Mar 3, 2019 at 18:20
  • 4
    ;) 13 more to go Mar 3, 2019 at 18:30
  • This is now released, so someone could give an answer and win muchos pointalones.
    – Brent Ozar
    Apr 8, 2020 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.