Ajuste del rendimiento de aplicaciones y bases de datos en Azure SQL Database e Instancia administrada de Azure SQLTune applications and databases for performance in Azure SQL Database and Azure SQL Managed Instance

SE APLICA A: Azure SQL Database Azure SQL Managed Instance

Una vez identificado un problema de rendimiento que experimente con Azure SQL Database e Instancia administrada de Azure SQL, este artículo le será de ayuda:Once you have identified a performance issue that you are facing with Azure SQL Database and Azure SQL Managed Instance, this article is designed to help you:

  • Ajuste la aplicación y aplique algunas prácticas recomendadas que puedan mejorar el rendimiento.Tune your application and apply some best practices that can improve performance.
  • Ajuste la base de datos cambiando los índices y consultas para que funcionen de manera más eficiente con los datos.Tune the database by changing indexes and queries to more efficiently work with data.

En este artículo se da por supuesto que ya ha llevado a cabo las recomendaciones del asesor de bases de datos de Azure SQL Database y las recomendaciones de ajuste automático de Azure SQL Database, si corresponden.This article assumes that you have already worked through the Azure SQL Database database advisor recommendations and the Azure SQL Database auto-tuning recommendations, if applicable. También se supone que ha revisado Optimización de la supervisión y el rendimiento y sus artículos relacionados relativos a la solución de problemas de rendimiento.It also assumes that you have reviewed An overview of monitoring and tuning and its related articles related to troubleshooting performance issues. Además, en este artículo se supone que no tiene un problema de rendimiento de recursos de CPU relacionado con la ejecución que se pueda resolver aumentando el tamaño de proceso o el nivel de servicio para proporcionar más recursos a la base de datos.Additionally, this article assumes that you do not have a CPU resources, running-related performance issue that can be resolved by increasing the compute size or service tier to provide more resources to your database.

Optimización de la aplicaciónTune your application

En instancias de SQL Server locales tradicionales, el proceso de planeamiento inicial de la capacidad con frecuencia está separado del proceso de ejecución de una aplicación en producción.In traditional on-premises SQL Server, the process of initial capacity planning often is separated from the process of running an application in production. Primero se adquieren las licencias de hardware y productos y luego se ajusta el rendimiento.Hardware and product licenses are purchased first, and performance tuning is done afterward. Cuando se usa Azure SQL, es una buena idea entrelazar el proceso de ejecutar una aplicación y ajustarla.When you use Azure SQL, it's a good idea to interweave the process of running an application and tuning it. Con el modelo de pago por capacidad a petición, puede ajustar su aplicación para que use el número mínimo de recursos necesarios, en lugar de aprovisionar en exceso el hardware en función de las estimaciones de planes de crecimiento futuro para una aplicación, que con frecuencia son incorrectas.With the model of paying for capacity on demand, you can tune your application to use the minimum resources needed now, instead of over-provisioning on hardware based on guesses of future growth plans for an application, which often are incorrect. Algunos clientes pueden decidir no optimizar una aplicación y, en su lugar, eligen aprovisionar en exceso los recursos de hardware.Some customers might choose not to tune an application, and instead choose to over-provision hardware resources. Este enfoque puede ser una buena idea si no quiere cambiar una aplicación clave durante un período de ocupación.This approach might be a good idea if you don't want to change a key application during a busy period. Sin embargo, la optimización de una aplicación puede minimizar los requisitos de recursos y reducir las facturas mensuales cuando se usan los niveles de servicio en Azure SQL Database e Instancia administrada de Azure SQL.But, tuning an application can minimize resource requirements and lower monthly bills when you use the service tiers in Azure SQL Database and Azure SQL Managed Instance.

Características de la aplicaciónApplication characteristics

