Crear una instancia de alto rendimiento de SQL Server

En este tutorial se muestra cómo crear una instancia de VM de Compute Engine que ejecute SQL Server y esté optimizada para el rendimiento. En este tutorial se explica cómo crear la instancia y, a continuación, configurar SQL Server para obtener un rendimiento óptimo enGoogle Cloud. Descubrirás varias opciones de configuración que te ayudarán a ajustar el rendimiento del sistema.

En este tutorial se usa SQL Server Standard Edition 2022, por lo que no todas las opciones de configuración que se presentan en esta guía funcionan para todos los usuarios y no todas proporcionan mejoras de rendimiento notables para todas las cargas de trabajo.

Crear la VM de Compute Engine con discos

Para crear una instancia de alto rendimiento de SQL Server, primero debes crear una instancia de VM con SQL Server y dos volúmenes de disco persistente.

Consideraciones sobre Persistent Disk

Para seleccionar el tipo de volúmenes de disco persistente de tu VM, ten en cuenta lo siguiente:

  • Un disco SSD local proporciona una ubicación de alto rendimiento para tempdb y el archivo de paginación de Windows.

    Hay algunas consideraciones importantes que debes tener en cuenta al usar un disco SSD local. Cuando apagas la instancia desde Windows o la restableces mediante la API, se elimina el disco SSD local. Esta acción hace que la instancia no se pueda arrancar. Para que la máquina vuelva a funcionar, tendrías que separar tus discos persistentes, crear una instancia con ellos y, a continuación, definir un nuevo disco SSD local. Después de iniciar el sistema, también tendrás que formatear el nuevo disco y reiniciar. Por lo tanto, no debes almacenar datos críticos de forma permanente en un disco SSD local ni apagar la instancia, a menos que estés preparado para volver a crearla.

  • Un Persistent Disk SSD proporciona almacenamiento de alto rendimiento para los archivos de la base de datos.

    El rendimiento de Persistent Disk se basa en un cálculo que utiliza el número de CPUs y el tamaño del disco. Con 32 vCPUs y un disco de 1 TB, el rendimiento alcanza un máximo de 40.000 operaciones de lectura por segundo y 30.000 operaciones de escritura. El rendimiento total sostenido de lectura y escritura es de 800 MB por segundo y 400 MB por segundo, respectivamente. Estas mediciones representan la suma de todos los volúmenes de disco persistente conectados a la máquina virtual, incluida la unidad C:\. Para asegurar un rendimiento constante, crea un disco SSD local y descarga todas las IOPS necesarias para el archivo de paginación,tempdb, los datos de almacenamiento provisional y las copias de seguridad.

Para obtener más información sobre el rendimiento de los discos, consulta el artículo Configurar discos para cumplir los requisitos de rendimiento.

Crear una VM de Compute Engine con discos

Para crear una VM que tenga SQL Server 2022 Standard preinstalado en Windows Server 2022, sigue estos pasos:

  1. En la Google Cloud consola, ve a la página Crear una instancia.

    Ir a Crear una instancia

  2. En Nombre, escribe ms-sql-server.

  3. En la sección Configuración de la máquina, seleccione Uso general y, a continuación, haga lo siguiente:

    1. En la lista Series (Series), haz clic en N2.
    2. En la lista Tipo de máquina, haga clic en n2-highmem-16 (16 vCPUs, 128 GB de memoria).

  4. En la sección Disco de arranque, haz clic en Cambiar y, a continuación, haz lo siguiente:

    1. En la pestaña Imágenes públicas, haga clic en la lista Sistema operativo y, a continuación, seleccione SQL Server en Windows Server.
    2. En la lista Versión, haz clic en SQL Server 2022 Standard en Windows Server 2022 Datacenter.
    3. En la lista Tipo de disco de arranque, haz clic en Disco persistente estándar.
    4. En el campo Tamaño (GB), define el tamaño del disco de arranque en 50 GB.
    5. Para guardar la configuración del disco de arranque, haz clic en Seleccionar.

  5. Despliega la sección Opciones avanzadas y haz lo siguiente:

    1. Despliega la sección Discos.

    2. Para crear discos locales, haga clic en Añadir SSD local y, a continuación, siga estos pasos:

      1. En la lista Interfaz, selecciona el protocolo que cumpla los requisitos de rendimiento de tu sistema.
      2. En la lista Capacidad del disco, selecciona una capacidad que admita el tamaño previsto de los archivos tempdb.
      3. Para terminar de crear este disco, haga clic en Guardar.

    3. Para crear discos adicionales, haz clic en Añadir disco.

      1. No cambies el campo Nombre.
      2. En la lista Tipo de fuente de disco, selecciona Disco en blanco.
      3. En la lista Tipo de disco, selecciona Disco persistente SSD.
      4. En el campo Tamaño, introduzca el tamaño del disco que pueda albergar el tamaño de la base de datos.
      5. Para terminar de crear el segundo disco, haz clic en Guardar.

  6. Para crear la VM, haz clic en Crear.

