Ajuste del rendimiento con la copia en caché del conjunto de resultadosPerformance tuning with result set caching

Cuando se habilita el almacenamiento en caché del conjunto de resultados, el grupo de SQL dedicado almacena automáticamente en caché los resultados de la consulta realizada en la base de datos de usuario para un uso repetido.When result set caching is enabled, dedicated SQL pool automatically caches query results in the user database for repetitive use. Esto permite que las ejecuciones posteriores de la consulta obtengan los resultados directamente de la memoria caché persistente, por lo que no es necesario volver a realizar el proceso.This allows subsequent query executions to get results directly from the persisted cache so recomputation is not needed. La copia en caché del conjunto de resultados mejora el rendimiento de las consultas y reduce la utilización de recursos de proceso.Result set caching improves query performance and reduces compute resource usage. Además, las consultas que usan conjuntos de resultados en la memoria caché no usan ningún espacio de simultaneidad y, por lo tanto, no cuentan para los límites de simultaneidad existentes.In addition, queries using cached results set do not use any concurrency slots and thus do not count against existing concurrency limits. Por seguridad, los usuarios solo pueden acceder a los resultados en la memoria caché si tienen los mismos permisos de acceso a los datos que los usuarios que crearon estos resultados.For security, users can only access the cached results if they have the same data access permissions as the users creating the cached results.

Comandos claveKey commands

Activación o desactivación (ON/OFF) de la copia en caché del conjunto de resultados de una base de datos de usuarioTurn ON/OFF result set caching for a user database

Activación o desactivación (ON/OFF) de la copia en caché del conjunto de resultados de una sesiónTurn ON/OFF result set caching for a session

Comprobación del tamaño del conjunto de resultados copiado en cachéCheck the size of cached result set

Limpieza de la memoria cachéClean up the cache

No se copia en cachéWhat's not cached

Una vez activada la copia en caché del conjunto de resultados de una base de datos, se copian en caché los resultados de todas las consultas hasta que la memoria caché está llena, excepto en el caso de las siguientes consultas:Once result set caching is turned ON for a database, results are cached for all queries until the cache is full, except for these queries:

  • Consultas con funciones integradas o expresiones en tiempo de ejecución que no son deterministas, incluso cuando no hay ningún cambio en los datos o la consulta de las tablas base.Queries with built-in functions or runtime expressions that are non-deterministic even when there’s no change in base tables’ data or query. Por ejemplo, DateTime.Now(), GetDate().For example, DateTime.Now(), GetDate().
  • Consultas con funciones definidas por el usuarioQueries using user defined functions
  • Consultas que usan tablas con la seguridad de nivel de fila o la seguridad de nivel de columna habilitadasQueries using tables with row level security or column level security enabled
  • Consultas que devuelven datos con un tamaño de fila superior a 64 kBQueries returning data with row size larger than 64KB
  • Consultas que devuelven datos de gran tamaño (más de 10 GB)Queries returning large data in size (>10GB)

Nota

  • Algunas funciones no deterministas y expresiones en tiempo de ejecución pueden ser deterministas para consultas repetitivas con los mismos datos.Some non-deterministic functions and runtime expressions can be deterministic to repetitive queries against the same data. Por ejemplo, ROW_NUMBER().For example, ROW_NUMBER().
  • Utilice ORDER BY en la consulta si el orden o la secuencia de las filas del conjunto de resultados de la consulta es importante para la lógica de la aplicación.Use ORDER BY in your query if the order/sequence of rows in the query result set is important to your application logic.
  • Si los datos de las columnas ORDER BY no son únicos, no se garantiza ningún orden de fila para las filas con los mismos valores en las columnas ORDER BY, con independencia de si el almacenamiento en caché del conjunto de resultados está habilitado o deshabilitado.If data in the ORDER BY columns are not unique, there's no garanteed row order for rows with the same values in the ORDER BY columns, regardless if result set caching is enabled or disabled.

Importante

