Hasta ahora hemos visto soluciones de performance que implican movimiento de ficheros para evitar cuellos de botella a nivel hardware, pero también es interesante contemplar soluciones cuando tenemos la BBDD bien parametrizada y hay que revisar cómo se están utilizando los objetos en operaciones de consulta, bien sea simplemente para recopilar de información o para realizar updates e inserts.
Hay una query muy interesante para descubrir cuáles son los procesos que más CPU consumen en la instancia y dentro de esta en qué BBDD se está ejecutando, para acotar un poco más el campo de búsqueda. Esta query devuelve el tiempo de CPU y ejecuciones de los procesos, y el tiempo medio de ejecución de los mismos, para encontrar cuáles son los que más consumen. También se indica en qué BBDD se ejecuta la sentencia que más CPU consume. Esta ordenada por la media de consumo por ejecución. Se puede acotar más la búsqueda con una instrucción WHERE o buscar solamente las que más consumen con la instrucción TOP.
SELECT qs.total_worker_time AS [Total CPU Time], qs.execution_count AS [Executions], qs.total_worker_time/qs.execution_count as [Avg CPU Time], SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end -qs.statement_start_offset)/2) AS [Query Text], db_name(qt.dbid) AS [DB Name], object_name(qt.objectid) AS [Object Name] FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Avg CPU Time] DESC
Hay varias razones que pueden influir en el consumo de CPU que pueden hacer que este se dispare y genere cuellos de botella y problemas en la instancia, pudiendo incluso llegar a bloquearlo, impidiendo que consultas de menor consumo no se puedan ejecutar con normalidad, o que operaciones generen bloqueos y estos se alarguen en el tiempo ya que dichas operaciones no consiguen terminar por esta lentitud.
Las razones más importantes son las siguientes:
- Fragmentación en los índices de las tablas implicadas en la consulta
Provoca que el acceso a los datos sea mucho más lento ya que los bloques físicos que contienen los datos están muy separadados. Se puede revisar la fragmentación de los índices con la siguiente consulta:
USE <database> GO SELECT db_name(database_id) AS [DB NAme], object_name(st.object_id) AS [Table Name], ind.name AS [Index Name], st.index_type_desc AS [Index Type], st.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) st INNER JOIN sys.indexes ind ON ind.object_id = st.object_id WHERE st.avg_fragmentation_in_percent > 30 ORDER BY st.avg_fragmentation_in_percent DESC
Hay una recomendación genérica de Microsoft de realizar un rebuild del índice si su fragmentación (avg_fragmentation_in_percent) es superior al 30%.
- Información desactualizada de las estadísticas de las tablas
Las estadísticas son un conjunto de conteos que se realizan sobre una tabla o índice para conocer el estado del mismo, tales como número de filas, bloques usados, nivel de profundidad de los índices, etc, y es usado por los motores de BBDD para establecer el mejor plan de ejecución posible cuando se accede a las tablas en una consulta. Las estadísticas, cuanto más actualizadas estén, mejor para que el motor de BBDD decida el mejor plan de ejecución de la consulta. Para consultar las estadísticas se puede usar la siguiente query:
SELECT tb.name AS [Table Name], st.name AS [Stat Name], STATS_DATE(tb.object_id,st.stats_id) AS [Last Updated] FROM sys.stats AS st INNER JOIN sys.tables AS tb ON st.object_id = tb.object_id WHERE tb.type = 'U'
Si es necesario ejecutar la recolección de las estadísticas por estar desactualizadas, se puede ejecutar:
USE <database>; GO UPDATE STATISTICS <database>.<table_name> <index_name>; GO
Esperamos que os haya servido de ayuda. Hasta la próxima….
Equipo de Base de datos