Aunque los niveles de servicio de Azure SQL Database e Instancia administrada de Azure SQL están diseñados para mejorar la estabilidad del rendimiento y la previsibilidad de una aplicación, algunos procedimientos recomendados pueden ayudarle a optimizar la aplicación para un mejor aprovechamiento de los recursos en un tamaño de proceso.Although Azure SQL Database and Azure SQL Managed Instance service tiers are designed to improve performance stability and predictability for an application, some best practices can help you tune your application to better take advantage of the resources at a compute size. Aunque muchas aplicaciones tienen importantes ganancias de rendimiento con solo cambiar a un tamaño de proceso o un nivel de servicio superior, otras aplicaciones necesitan ajustes adicionales para beneficiarse de un nivel de servicio más alto.Although many applications have significant performance gains simply by switching to a higher compute size or service tier, some applications need additional tuning to benefit from a higher level of service. Para aumentar el rendimiento, puede realizar ajustes adicionales en las aplicaciones para que tengan estas características:For increased performance, consider additional application tuning for applications that have these characteristics:

  • Aplicaciones que tienen un rendimiento lento debido a un comportamiento "comunicativo"Applications that have slow performance because of "chatty" behavior

    Las aplicaciones comunicativas realizan excesivas operaciones de acceso a los datos que son sensibles a la latencia de red.Chatty applications make excessive data access operations that are sensitive to network latency. Para reducir el número de operaciones de acceso a datos de la base de datos, puede que tenga que modificar estos tipos de aplicaciones.You might need to modify these kinds of applications to reduce the number of data access operations to the database. Por ejemplo, puede mejorar el rendimiento de la aplicación mediante técnicas como el procesamiento por lotes de consultas ad hoc o el movimiento de las consultas a procedimientos almacenados.For example, you might improve application performance by using techniques like batching ad hoc queries or moving the queries to stored procedures. Para más información, consulte Consultas por lotes.For more information, see Batch queries.

  • Bases de datos con una carga de trabajo intensiva que no se admiten en una sola máquinaDatabases with an intensive workload that can't be supported by an entire single machine

    Las bases de datos que superen los recursos del tamaño de proceso Premium más podrían beneficiarse de escalar horizontalmente la carga de trabajo.Databases that exceed the resources of the highest Premium compute size might benefit from scaling out the workload. Para más información, consulte Particionamiento entre bases de datos y Creación de particiones funcional.For more information, see Cross-database sharding and Functional partitioning.

  • Aplicaciones que tienen consultas poco óptimasApplications that have sub-optimal queries

    Puede que las aplicaciones, especialmente las de la capa de acceso a datos, que tengan consultas poco optimizadas no se beneficien de un mayor tamaño de proceso.Applications, especially those in the data access layer, that have poorly tuned queries might not benefit from a higher compute size. Esto incluye consultas que carecen de una cláusula WHERE, con índices que faltan o tienen estadísticas anticuadas.This includes queries that lack a WHERE clause, have missing indexes, or have outdated statistics. Estas aplicaciones se benefician de las técnicas de optimización del rendimiento de consultas estándar.These applications benefit from standard query performance-tuning techniques. Para más información, consulte Índices que faltan y Optimización de consultas y sugerencias.For more information, see Missing indexes and Query tuning and hinting.

  • Aplicaciones que tienen un diseño de acceso a datos poco óptimoApplications that have sub-optimal data access design

    Puede que las aplicaciones que tienen problemas inherentes de simultaneidad del acceso a los datos, por ejemplo, interbloqueos, no se beneficien de un tamaño de proceso más alto.Applications that have inherent data access concurrency issues, for example deadlocking, might not benefit from a higher compute size. Considere la posibilidad de reducir los recorridos de ida y vuelta a la base de datos almacenando en caché los datos del lado cliente con el Servicio de almacenamiento en caché de Azure u otra tecnología de almacenamiento en caché.Consider reducing round trips against the database by caching data on the client side with the Azure Caching service or another caching technology. Consulte Almacenamiento en caché de la capa de aplicación.See Application tier caching.

Ajuste de la base de datosTune your database

En esta sección, examinamos algunas técnicas que puede usar para ajustar la base de datos para obtener el mejor rendimiento de la aplicación y ejecutarla con el menor tamaño de proceso posible.In this section, we look at some techniques that you can use to tune database to gain the best performance for your application and run it at the lowest possible compute size. Algunas de estas técnicas coinciden con los procedimientos recomendados de optimización tradicionales de SQL Server, pero otras son específicas de Azure SQL Database e Instancia administrada de Azure SQL.Some of these techniques match traditional SQL Server tuning best practices, but others are specific to Azure SQL Database and Azure SQL Managed Instance. En algunos casos, puede examinar los recursos consumidos en una base de datos para encontrar áreas de mejora adicional y ampliar las técnicas de SQL Server tradicionales para trabajar en Azure SQL Database e Instancia administrada de Azure SQL.In some cases, you can examine the consumed resources for a database to find areas to further tune and extend traditional SQL Server techniques to work in Azure SQL Database and Azure SQL Managed Instance.

Identificación y adición de índices que faltanIdentifying and adding missing indexes

Un problema común del rendimiento de las bases de datos OLTP está relacionado con el diseño físico de la base de datos.A common problem in OLTP database performance relates to the physical database design. A menudo, los esquemas de base de datos se diseñan y se entregan sin realizar pruebas a escala (ya sea en la carga o en el volumen de datos).Often, database schemas are designed and shipped without testing at scale (either in load or in data volume). Lamentablemente, el rendimiento de un plan de consultas puede ser aceptable a pequeña escala, pero se puede degradar sustancialmente cuando se enfrenta a los volúmenes de datos del nivel de producción.Unfortunately, the performance of a query plan might be acceptable on a small scale but degrade substantially under production-level data volumes. El origen más común de este problema es la falta de índices adecuados para satisfacer los filtros u otras restricciones en una consulta.The most common source of this issue is the lack of appropriate indexes to satisfy filters or other restrictions in a query. A menudo, la falta de índices se manifiesta como un recorrido de tabla cuando podría ser suficiente una búsqueda de índice.Often, missing indexes manifests as a table scan when an index seek could suffice.

