• Articles
• admin

Recientemente, hemos trabajado en la resolución de problemas de rendimiento de SQL server para algunos de nuestros grandes clientes que ejecutan SQL server como su base de datos. En este artículo, resumiré lo que he aprendido del proceso describiendo algunos pasos que podemos tomar para solucionar problemas de rendimiento.

Compruebe la configuración de SQL Server

Asegúrese de que el servidor de base de datos esté configurado con suficientes recursos, como el número de núcleos de CPU y la cantidad de memoria.

Para comprobar la configuración del servidor, puede abrir la vista «Información del sistema»:

Para comprobar la configuración de memoria de SQL Server,

1. Inicie SQL Server Management Studio.
2. Haga clic con el botón derecho en su instancia de base de datos y seleccione «Propiedades».
3. Haga clic en la tabla » Memoria «en la ventana emergente» Propiedades del servidor».
4. Compruebe la configuración de la memoria.

Asegúrese de que el modo de instantánea esté Activado

Asegúrese de que el modo de instantánea esté activado para la base de datos. Para evitar que SQL Server se bloquee, el indicador de modo de instantánea debe estar activado. Ejecute la siguiente consulta para comprobar si el indicador está activado:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<database_name>'

Si la consulta devuelve ‘1’, el modo de instantánea ya está activado. De lo contrario, ejecute la siguiente consulta para activarla.

ALTER DATABASE <database_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

RELACIONADOS: Definir e Implementar las líneas de base de requisitos

Comprobar los índices de la base de datos

Comprobar los índices de la base de datos para asegurarse de que no faltan índices. Ejecute la siguiente consulta para listar todos los índices de la base de datos (crédito: tsql – Lista de todos los índices & columnas de índice en el desbordamiento de pila de bases de datos de SQL Server):

SELECT t.name TableName, col.name ColumnName, ind.name IndexNameFROM sys.indexes indINNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_idINNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_idINNER JOIN sys.tables t ON ind.object_id = t.object_idWHERE (ind.is_primary_key = 0 AND t.is_ms_shipped = 0)ORDER BY t.name, col.name, ind.name

Guarde los resultados de la consulta en un archivo de texto con columnas delimitadas por tabulaciones para que puedan importarse posteriormente a una aplicación de hoja de cálculo.

Evite la fragmentación

Asegúrese de que los índices de la base de datos no estén fragmentados. Los índices de la base de datos ayudan a acelerar la consulta de la base de datos. Pero cuando se fragmentan, las consultas pueden ejecutarse muy lentamente. Como parte del mantenimiento de la base de datos, los índices deben reorganizarse o reconstruirse periódicamente.

Ejecute la siguiente consulta para comprobar el porcentaje de fragmentación de todos los índices de la base de datos (crédito: Cómo comprobar la fragmentación de índices en Índices de una base de datos):

SELECT dbschemas.Jama Software AS 'Schema', dbtables.Jama Software AS 'Table', dbindexes.Jama Software AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, dbindexes.fill_factorFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstatsINNER JOIN sys.tables dbtables ON dbtables. = indexstats.INNER JOIN sys.schemas dbschemas ON dbtables. = dbschemas.INNER JOIN sys.indexes AS dbindexes ON dbindexes. = indexstats. AND indexstats.index_id = dbindexes.index_idWHERE indexstats.database_id = DB_ID()ORDER BY indexstats.avg_fragmentation_in_percent DESC

Aquí hay un resultado de consulta de ejemplo para una base de datos con índices muy fragmentados:

Para índices con recuentos de páginas superiores a 1000, el «avg_framentation_in_percent» debe mantenerse por debajo del 10%. No importa mucho para índices pequeños. Como regla general, los índices con porcentajes de fragmentación entre el 5% y el 30% podrían reorganizarse, y los índices con porcentajes de fragmentación superiores al 30% deberían reconstruirse.

Para reorganizar un único índice:

ALTER INDEX REORGANIZE

Para reconstruir un único índice:

ALTER INDEX REBUILD WITH (ONLINE = ON)

También puede reorganizar o reconstruir un índice con SQL Server Management Studio haciendo clic con el botón derecho en el índice que desea reconstruir o reorganizar.

Si hay muchos índices desfragmentados en la base de datos, puede ejecutar la siguiente consulta para reconstruirlos todos (crédito: SQL SERVER – 2008 -2005-Reconstruir todos los índices de todas las tablas de la Base de datos – Reconstruir el índice con FillFactor). Tenga en cuenta que la consulta puede ejecutarse durante un tiempo para una base de datos grande. Se recomienda ejecutar la consulta mientras la base de datos está inactiva o sin conexión.

DECLARE @TableName VARCHAR(255)DECLARE @sql NVARCHAR(500)DECLARE TableCursor CURSOR FORSELECT OBJECT_SCHEMA_NAME()+'.'+name AS TableNameFROM sys.tablesOPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINSET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'PRINT @sqlEXEC (@sql)FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursorGO

Considere la posibilidad de configurar trabajos de mantenimiento en SQL Server studio para ejecutar la reorganización del índice de la base de datos con regularidad. Aunque la reconstrucción de índices en SQL Server probablemente debería hacerse sin conexión o cuando el sistema está inactivo, la reorganización de índices podría hacerse en línea. Aquí hay un buen artículo sobre este tema: Reconstruir o reorganizar: Mantenimiento de índices de SQL Server.

Para configurar un plan de reorganización de índices en SQL Server, haga clic con el botón derecho en «Administración», luego en «Planes de mantenimiento» y seleccione «Nuevo Plan de mantenimiento» o «Asistente de Plan de mantenimiento». Siga las instrucciones para crear un plan.

RELACIONADO: Opciones de administración de versiones en Jama Connect

Ejecutar Informe de índice faltante

SQL Server proporciona una herramienta que analiza las actividades de la base de datos y recomienda índices adicionales que pueden ayudar con el rendimiento de las consultas. El informe podría darnos algunas ideas sobre por qué ciertas consultas son lentas.

Para generar el informe, ejecute la siguiente consulta después de que la instancia de la base de datos se haya utilizado durante un tiempo (crédito: ¡No cree ciegamente esos índices «faltantes»!):

SELECT d., s = OBJECT_SCHEMA_NAME(d.), o = OBJECT_NAME(d.), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, s.avg_total_user_cost, s.avg_user_impactFROM sys.dm_db_missing_index_details AS dINNER JOIN sys.dm_db_missing_index_groups AS gON d.index_handle = g.index_handleINNER JOIN sys.dm_db_missing_index_group_stats AS sON g.index_group_handle = s.group_handleWHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d., 'IsMsShipped') = 0

Lea el artículo Búsqueda de índices faltantes para comprender el resultado de esta consulta.

Supervisar sesiones de base de datos

Utilice el Monitor de actividad para supervisar sesiones de base de datos. Microsoft SQL Server Management Studio viene con Monitor de actividad, que se puede utilizar para supervisar las sesiones de la base de datos.

Busque sesiones que estén bloqueadas por otras sesiones durante mucho tiempo. Haga clic con el botón derecho en la sesión y seleccione «Detalles» para mostrar los detalles de la consulta que está asociada con el proceso.

Usar Monitor de recursos de Windows

El monitor de recursos de Windows se puede utilizar para supervisar el uso de memoria y CPU del proceso de SQL Server para asegurarse de que hay suficiente memoria en el sistema y de que la CPU no está saturada. Tenga en cuenta que existe un problema conocido para SQL Server 2008 en el que la figura de memoria que se muestra para el proceso de SQL Server en el Monitor de recursos no es correcta.

Identificar consultas lentas

Si hemos determinado que el rendimiento no está relacionado con los recursos, el siguiente paso es identificar consultas lentas que conducen a un mal rendimiento.

La siguiente consulta devuelve las 100 consultas lentas principales que se ejecutan durante más de 300 ms (crédito: Consultas de ejecución larga):

SELECT st.text, qp.query_plan, qs.*FROM ( SELECT TOP 50 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC) AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE qs.max_worker_time > 300 OR qs.max_elapsed_time > 300

Aquí hay una captura de pantalla de algunos resultados de muestra:

Una vez que hayamos reunido una lista de consultas lentas, podríamos ejecutarlas directamente en la base de datos y mirar los planes de ejecución para comprender esas consultas.

Otras herramientas

Aquí hay una lista de otras herramientas que se pueden usar para solucionar problemas de aplicaciones SQL Server y Java:

• Generador de perfiles de SQL Server: se puede utilizar para identificar consultas lentas. Puede iniciarlo desde SQL Server Management Studio
• VisualVM: se puede usar para monitorear el uso de recursos Java y hacer volcados de subprocesos
• Control de misión Java/Grabación de vuelos: monitorear y grabar eventos del sistema
• JProfiler: identificar llamadas lentas a servicios o bases de datos (desarrollo, no compatible con 8.implementación de x)
• New Relic: puede monitorear el rendimiento de la base de datos y registrar consultas lentas

Listas de verificación

Aquí hay una lista de información que se puede recopilar para solucionar problemas de SQL Server:

1. Configuración del servidor de base de datos (número de núcleos de CPU, memoria física, espacio en disco, versión de Windows Server, versión de SQL Server, configuración de memoria de SQL Server)
2. Configuración del servidor de aplicaciones (número de núcleos de CPU, memoria física, espacio en disco, configuración de memoria de Java core)
3. Estadísticas de índice de base de datos
4. Estadísticas de fragmentación de base de datos
5. Informe de índice faltante de SQL server
6. Estadísticas de CPU y memoria del servidor de base de datos cuando el servidor es lento
7. Operaciones que hacen que el sistema sea lento
8. Informe de consulta lenta de SQL server
9. Estadísticas de tablas de base de datos
10. Un par de volcados de subprocesos de aplicaciones Java tomados durante las operaciones lentas, si corresponde

Conclusión

La depuración de problemas de rendimiento de SQL server no siempre es sencilla. Pero con las herramientas adecuadas y la paciencia, deberíamos ser capaces de entender la causa de estos problemas. Espero que la información de este artículo ayude con eso.