The benefits of caching have been stated numerous times throughout this book and in this chapter. The fact is, retrieving data from memory is faster than retrieving it from disk, which is an I/O transaction. What has not been mentioned is the fact that dedicated SQL pools will cache repetitive query results with a feature, once enabled, called result set caching. Not only does that make queries return faster, but it also reduces the demand for computational resources, as the query does not have to be recompiled. To check if result set caching is enabled, execute the following SQL query, which is followed by sample results:

SELECT name, is_result_set_caching_on
FROM   sys.databases

Since the result renders a value of False for the database identified in the name column, execute the following SQL command to enable result set caching. The target database must be master, and you need to replace databaseName with your database. After the successful execution of this SQL command, the value for is_result_set_caching_on for the targeted database will be set to True.

ALTER DATABASE <databaseName> SET RESULT_SET_CACHING ON

Table 10.4 contains a DMV named dm_pdw_exec_requests that contains a column named result_cache_hit, which will identify if and how the query utilized caching. Execute the following query, which provides insights into whether or not a query is being retrieved from cache, and is followed by some sample results:

SELECT request_id, total_elapsed_time, command, result_cache_hit
FROM sys.dm_pdw_exec_requests
WHERE result_cache_hit IS NOT NULL
ORDER BY submit_time DESC

The first time a SQL query is executed, it is not cached in the result set cache. You can see this behavior in the table where the result_cache_hit is equal to 0. Also note that the amount of time to perform the query is 125 ms, which is fast but three times slower than the same query run against the cache, which completed in 46 ms. The first line in the previous table was the second execution of the SQL query and was read from cache, as identified by the value of 1. If this query were executed 100 million times per day, then at the end of the month that would be a significant time savings. There are also some Database Console Commands (DBCC) that are helpful with managing result set cache, for example, SHOWRESULTCACHESPACEUSED and DROPRESULTSETCACHE. As its name suggests, SHOWRESULTCACHESPACEUSED will show you information about the storage space used for caching for the database on which it is run. The following is an example of the SHOWRESULTCACHESPACEUSED output:

+—————-+————+————-+————–+
| RESERVED_SPACE | DATA_SPACE | INDEX_SPACE | UNUSED_SPACE |
+—————-+————+————-+————–+
| 5938704        | 5935848    | 1656        | 1200         |
+—————-+————+————-+————–+

The DROPRESULTSETCACHE command does just as the name implies, in that executing it will remove all the cache entries from the database.

There is one other form of caching available for a dedicated SQL pool: adaptive cache. This was introduced in Chapter 9, illustrated in Figure 9.17, and described in Table 9.6. This caching method delivers performance gains by knowing where data needs to be located based on access frequency and patterns. The data is physically stored as close to the compute as possible using a combination of both a fast SDD cache layer and remote storage. The metrics to determine the success and impact of the adaptive cache are Adaptive Cache Hit Percentage and Adaptive Cache Used Percentage. Low numbers for those metrics can signify that your dedicated SQL pool needs additional capacity.