En este ejemplo, el plan de consulta seleccionado usa una exploración cuando bastaría con una búsqueda:In this example, the selected query plan uses a scan when a seek would suffice:

DROP TABLE dbo.missingindex;
CREATE TABLE dbo.missingindex (col1 INT IDENTITY PRIMARY KEY, col2 INT);
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
    WHILE @a < 20000
    BEGIN
        INSERT INTO dbo.missingindex(col2) VALUES (@a);
        SET @a += 1;
    END
    COMMIT TRANSACTION;
    GO
SELECT m1.col1
    FROM dbo.missingindex m1 INNER JOIN dbo.missingindex m2 ON(m1.col1=m2.col1)
    WHERE m1.col2 = 4;

Plan de consulta con índices que faltan

Azure SQL Database e Instancia administrada de Azure SQL pueden ayudarle a encontrar y corregir condiciones de falta de índice comunes.Azure SQL Database and Azure SQL Managed Instance can help you find and fix common missing index conditions. Las vistas de administración dinámica que se crean en Azure SQL Database e Instancia administrada de Azure SQL examinan compilaciones de consultas en las que un índice reduciría considerablemente el costo estimado de ejecutar una consulta.DMVs that are built into Azure SQL Database and Azure SQL Managed Instance look at query compilations in which an index would significantly reduce the estimated cost to run a query. Durante la ejecución de las consultas, el motor de base de datos hace un seguimiento de la frecuencia con que se ejecuta cada plan de consulta, así como de la diferencia estimada entre el plan de consulta en ejecución y el imaginario en el que existía ese índice.During query execution, the database engine tracks how often each query plan is executed, and tracks the estimated gap between the executing query plan and the imagined one where that index existed. Estas DMV se pueden usar para realizar suposiciones rápidas sobre qué cambios en el diseño de la base de datos física podrían mejorar el costo de la carga de trabajo general de una base de datos y su carga de trabajo real.You can use these DMVs to quickly guess which changes to your physical database design might improve overall workload cost for a database and its real workload.

Esta consulta se puede usar para evaluar los posibles índices que falten:You can use this query to evaluate potential missing indexes:

SELECT
   CONVERT (varchar, getdate(), 126) AS runtime
   , mig.index_group_handle
   , mid.index_handle
   , CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS improvement_measure
   , 'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' +
        CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + '
        (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL
        AND mid.inequality_columns IS NOT NULL
        THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
   , migs.*
   , mid.database_id
   , mid.[object_id]
FROM sys.dm_db_missing_index_groups AS mig
   INNER JOIN sys.dm_db_missing_index_group_stats AS migs
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

En este ejemplo, la consulta ha dado como resultado esta sugerencia:In this example, the query resulted in this suggestion:

CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])  

Después de crearla, esa misma instrucción SELECT elige un plan diferente, que usa una búsqueda en lugar de una exploración y, luego, ejecuta el plan de forma más eficaz:After it's created, that same SELECT statement picks a different plan, which uses a seek instead of a scan, and then executes the plan more efficiently:

Plan de consulta con índices corregidos

El principal dato es que la capacidad de E/S de un sistema compartido es más limitada que la de una máquina servidor dedicada.The key insight is that the IO capacity of a shared, commodity system is more limited than that of a dedicated server machine. Es de especial valor minimizar la E/S innecesaria para sacar el máximo partido del sistema dentro de los recursos de cada tamaño de proceso de los niveles de servicio.There's a premium on minimizing unnecessary IO to take maximum advantage of the system in the resources of each compute size of the service tiers. La elección adecuada de las opciones de diseño de bases de datos físicas puede mejorar considerablemente la latencia de las consultas individuales y la capacidad de procesamiento de solicitudes simultáneas que puede realizar por unidad de escalado, así como minimizar los costos necesarios para satisfacer la consulta.Appropriate physical database design choices can significantly improve the latency for individual queries, improve the throughput of concurrent requests handled per scale unit, and minimize the costs required to satisfy the query. Para obtener más información acerca de las DMV de índices que faltan, consulte sys.dm_db_missing_index_details.For more information about the missing index DMVs, see sys.dm_db_missing_index_details.

Optimización de consultas y sugerenciasQuery tuning and hinting

