Troubleshooting problemas de performance de SQL Server para Sharepoint–Parte 1

Voy a arrancar una serie de post relacionados al troubleshooting de problemas de performance de SQL Server para Sharepoint. Anteriormente había creado una serie de post sobre las mejores prácticas de SQL Server para Sharepoint:

Parte 1: Optimización de los servidores de SQL Server

Parte 2: Optimización de la instancia de SQL Server

Parte 3: Mantenimiento de SQL Server

En este caso, me voy a enfocar especificamente en los problemas de performance (lentitud de consultas, consumo alto de RAM o CPU, etc).

Hoy voy a revisar el parámetro “Page Life Expectancy” (PLE). El cual es el tiempo esperado, en segundos, que una página de un data file es sacada (pushed out) del buffer pool (in-memory cache de data files pages) para hacer espacio para otra página diferente de un data file. En resumen, indica la presión sobre el buffer pool para leer páginas de data files. Un número alto es mejor, ya que si no hay memoria suficiente en el sistema, se sacan más rapidamente las páginas del buffer pool.

Microsoft recomendaba que este contador supere los 300 ms (>). Si nuestro ambiente. nos da que PLE ronda los 300 ms, significa que el buffer pool completo se saca (flushed) y se leer de forma completa cada 5 minutos. Este valor fue cambiando con los sistemas actuales, que tienen muchos GB de RAM.

La fórmula correcta para evaluar este parámetro es: ( Buffer pool memory in GB / 4 ) x 300

Con la siguiente query, puedo saber el valor de este contador.

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Page life expectancy'

Recomiendo ejecutar este comando cada 6 horas, para tener una certeza más correcta de este comando. Ya que hay operaciones tales como DBCC CHECKDB o index rebuilds pueden afectar el valor de este contador.

En este caso, ejecuté el comando sobre un servidor con 16 GB de RAM (SQL Server 2012).

Para saber el tamaño del buffer pool memory, ejecuto el siguiente comando

select DB_NAME(database_id) Database_Name, count(*) Pages
from sys.dm_os_buffer_descriptors
group by database_id 
order by Pages desc

El cual nos devuelve todas las bases de datos que tienen páginas en el buffer

Un punto importante, para ver mediante esta consulta, es que nos da una idea de que bases de datos tienen más páginas en el buffer, con lo cual nos da un parámetro para saber que bases de Sharepoint son las que más actividad tienen. En la imagen superior, la base de contenido “WSS_Content_SPS_2” tiene casi 9 GB de datos cargados en la RAM (1146634*8/1024), lo cual es demasiado para una base. En este caso recomiendo crear otra base de contenidos y mover algunos sites collection a esa base, para bajar este número. Más adelante daré otros tips para subir el PLE lo cual puede mejorar la cantidad de páginas para esta base de contenido.

La suma de todas las páginas (de todas las bases) es : 1347406

(1347406*8)/1024 =~ 10526.61 –> 10.28 GB.

Agrego este valor a la fórmula anterior, y evaluo el resultado

(10.28GB /4) *300= 771 lo cual es un valor adecuado para el contador PLE.

Si este contador nos da demasiado bajo, podemos revisar lo siguiente:

• Índices no actualizados
• Estadísticas no actualizados
• Fragmentación de datafiles

La base de search en general tiene bastantes páginas en el buffer, ya que en general se obtienen datos random de las queries.

Una manera de revisar que queries son las que causan más lecturas, es ejecutando la siguiente query.

SELECT TOP 25 cp.usecounts AS [execution_count]
      ,qs.total_worker_time AS CPU
      ,qs.total_elapsed_time AS ELAPSED_TIME
      ,qs.total_logical_reads AS LOGICAL_READS
      ,qs.total_logical_writes AS LOGICAL_WRITES
      ,qs.total_physical_reads AS PHYSICAL_READS
      ,SUBSTRING(text,
                   CASE WHEN statement_start_offset = 0
                          OR statement_start_offset IS NULL 
                           THEN 1 
                           ELSE statement_start_offset/2 + 1 END,
                   CASE WHEN statement_end_offset = 0
                          OR statement_end_offset = -1 
                          OR statement_end_offset IS NULL 
                           THEN LEN(text) 
                           ELSE statement_end_offset/2 END -
                     CASE WHEN statement_start_offset = 0
                            OR statement_start_offset IS NULL
                             THEN 1 
                             ELSE statement_start_offset/2  END + 1
                  )  AS [Statement]       
FROM sys.dm_exec_query_stats qs 
   join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
ORDER BY qs.total_physical_reads DESC;

En este caso, veo que la query

SELECT          SUBSTRING(ADS.Content, @Offset, @Length)      FROM          TVF_AllDocStreams_SiteDocIdInternalVersion(@SiteId, @DocId, @InternalVersion) AS ADS     

AllDocStreams es la tabla donde se almacenan los documentos de Sharepoint.

Otra query para saber el tamaño en MB de las páginas que están en el buffer pool

SELECT count(*)*8/1024 AS 'Data Cache Size(MB)' ,CASE database_id WHEN 32767 THEN 'RESOURCEDB' ELSE db_name(database_id) END AS 'DatabaseName' FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY 'Data Cache Size(MB)' DESC

Hay mucha información sobre PLE en Internet, la idea es mostrarle un par de consultas y parámetros para ayudarlos en algún problema que tengan con su SQL de Sharepoint.