Cómo optimizar el servidor SQL Server

Los archivos de datos y de registro generarán una gran cantidad de E /S. Cuando sea posible, los archivos de registro deben almacenarse en un disco duro diferente al de los datos y los archivos de datos de índice para expandir la E /S, al mismo tiempo que facilita la recuperación de desastres de la base de datos.

2. La base de datos Tempdb se almacena por separado en diferentes discos

La base de datos tempdb es una base de datos temporal que todas las demás bases de datos pueden usar. Cuando utiliza select en para ejecutar Orderby en una columna que no está indexada, se genera una tabla temporal en la base de datos tempdb para almacenar los datos intermedios. Dado que la construcción y el llenado de tablas temporales pueden degradar gravemente el rendimiento del sistema, las columnas a clasificar deben indexarse ​​siempre que sea posible. Al mismo tiempo, la base de datos tempdb es compartida por todos los usuarios y aplicaciones, por lo que si un usuario ocupa todo el espacio en la base de datos tempdb, otras bases de datos ya no estarán disponibles. Cuando sea posible, la base de datos tempdb se debe colocar por separado en un disco duro más rápido o en una matriz RAID. Separe las operaciones de E /S de la base de datos tempdb para acelerar el rendimiento. La base de datos tempdb debe tener la capacidad adecuada para satisfacer las necesidades del usuario. Se debe permitir que el espacio de la base de datos tempdb crezca automáticamente. Si se configura para no permitir el crecimiento automático, la operación no se completará cuando la operación de consulta establezca una tabla temporal que exceda la capacidad de la base de datos tempdb.

Establezca correctamente la tasa de crecimiento de la base de datos tempdb. Una tasa de crecimiento demasiado pequeña generará más residuos externos y ocupará más recursos.

3. Evite la generación de datos en caliente

Antes de SQL Server 7.0, para tablas sin un índice agrupado (tabla de pila), la fila de datos recién insertada siempre se coloca en el disco. Al final Si hay muchos usuarios simultáneos y al mismo tiempo realiza una operación de inserción o actualización de datos en la tabla, esto permitirá generar puntos de acceso de datos al final de una tabla muy ocupada. Las operaciones de E /S simultáneas se centran en un pequeño número de páginas, lo que puede provocar una disminución en el rendimiento de la base de datos.

En SQL Server, la asignación de espacio de almacenamiento físico para nuevas filas de datos se realiza a través de páginas PFS. El algoritmo de administración de la página PFS distribuye las operaciones de inserción para evitar generar puntos de acceso a datos.

Al diseñar aplicaciones y bases de datos, evite crear claves primarias en columnas que crecen naturalmente, lo que puede generar datos importantes.

4. Menos tipos de datos

Al diseñar tablas, use el menor número posible de tipos de datos. La mayor cantidad de información se puede guardar en dicha página de datos. Hay menos páginas de datos y menos operaciones de E /S para recuperar páginas de datos, por lo que la eficiencia es alta.

5. Supervisar y desfragmentar los desechos del espacio

El crecimiento automático de los espacios de archivos mejora la gestión automática, pero puede provocar la fragmentación del espacio. El espacio lógico del espacio físico y los datos ya no es continuo. El monitoreo regular y la desfragmentación del espacio ayudan a mejorar el rendimiento de E /S.

6. Utilizando el archivo de datos primario y el archivo de datos secundario

Un archivo de datos primario para cada base de datos pertenece al grupo de archivos principal. Para una base de datos de aproximadamente 1 GB de tamaño, un archivo de datos es suficiente. Si hay un archivo de datos secundario, el archivo de datos principal tiene un puntero para administrar el archivo de datos secundario.

Cuando se usan varios archivos de datos, el archivo de datos principal se usa para almacenar objetos y tablas del sistema, y ​​los archivos de datos secundarios se usan para almacenar los datos e índices del usuario. Cuando sea posible, los archivos de datos primarios y secundarios se pueden almacenar por separado en discos separados para distribuir la E /S.

Si se utilizan varios archivos de datos, se recomienda que los archivos de datos primarios almacenen los datos del sistema y que los archivos de datos secundarios almacenen los datos e índices del usuario, lo que ayudará a mejorar el rendimiento de E /S.

7. Mejore el rendimiento con grupos de archivos

En sistemas de bases de datos grandes, considere crear grupos de archivos para administrar archivos de datos. La tabla y el índice se almacenan en diferentes discos físicos para monitorear y comparar el rendimiento, y finalmente se obtiene una solución de almacenamiento optimizada.

8. Preste atención a los problemas de rendimiento que pueden ser causados ​​por el crecimiento automático y la reducción automática

El crecimiento automático y la reducción automática de los archivos de base de datos es muy útil para la administración de bases de datos pequeñas. Pero puede causar problemas de rendimiento con grandes bases de datos. Debido al crecimiento natural de los archivos, puede producirse una fragmentación del almacenamiento. Cuando el espacio de archivos se hace más grande, el espacio recién asignado no es necesariamente continuo con el espacio original. Cuando el espacio de archivos se reduce, parte del espacio se libera. Sin embargo, cuando el archivo necesita aumentar el espacio de almacenamiento pero no puede usar el espacio que se lanzó originalmente, también causará la fragmentación.

9. Separación de datos del sistema y datos del usuario

Separación de las bases de datos del sistema y del usuario en diferentes discos físicos ayuda a mejorar el rendimiento de E /S y la copia de seguridad y recuperación de bases de datos .

10. Optimizar el diseño del índice

El diseño del índice es muy importante para el rendimiento de la base de datos. Para más detalles, consulte este artículo relacionado con el blog.

11. Actualice periódicamente las estadísticas

SQL Server utiliza la optimización basada en costos de forma predeterminada, por lo que la actualización oportuna de las estadísticas es muy importante para la optimización de consultas.

12. Comprobación de coherencia periódica

Compruebe periódicamente la coherencia de la base de datos para garantizar la integridad de la base de datos.