Configurar Windows

Ahora que tienes una instancia de SQL Server en funcionamiento, conéctate a ella y configura el sistema operativo Windows. Después, aprenderás a configurar SQL Server en una sección posterior.

Conectarse a la instancia

  1. En la consola de Google Cloud , ve a la página Instancias de VM.

    Ir a instancias de VM

  2. En la columna Nombre, haz clic en el nombre de tu instancia, ms-sql-server.

  3. En la parte superior de la página de detalles de la instancia, haz clic en el botón Establecer contraseña de Windows.

  4. Especifica un nombre de usuario.

  5. Haz clic en Definir para generar una contraseña nueva para esta instancia de Windows.

  6. Anota el nombre de usuario y la contraseña para poder iniciar sesión en la instancia.

  7. Conéctate a tu instancia mediante RDP.

Configurar volúmenes de disco

Crea y da formato a los volúmenes:

  1. En el menú Inicio, busca "Administración de equipos" y ábrelo.
  2. En la sección Almacenamiento, selecciona Administración de discos.
  3. Cuando se te pida que inicialices los discos, acepta las selecciones predeterminadas y haz clic en Aceptar.

  4. Crea una partición para los discos SSD locales:

    Para localizar un disco SSD local, haz clic con el botón derecho en un disco y selecciona Propiedades. El nombre de las propiedades del disco SSD local será Google EphemeralDisk para una interfaz SCSI o nvme_card para una interfaz NVMe. Tanto los discos SSD locales como los SSD persistentes están marcados como que tienen particiones Unallocated.

    1. Si la máquina virtual solo contiene una unidad SSD local, sigue estos pasos:

      1. En la lista de unidades de disco, haz clic con el botón derecho en el disco SSD local de 374,98 GB y selecciona Nuevo volumen simple.
      2. En la pantalla de bienvenida, haz clic en Siguiente para iniciar el asistente de volumen de disco.
      3. En el paso Specify Volume Size (Especificar tamaño del volumen), deje el tamaño del volumen con el valor predeterminado y haga clic en Next (Siguiente) para continuar.
      4. En el paso Asignar letra de unidad o ruta, elige P: como letra de unidad y haz clic en Siguiente para continuar.

      5. En el paso Formatear volumen, cambia el Tamaño de unidad de asignación a 8192 y escribe "pagefile" en Etiqueta del volumen. Haz clic en Siguiente para continuar.

        Asistente para crear un volumen

      6. Haz clic en Finalizar para completar el asistente de volumen de disco.

    2. Si la máquina virtual contiene varias unidades SSD locales, sigue estos pasos:

      1. En la lista de unidades de disco, haz clic con el botón derecho en el primer disco SSD local de 374,98 GB y selecciona Nuevo volumen seccionado.
      2. En la pantalla de bienvenida, haz clic en Siguiente para iniciar el asistente de volumen de disco.

      3. En el paso Seleccionar discos, añade todos los discos disponibles con el tamaño 383.982 MB a la sección Seleccionados. Haz clic en Siguiente para continuar.

        Añadir discos segmentados

      4. En el paso Asignar letra de unidad o ruta, elige P: como letra de unidad y haz clic en Siguiente para continuar.

      5. En el paso Formatear volumen, cambia el Tamaño de unidad de asignación a 8192 y escribe "pagefile" en Etiqueta del volumen. Haz clic en Siguiente para continuar.

        Asistente para crear un volumen

      6. Haz clic en Finalizar para completar el asistente de volumen de disco.

  5. Repite los pasos anteriores para crear un Nuevo volumen simple para el disco SSD, con los tres cambios siguientes:

    • Elige D: como letra de unidad.

    • Asigne el valor 64k a Tamaño de la unidad de asignación.

      Para obtener información sobre cómo seleccionar el tamaño de una unidad de asignación, consulta las prácticas recomendadas para instancias de SQL Server.

    • Introduce sqldata en Etiqueta de volumen.

Mover el archivo de paginación de Windows

