Mejores Prácticas SQL Server para Sharepoint 2010/2013 (Best Practices SQL Server for Sharepoint 2010/2013)–Parte 3

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 (acá estamos)

MANTENIMIENTO DE SQL SERVER

• Verificar que no haya bases de contenido con tamaño >=200 GB. Se recomienda crear una nueva base de contenido y mover sites collection a esa base (Move-SPSite).
• Verificar latencia de disco para las bases de datos: utilizar el siguiente ReadAndWriteLatency.

El script nos retornará la latencia de disco (Avg Read Transfer/ms, Avg Write Transfer/ms). Microsoft sugiere los siguientes thresholds:

Database data files:

· Target: <10ms

· Acceptable: 10-20ms

· Unacceptable: >20ms

Database log files:

· Target: <5ms

· Acceptable: 5-15ms

· Unacceptable: >15ms

• Ejecutar semanalmente DBCC CHECKDB. Puedes utilizar el script de Hallengren: http://ola.hallengren.com/sql-server-integrity-check.html  (recuerda NO utilizar checkdb con la opción de rebuild, en cualquiera de sus formatos)

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'

• Monitorear semanalmente por Wait Events. Puedes utilizar el siguiente script: WaitStats. Dependiendo de los eventos externos por los cuales SQL espera, se realizará una acción. Ej: agregar más CPU, distribuir los I/O, etc.  Más información

• Monitorear los growth de la tempDB. Deberían minimizarse al mínimo los growth de la tempDB. El siguiente script nos permite monitorear esos growth, y en el caso que haya muchos, deberíamos iniciar la tempDB con un tamaño mayor y tener tamaños más grande autogrowth: InitialSizeAndGrowth

• El siguiente script es similar al anterior, pero monitorea las bases de usuario. AutogrowthRecent

Si el script nos retorna que hubo >=7 growth para una base de usuario, significa que no está seteado correctamente el tamaño del autogrow.

Recuerda habilitar el trace en el caso que se haya deshabilitado.

select name, value_in_use

from sys.configurations

where name='default trace enabled'

Si value_in_use no es 1, se deberá habilitar.

sp_configure 'default trace enabled', 1

go

reconfigure with override

go

• Realizar desfragmentación de indices semalmente. Sharepoint tiene el store proc_UpdateStatistics, que realiza tareas de mantenimiento de indices. El problema de este store es que no se ejecuta para todas las bases de Sharepoint, sólo las de contendio. Se deberá ejecutar el siguiente script para evaluar la fragmentación de los índices:

Fragmentation Index.sql

Columna	Descripcion
avg_fragmentation_in_percent	El porcentaje de fragmentación lógica (páginas fuera de orden en el índice)
fragment_count	El número de fragmentos (páginas de hojas físicamente consecutivas)
avg_fragment_size_in_pages	Número promedio de páginas de un fragmento en un índice

Una vez verificado el estado de los índices, evaluar la siguiente tabla

avg_fragmentation_in_percent value	Sentencia correctiva
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30 %	ALTER INDEX REBUILD WITH (ONLINE= ON)

Para hacer un rebuild de los índices fragmentados, se deberá ejecutar el siguiente script (se utilizará el script de ola.hallengren.com):

1. Crear el procedimiento de desfragmentación de índices con el siguiente script

MaintenanceSolution.sql

2. Ejecutar el siguiente script

RebuildIndices.sql

El script anterior se puede crear un job para que se ejecute semanalmente (Sunday).

Ejecutar de nuevo el script FragmentationIndex.sql y ejecutar el siguiente script para verificar el estado de las estadísticas:

ReviewStatistics.sql

Si quieres utilizar el store de Sharepoint para desfragmentar los indices, puedes usar el siguiente script:

EXECUTE sp_msforeachdb
'USE [?];
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
     begin
          print ''updating statistics in database  ==> '' + db_name()
          if exists (select 1 from sys.objects where name = ''proc_updatestatistics'')
             begin
                  print ''updating statistics via proc_updatestatistics''
                  exec proc_updatestatistics
             end
         else
             begin
                  print ''updating statistics via sp_updatestats''
                  exec sp_updatestats
             end
    end'

Más información en el siguiente link, link2.

• NUNCA hacer shrinks de datafiles (hay casos especiales, tales como borrado de mucha información, o tamaño excesivo de la base de crawl: link). Se puede hacer shrink de los logs, pero es preferible setear a modo simple las bases y evitar tamaños grandes de logs. En vez de Shrink de logs, se podría crear una buena política de backups y evitar el shrink, ya que al hacer el backup de las bases automaticamente realiza un shrink de los logs.
• En el caso excesivo de consumo de CPU y RAM en el crawl del Search, se puede utilizar Resource Governor. Link
• Verifica semanalmente las best practices definidas en el punto 2 de esta serie de post (Ej: auto update statistics=false, autoshrink=false, fill factor=80, etc)
• Instalar SQL Best Practices Analyser, y verificar mensualmente las mejores práctices (se actualiza de forma continua): http://www.microsoft.com/en-us/download/details.aspx?id=29302 (recuerda, no siempre las mejores prácticas de SQL Server aplican para Sharepoint)
• Semanalmente desfragmentar los volumenes donde se encuentran los datafiles de SQL Server. Una excelente herramienta para evaluar la fragmentación de los datafiles es contig de SysInternals.contig -a "E:\Data\nombreBaseDatos.mdf". Recuerda de poner la base de datos a offline y ejecutar el proceso de desfragmentación, al finalizar ponla de nuevo en online a la base de datos.
• Evaluar la base de datos que más memoria consume

MostMemoryXDatabase.sql

• Monitorear los archivos lógicos (VLF).Lo indicado en tener ente 20 a 100 vlf`s.  Más información en el siguiente link

ListVLFCounts.sql

En el caso que quiera evaluar específicamente la tempdb, puede ejecutar el siguiente comando:

DBCC LOGINFO

Para reducir la cantidad de vlfs, puede consultar los siguientes links:

http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx

• Monitores útiles:

A continuación una lista de contadores a evaluar:

· Memory – Available MBytes

· Paging File – % Usage

· Physical Disk – Avg. Disk sec/Read

· Physical Disk – Avg. Disk sec/Write

· Physical Disk – Disk Reads/sec

· Physical Disk – Disk Writes/sec

· Processor – % Processor Time

· Processor – % Privileged Time

· Process: IO Read Bytes/sec

· Process: IO Read Bytes/sec

· Process (sqlservr.exe)

· SQLServer: Buffer Manager – Page life expectancy

· SQLServer: Buffer Manager – Lazy writes/sec

· SQLServer: General Statistics – User Connections

· SQLServer: Memory Manager – Memory Grants Pending

· SQLServer: SQL Statistics – Batch Requests/sec

· SQLServer: SQL Statistics – Compilations/sec

· SQLServer: SQL Statistics – Recompilations/sec

· System – Processor Queue Length

• Instalar SQL Server 2012 Performance Dashboard Reports

Se deberá instalar SQL Server 2012 Performance Dashboard Reports, el cual permite generar reportes de problemas de performance de forma rápida. Algunos reportes incluyen lo siguiente:

· CPU bottlenecks (and what queries are consuming the most CPU)

· IO bottlenecks (and what queries are performing the most IO)

· Index recommendations generated by the query optimizer (missing indexes)

· Blocking

· Latch contention

http://www.microsoft.com/en-us/download/details.aspx?id=29063