Todo Sharepoint: Troubleshooting problemas de performance de SQL Server para Sharepoint

Seguimos con la serie de post sobre la performance de SQL Server para Sharepoint

En este post estaré hablando sobre VLFs (Virtual Log Files). Los transaction logs de una base de datos están compuestos de uno o más archivos físicos. Por cada base de datos, SQL Server sólo escribe un transactional log físico a la vez. Internalmente los archivos físicos que SQL Server usa para los Transaction Log son estructurados conocidos cómo Virtual Log Files (VLFs). SQL Server no debe tener un número excesivo de Virtual Log Files (VLFs) denntro de los Transaction Log.

Algunos problemas de tener un número grande de VLFs:

Restore´s o startup´s muy lentos.
Insert, delete, updates muy lentos
Locking excesivos
Puede hacer lento el mirroring en ambiente de alta disponibilidad
Backups muy lentos

Se puede ver la cantidad de VLFs para una base, mediante el siguiente comando: DBCC LOGINFO

En general un número excesivo de small´s autogrow generan muchos VLFs.

Para Sharepoint se recomienda tener entre 50 a 100 VLFs por base de datos. Un número chico (ej: 10) NO es recomendado o un número grande (Ej: 1000) tammpoco es recomendado

Bajen el siguiente script para ver la cantidad de VLFs por base de datos.

https://onedrive.live.com/redir?resid=137CB2CC363CB937%211519

Cómo podemos reducir los VLFs? Ejecutando el siguiente script

USE <Nombre_BaseDatos> --Setear el nombre de la base antes de ejecutarlo

DECLARE @file_name sysname,
@file_size int,
@file_growth int,
@shrink_command nvarchar(max),
@alter_command nvarchar(max)

SELECT @file_name = name,
@file_size = (size / 128)
FROM sys.database_files
WHERE type_desc = 'log'

SELECT @shrink_command = 'DBCC SHRINKFILE (N''' + @file_name + ''' , 0, TRUNCATEONLY)'
PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @shrink_command = 'DBCC SHRINKFILE (N''' + @file_name + ''' , 0)'
PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @alter_command = 'ALTER DATABASE [' + db_name() + '] MODIFY FILE (NAME = N''' + @file_name + ''', SIZE = ' + CAST(@file_size AS nvarchar) + 'MB)'
PRINT @alter_command
EXEC sp_executesql @alter_command

Ej: cuando lo ejecuté sobre WSS_Content, después los VLFs quedaron así.

VLF lecciones aprendidas:

Evitar el autogrow automático en el caso que puedas. Pre-sizing tanto los datafiles como los log files
Setear autogrow razonables (en el caso que requieras autogrow). El tamaño depende obviamente de tu ambiente. Entre 1GB a 3GB suele ser algo común. Para base de datos con alto crecimiento deberías tener un capacity planning adecuado

Verificar mensualmente los VLFs y has un regrowing para reducir el número total de VLFs
Nunca hagas un shrink de los datos o log files de forma regular. Shrinking debe ser algo extremadamente raro en respuesta de algo específico.

Tema adicional: fragmentación de los datafiles.

Cómo podemos ver la fragmentación de los datafiles? Con el programa Contig de SysInternals.https://technet.microsoft.com/en-us/sysinternals/bb897428

Ej: veamos la fragmentación de la base WSS_Content. Dejamos el .exe en el mismo path del datafile

Abrimos una línea de comando con permisos de administrador y ejecutamos lo siguiente.

contig -a WSS_Content.mdf

Cómo pueden ver tiene 119 fragmentos, no son muchos (se recomienda desfragmentar cuando se tiene más de 500 fragmentos)

Para desfragmentar hago lo siguiente.

Pongo la base en modo offline

ALTER DATABASE WSS_Content SET OFFLINE
GO

ejecuto lo siguiente

contig WSS_Content.mdf

Después ejecuto

ALTER DATABASE WSS_Content SET ONLINE
GO

Claramente tendrás corte de servicio, por eso es muy importante configurar correctamente el growth de los datafiles al crear la base de datos. Si tarda mucho las bases en ponerse offline, lee el siguiente artículo. http://blog.degree.no/2013/03/long-wait-time-when-taking-sql-server-database-offline/

Referencias recomendadas:

https://www.youtube.com/watch?v=lcmYeE-cqQo

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/