Ahora que se han creado y montado los nuevos volúmenes, mueve el archivo de paginación de Windows al disco SSD local, lo que libera IOPS del disco persistente y mejora el tiempo de acceso de tu memoria virtual.

  1. En el menú Inicio, busca Ver configuración avanzada del sistema y abre el cuadro de diálogo.
  2. Haz clic en la pestaña Avanzado y, en la sección Rendimiento, haz clic en Configuración.
  3. En la sección Memoria virtual, haz clic en el botón Cambiar.
  4. Desmarca la casilla Gestionar automáticamente el tamaño del archivo de paginación de todas las unidades. El sistema ya debería haber configurado el archivo de paginación en la unidad C:\, y debes moverlo.
  5. Haz clic en C: y, a continuación, en el botón de radio Sin archivo de paginación.
  6. Haz clic en el botón Definir.
  7. Para crear el nuevo archivo de paginación, haga clic en la unidad P: y, a continuación, en el botón de radio Tamaño gestionado por el sistema.
  8. Haz clic en el botón Definir.

  9. Haz clic en Aceptar tres veces para salir de las propiedades avanzadas del sistema.

    El equipo de Asistencia de Microsoft ha publicado consejos adicionales sobre la configuración de la memoria virtual.

Configurar el perfil de energía

Configura el perfil de energía en High-Performance en lugar de Balanced.

  1. En el menú Inicio, busca "Elegir un plan de energía" y, a continuación, abre las opciones de energía.
  2. Selecciona el botón de radio Alto rendimiento.
  3. Sal del cuadro de diálogo.

Configurar SQL Server

Usa SQL Server Management Studio para realizar la mayoría de las tareas administrativas. Las imágenes preconfiguradas de SQL Server incluyen Management Studio. Inicia Management Studio y haz clic en Conectar para conectarte a la base de datos predeterminada.

Mover los datos y los archivos de registro

La imagen preconfigurada de SQL Server incluye todo lo que está instalado en la unidad C:\, incluidas las bases de datos del sistema. Para optimizar la configuración, mueve esos archivos a la nueva unidad D:\ que has creado. Recuerda también crear todas las bases de datos en la unidad D:\. Como usas una unidad SSD, no es necesario que almacenes los archivos de datos y los archivos de registro en particiones de disco independientes.

Hay dos formas de mover la instalación al disco secundario: usar el instalador o mover los archivos manualmente.

Usar el instalador

Para usar el instalador, ejecuta c:\setup.exe y selecciona una nueva ruta de instalación en tu disco secundario.

Mover los archivos manualmente

Mueve las bases de datos del sistema y configura SQL Server para que guarde los archivos de datos y de registro en el mismo volumen:

  1. Crea una carpeta llamada D:\SQLData.
  2. Abre una ventana de comandos.

  3. Introduce el siguiente comando para conceder acceso completo a NT Service\MSSQLSERVER:

    icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"

  4. Usa Management Studio y las siguientes guías para mover tus bases de datos del sistema y cambiar las ubicaciones de archivo predeterminadas de las nuevas bases de datos.

  5. Si tienes previsto usar las funciones de Report Server, mueve también los archivos ReportServer y ReportServerTempDB.

Después de mover los archivos de la base de datos de configuración principal y reiniciar, debe configurar el sistema para que apunte a la nueva ubicación de las bases de datos de modelos y MSDB. Aquí tienes una secuencia de comandos auxiliar para ejecutar en Management Studio:

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

Después de ejecutar estos comandos:

  1. Usa el complemento services.msc para detener el servicio de base de datos de SQL Server.
  2. Usa el explorador de archivos de Windows para mover los archivos físicos de la unidad C:\ donde se encontraba la base de datos master al directorio D:\SQLData.
  3. Inicia el servicio de base de datos de SQL Server.

Definir permisos del sistema

Después de mover las bases de datos del sistema, modifique algunos ajustes adicionales. Empiece por los permisos de la cuenta de usuario de Windows creada para ejecutar el proceso de SQL Server, que se llama NT Service\MSSQLSERVER.

Conceder el permiso Lock Pages in Memory

El permiso de la directiva de grupo Lock Pages in Memory impide que Windows mueva páginas de la memoria física a la memoria virtual. Para mantener la memoria física libre y organizada, Windows intenta intercambiar páginas antiguas que se modifican con poca frecuencia al archivo de paginación de memoria virtual en el disco.

SQL Server almacena información importante en la memoria, como estructuras de tablas, planes de ejecución y consultas almacenadas en caché. Parte de esta información cambia muy poco, por lo que se convierte en un objetivo para el archivo de paginación. Si esta información se mueve al archivo de paginación, el rendimiento de SQL Server puede disminuir. Si se concede el permiso de política de grupo Lock Pages in Memory a la cuenta de servicio de SQL Server, se evita este intercambio.

Sigue estos pasos:

  1. Haz clic en Inicio y busca Editar directiva de grupo para abrir la consola.
  2. Expande Directiva de equipo local > Configuración del equipo > Configuración de Windows > Configuración de seguridad > Directivas locales > Asignación de derechos de usuario.
  3. Busca Bloquear páginas en memoria y haz doble clic en ella.
  4. Haz clic en Añadir usuario o grupo.
  5. Busca "NT Service\MSSQLSERVER".
  6. Si ves varios nombres, haz doble clic en el nombre MSSQLSERVER.
  7. Haz clic en Aceptar dos veces.
  8. Mantén abierta la consola Editor de directivas de grupo.

