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

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.

| improve this answer | |
10

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

| improve this answer | |
  • 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 '19 at 18:20
  • 4
    ;) 13 more to go – Sean Gallardy - Retired User Mar 3 '19 at 18:30
  • This is now released, so someone could give an answer and win muchos pointalones. – Brent Ozar Apr 8 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.