Análisis de la carga de trabajo de una base de datos de una instancia de base de datos de Amazon RDS for SQL Server con el Asistente para la optimización del motor de base de datos

El Asistente para la optimización del motor de base de datos es una aplicación cliente proporcionada por Microsoft que analiza la carga de trabajo de la base de datos y recomienda un conjunto de índices óptimo para las bases de datos de Microsoft SQL Server en función de los tipos de consultas que se ejecutan. Al igual que SQL Server Management Studio, el Asistente para la optimización se ejecuta desde un equipo conectado a la instancia de base de datos de Amazon RDS que ejecuta SQL Server. El equipo cliente puede ser un equipo local que ejecuta localmente en su propia red o una instancia de Amazon EC2 Windows que se ejecuta en la misma región que la instancia de base de datos de Amazon RDS.

Esta sección muestra cómo capturar una carga de trabajo para que la analice el Asistente para la optimización. Este es el proceso preferido para capturar una carga de trabajo, ya que Amazon RDS restringe el acceso de host a la instancia de SQL Server. Para obtener más información, consulte el Asesor de ajuste del motor de base de datos en la documentación de Microsoft.

Para usar el Asistente para la optimización, debe proporcionar al asistente lo que se denomina una carga de trabajo. Una carga de trabajo es un conjunto de instrucciones Transact-SQL que se ejecutan en una o varias bases de datos que se desea optimizar. El Asistente para la optimización del motor de base de datos usa archivos de seguimiento, tablas de seguimiento, scripts de Transact-SQL o archivos XML como entradas de carga de trabajo al ajustar las bases de datos. Cuando se trabaja con Amazon RDS, una carga de trabajo puede ser un archivo en un equipo cliente o una tabla una base de datos Amazon RDS for SQL Server a la que su equipo cliente pueda obtener acceso. El archivo o la tabla deben contener consultas para las bases de datos que se desea ajustar en un formato adecuado para la repetición.

Para que el Asistente para la optimización sea más eficaz, una carga de trabajo debe ser lo más realista posible. Puede generar un archivo o una tabla de carga de trabajo realizando un seguimiento en su instancia de base de datos. Mientras se ejecuta un seguimiento, puede simular una carga en su instancia de base de datos o ejecutar sus aplicaciones con una carga normal.

Hay dos tipos de seguimientos: del lado del cliente y del lado del servidor. Un seguimiento del lado del cliente es más fácil de configurar y permite ver los eventos de seguimiento mientras se capturan en tiempo real en SQL Server Profiler. Un seguimiento del lado del servidor es más difícil de configurar y requiere scripting de Transact-SQL. Además, como el seguimiento se escribe en un archivo en la instancia de base de datos de Amazon RDS, consume espacio de almacenamiento. Es importante realizar un seguimiento del espacio de almacenamiento que usa un seguimiento del lado del servidor en ejecución, ya que la instancia de base de datos podría llegar a un estado de almacenamiento lleno y dejar de estar disponible si se queda sin espacio de almacenamiento.

Para un seguimiento del lado del cliente, cuando se ha capturado la cantidad necesaria de datos de seguimiento en SQL Server Profiler, puede generar el archivo de carga de trabajo guardando el seguimiento en un archivo del equipo local o en una tabla de base de datos de una instancia de base de datos que esté disponible en el equipo cliente. La principal desventaja de usar un seguimiento del lado del cliente es que el seguimiento podría no capturar todas las consultas si la carga es elevada. Esto podría reducir la eficacia del análisis realizado por el Asistente para la optimización del motor de base de datos. Si necesita ejecutar un seguimiento con una carga elevada y quiere asegurarse de que captura todas las consultas que se producen durante una sesión de seguimiento, debe usar un seguimiento del lado del servidor.

Para un seguimiento del lado del servidor, debe convertir los archivos de seguimiento de la instancia de base de datos en un archivo de carga de trabajo adecuado o puede guardar el seguimiento en una tabla de la instancia de base de datos una vez que se complete el seguimiento. Puede usar SQL Server Profiler para guardar el seguimiento en un archivo de su equipo local o hacer que el Asistente para la optimización lea de la tabla de seguimiento de la instancia de base de datos.

Ejecución de un seguimiento del lado del cliente en una instancia de base de datos de SQL Server

Para ejecutar un seguimiento del lado del cliente en una instancia de base de SQL Server

1. Inicie SQL Server Profiler. Está instalado en la carpeta Performance Tools de la carpeta de la instancia de SQL Server. Debe cargar o definir una plantilla de definición de seguimiento para iniciar un seguimiento del lado del cliente.

2. En el menú Archivo de SQL Server Profiler, elija New Trace (Nuevo seguimiento). En el cuadro de diálogo Conectar con el servidor, escriba el punto de enlace de la instancia de base de datos, el puerto, el nombre del usuario maestro y la contraseña de la base de datos en la que desea ejecutar un seguimiento.

3. En el cuadro de diálogo Propiedades de seguimiento, escriba un nombre de seguimiento y elija una plantilla de definición de seguimiento. Con la aplicación se suministra una plantilla predeterminada, TSQL_Replay. Puede editar esta plantilla para definir el seguimiento. Edite los eventos y la información de eventos en la pestaña Selección de eventos del cuadro de diálogo Propiedades de seguimiento.

   Para obtener más información acerca de las plantillas de definición de seguimiento y acerca del uso de SQL Server Profiler para especificar un seguimiento del lado del cliente, consulte Asesor de ajuste del motor de baseen la documentación de Microsoft.

4. Inicie el seguimiento del lado del cliente y vea las consultas de SQL en tiempo real mientras se ejecutan en la instancia de base de datos.