Bloquear páginas

Conceder el permiso Perform volume maintenance tasks

De forma predeterminada, cuando una aplicación solicita una porción de espacio en disco a Windows, el sistema operativo localiza un fragmento de espacio en disco del tamaño adecuado y, a continuación, pone a cero todo el fragmento de disco antes de devolverlo a la aplicación. Como SQL Server es bueno para aumentar el tamaño de los archivos y llenar el espacio en disco, este comportamiento no es óptimo.

Hay una API independiente para asignar espacio en disco a una aplicación, a menudo denominada inicialización instantánea de archivos. Lamentablemente, este ajuste solo funciona con archivos de datos, pero en una sección posterior aprenderás a controlar el crecimiento de los archivos de registro. La inicialización instantánea de archivos requiere que la cuenta de servicio que ejecuta el proceso de SQL Server tenga otro permiso de política de grupo, llamado Perform volume maintenance tasks.

  1. En el Editor de directivas de grupo, busca "Realizar tareas de mantenimiento de volumen".
  2. Añade la cuenta "NT Service\MSSQLSERVER" como hiciste en la sección anterior.
  3. Reinicia el proceso de SQL Server para activar ambos ajustes.

Configurando tempdb

Antes, se recomendaba optimizar el uso de la CPU de SQL Server creando un archivo tempdb por CPU. Sin embargo, como el número de CPUs ha aumentado con el tiempo, seguir esta directriz puede provocar que el rendimiento disminuya. Como punto de partida, puedes usar 4 archivos tempdb. Mientras mides el rendimiento de tu sistema, en casos excepcionales, es posible que tengas que aumentar gradualmente el número de archivos tempdb hasta un máximo de 8.

Puedes ejecutar una secuencia de comandos de Transact-SQL (T-SQL) en SQL Server Management Studio para mover los archivos tempdb a una carpeta de la unidad `p:`.

  1. Crea el directorio p:\tempdb.

  2. Concede acceso de seguridad completo a la cuenta de usuario "NT Service\MSSQLSERVER":

    icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"

  3. Ejecuta la siguiente secuencia de comandos en SQL Server Management Studio para mover el archivo de datos tempdb y el archivo de registro:

    USE master
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
GO

  4. Reinicia SQL Server.

  5. Ejecuta la siguiente secuencia de comandos para modificar los tamaños de los archivos y crear tres archivos de datos adicionales para el nuevo tempdb.

    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
GO

    Si usas SQL Server 2016, hay 3 archivos tempdb adicionales que debes eliminar después de seguir los pasos anteriores:

    ALTER DATABASE [tempdb] REMOVE FILE temp2;
ALTER DATABASE [tempdb] REMOVE FILE temp3;
ALTER DATABASE [tempdb] REMOVE FILE temp4;

  6. Vuelve a reiniciar SQL Server.

  7. Elimina los archivos model, MSDB, master y tempdb de la ubicación original de la unidad C:\.

Has movido correctamente tus archivos tempdb a la partición del disco SSD local. Este movimiento conlleva algunos riesgos, como se ha mencionado anteriormente, pero si se pierden por cualquier motivo,SQL Server vuelve a compilar los archivos tempdb. Al mover tempdb, obtendrás el rendimiento adicional de la unidad SSD local y se reducirán las IOPS utilizadas en tus volúmenes de Persistent Disk.

Configurando max degree of parallelism

La configuración predeterminada recomendada para max degree of parallelism es que coincida con el número de CPUs del servidor. Sin embargo, llega un punto en el que ejecutar una consulta en 16 o 32 fragmentos paralelos y combinar los resultados es mucho más lento que ejecutarla en un solo proceso. Si usas una instancia de 16 o 32 núcleos, puedes definir el valor max degree of parallelism en 8 con el siguiente T-SQL:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

Configurando max server memory

Este ajuste tiene un valor predeterminado muy alto, pero debes establecerlo en el número de megabytes de RAM física disponible, menos un par de gigabytes para el sistema operativo y la sobrecarga. En el siguiente ejemplo de T-SQL se ajusta max server memory a 100 GB. Modifícalo para ajustar el valor a su instancia. Consulta el documento Opciones de configuración del servidor de memoria del servidor para obtener más información.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

Finalizando

Reinicia la instancia una vez más para asegurarte de que todos los ajustes nuevos se apliquen. Tu sistema SQL Server está configurado y puedes crear tus propias bases de datos y empezar a probar tus cargas de trabajo específicas. Consulta la guía de prácticas recomendadas de SQL Server para obtener más información sobre las actividades operativas, otras consideraciones sobre el rendimiento y las funciones de la edición Enterprise.