El optimizador de consultas de Azure SQL Database e Instancia administrada de Azure SQL es similar al optimizador de consultas tradicional de SQL Server.The query optimizer in Azure SQL Database and Azure SQL Managed Instance is similar to the traditional SQL Server query optimizer. La mayoría de los procedimientos recomendados para optimizar consultas y entender las limitaciones del modelo de razonamiento para el optimizador de consultas se aplican también a Azure SQL Database e Instancia administrada de Azure SQL.Most of the best practices for tuning queries and understanding the reasoning model limitations for the query optimizer also apply to Azure SQL Database and Azure SQL Managed Instance. Si optimiza las consultas en Azure SQL Database e Instancia administrada de Azure SQL, puede obtener el beneficio adicional de reducir las demandas de recursos agregados.If you tune queries in Azure SQL Database and Azure SQL Managed Instance, you might get the additional benefit of reducing aggregate resource demands. La aplicación podría ejecutarse con un menor costo que una equivalente sin optimizar porque se puede ejecutar con un tamaño de proceso menor.Your application might be able to run at a lower cost than an un-tuned equivalent because it can run at a lower compute size.

Un ejemplo común en SQL Server y que también se aplica a Azure SQL Database e Instancia administrada de Azure SQL es cómo el optimizador de consultas examina los parámetros.An example that is common in SQL Server and which also applies to Azure SQL Database and Azure SQL Managed Instance is how the query optimizer "sniffs" parameters. Durante la compilación, el optimizador de consultas evalúa el valor actual de un parámetro para determinar si puede generar un plan de consulta más óptimo.During compilation, the query optimizer evaluates the current value of a parameter to determine whether it can generate a more optimal query plan. Aunque con frecuencia esta estrategia puede conducir a un plan de consultas bastante más rápido que un plan compilado sin valores de parámetros conocidos, actualmente no funciona muy bien en SQL Server, en Azure SQL Database y en Instancia administrada de Azure SQL.Although this strategy often can lead to a query plan that is significantly faster than a plan compiled without known parameter values, currently it works imperfectly both in SQL Server, in Azure SQL Database, and Azure SQL Managed Instance. A veces el parámetro no se examina y otras veces sí, pero el plan generado es poco óptimo para el conjunto completo de valores de parámetros de una carga de trabajo.Sometimes the parameter is not sniffed, and sometimes the parameter is sniffed but the generated plan is sub-optimal for the full set of parameter values in a workload. Microsoft incluye sugerencias de consulta (directivas) para que pueda especificar más deliberadamente la intención y reemplazar el comportamiento predeterminado de examen de parámetros.Microsoft includes query hints (directives) so that you can specify intent more deliberately and override the default behavior of parameter sniffing. A menudo, el uso de sugerencias puede corregir los casos en los que el comportamiento predeterminado de SQL Server, Azure SQL Database o Instancia administrada de Azure SQL es imperfecto para una carga de trabajo de un cliente determinado.Often, if you use hints, you can fix cases in which the default SQL Server, Azure SQL Database, and Azure SQL Managed Instance behavior is imperfect for a specific customer workload.

En el ejemplo siguiente se muestra cómo el procesador de consultas puede generar un plan poco óptimo para los requisitos de rendimiento y recursos.The next example demonstrates how the query processor can generate a plan that is sub-optimal both for performance and resource requirements. Este ejemplo también muestra que si usa una sugerencia de consulta, puede reducir los requisitos de recursos y el tiempo de ejecución de consultas de su base de datos:This example also shows that if you use a query hint, you can reduce query run time and resource requirements for your database:

DROP TABLE psptest1;
CREATE TABLE psptest1(col1 int primary key identity, col2 int, col3 binary(200));
DECLARE @a int = 0;
SET NOCOUNT ON;
BEGIN TRANSACTION
   WHILE @a < 20000
   BEGIN
     INSERT INTO psptest1(col2) values (1);
     INSERT INTO psptest1(col2) values (@a);
     SET @a += 1;
   END
   COMMIT TRANSACTION
   CREATE INDEX i1 on psptest1(col2);
GO

