Database Engine Tuning AdvisorDatabase Engine Tuning Advisor

SE APLICA A: síSQL Server noAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

El Asistente para la optimización de motor de base de datos de MicrosoftMicrosoft (DTA) analiza las bases de datos y hace recomendaciones que puede usar para optimizar el rendimiento de las consultas.The MicrosoftMicrosoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. Puede usar el Asistente para la optimización de motor de base de datos a fin de seleccionar y crear un conjunto óptimo de índices, vistas indizadas o particiones de tabla sin necesidad de conocer detalladamente la estructura de la base de datos ni el funcionamiento interno de SQL ServerSQL Server.You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL ServerSQL Server. Con DTA, puede realizar las siguientes tareas.Using the DTA, you can perform the following tasks.

  • Solucionar problemas del rendimiento de una consulta específicaTroubleshoot the performance of a specific problem query

  • Optimizar un conjunto grande de consultas en una o varias bases de datosTune a large set of queries across one or more databases

  • Realizar análisis condicionales de exploración de posibles cambios de diseño físicosPerform an exploratory what-if analysis of potential physical design changes

  • Administrar el espacio de almacenamientoManage storage space

Ventajas del Asistente para la optimización de motor de base de datosDatabase Engine Tuning Advisor Benefits

La optimización del rendimiento de las consultas puede ser difícil sin un conocimiento completo de la estructura de la base de datos y de las consultas que se ejecutan en ella.Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. El Asistente para la optimización de motor de base de datos (DTA) puede facilitar esta tarea mediante el análisis de la caché del plan de consulta actual o de la carga de trabajo de las consultas de Transact-SQLTransact-SQL que crea, y con la recomendación de un diseño físico adecuado.The Database Engine Tuning Advisor (DTA) can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQLTransact-SQL queries that you create and recommending an appropriate physical design. Para administradores de bases de datos más avanzadas, DTA expone un mecanismo eficaz para realizar análisis condicionales de exploración de diferentes alternativas de diseño físico.For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. DTA puede proporcionar la siguiente información.The DTA can provide the following information.

  • Recomendar la mejor combinación de índices de almacén de filas y de columnas para las bases de datos mediante el uso del optimizador de consultas para analizar las consultas de una carga de trabajo.Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.

  • Recomendar particiones alineadas y no alineadas para las bases de datos a las que se hace referencia en una carga de trabajo.Recommend aligned or non-aligned partitions for databases referenced in a workload.

  • Recomendar vistas indizadas para las bases de datos a las que se hace referencia en una carga de trabajo.Recommend indexed views for databases referenced in a workload.

  • Analizar los efectos de los cambios propuestos en aspectos tales como el uso de ííndices, la distribución de consultas entre tablas y el rendimiento de las consultas de la carga de trabajo.Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.

  • Recomendar métodos para optimizar la base de datos con respecto a un pequeño conjunto de consultas problemáticas.Recommend ways to tune the database for a small set of problem queries.

  • Permitirle personalizar la recomendación mediante la especificación de opciones avanzadas como, por ejemplo, las restricciones de espacio en disco.Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

  • Proporcionar informes que resuman los efectos de la implementación de las recomendaciones en una carga de trabajo concreta.Provide reports that summarize the effects of implementing the recommendations for a given workload.

  • Considerar alternativas en las que se ofrezcan posibles opciones de diseño en forma de configuraciones hipotéticas para que el Asistente para la optimización de motor de base de datos pueda evaluarlas.Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.

  • Ajuste las cargas de trabajo de una variedad de orígenes como Almacén de consultas de SQL Server, Caché del plan, archivos o tablas de Archivos de seguimiento de SQL Server o un archivo .SQL.Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

El Asistente para la optimización de motor de base de datos está diseñado para controlar los siguientes tipos de cargas de trabajo de consulta:The Database Engine Tuning Advisor is designed to handle the following types of query workloads:

  • Solo consultas de proceso de transacciones en línea (OLTP)Online transaction processing (OLTP) queries only

  • Solo consultas de procesamiento analítico en línea (OLAP)Online analytical processing (OLAP) queries only

  • Consultas OLTP y OLAP mixtasMixed OLTP and OLAP queries

  • Cargas de trabajo con muchas consultas (más consultas que modificaciones de datos)Query-heavy workloads (more queries than data modifications)

  • Cargas de trabajo con muchas actualizaciones (más modificaciones de datos que consultas)Update-heavy workloads (more data modifications than queries)

Componentes y conceptos de DTADTA Components and Concepts

Interfaz gráfica de usuario del Asistente para la optimización de motor de base de datosDatabase Engine Tuning Advisor Graphical User Interface
Una interfaz fácil de usar en la que puede especificar la carga de trabajo y seleccionar otras opciones de optimización.An easy-to-use interface in which you can specify the workload and select various tuning options.