5. Seleccione Stop Trace (Detener seguimiento) en el menú File (Archivo) cuando haya completado el seguimiento. Guarde los resultados como un archivo o como una tabla de seguimiento en su instancia de base de datos.

Ejecución de un seguimiento del lado del servidor en una instancia de base de datos de SQL Server

Escribir scripts para crear un seguimiento del lado del servidor puede ser complicado y está fuera del alcance de este documento. Esta sección contiene scripts de muestra que se pueden usar como ejemplos. Al igual que en el seguimiento del lado del cliente, el objetivo es crear un archivo de carga de trabajo o una tabla de seguimiento que se puede abrir con el Asistente para la optimización del motor de base de datos.

A continuación se muestra un script de ejemplo abreviado que inicia un seguimiento del lado del servidor y captura detalles en un archivo de carga de trabajo. El seguimiento se guarda inicialmente en el archivo RDSTrace.trc del directorio D:\RDSDBDATA\Log y cambia cada 100 MB, de modo que los archivos de seguimiento posteriores se llaman RDSTrace_1.trc, RDSTrace_2.trc, etc.

DECLARE @file_name NVARCHAR(245) = 'D:\RDSDBDATA\Log\RDSTrace';
DECLARE @max_file_size BIGINT = 100;
DECLARE @on BIT = 1
DECLARE @rc INT
DECLARE @traceid INT

EXEC @rc = sp_trace_create @traceid OUTPUT, 2, @file_name, @max_file_size
IF (@rc = 0) BEGIN
   EXEC sp_trace_setevent @traceid, 10, 1, @on
   EXEC sp_trace_setevent @traceid, 10, 2, @on
   EXEC sp_trace_setevent @traceid, 10, 3, @on
 . . .
   EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Profiler'
   EXEC sp_trace_setstatus @traceid, 1
   END

En el siguiente ejemplo se muestra un script que detiene un seguimiento. Un seguimiento creado por el script anterior sigue en ejecución hasta que el seguimiento se detiene expresamente o el proceso se queda sin espacio en el disco.

DECLARE @traceid INT
SELECT @traceid = traceid FROM ::fn_trace_getinfo(default) 
WHERE property = 5 AND value = 1 AND traceid <> 1 

IF @traceid IS NOT NULL BEGIN
   EXEC sp_trace_setstatus @traceid, 0
   EXEC sp_trace_setstatus @traceid, 2
END 

Puede guardar los resultados del seguimiento del lado del servidor en una tabla de base de datos y usar dicha tabla como carga de trabajo en el Asistente para la optimización por medio de la función fn_trace_gettable. Los siguientes comandos cargan los resultados de todos los archivos con el nombre RDSTrace.trc en el directorio D:\rdsdbdata\Log, incluidos todos los archivos de sustitución incremental, como RDSTrace_1.trc, en una tabla llamada RDSTrace en la base de datos actual.

SELECT * INTO RDSTrace
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace.trc', default); 

Para guardar un archivo de sustitución incremental en una tabla, por ejemplo el archivo RDSTrace_1.trc, especifique el nombre del archivo de sustitución incremental y cambie por 1 el valor predeterminado del último parámetro de fn_trace_gettable.

SELECT * INTO RDSTrace_1
FROM fn_trace_gettable('D:\rdsdbdata\Log\RDSTrace_1.trc', 1); 

Ejecución del Asistente para la optimización con un seguimiento

Una vez que se crea un seguimiento, como archivo local o como tabla de base de datos, se puede ejecutar el Asistente para la optimización en una instancia de base de datos. Usar el Asistente para la optimización con Amazon RDS es el mismo proceso que trabajar con una instancia de SQL Server independiente y remota. Puede usar la interfaz de usuario del Asistente para la optimización de su equipo cliente o usar la utilidad dta.exe desde la línea de comando. En ambos casos, debe conectar con la instancia de base de datos de Amazon RDS a través del punto de enlace de la instancia de base de datos y proporcionar su nombre de usuario maestro y su contraseña de usuario maestra cuando utilice el Asistente para la optimización.

El siguiente ejemplo de código demuestra el uso de la utilidad de línea de comando dta.exe con una instancia de base de datos de Amazon RDS con un punto de enlace de dta.cnazcmklsdei.us-east-1.rds.amazonaws.com. El ejemplo incluye el nombre de usuario maestro admin y la contraseña testde usuario maestro; la base de datos de ejemplo que se va a ajustar se denomina máquina denominada C:\RDSTrace.trc. El código de ejemplo de la línea de comando especifica también una sesión de seguimiento llamada RDSTrace1 y archivos de salida en el equipo local con los nombres RDSTrace.sql para el script de salida de SQL, RDSTrace.txt para un archivo de resultado y RDSTrace.xml para un archivo XML del análisis. También se especifica en la base de datos RDSDTA una tabla de errores denominada RDSTraceErrors.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -if C:\RDSTrace.trc -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors 

A continuación se muestra el mismo código de línea de comando de ejemplo, salvo en que la carga de trabajo de entrada es una tabla en la instancia de Amazon RDS remota llamada RDSTrace que está en la base de datos RDSDTA.

dta -S dta.cnazcmklsdei.us-east-1.rds.amazonaws.com -U admin -P test -D RDSDTA -it RDSDTA.dbo.RDSTrace -s RDSTrace1 -of C:\ RDSTrace.sql -or C:\ RDSTrace.txt -ox C:\ RDSTrace.xml -e RDSDTA.dbo.RDSTraceErrors

Para obtener una lista completa de los parámetros de línea de comandos de la utilidad dta, consulte Utilidad dta en la documentación de Microsoft.