CREATE PROCEDURE psp1 (@param1 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1
      WHERE col2 = @param1
      ORDER BY col2;
    END
    GO

CREATE PROCEDURE psp2 (@param2 int)
   AS
   BEGIN
      INSERT INTO t1 SELECT * FROM psptest1 WHERE col2 = @param2
      ORDER BY col2
      OPTION (OPTIMIZE FOR (@param2 UNKNOWN))
   END
   GO

CREATE TABLE t1 (col1 int primary key, col2 int, col3 binary(200));
GO

El código de configuración crea una tabla que contiene una distribución de datos sesgados.The setup code creates a table that has skewed data distribution. El plan de consulta óptimo varía en función del parámetro que seleccione.The optimal query plan differs based on which parameter is selected. Desafortunadamente, el comportamiento de almacenar en caché el plan no siempre recompila la consulta según el valor de parámetro más común.Unfortunately, the plan caching behavior doesn't always recompile the query based on the most common parameter value. Por tanto, se puede almacenar en caché un plan poco óptimo y usarse para muchos valores, aunque un plan diferente podría ser una mejor opción por término medio.So, it's possible for a sub-optimal plan to be cached and used for many values, even when a different plan might be a better plan choice on average. A continuación, el plan de consulta crea dos procedimientos almacenados que son idénticos, salvo que uno tiene una sugerencia de consulta especial.Then the query plan creates two stored procedures that are identical, except that one has a special query hint.

-- Prime Procedure Cache with scan plan
EXEC psp1 @param1=1;
TRUNCATE TABLE t1;

-- Iterate multiple times to show the performance difference
DECLARE @i int = 0;
WHILE @i < 1000
   BEGIN
      EXEC psp1 @param1=2;
      TRUNCATE TABLE t1;
      SET @i += 1;
    END

Se recomienda esperar al menos 10 minutos para empezar la parte 2 del ejemplo, con el fin de que los resultados sean distintos en los datos de telemetría resultantes.We recommend that you wait at least 10 minutes before you begin part 2 of the example, so that the results are distinct in the resulting telemetry data.

EXEC psp2 @param2=1;
TRUNCATE TABLE t1;

DECLARE @i int = 0;
    WHILE @i < 1000
    BEGIN
        EXEC psp2 @param2=2;
        TRUNCATE TABLE t1;
        SET @i += 1;
    END

Cada parte de este ejemplo intenta ejecutar 1000 veces una instrucción insert parametrizada (para generar una carga suficiente para usarla como un conjunto de datos de prueba).Each part of this example attempts to run a parameterized insert statement 1,000 times (to generate a sufficient load to use as a test data set). Al ejecutar los procedimientos almacenados, el procesador de consultas examina el valor de los parámetros pasados al procedimiento durante su primera compilación (lo que se conoce como examinar parámetros).When it executes stored procedures, the query processor examines the parameter value that is passed to the procedure during its first compilation (parameter "sniffing"). El procesador almacena en caché el plan resultante y se usa para invocaciones posteriores, aunque el valor del parámetro sea diferente.The processor caches the resulting plan and uses it for later invocations, even if the parameter value is different. El plan óptimo no podría utilizarse en todos los casos.The optimal plan might not be used in all cases. En ocasiones, es necesario guiar al optimizador para que seleccione un plan que sea mejor para el caso medio en lugar de para el caso específico de cuando la consulta se compila por primera vez.Sometimes you need to guide the optimizer to pick a plan that is better for the average case rather than the specific case from when the query was first compiled. En este ejemplo, el plan inicial genera un plan de "exploración" que lee todas las filas para encontrar todos los valores que coinciden con el parámetro.In this example, the initial plan generates a "scan" plan that reads all rows to find each value that matches the parameter:

Optimización de consultas mediante un plan de exploración

Como el procedimiento se ejecutó con el valor 1, el plan resultante era óptimo para 1 pero poco óptimo para los restantes valores de la tabla.Because we executed the procedure by using the value 1, the resulting plan was optimal for the value 1 but was sub-optimal for all other values in the table. Es probable que el resultado no sea lo que esperaría si seleccionara cada plan de manera aleatoria, dado que el plan se ejecuta más despacio y usa menos recursos.The result likely isn't what you would want if you were to pick each plan randomly, because the plan performs more slowly and uses more resources.

Si ejecuta la prueba con SET STATISTICS IO establecido en ON, el trabajo de exploración lógica en este ejemplo se realiza en segundo plano.If you run the test with SET STATISTICS IO set to ON, the logical scan work in this example is done behind the scenes. Puede ver que hay 1148 lecturas que realiza el plan (lo que es ineficaz, si el caso medio es devolver solo una fila):You can see that there are 1,148 reads done by the plan (which is inefficient, if the average case is to return just one row):

Optimización de consultas mediante una exploración lógica

La segunda parte del ejemplo usa una sugerencia de consulta para indicar al optimizador que use un valor específico durante el proceso de compilación.The second part of the example uses a query hint to tell the optimizer to use a specific value during the compilation process. En este caso, obliga al procesador de consultas a omitir el valor que se pasa como parámetro, y en su lugar a asumir UNKNOWN.In this case, it forces the query processor to ignore the value that is passed as the parameter, and instead to assume UNKNOWN. Este valor hace referencia a un valor que tiene la frecuencia media en la tabla (omitiendo el sesgo).This refers to a value that has the average frequency in the table (ignoring skew). El plan resultante está basado en búsquedas, es más rápido y usa menos recursos, como términos medio, que el plan de la parte 1 de este ejemplo:The resulting plan is a seek-based plan that is faster and uses fewer resources, on average, than the plan in part 1 of this example:

Optimización de consultas mediante una sugerencia de consulta

Puede ver el efecto en la tabla sys.resource_stats (hay un retraso desde el momento en que ejecuta la prueba y cuando los datos llenan la tabla).You can see the effect in the sys.resource_stats table (there is a delay from the time that you execute the test and when the data populates the table). En este ejemplo, la parte 1 se ejecutó en el período de tiempo de 22:25:00 y la parte 2 se ejecutó a las 22:35:00.For this example, part 1 executed during the 22:25:00 time window, and part 2 executed at 22:35:00. El período de tiempo anterior usó más recursos en ese período que el posterior (debido a las mejoras de eficiencia del plan).The earlier time window used more resources in that time window than the later one (because of plan efficiency improvements).

SELECT TOP 1000 *
FROM sys.resource_stats
WHERE database_name = 'resource1'
ORDER BY start_time DESC

Resultados del ejemplo de optimización de consultas

Nota

Aunque en este ejemplo el volumen es deliberadamente pequeño, el efecto de los parámetros poco óptimos puede ser considerable, especialmente en bases de datos más grandes.Although the volume in this example is intentionally small, the effect of sub-optimal parameters can be substantial, especially on larger databases. La diferencia, en casos extremos, puede estar entre segundos, para los casos más rápidos, y horas, para los más lentos.The difference, in extreme cases, can be between seconds for fast cases and hours for slow cases.

Puede examinar sys.resource_stats para determinar si el recurso de una prueba usa más o menos recursos que otra prueba.You can examine sys.resource_stats to determine whether the resource for a test uses more or fewer resources than another test. Al comparar los datos, separe el tiempo de pruebas para que no se encuentren en el mismo período de 5 minutos en la vista sys.resource_stats.When you compare data, separate the timing of tests so that they are not in the same 5-minute window in the sys.resource_stats view. El objetivo del ejercicio es minimizar la cantidad total de recursos usados, no minimizar los recursos máximos.The goal of the exercise is to minimize the total amount of resources used, and not to minimize the peak resources. Por lo general, al optimizar la latencia de un fragmento de código, también se reduce el consumo de recursos.Generally, optimizing a piece of code for latency also reduces resource consumption. Asegúrese de que los cambios realizados en una aplicación sean necesarios y que no afecten negativamente a la experiencia del cliente para alguien que podría estar usando sugerencias de consulta en la aplicación.Make sure that the changes you make to an application are necessary, and that the changes don't negatively affect the customer experience for someone who might be using query hints in the application.

Si una carga de trabajo contiene un conjunto de consultas repetidas, con frecuencia tiene sentido capturar y validar la idoneidad de esas elecciones del plan, ya que controlarán la unidad de tamaño mínima de los recursos necesaria para hospedar la base de datos.If a workload has a set of repeating queries, often it makes sense to capture and validate the optimality of your plan choices because it drives the minimum resource size unit required to host the database. Después de validarlo, vuelva a examinar de vez en cuando los planes para asegurarse de que no se han degradado.After you validate it, occasionally reexamine the plans to help you make sure that they have not degraded. Puede aprender más sobre las sugerencias de consulta (Transact-SQL).You can learn more about query hints (Transact-SQL).

Arquitecturas de bases de datos de gran tamañoVery large database architectures

Antes del lanzamiento del nivel de servicio de Hiperescala para bases de datos únicas en Azure SQL Database, los clientes solían alcanzar los límites de capacidad en las bases de datos individuales.Before the release of Hyperscale service tier for single databases in Azure SQL Database, customers used to hit capacity limits for individual databases. Estos límites aún existen para las bases de datos agrupadas en grupos elásticos de Azure SQL Database y para bases de datos de instancia en Instancias administradas de Azure SQL.These capacity limits still exist for pooled databases in Azure SQL Database elastic pools and instance databases in Azure SQL Managed Instances. En las dos secciones siguientes se describen dos opciones para solucionar problemas de bases de datos de gran tamaño en Azure SQL Database e Instancia administrada de Azure SQL cuando no se puede usar el nivel de servicio de Hiperescala.The following two sections discuss two options for solving problems with very large databases in Azure SQL Database and Azure SQL Managed Instance when you cannot use the Hyperscale service tier.

Particionamiento entre bases de datosCross-database sharding

Como Azure SQL Database e Instancia administrada de Azure SQL se ejecutan en hardware estándar, los límites de capacidad para una base de datos individual son inferiores a los de una instalación local de SQL Server tradicional.Because Azure SQL Database and Azure SQL Managed Instance runs on commodity hardware, the capacity limits for an individual database are lower than for a traditional on-premises SQL Server installation. Algunos clientes usan técnicas de particionamiento para repartir las operaciones de base de datos entre varias bases de datos cuando las operaciones no entran en los límites de una base de datos individual en Azure SQL Database e Instancia administrada de Azure SQL.Some customers use sharding techniques to spread database operations over multiple databases when the operations don't fit inside the limits of an individual database in Azure SQL Database and Azure SQL Managed Instance. La mayoría de los clientes que usan técnicas de particionamiento en Azure SQL Database e Instancia administrada de Azure SQL dividen sus datos en una única dimensión entre varias bases de datos.Most customers who use sharding techniques in Azure SQL Database and Azure SQL Managed Instance split their data on a single dimension across multiple databases. En este enfoque, debe comprender que las aplicaciones OLTP a menudo realizan transacciones que se aplican a una sola fila o a un pequeño grupo de filas del esquema.For this approach, you need to understand that OLTP applications often perform transactions that apply to only one row or to a small group of rows in the schema.

Nota

Tenga en cuenta que Azure SQL Database ahora proporciona una biblioteca para ayudar con el particionamiento.Azure SQL Database now provides a library to assist with sharding. Para obtener más información, consulte Información general de la biblioteca de cliente de Elastic Database.For more information, see Elastic Database client library overview.

Por ejemplo, si una base de datos tiene el nombre del cliente, el pedido y los detalles del pedido (como la base de datos tradicional de ejemplo Northwind que se incluye con SQL Server), podría dividir estos datos en varias bases de datos mediante la agrupación de un cliente con el pedido relacionado y la información detallada del pedido.For example, if a database has customer name, order, and order details (like the traditional example Northwind database that ships with SQL Server), you could split this data into multiple databases by grouping a customer with the related order and order detail information. Puede garantizar que los datos del cliente permanezcan en una base de datos individual.You can guarantee that the customer's data stays in an individual database. La aplicación dividiría los distintos clientes entre las bases de datos, repartiendo la carga eficazmente entre varias bases de datos.The application would split different customers across databases, effectively spreading the load across multiple databases. El particionamiento no solo permite que los clientes eviten el límite de tamaño máximo de la base de datos, sino también que Azure SQL Database e Instancia administrada de Azure SQL puedan procesar cargas de trabajo que sean mucho mayores que los límites de los distintos tamaños de proceso, siempre y cuando cada base de datos se ajuste a sus límites de nivel de servicio.With sharding, customers not only can avoid the maximum database size limit, but Azure SQL Database and Azure SQL Managed Instance also can process workloads that are significantly larger than the limits of the different compute sizes, as long as each individual database fits into its service tier limits.

Aunque el particionamiento de base de datos no reduce la capacidad de recursos agregados para una solución, es muy eficaz a la hora de admitir soluciones muy grandes que se distribuyen entre varias bases de datos.Although database sharding doesn't reduce the aggregate resource capacity for a solution, it's highly effective at supporting very large solutions that are spread over multiple databases. Cada base de datos se puede ejecutar con un tamaño de proceso diferente para admitir bases de datos "eficaces" muy grandes con requisitos elevados de recursos.Each database can run at a different compute size to support very large, "effective" databases with high resource requirements.

Creación de particiones funcionalFunctional partitioning

Los usuarios suelen combinar varias funciones en una base de datos individual.Users often combine many functions in an individual database. Por ejemplo, si una aplicación contiene lógica para administrar el inventario de un almacén, esa base de datos podría contener lógica asociada con el inventario, el seguimiento de los pedidos de compra, los procedimientos almacenados y las vistas indizadas o materializadas que administran los informes de fin de me.For example, if an application has logic to manage inventory for a store, that database might have logic associated with inventory, tracking purchase orders, stored procedures, and indexed or materialized views that manage end-of-month reporting. Esta técnica facilita la administración de la base de datos para operaciones como la copia de seguridad, pero también requiere ajustar el tamaño del hardware para administrar la carga máxima en todas las funciones de una aplicación.This technique makes it easier to administer the database for operations like backup, but it also requires you to size the hardware to handle the peak load across all functions of an application.

Si usa una arquitectura de escalado horizontal en Azure SQL Database e Instancia administrada de Azure SQL, es una buena idea repartir las distintas funciones de una aplicación en diferentes bases de datos.If you use a scale-out architecture in Azure SQL Database and Azure SQL Managed Instance, it's a good idea to split different functions of an application into different databases. Mediante esta técnica, cada aplicación se escala de forma independiente.By using this technique, each application scales independently. A medida que una aplicación realiza una actividad mayor (y aumenta la carga en la base de datos), el administrador puede elegir tamaños de proceso independientes para cada función de la aplicación.As an application becomes busier (and the load on the database increases), the administrator can choose independent compute sizes for each function in the application. En el límite, una aplicación con esta arquitectura puede ser más grande de lo que puede controlar una única máquina estándar por lo que la carga se reparte entre varias máquinas.At the limit, with this architecture, an application can be larger than a single commodity machine can handle because the load is spread across multiple machines.

Consultas por lotesBatch queries

Para las aplicaciones que acceden a datos mediante consultas ad hoc frecuentes de gran volumen, una parte sustancial del tiempo de respuesta se dedica a la comunicación de red entre la capa de aplicación y la de la base de datos.For applications that access data by using high-volume, frequent, ad hoc querying, a substantial amount of response time is spent on network communication between the application tier and the database tier. Incluso si la aplicación y la base de datos residen en el mismo centro de datos, la latencia de red entre ambas podría verse aumentada por un número elevado de operaciones de acceso a datos.Even when both the application and the database are in the same data center, the network latency between the two might be magnified by a large number of data access operations. Para reducir los recorridos de ida y vuelta de red en las operaciones de acceso a datos, puede usar la opción para procesar por lotes todas las consultas ad hoc o para compilarlas como procedimientos almacenados.To reduce the network round trips for the data access operations, consider using the option to either batch the ad hoc queries, or to compile them as stored procedures. Si procesa por lotes las consultas ad hoc, puede enviar varias consultas como un lote grande en un solo recorrido a la base de datos.If you batch the ad hoc queries, you can send multiple queries as one large batch in a single trip to the database. Si compila las consultas ad hoc en un procedimiento almacenado, podría lograr el mismo resultado que si las procesa por lotes.If you compile ad hoc queries in a stored procedure, you could achieve the same result as if you batch them. El uso de un procedimiento almacenado también ofrece la ventaja de aumentar la probabilidad de almacenar en caché los planes de consulta en la base de datos de modo que pueda usar de nuevo el procedimiento almacenado.Using a stored procedure also gives you the benefit of increasing the chances of caching the query plans in the database so you can use the stored procedure again.

Algunas aplicaciones requieren operaciones de escritura intensivas.Some applications are write-intensive. En ocasiones se puede reducir la carga total de E/S en una base de datos si se considera cómo procesar por lotes las escrituras juntas.Sometimes you can reduce the total IO load on a database by considering how to batch writes together. Con frecuencia es tan sencillo como usar transacciones explícitas en lugar de transacciones de confirmación automática dentro de los procedimientos almacenados y lotes ad hoc.Often, this is as simple as using explicit transactions instead of auto-commit transactions in stored procedures and ad hoc batches. Para ver una evaluación de las distintas técnicas que se pueden usar, consulte Técnicas de procesamiento por lotes para aplicaciones de base de datos en Azure.For an evaluation of different techniques you can use, see Batching techniques for database applications in Azure. Experimente con su propia carga de trabajo para encontrar el modelo adecuado de procesamiento por lotes.Experiment with your own workload to find the right model for batching. Asegúrese de entender que un modelo puede tener garantías de coherencia transaccional ligeramente diferentes.Be sure to understand that a model might have slightly different transactional consistency guarantees. Para encontrar la carga de trabajo adecuada que minimiza el uso de recursos, es necesario encontrar la combinación correcta de coherencia y rendimiento.Finding the right workload that minimizes resource use requires finding the right combination of consistency and performance trade-offs.

Almacenamiento en caché de la capa de aplicaciónApplication-tier caching

Algunas aplicaciones de base de datos tienen cargas de trabajo con operaciones de lectura intensivas.Some database applications have read-heavy workloads. El almacenamiento en caché de las capas podría reducir la carga en la base de datos y también, posiblemente, el tamaño de proceso necesario para admitir una base de datos con Azure SQL Database e Instancia administrada de Azure SQL.Caching layers might reduce the load on the database and might potentially reduce the compute size required to support a database by using Azure SQL Database and Azure SQL Managed Instance. Con Azure Cache for Redis, si tiene una carga de trabajo con muchas operaciones de lectura, puede leer los datos una vez (o quizás una vez por máquina de nivel de aplicación, según cómo esté configurada) y luego almacenar esos datos fuera de la base de datos.With Azure Cache for Redis, if you have a read-heavy workload, you can read the data once (or perhaps once per application-tier machine, depending on how it is configured), and then store that data outside of your database. Se trata de una forma de reducir la carga de la base de datos (CPU y E/S de lectura), pero tiene efectos sobre la coherencia transaccional porque los datos que se leen de la caché podrían estar desincronizados con respectos a los datos de la base de datos.This is a way to reduce database load (CPU and read IO), but there is an effect on transactional consistency because the data being read from the cache might be out of sync with the data in the database. Aunque en muchas aplicaciones algún nivel de incoherencia es aceptable, esto no se aplica a todas las cargas de trabajo.Although in many applications some level of inconsistency is acceptable, that's not true for all workloads. Debería comprender totalmente los requisitos de una aplicación antes de emplear una estrategia de almacenamiento en caché de la capa de aplicación.You should fully understand any application requirements before you implement an application-tier caching strategy.

Pasos siguientesNext steps