dta (utilidad)dta Utility
Versión del símbolo del sistema del Asistente para la optimización de motor de base de datos.The command prompt version of Database Engine Tuning Advisor. La utilidad dta está diseñada para permitir usar la funcionalidad del Asistente para la optimización de motor de base de datos en aplicaciones y scripts.The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

carga de trabajoworkload
Archivo de script Transact-SQL, archivo de seguimiento o tabla de seguimiento que contenga una carga de trabajo representativa para las bases de datos que desea optimizar.A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. A partir de SQL Server 2012 (11.x)SQL Server 2012 (11.x), puede especificar la memoria caché del plan como carga de trabajo.Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), you can specify the plan cache as the workload. A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), puede especificar el Almacén de datos de consultas como carga de trabajo.Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can specify the Query Store as the workload.

Archivo de entrada XMLXML input file
Un archivo con formato XML que el Asistente para la optimización de motor de base de datos puede usar para optimizar las cargas de trabajo.A XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. El archivo de entrada XML admite las opciones avanzadas de optimización que no están disponibles en la GUI ni en la utilidad dta .The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

Limitaciones y restriccionesLimitations and Restrictions

El Asistente para la optimización de motor de base de datos tiene las siguientes limitaciones y restricciones.The Database Engine Tuning Advisor has the following limitations and restrictions.

  • No puede agregar o quitar índices únicos o índices que aplican restricciones PRIMARY KEY o UNIQUE.It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • No puede analizar una base de datos que esté configurada en modo de usuario único.It cannot analyze a database that is set to single-user mode.

  • Si especifica un espacio en disco máximo en las recomendaciones de optimización que supere el espacio disponible real, el Asistente para la optimización de motor de base de datos usa el valor especificado.If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. Sin embargo, al ejecutar el script de recomendaciones para implementarlo, el script puede generar un error si antes no se agrega más espacio en disco.However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. El espacio en disco máximo puede especificarse mediante la opción -B de la utilidad dta o especificando un valor en el cuadro de diálogo Opciones avanzadas de optimización .Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • Por motivos de seguridad, el Asistente para la optimización de motor de base de datos no puede optimizar una carga de trabajo de una tabla de seguimiento que resida en un servidor remoto.For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. Para evitar esta limitación, puede usar un archivo de seguimiento en lugar de una tabla de seguimiento o copiar la tabla de seguimiento en el servidor remoto.To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

  • Al imponer restricciones, como las impuestas al especificar el espacio en disco máximo en las recomendaciones de optimización (mediante la opción -B o el cuadro de diálogo Opciones avanzadas de optimización ), el Asistente para la optimización de motor de base de datos puede verse forzado a quitar algunos índices existentes.When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. En ese caso, la recomendación resultante del Asistente para la optimización de motor de base de datos puede producir lo contrario a la mejora esperada.In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • Al especificar una restricción para limitar el tiempo de optimización (mediante la opción -A con la utilidad dta o activando Limitar tiempo de optimización en la pestaña Opciones de optimización ), el Asistente para la optimización de motor de base de datos puede exceder ese límite de tiempo para generar la mejora esperada exacta e informes de análisis de la parte de la carga de trabajo que se ha consumido hasta ahora.When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

  • El Asistente para la optimización de motor de base de datos no hace recomendaciones en las siguientes circunstancias:Database Engine Tuning Advisor might not make recommendations under the following circumstances:

    1. La tabla que se está optimizando contiene menos de 10 páginas de datos.The table being tuned contains less than 10 data pages.

    2. Los índices recomendados no ofrecen claras posibilidades de mejora del rendimiento de las consultas respecto al diseño de la base de datos física actual.The recommended indexes would not offer enough improvement in query performance over the current physical database design.

    3. El usuario que ejecuta el Asistente para la optimización de motor de base de datos no es miembro del rol de base de datos db_owner ni del rol fijo de servidor sysadmin .The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. Las consultas de la carga de trabajo se analizan en el contexto de seguridad del usuario que ejecuta el Asistente para la optimización de motor de base de datos.The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. El usuario debe ser miembro del rol de base de datos db_owner .The user must be a member of the db_owner database role.

  • El Asistente para la optimización de motor de base de datos almacena la información de optimización de la sesión y otros datos en la base de datos msdb .Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Si se realizan cambios en la base de datos msdb , existe el riesgo de que se pierdan los datos de optimización de la sesión.If changes are made to the msdb database you may risk losing tuning session data. Para eliminar este riesgo, implemente una estrategia de copia de seguridad adecuada para la base de datos msdb .To eliminate this risk, implement an appropriate backup strategy for the msdb database.

Consideraciones de rendimientoPerformance Considerations