Las operaciones para crear la caché del conjunto de resultados y recuperar datos de la caché se producen en el nodo de control de una instancia de grupo de SQL dedicado.The operations to create result set cache and retrieve data from the cache happen on the control node of a dedicated SQL pool instance. Cuando se activa el almacenamiento en caché de conjuntos de resultados, la ejecución de consultas que devuelven un conjunto de resultados grande (por ejemplo, > 1 GB) puede provocar una limitación elevada del nodo de control y ralentizar la respuesta de consulta general en la instancia.When result set caching is turned ON, running queries that return large result set (for example, >1GB) can cause high throttling on the control node and slow down the overall query response on the instance. Estas consultas se suelen usar durante la exploración de datos o las operaciones ETL.Those queries are commonly used during data exploration or ETL operations. Para evitar el esfuerzo del nodo de control y los problemas de rendimiento, los usuarios deben desactivar el almacenamiento en caché de los conjuntos de resultados en la base de datos antes de ejecutar esos tipos de consultas.To avoid stressing the control node and cause performance issue, users should turn OFF result set caching on the database before running those types of queries.

Ejecute esta consulta por el tiempo que tardan las operaciones de almacenamiento en caché del conjunto de resultados para una consulta:Run this query for the time taken by result set caching operations for a query:

SELECT step_index, operation_type, location_type, status, total_elapsed_time, command
FROM sys.dm_pdw_request_steps
WHERE request_id  = <'request_id'>;

Este es un ejemplo de una consulta ejecutada con el almacenamiento en caché de conjunto de resultados deshabilitado.Here is an example output for a query executed with result set caching disabled.

Captura de pantalla que muestra los resultados de la consulta, incluidos el tipo de ubicación y el comando.

Este es un ejemplo de una consulta ejecutada con el almacenamiento en caché de conjunto de resultados habilitado.Here is an example output for a query executed with result set caching enabled.

La captura de pantalla muestra los resultados de la consulta con el comando seleccionado * de [D W ResultCache D b] punto d b o.

Uso de los resultados en la memoria cachéWhen cached results are used

El conjunto de resultados en caché se reutiliza en una consulta si se cumplen todos los requisitos siguientes:Cached result set is reused for a query if all of the following requirements are all met:

  • El usuario que ejecuta la consulta tiene acceso a todas las tablas a las que se hace referencia en la consulta.The user who's running the query has access to all the tables referenced in the query.
  • Hay una coincidencia exacta entre la nueva consulta y la consulta anterior que generó la caché del conjunto de resultados.There is an exact match between the new query and the previous query that generated the result set cache.
  • No hay cambios en los datos o en el esquema de las tablas a partir de las cuales se generó el conjunto de resultados en caché.There is no data or schema changes in the tables where the cached result set was generated from.

Ejecute este comando para comprobar si una consulta se ejecutó con un acierto o un error de caché de resultados.Run this command to check if a query was executed with a result cache hit or miss. La columna result_cache_hit devuelve 1 para aciertos de caché, 0 para errores de caché y valores negativos para los motivos por los que no se ha usado el almacenamiento en caché del conjunto de resultados.The result_cache_hit column returns 1 for cache hit, 0 for cache miss, and negative values for reasons why result set caching was not used. Para más información, consulte sys.dm_pdw_exec_requests.Check sys.dm_pdw_exec_requests for details.

SELECT request_id, command, result_cache_hit FROM sys.dm_pdw_exec_requests
WHERE request_id = <'Your_Query_Request_ID'>

Administración de los resultados en cachéManage cached results

El tamaño máximo de la memoria caché del conjunto de resultados es de 1 TB por base de datos.The maximum size of result set cache is 1 TB per database. Los resultados en caché se invalidan automáticamente cuando cambian los datos de la consulta subyacente.The cached results are automatically invalidated when the underlying query data change.

El grupo de SQL dedicado administra automáticamente la expulsión de la caché según la programación siguiente:The cache eviction is managed by dedicated SQL pool automatically following this schedule:

  • Cada 48 horas si el conjunto de resultados no se ha usado o se ha invalidado.Every 48 hours if the result set hasn't been used or has been invalidated.
  • Cuando la memoria caché del conjunto de resultados se aproxima al tamaño máximo.When the result set cache approaches the maximum size.

Los usuarios pueden vaciar manualmente toda la memoria caché del conjunto de resultados mediante una de estas opciones:Users can manually empty the entire result set cache by using one of these options:

  • Desactivación de la característica de copia en caché del conjunto de resultados de la base de datosTurn OFF the result set cache feature for the database
  • Ejecución de DBCC DROPRESULTSETCACHE mientras está conectado a la base de datosRun DBCC DROPRESULTSETCACHE while connected to the database

Al pausar una base de datos, no se vaciará el conjunto de resultados en caché.Pausing a database won't empty cached result set.

Pasos siguientesNext steps

Para obtener más sugerencias sobre desarrollo, vea la información general sobre desarrollo.For more development tips, see development overview.