El Asistente para la optimización de motor de base de datos puede consumir muchos recursos de procesador y memoria durante el análisis.Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. Para evitar que el servidor de producción se ralentice, siga una de estas estrategias:To avoid slowing down your production server, follow one of these strategies:

  • Optimice las bases de datos cuando el servidor esté libre.Tune your databases when your server is free. El Asistente para la optimización de motor de base de datos puede afectar al rendimiento de las tareas de mantenimiento.Database Engine Tuning Advisor can affect maintenance task performance.

  • Utilice la característica de servidor de prueba/producción.Use the test server/production server feature. Para obtener más información, vea Reducir la carga de optimización del servidor de producción.For more information, see Reduce the Production Server Tuning Load.

  • Especifique solo las estructuras de diseño de la base de datos física que desee que el Asistente para la optimización de motor de base de datos analice.Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. El Asistente para la optimización de motor de base de datos proporciona muchas opciones, pero especifica solo las necesarias.Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Dependencia en el procedimiento almacenado extendido xp_msverDependency on xp_msver Extended Stored Procedure

El Asistente para la optimización de motor de base de datos depende del procedimiento almacenado extendido xp_msver para poder ofrecer una funcionalidad completa.Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. Este procedimiento almacenado extendido está activado de manera predeterminada.This extended stored procedure is turned on by default. El Asistente para la optimización de motor de base de datos usa este procedimiento almacenado extendido para obtener el número de procesadores y la memoria disponible del equipo en el que reside la base de datos que está optimizando.Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. Si xp_msver no está disponible, el Asistente para la optimización de motor de base de datos adopta las características de hardware del equipo donde se ejecuta el Asistente para la optimización de motor de base de datos.If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. Si no están disponibles las características de hardware del equipo donde se ejecuta el Asistente para la optimización de motor de base de datos, se presuponen un procesador y 1.024 MB de memoria.If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

Esta dependencia afecta a las recomendaciones de partición porque el número de particiones recomendadas depende de estos dos valores (número de procesadores y memoria).This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). La dependencia afecta además a los resultados de optimización si utiliza un servidor de prueba para optimizar el servidor de producción.The dependency also affects your tuning results when you use a test server to tune your production server. En este escenario, el Asistente para la optimización de motor de base de datos usa xp_msver para obtener propiedades de hardware del servidor de producción.In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. Después de optimizar la carga de trabajo en el servidor de prueba, el Asistente para la optimización de motor de base de datos usa estas propiedades de hardware para generar una recomendación.After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation. Para obtener más información, vea xp_msver (Transact-SQL).For more information, see xp_msver (Transact-SQL).

Tareas del Asistente para la optimización de motor de base de datosDatabase Engine Tuning Advisor Tasks

En la tabla siguiente se enumeran las tareas comunes del Asistente para la optimización de motor de base de datos y los temas en los que se describe cómo realizarlas.The following table lists common Database Engine Tuning Advisor tasks and the topics that describe how to perform them.

Tarea del Asistente para la optimización de motor de base de datosDatabase Engine Tuning Advisor Task TemaTopic
Inicializar e iniciar el Asistente para la optimización de motor de base de datos.Initialize and start the Database Engine Tuning Advisor.

Crear una carga de trabajo mediante la especificación de la memoria caché del plan, la creación de un script o la generación de un archivo o una tabla de seguimiento.Create a workload by specifying the plan cache, by creating a script, or by generating a trace file or trace table.

Optimizar una base de datos mediante la herramienta de interfaz gráfica de usuario del Asistente para la optimización de motor de base de datos.Tune a database by using the Database Engine Tuning Advisor graphical user interface tool.

Crear archivos de entrada XML para optimizar cargas de trabajo.Create XML input files to tune workloads.

Ver descripciones de las opciones de la interfaz de usuario del Asistente para la optimización de motor de base de datos.View descriptions of the Database Engine Tuning Advisor user interface options.
Iniciar y utilizar el Asistente para la optimización de motor de base de datosStart and Use the Database Engine Tuning Advisor
Ver los resultados de la operación de optimización de la base de datos.View the results of the database tuning operation.

Seleccionar e implementar las recomendaciones de optimización.Select and implement tuning recommendations.

Realizar análisis de exploración condicionales en la carga de trabajo.Perform what-if exploratory analysis against the workload.

Revisar sesiones de optimización existentes, clonar sesiones basándose en las existentesReview existing tuning sessions, clone sessions based on existing ones
o editar recomendaciones de optimización existentes para su posterior evaluación o implementación.or edit existing tuning recommendations for further evaluation or implementation.

Ver descripciones de las opciones de la interfaz de usuario del Asistente para la optimización de motor de base de datos.View descriptions of the Database Engine Tuning Advisor user interface options.
Ver y trabajar con la salida del Asistente para la optimización de motor de base de datosView and Work with the Output from the Database Engine Tuning Advisor