sql server health check análisis para ax-sqlserver (002).pdfsolidq - solidq.comespaña y portugal...
TRANSCRIPT
SolidQ - España y Portugal
Rey Juan Carlos I, 88, 4C,
03340, Albatera,
Alicante, España
solidq.com
+34 800 300 800 oficina
+34 965 485 692 oficina (intl)
+34 902 946 458 fax
SQL Server Health Check
Análisis para AX-SQLServer
SolidQ
13/09/2016
solidq.com
2
Contenido
Nota: en amarrillo se resaltan secciones no cubiertas en HC-Lite
Introducción ............................................................................................................................................................................................................. 5
Objetivo del documento ...................................................................................................................................................................................... 5
Resumen Ejecutivo ................................................................................................................................................................................................... 5
Corto Plazo ........................................................................................................................................................................................................... 6
Medio Plazo ......................................................................................................................................................................................................... 6
Largo Plazo ........................................................................................................................................................................................................... 7
Información sobre el servidor analizado .................................................................................................................................................................. 8
Número de procesadores físicos: 4 ..................................................................................................................................................................... 8
Memoria .............................................................................................................................................................................................................. 9
Adaptadores de red ........................................................................................................................................................................................... 10
Recursos compartidos ....................................................................................................................................................................................... 11
Tipo de Servidor ................................................................................................................................................................................................. 11
Subsistema de disco y niveles RAID ................................................................................................................................................................... 12
Sistemas de almacenamiento ............................................................................................................................................................................ 12
Alineamiento de particiones .............................................................................................................................................................................. 13
Otros servicios corriendo en el servidor ............................................................................................................................................................ 15
Contadores de rendimiento ................................................................................................................................................................................... 16
Procesador ............................................................................................................................................................................................................. 16
% de Uso de Procesador .................................................................................................................................................................................... 16
solidq.com
3
Disco ....................................................................................................................................................................................................................... 17
Promedio de Segundos de Disco / Lectura - Escritura ....................................................................................................................................... 17
Memoria ................................................................................................................................................................................................................. 25
Page Life Expectancy.......................................................................................................................................................................................... 25
Tarjetas de Red ...................................................................................................................................................................................................... 27
% Uso de la tarjeta de Red ................................................................................................................................................................................. 27
Configuración de SQL Server: SQLAX-SERVER12 .................................................................................................................................................... 28
Versión de SQL Server ........................................................................................................................................................................................ 28
Configuración de la instancia de SQL Server ..................................................................................................................................................... 29
Configuración Avanzada .................................................................................................................................................................................... 32
Cuenta de servicio de SQL Server ...................................................................................................................................................................... 33
SQL Server Tempdb Information ....................................................................................................................................................................... 35
Database Mail .................................................................................................................................................................................................... 37
MSDB ................................................................................................................................................................................................................. 38
Collations ........................................................................................................................................................................................................... 39
Información sobre el sistema operativo ................................................................................................................................................................ 43
Ficheros de base de datos: Espacio utilizado ......................................................................................................................................................... 43
Espacio por objetos de base de datos.................................................................................................................................................................... 45
Información de Backups .................................................................................................................................................................................... 47
Análisis de eficiencia de los Backups diferenciales ............................................................................................................................................ 48
Caché de procesos ................................................................................................................................................................................................. 49
SQL Server 2000 ................................................................................................................................................................................................. 49
solidq.com
4
Desde SQL Server 2005 ...................................................................................................................................................................................... 51
Análisis del uso de la caché de procesos ........................................................................................................................................................... 52
Databases Configuration ........................................................................................................................................................................................ 57
Estadísticas ........................................................................................................................................................................................................ 57
Modo de Bloqueo de BBDD de las bases de datos de AX .................................................................................................................................. 57
Sección de visor de eventos ................................................................................................................................................................................... 58
Tiempos de espera (WAITSTATS) ........................................................................................................................................................................... 60
Patrones de consulta en la instancia SQLAX-SERVER12......................................................................................................................................... 64
Patrones más costosos por CPU ........................................................................................................................................................................ 64
Patrones más costosos por Número de Lecturas .............................................................................................................................................. 64
Patrones más costosos por Número de Escrituras ............................................................................................................................................ 65
Patrones más costosos por Duración ................................................................................................................................................................ 65
Patrones más costosos por Número de Ejecuciones ......................................................................................................................................... 65
solidq.com
5
Introducción
Objetivo del documento
El objetivo de este documento es la identificación de cualquier práctica o configuración que debería ser modificada para mejorar la
escalabilidad, seguridad y rendimiento del servicio de SQL Server. Basándonos en los datos recolectados de vuestros sistemas SQL Server
detallaremos los aspectos que se pueden mejorar y además nuestras recomendaciones para mejorar los mismos. Estas recomendaciones
de Buenas prácticas se realizan basándonos en nuestra amplia experiencia trabajando con SQL Server.
Resumen Ejecutivo
NOTA: En amarillo se resalta información no incluida en HC-Lite
A la hora de valorar el hardware en su conjunto tenemos que valorar todos los elementos analizados CPU, disco, memoria y tarjetas de red:
El estado del procesador es correcto
El estado de los discos en es: o El disco G (Datos) estado crítico o El disco H (Logs) estado warning
El estado de la memoria es correcto
El estado de las tarjetas de red es correcto
Se debe cambiar el tipo de discos ya que los discos normales de Azure solo tienen 500 IOPS. Estos discos deben ser sustituidos por discos
de almacenamiento premium puesto que es lo que aconseja Microsoft cuando se usa SQL Server sobre máquinas en Azure. Ver punto
Subsistema de disco y niveles RAID.
Por otro lado y aunque el disco I no presenta problemas recomendamos poner sus ficheros en el disco D: temporal de la máquina de Azure
que es ssd y tiene muchísimo mejor rendimiento que un disco normal.
solidq.com
6
Corto Plazo
Actualizar la versión de SQL Server 2012 con el SP3 si las aplicaciones AX y SharePoint instaladas son compatibles. Esto solucionaría los problemas de errores que se disparan constantemente en el log de errores de SQL Server
Crear un job o utilizar Resource Governor para cambiar el grado de paralelismo para aumentar la rapidez de las tareas de mantenimiento de las bbdd
Cambiar las cuentas de servicio de SQL Server. Cada servicio debe tener una cuenta distinta con mínimos permisos. La actual es administrador de SQL Server. Esta tarea requiere parada del servicio
Hay un número excesivo de ficheros de datos en la base datos Tempdb. Se deben dejar 4 y eliminar 3. Por nuestra experiencia si el sistema usa mucho la tempdb es posible que SQL Server no deje borrarlos en caliente, por lo que es posible que hubiera que hacer parada del servicio
Se recomienda usar una cuenta de Database Mail en lugar de SQL Mail para las notificaciones por correo
Recomendamos crear un plan de mantenimiento que borre los registros históricos antiguos de las ejecuciones de jobs. Actualmente hay 3 años de información, consideramos que con un año es suficiente si no se está utilizando esa información para ningún análisis
Recomendamos crear un plan de mantenimiento que recalcule las estadísticas de las bases de datos
Activar los trace flags 1117 y 1118. Este cambio requiere parada del servicio
Activar en la instancia la opción “Optimize for Ad Hoc Workloads” y en la base de datos “DAX_PROD” la parametrización forzada para disminuir el número de planes ad hoc y optimizar el uso de la memoria
Cambiar el modo de bloqueo de la bbdd DAX_PROD a Read Committed Snapshot tal y como recomienda Microsoft
Medio Plazo
Se recomienda incorporar backups diferenciales para aquellas bases de datos con más uso
Hacer upgrade de la máquina pasando de tipo A5 a al menos tipo DS3 según indican las recomendaciones de MS para SQL Server en Azure
Utilizar almacenamiento premium según indican las recomendaciones de MS para SQL Server en Azure
Utilizar el disco D: de la máquina de azure como disco para la tempdb
Revisar los patrones con más consumo de recursos y optimizarlos
solidq.com
7
Largo Plazo
No se ha detectado ninguna estrategia de Disaster Recovery. Recomendamos incorporar algún sistema de alta disponibilidad para evitar en caso de caída tener que recuperar todas las bases de datos mediante restores de backups. Los más comunes que recomendamos son:
o Grupos de Disponibilidad Always On. Failover automático y permite la reutilización de los nodos secundarios para consultas o Cluster de SQL Server. Failover automático o Log Shipping. Failover manual
solidq.com
8
Información sobre el servidor analizado
El servidor analizado funciona mediante el sistema operativo Microsoft Windows Server 2012 Datacenter versión 6.2.9200 (9200).
Dispone de 28671 Mb de RAM físicos, de los cuales en el momento de la captura de datos, se encontraban disponibles 8375 Mb.
Número de procesadores físicos: 4
Nº de procesador: 1
Velocidad de procesador (Mhz): 2.095
Tamaño de caché L2: 0
El modo de energía del servidor está establecido en “High Performance” por lo que en caso de que necesitara saltar el turbo podría hacerlo.
solidq.com
9
Memoria
Número de bancos de memoria instaladas: 2
Etiqueta del banco de memoria: Physical Memory 0
Velocidad bus(MHZ): 0
Tamaño (Mb): 512
solidq.com
10
Etiqueta del banco de memoria: Physical Memory 1
Velocidad bus(MHZ): 0
Tamaño (Mb): 28160
Adaptadores de red
Descripción Alias MAC Velocidad de conexión actual (Mb/s)
WAN Miniport (L2TP) -1
WAN Miniport (SSTP) -1
WAN Miniport (IKEv2) -1
WAN Miniport (PPTP) -1
WAN Miniport (PPPOE) -1
WAN Miniport (IP) -1
WAN Miniport (IPv6) -1
WAN Miniport (Network Monitor) -1
Microsoft Kernel Debug Network Adapter -1
RAS Async Adapter -1
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SystemInfo__20160913-0131" En la hoja "Network
Adapters".
solidq.com
11
Recursos compartidos
Recurso Ruta compartida Descripción
ADMIN$ C:\Windows Remote Admin
AXFiles G:\AXFiles
C$ C:\ Default share
D$ D:\ Default share
G$ G:\ Default share
H$ H:\ Default share
I$ I:\ Default share
IPC$ Remote IPC
J$ J:\ Default share
K$ K:\ Default share
Tipo de Servidor
Este servidor está en Azure siendo el tipo de máquina una A5. Microsoft recomienda para máquinas con SQL Server Enterprise en Azure al
menos máquinas de tipo DS3.
Referencia: https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/
solidq.com
12
Subsistema de disco y niveles RAID
Los discos que presenta son discos Azure normales de 500 IOPS sin ninguna configuración raid. Según la información que nos proporciona
Microsoft para máquinas con SQL Server en Azure se requiere:
Almacenamiento Premium al menos 2 discos P30 deshabilitando la georedundancia
Activar la cache de lectura en estos discos
Referencia: https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/
Sistemas de almacenamiento
Esta sección establece que la organización de los mecanismos de almacenamiento y de distribución de las estructuras persistentes cumple
con las buenas prácticas.
La tabla siguiente muestra los volúmenes actuales del servidor y los tamaños de bloque del clúster.
Unidad Sistema de ficheros Tamaño de bloque Tipo
C: NTFS 4.096 Disco lógico
D: NTFS 4.096 Disco lógico
K: NTFS 65.536 Disco lógico
G: NTFS 65.536 Disco lógico
H: NTFS 65.536 Disco lógico
I: NTFS 65.536 Disco lógico
J: NTFS 4.096 Disco lógico
solidq.com
13
Recomendación: Para mejorar el rendimiento de las operaciones de Entrada/Salida se deben formatear las nuevas particiones a 64Kb como el tamaño de la unidad de alojamiento. Como se muestra en la imagen siguiente:
Alineamiento de particiones
Esta sección establece si las divisiones verticales de las estructuras de datos son adecuadas para conseguir los requerimientos de
rendimiento y de carga. Necesitamos asegurarnos de que el desplazamiento del primer bloque de cada partición es un múltiplo de 64k:
solidq.com
14
Nota: Este diagrama se corresponde con el modelo de almacenamiento NTFS por defecto, con formateo de 4kb por bloque. El diagrama no está escalado.
Si asumimos un tamaño de división de 64Kb, hay 8-512B sectores por cada clúster de 4KB de NTFS; hay 16-4Kb clústeres NTFS por cada unidad de 64Kb
Por definición, existen 63 sectores ocultos desalineados (amarillo)
Si nos fijamos en el punto 3 de la imagen (Default layout), al realizar la lectura del enésimo clúster, tendremos que realizar dos lecturas E/S reales puesto que dicho clúster se encuentra en dos bloques diferentes
En el caso 4 de la imagen (Aligned layout), siempre necesitaremos una única lectura únicamente, leamos el clúster que leamos
La siguiente tabla muestra el alineamiento de las particiones de los discos del servidor:
Particion Tamaño de bloque Desplazamiento inicial ¿Está alineado?
Disk #3, Partition #0 512 1.048.576 Cierto
Disk #1, Partition #0 512 1.048.576 Cierto
Disk #5, Partition #0 512 1.048.576 Cierto
solidq.com
15
Disk #0, Partition #0 512 1.048.576 Cierto
Disk #2, Partition #0 512 1.048.576 Cierto
Disk #4, Partition #0 512 1.048.576 Cierto
Disk #6, Partition #0 512 1.048.576 Cierto
Otros servicios corriendo en el servidor
Además del servicio de SQL Server hay más servicios ejecutándose en el servidor y utilizando recursos que pueden reducir el rendimiento
de SQL Server:
AeLookupSvc; Appinfo; BFE; BrokerInfrastructure; CertPropSvc; CryptSvc; DcomLaunch; Dhcp; DIAHostService; Dnscache; DPS; EFS;
EventLog; EventSystem; FontCache; gpsvc; IKEEXT; iphlpsvc; KeyIso; LanmanServer; LanmanWorkstation; lmhosts; LSM; MpsSvc;
Netlogon; Netman; netprofm; NlaSvc; nsi; pla; PlugPlay; PolicyAgent; Power; ProfSvc; RemoteRegistry; RpcEptMapper; RpcSs; SamSs;
Schedule; SENS; SessionEnv; ShellHWDetection; Spooler; sppsvc; SQLSERVERAGENT; TermService; Themes; TrkWks; TrustedInstaller;
UALSVC; UmRdpService; vmicheartbeat; vmickvpexchange; vmicrdv; vmicshutdown; vmictimesync; vmicvss; W32Time; Winmgmt;
WinRM; wmiApSr
solidq.com
16
Contadores de rendimiento
Procesador
% de Uso de Procesador
Este contador nos muestra el grado de uso de la CPU en un periodo de tiempo. Para considerar nivel warning tendría que mantenerse en
un 50% durante la muestra. Para considerar nivel critical tendría que mantenerse en un 80% durante la muestra.
La media en este periodo ha sido del 9.5%. Como se puede apreciar se producen picos periódicos. La siguiente imagen es un zoom de esos
picos.
solidq.com
17
Los picos se producen cada hora en punto y se deben a los backups del log de transacciones, durando una media de 4 minutos. Como se
puede apreciar no salvo cuando se producen los backups este contador no llega al nivel warning (50%) por lo que este contador se puede
considerar como correcto.
Disco
Promedio de Segundos de Disco / Lectura - Escritura
Estos contadores marcan el tiempo medio que se tarda en realizar una lectura en 1 segundo determinado. Si el tiempo medio es igual o
superior a 15 milisegundos se considera nivel warning (línea naranja del gráfico), y si es superior a 25 nivel crítico (línea roja del gráfico).
solidq.com
18
Los discos que nos interesa medir son los que contienen ficheros de log o de datos de SQL Server que son la unidad G: (ficheros de datos),
H: (ficheros de log) e I: (ficheros de tempdb, datos + log).
Lectura Disco G (Datos)
Este disco supera ampliamente el nivel crítico de 25 milisegundos por lo que lo consideramos que su estado es crítico. Su media es de 48
milisegundos.
solidq.com
19
Lectura Disco H (Log)
Este disco presenta una media de 16 milisegundos (nivel warning).
solidq.com
20
Haciendo un zoom para ver en detalle esa área de picos vemos que los picos no son tan frecuentes como parecen, se puede decir que pasan
entre uno y otro entre media o una hora.
En base a estos datos consideramos que su estado es correcto, no obstante hay que seguir observándolo periódicamente no sea que los
picos vayan a volverse más frecuentes y/o formen mesetas, lo cual impactaría críticamente en el rendimiento del sistema.
solidq.com
21
Lectura Disco I (Tempdb)
Este disco presenta una media de 16 milisegundos (nivel warning).
solidq.com
22
Como en el caso anterior haciendo un zoom para ver en detalle esa área de picos. Vemos que los picos no son tan frecuentes como parecen,
aunque no hay un patrón temporal concreto.
En base a estos datos consideramos que su estado es correcto, no obstante, hay que seguir observándolo periódicamente no sea que los
picos vayan a volverse más frecuentes y/o formen mesetas, lo cual impactaría críticamente en el rendimiento del sistema.
solidq.com
23
Escritura Disco G (Datos), H (Log), I (Tempdb)
En este caso estos tres discos en o superan el nivel warning por poco (I azul claro) o lo están rozando, disco (H (verde claro) y G (magenta)).
Puesto que estos márgenes son muy cercanos al nivel warning para despejar dudas vamos a compararlos con los datos que nos ofrecer
SQL Server para tener una visión más completa que nos aclare la situación usando los mismos colores por disco.
solidq.com
24
En base a estos datos confirmamos que el disco I (tempdb) se encuentra en nivel warning ya que en la mayoría de sus intervalos supera
este nivel. El disco H (Logs) tan solo los supera en 3 llegando a niveles críticos y siendo 2 de ellos en periodo nocturno por lo que
consideramos que el disco está en un estado correcto, aunque hay que monitorizados para que no nos pille por sorpresa si su
comportamiento cambia. Por último, el disco G (Datos) en un tercio de las horas supera el nivel warning, pero dado que en las operaciones
de lectura está en nivel crítico consideramos que su estado es crítico.
Estos datos de SQL Server se pueden encontrar la tabla completa con los datos en el libro de Excel " SQL-AX_FileIOStat__20160915-0130 "
En la hoja " ESCRITURAS_POR_DISCO_HORAS".
solidq.com
25
La conclusión a la que llegamos es:
El disco G (Datos) estado crítico
El disco H (Logs) estado warning
Lo que debe de hacerse es cambiar el tipo de discos ya que los discos normales de Azure solo tienen 500 IOPS. Estos discos deben ser
sustituidos por discos de almacenamiento premium puesto que es lo que aconseja Microsoft cuando se usa SQL Server sobre máquinas en
Azure. Ver punto Subsistema de disco y niveles RAID.
Por otro lado y aunque el disco I no presenta problemas recomendamos poner sus ficheros en el disco D: temporal de la máquina de Azure
que es ssd y tiene muchísimo mejor rendimiento que un disco normal.
Memoria
Page Life Expectancy
Es el tiempo que debe durar una página en memoria. Microsoft da un criterio para esto:
Una página debe durar en memoria al menos 300 segundos (5 minutos) para 4 Gb de memoria, como el servidor tiene 7GB para el nivel
crítico el valor será de 525 segundos (línea roja) y 1050 segundos línea naranja para el nivel warning.
solidq.com
26
Puesto que el PLE (línea verde) no está llegando al nivel warning consideramos este contador como correcto.
solidq.com
27
Tarjetas de Red
% Uso de la tarjeta de Red
Este gráfico muestra el porcentaje de uso de las tarjetas de red. Consiste en la relación entre el ancho de banda nominal y los bytes leídos
o escritos. Para considerar nivel warning tendría que mantenerse en un 50% durante la muestra. Para considerar nivel critical tendría que
mantenerse en un 80% durante la muestra. Como se puede ver el grado de uso es muy bajo (no llega al 1%) por lo que este indicador se
considera correcto.
Estos datos de SQL Server se pueden encontrar la tabla completa con los datos en el libro de Excel " SQL-AX_NetworkInterface" En la hoja
"Resumen".
solidq.com
28
Configuración de SQL Server: SQLAX-SERVER12
Versión de SQL Server
La instancia que estamos analizando es una 2012 Enterprise SP1 y actualmente existe hasta un SP3. Según hemos visto en la instancia está
instalada en la instancia AX y Sharepoint. Aconsejamos revisar con los proveedores de dichos productos si las versiones de estos programas
son incompatibles con el SP3. Si no son incompatibles recomendamos instalarlo en un entorno de prueba y tras realizar los tests que se
consideren convenientes para cada producto si las pruebas son correctas implantarlo en producción.
Referencia: http://sqlserverbuilds.blogspot.com.es/
solidq.com
29
Configuración de la instancia de SQL Server
La siguiente tabla muestra algunas de las propiedades más interesantes de sp_configure:
Propiedad Valor de ejecución
backup compression default 1
clr enabled 0
default trace enabled 1
filestream access level 0
fill factor (%) 0
max degree of parallelism 1
max server memory (MB) 7.168
max worker threads 0
solidq.com
30
min memory per query (KB) 1.024
min server memory (MB) 4.096
optimize for ad hoc workloads 0
remote access 1
remote admin connections 0
xp_cmdshell 0
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "SpConfigure".
Los parámetros más importantes:
Backup compression default, esta establecidos con valor 1, esto quiere decir que por defecto los backups los realizará comprimiendo
Max server memory (MB), la instancia tiene configurado un máximo de 7.168 MB de memoria máxima por lo que eso garantiza que no podrá coger la cantidad total de memoria del servidor 28 GB y por tanto competir con el sistema operativo o con otros servicios. Importante, el que este configurada esta cantidad de 7 GB no quiere decir que sea suficiente. Para conocer esto se necesitaría un HC Normal.
Max degree of paralelism, el valor actual es 1, este valor es correcto ya que el producto AX indica en sus especificaciones que debe ser así:
Esta especificación cambia el valor cuando se tiene que actualizar debido a algún upgrade, momento en el cual se usa esta otra regla:
solidq.com
31
Importante, el configurar el grado de paralelismo con valor 1 hace que cualquier tarea de la instancia no pueda usar paralelismo (varios cores al mismo tiempo). Esto puede afectar a la duración de las tareas de mantenimiento de las bases de datos como (backups, checkdbs, defragmentación, recálculo de estadísticas etc). El grado de paralelismo para estas tareas que no son de AX sigue el siguiente criterio:
a) For servers that have eight or less processors, use the following configuration where N equals the number of processors: max
degree of parallelism = 0 to N.
b) For servers that use more than eight processors, use the following configuration: max degree of parallelism = 8.
c) For servers that have NUMA configured, max degree of parallelism should not exceed the number of CPUs that are assigned to
each NUMA node with the max value capped to 8. This will increase the likelyhood of all parallel threads of a query to be located
within a NUMA Node and avoid costly remote node data look ups.
d) For servers that have hyper-threading enabled, the max degree of parallelism value should not exceed the number of physical
processors.
Use these same guidelines when setting the MAXDOP option for Resource Governor workload groups. The maximum value of 8
provided in the above guideline is applicable for typical SQL Server activity and the overhead for the exchange operators used in
parallel query plans. You could vary this maximum value depending upon your specific application patterns and concurrent activity
on the SQL Server. For example:
- If you have very small number of concurrently executing queries relative to the number of processors, then you can set maxdop
to a higher value like 16.
- If you have very large number of concurrently executing queries relative to the number of processors, then you can set maxdop
to a lower value such as 4.
Any value you propose to use should be thoroughly tested against the specific application activity or pattern of queries.
solidq.com
32
For more info: http://support.microsoft.com/kb/2023536
Lo que se propone para solventar este problema y cumplir con las disposiciones de AX sugerimos: o Crear un job que cambie el grado de paralelismo a valor 4 (es el que le corresponde según la regla anterior) cuando comiencen
estas tareas de mantenimiento (Tarde/Noche). Cuando finalicen volverlo a poner en 1 que es lo que solicita AX (8 de la mañana por ejemplo)
o Puesto que tenéis versión Enterprise se puede usar Resource Governor. Esta utilidad clasifica las conexiones y permite garantizar una cantidad de recursos (memoria, cpu, grado de paralelismo, iops de disco). Creando un grupo específico para AX y otro para las tareas de mantenimiento se solucionaría
Configuración Avanzada
Trace Flags
Se recomienda activar los traces flags 1118 y 1117 en el servicio relacional de SQL Server para mejorar el desempeño de la tempdb. Esta
operación requiere parada del servicio.
TF 1118. Esta marca de seguimiento asigna las ocho páginas desde la misma extensión al crear objetos nuevos, con lo que se minimiza la necesidad de realizar un examen de la página SGAM lo cual evita contención en la tempdb si se crean muchos objetos concurrentemente
Referencia: https://msdn.microsoft.com/en-us/library/ms188396.aspx
TF 1117. Este trace flag permite que los archivos de datos mantengan siempre el mismo tamaño en el caso de que necesiten autocrecimientos.
Referencia: https://msdn.microsoft.com/en-us/library/ms188396.aspx
solidq.com
33
Cuenta de servicio de SQL Server
El servicio de SQL server que estamos analizando arranca bajo el usuario SQLSERVER\admACT:
Como se puede ver en la siguiente imagen este usuario del dominio es administrador en SQL Server.
solidq.com
34
Microsoft recomienda ejecutar los servicios de SQL Server por separado en distintas cuentas de Windows. Siempre que sea posible, utilice
derechos de Windows independientes y bajos, o cuentas de usuario local para cada servicio de SQL Server.
Hay que crear una cuenta distinta para cada servicio y con los mínimos privilegios posibles
El aislamiento de servicios reduce el riesgo de que se utilice un servicio cuya seguridad se haya vulnerado para vulnerar la seguridad de
otros servicios. Si se está aplicando este usuario debido a que hay Jobs que usan la cuenta de servicio recomendamos usar un usuario proxy.
IMPORTANTE: esta operación requiere parada del servicio.
Referencia: https://msdn.microsoft.com/es-es/library/ms143504.aspx
SQL server escucha en el Puerto TCPIP
SQL Server se encuentra escuchando en el Puerto tcpip por defecto (1433). Valorar si no es requisito de ninguna aplicación que tenga
abierto este puerto concretamente, y cambiarlo por otro puerto.
Recomendación: Es recomendable no configurar para que se escuche el Puerto por defecto si el servidor está expuesto a internet. Los
ataques típicamente van dirigidos contra los puertos por defecto.
solidq.com
35
SQL Server Tempdb Information
SQL Server debe alinear el nº de ficheros de tempdb con el nº de procesadores lógicos de que se dispone para mejorar la eficiencia en
operaciones de reserva y liberación de extensiones de objetos temporales. Por nuestra experiencia recomendamos que el nº de ficheros de
tempdb no sobrepasen el valor de 8 como norma general.
El criterio general es el siguiente:
1. Número de ficheros. El número de ficheros de datos se fija de acuerdo al siguiente criterio y aplica a los ficheros de datos no de logs:
o Si tienes menos de 8 cores, el número de ficheros debe ser igual al número de cores. o Si tienes más de 8 cores usa 8 ficheros y si notas contención de memoria añade 4 ficheros más o En casos extremos o emergencias número de cores = número de ficheros
Referencia: Summit SQL Server 2011, ponencia de Bob Ward (Principal Architect Escalation Engineer, Microsoft) http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1869
o Considerar el número de CPUs sin hyperthreading ej: Un Dual core cuenta como 2 cores; si tuviera hyperthreading los 2 procesadores lógicos no contarían
solidq.com
36
Puesto que el número de cores es 4 y el total de ficheros es 7, la configuración actual indica que es incorrecta.
En base al criterio anterior el número de fichero correcto sería 4.
solidq.com
37
Database Mail
Se ha detectado que se encuentra habilitado SQL Mail. Se recomienda substituir por Database Mail, ya que este último no requiere la
instalación del cliente MAPI extendido en el servidor.
Referencia: http://msdn.microsoft.com/en-us/library/ms175951.aspx
Database Mail es la solución para el envío de mensajes de correo electrónico del motor de base de datos de SQL Server, utiliza el estándar SMTP para enviar correo, se puede usar sin necesidad de tener instalado un cliente MAPI, para más información:
Referencia: http://msdn.microsoft.com/en-us/library/ms175887.aspx
Perfil Cuenta
SolidQ-ITSupport ITSupport
solidq.com
38
MSDB
Histórico de Jobs
Histórico de días de jobs
853
Como se puede ver se están acumulando casi 3 años de histórico de Jobs, aconsejamos poner un job que deje tan solo la información relativa
al último año.
Páginas sospechosas
No se ha encontrado ninguna página sospechosa de estar corruptas en los procesos de verificación de base de datos.
solidq.com
39
Collations
En SQL Server cada instancia, Base de datos, tabla y campo tiene su propia propiedad collation. La propiedad de collation define como la
base de datos almacena y opera con caracteres y datos Unicode. En esta sección compararemos las collations de 2 maneras distintas. La
primera será comparando el collation de la instancia de SQL con el de las diferentes bases de datos, mientras que el segundo comparará el
collation de cada campo de una base de datos con el collation de la misma base de datos que lo contiene. Para poder comparar y ordenar
caracteres y datos Unicode más eficientemente debemos establecer la misma collation para todo dentro de una misma instancia.
Ahora vamos a ver un ejemplo para demostrar cuanto puede variar un resultado por las collations. Declaramos
2 tablas con un único campo y “case insensitive”:
declare @t1 as table(field1 nvarchar(10) collate Modern_Spanish_CI_AS)
declare @t2 as table(field1 nvarchar(10) collate Modern_Spanish_CI_AS)
Insertamos el valor ‘a’ dentro de la primera table y los valores ‘a’ y ‘A’ en la segunda.
insert into @t1 values('a')
insert into @t2 values('a'),('A')
Si ejecutamos la siguiente consulta
select * from @t1 t1 inner join @t2 t2 on t1.field1 = t2.field1
El resultado es el siguiente:
solidq.com
40
Cuando comparamos (on t1.field1 = t2.field1) en la consulta anterior, la comparacion es “Case insensitive”
lo que significa que no importan mayusculas ni minusculas, por eso ‘a’ y ‘A’ casan con la comparación. Sin
embargo, si forzamos a la consulta a comparar cambiando la collation a “Case Sensitive” ‘a’ y ‘A’ no casaran.
Vamos a ver el ejemplo forzando a cambiar de collation y el resultado obtenido.
select * from @t1 t1 inner join @t2 t2 on t1.field1 collate Modern_Spanish_CS_AS= t2.field1
La instancia de SQL Server tiene la siguiente collation: SQL_Latin1_General_CP1_CI_AS
Tempdb tiene la collation: SQL_Latin1_General_CP1_CI_AS
A continuación, se muestra una tabla con las bases de datos que tienen un collation diferente a la instancia de SQL Server:
Nombre de tabla Nombre del Collation
ReportServer Latin1_General_CI_AS_KS_WS
ReportServerTempDB Latin1_General_CI_AS_KS_WS
SharePoint_Config Latin1_General_CI_AS_KS_WS
SharePoint_AdminContent_dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
WSS_Content Latin1_General_CI_AS_KS_WS
Secure_Store_Service_DB_ dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
StateService_ dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
AppMng_Service_DB_ dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
WSS_Logging Latin1_General_CI_AS_KS_WS
solidq.com
41
Bdc_Service_DB_ dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
Puesto que estas bases de datos son de SharePoint y este producto utiliza el collation específico Latin1_General_CI_AS_KS_WS podemos
afirmar que se trata de falsos positivos.
Comparación de collations entre los distintos campos en cada tabla de una misma BBDD:
SharePoint_AdminContent_ dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
NameValuePair_Hebrew_CI_AS Value Hebrew_CI_AS
NameValuePair_Hindi_CI_AS Value Hindi_CI_AS
NameValuePair_Hungarian_CI_AS Value Hungarian_CI_AS
NameValuePair_Hungarian_Technical_CI_AS Value Hungarian_Technical_CI_AS
NameValuePair_Icelandic_CI_AS Value Icelandic_CI_AS
NameValuePair_Japanese_CI_AS Value Japanese_CI_AS
NameValuePair_Japanese_Unicode_CI_AS Value Japanese_Unicode_CI_AS
NameValuePair_Korean_Wansung_CI_AS Value Korean_Wansung_CI_AS
NameValuePair_Korean_Wansung_Unicode_CI_AS Value Korean_Wansung_Unicode_CI_AS
NameValuePair_Latin1_General_CI_AS Value Latin1_General_CI_AS
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Collations
(SharePoint_AdminContent_ dx31dfdd1121f10)".
WSS_Content Latin1_General_CI_AS_KS_WS
NameValuePair_Hebrew_CI_AS Value Hebrew_CI_AS
NameValuePair_Hindi_CI_AS Value Hindi_CI_AS
NameValuePair_Hungarian_CI_AS Value Hungarian_CI_AS
NameValuePair_Hungarian_Technical_CI_AS Value Hungarian_Technical_CI_AS
NameValuePair_Icelandic_CI_AS Value Icelandic_CI_AS
solidq.com
42
NameValuePair_Japanese_CI_AS Value Japanese_CI_AS
NameValuePair_Japanese_Unicode_CI_AS Value Japanese_Unicode_CI_AS
NameValuePair_Korean_Wansung_CI_AS Value Korean_Wansung_CI_AS
NameValuePair_Korean_Wansung_Unicode_CI_AS Value Korean_Wansung_Unicode_CI_AS
NameValuePair_Latin1_General_CI_AS Value Latin1_General_CI_AS
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Collations
(WSS_Content)".
Bdc_Service_DB_ dx31dfdd1121f10 Latin1_General_CI_AS_KS_WS
AR_MetadataObject Name SQL_Latin1_General_CP1_CS_AS
AR_MetadataObjectSecurity IdentityName SQL_Latin1_General_CP1_CS_AS
AR_MetadataObjectSecurity DisplayName SQL_Latin1_General_CP1_CS_AS
AR_MetadataObjectSecurity SettingId SQL_Latin1_General_CP1_CS_AS
AR_Property Name SQL_Latin1_General_CP1_CS_AS
AR_Property SettingId SQL_Latin1_General_CP1_CS_AS
AR_Class Namespace SQL_Latin1_General_CP1_CS_AS
AR_SystemData Name SQL_Latin1_General_CP1_CS_AS
AR_Method LobName SQL_Latin1_General_CP1_CS_AS
AR_FilterDescriptor FilterField SQL_Latin1_General_CP1_CS_AS
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Collations
(Bdc_Service_DB_ dx31dfdd1121f10)".
Estos ejemplos de collations de columnas distintos en dentro de una base de datos de SharePoint también son falsos positivos ya que es
algo habitual.
solidq.com
43
Información sobre el sistema operativo
Ficheros de base de datos: Espacio utilizado
La tabla siguiente muestra el espacio usado actualmente por cada fichero de las bases de datos.
Base de
datos
Fichero de
BBDD
Ruta a fichero Unidad Espacio
reservado
Espacio
usado
% espacio
usado
Desc
tipo
master master C:\Obfuscated
Path\master.mdf
C:\ 4 3,44 85,94 ROWS
master mastlog C:\Obfuscated
Path\mastlog.ldf
C:\ 1 0,64 64,32 LOG
tempdb tempdev I:\Obfuscated
Path\tempdb.mdf
I:\ 2.048 8,69 0,42 ROWS
tempdb templog I:\Obfuscated
Path\templog.ldf
I:\ 2.048 1.094,86 53,46 LOG
tempdb tempdev1 I:\Obfuscated
Path\tempdev1.ndf
I:\ 2.048 5,81 0,28 ROWS
tempdb tempdev2 I:\Obfuscated
Path\tempdev2.ndf
I:\ 2.048 6,13 0,3 ROWS
tempdb tempdev3 I:\Obfuscated
Path\tempdev3.ndf
I:\ 2.048 6,44 0,31 ROWS
tempdb tempdev5 I:\Obfuscated
Path\tempdev5.ndf
I:\ 2.048 5,69 0,28 ROWS
tempdb tempdev6 I:\Obfuscated
Path\tempdev6.ndf
I:\ 2.048 5,88 0,29 ROWS
solidq.com
44
tempdb tempdev7 I:\Obfuscated
Path\tempdev7.ndf
I:\ 2.048 6,25 0,31 ROWS
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Database Spaced
used Block".
solidq.com
45
Espacio por objetos de base de datos
La siguiente tabla muestra el espacio utilizado por los distintos objetos que posee cada base de datos:
Base de
datos
Esqu
ema
Nombre de
tabla
Desc
tipo
Tam
año
med
io
de
fila
Nu
m
de
fila
s
Reser
vado
(Kb)
Dat
os
(Kb
)
Da
tos
in
ro
w
(K
b)
Da
tos
sin
us
ar
(K
b)
Es
agru
pado
?
Está
partici
onado?
Está
compri
mido?
Fecha
de
creaci
ón
LOB
(Kb
)
Row
over
flow
(Kb)
[Report
Server]
dbo Catalog USER_
TABLE
304.
670
1.9
50
582.7
84
580.
184
1.1
68
1.3
20
1 0 0 21/12
/2013
579.
320
0
[Report
Server]
dbo Segment USER_
TABLE
7.71
9
13.
49
4
102.7
44
101.
728
29
6
46
4
1 0 0 21/12
/2013
31.4
80
0
[Report
Server]
dbo ExecutionLo
gStorage
USER_
TABLE
3.92
6
41
1
2.752 1.57
6
16 1.1
28
1 0 0 21/12
/2013
1.12
0
0
[Report
Server]
dbo ChunkSegme
ntMapping
USER_
TABLE
58 13.
49
4
2.264 768 1.2
40
20
8
1 0 0 21/12
/2013
0 0
[Report
Server]
dbo DataSource USER_
TABLE
246 2.0
62
800 496 16
0
96 1 0 0 21/12
/2013
0 0
[Report
Server]
dbo SegmentedC
hunk
USER_
TABLE
111 2.4
32
800 264 35
2
13
6
1 0 0 21/12
/2013
0 0
[Report
Server]
dbo SnapshotDat
a
USER_
TABLE
68 1.9
06
216 128 56 0 1 0 0 21/12
/2013
0 0
solidq.com
46
[Report
Server]
dbo SecData USER_
TABLE
19.1
14
3 80 56 8 0 1 0 0 21/12
/2013
48 0
[Report
Server]
dbo Keys USER_
TABLE
12.2
88
2 32 24 0 0 1 0 0 21/12
/2013
16 0
[Report
Server]
dbo DBUpgrade
History
USER_
TABLE
182 45 16 8 0 0 1 0 0 21/12
/2013
0 0
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Database Object
Space used block".
solidq.com
47
Información de Backups
Base de datos Tipo de copia de seguridad Ultima fecha de copia de seguridad Dias desde última copia de seguridad Ultimo fichero de copia de seguridad
AppMng_Service_DB_ dx31dfdd1121f10 Database 12/09/2016 1 K:\Obfuscated Path\ Backup\Full\AppMng_Service_backup_2016_09_12_220001_8545993.bak
Bdc_Service_DB_ dx31dfdd1121f10 Database 12/09/2016 1 K:\Obfuscated Path\Backup\Full\Bdc_Service_DB _backup_2016_09_12_220001_8702214.bak
DAX_PROD Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\AX_PROD_backup_2016_09_12_220001_7921002.bak
DAX_PROD_Baseline Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\DAX_PROD_Baseline_backup_2016_09_12_220001_8080372.bak
DAX_PROD_model Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\DAX_PROD_model_backup_2016_09_12_220001_8080372.bak
ReportServer Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\ReportServer_backup_2016_09_12_220001_7764724.bak
ReportServerTempDB Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\ReportServerTempDB\ReportServerTempDB_backup_2016_09_12_220001_7921002.bak
Secure_Store_Service_DB_ dx31dfdd1121f10 Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\Secure_Store_Service_DB_backup_2016_09_12_220001_8389755.bak
SharePoint_AdminContent_ dx31dfdd1121f10 Database 12/09/2016 1 K:\ Obfuscated Path \Backup\Full\SharePoint_AdminContent_backup_2016_09_12_220001_8233506.bak
AppMng_Service_DB_ dx31dfdd1121f10 Database 12/09/2016 1 K:\Obfuscated Path\ Backup\Full\AppMng_Service_backup_2016_09_12_220001_8545993.bak
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Información de
backups".
Al dia con backups
AppMng_Service_DB_ dx31dfdd1121f10
AX_SolidQ_View
solidq.com
48
Bdc_Service_DB_ dx31dfdd1121f10
DAX_PROD
DAX_PROD_Baseline
DAX_PROD_model
ReportServer
ReportServerTempDB
Secure_Store_Service_DB_ dx31dfdd1121f10
SharePoint_AdminContent_ dx31dfdd1121f10
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Actualizado con
los Backups".
Análisis de eficiencia de los Backups diferenciales
Los backups diferenciales ayudan a las estrategias desde 2 ángulos distintos:
En algunos escenarios se requieren menos transacciones de log para restaurar: Restaurando una base de datos, aplicando restauración
por backup diferencial, se evita la necesidad de restaurar el log de transacciones durante el proceso.
Los Backups diferenciales reducen el tiempo necesario de realizar backups: A veces no vale la pena ejecutar un backup completo porque
no hay tanta información cambiante desde el último backup que se realizó y es más eficiente ejecutar un backup diferencial.
En esta sección recomendaremos que tipo de backup es más apropiado para cada base de datos. La siguiente tabla mostrara el porcentaje
de extents que han cambiado de cada base de datos y en consecuencia el tipo de backup que sea más conveniente. Toda la información
acerca de los extents cambiantes ha sido obtenida de las páginas de SQL Server. Existen diferentes tipos de páginas, y una de ellas es la
DCM o Differential changed map. Esta página contiene información acerca de los extents que han cambiado desde el último BACKUP
DATABASE ejecutado. Con toda esta información somos capaces de calcular los porcentajes y decidir que sería mejor hacer en cada caso.
solidq.com
49
Database Extensiones
(total)
Extensiones(cambios) %
cambios
Backup
recomendado
AppMng_Service_DB_dx31dfdd1121f10 65 16 24.62 DIFFERENTIAL
AX_SolidQ_View 64 12 18.75 DIFFERENTIAL
Bdc_Service_DB_ dx31dfdd1121f10 65 28 43.08 FULL
DAX_PROD 105649 26500 25.08 DIFFERENTIAL
DAX_PROD_Baseline 16384 36 0.22 DIFFERENTIAL
DAX_PROD_model 122571 30688 25.04 DIFFERENTIAL
ReportServer 12049 1294 10.74 DIFFERENTIAL
ReportServerTempDB 673 37 5.50 DIFFERENTIAL
Secure_Store_Service_DB_ dx31dfdd1121f10 65 22 33.85 FULL
SharePoint_AdminContent_
dx31dfdd1121f10
6785 3383 49.86 FULL
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_SqlServer__20160913-0131" En la hoja "Change Data".
El gráfico se puede encontrar en: SQL-AX_SqlServer__20160913-0131 , en la hoja Change Data
Caché de procesos
Si bien el concepto de caché de procesos es común en todas las versiones de SQL Server su implementación no ya que los datos almacenados
en las versiones varían. A continuación se explica el funcionamiento por versiones.
SQL Server 2000
La caché de procesos es una tabla virtual de sistema (syscacheobjects) que contiene los planes de ejecución de las consultas y
procedimientos almacenados que se ejecutan en el servidor. Cuando la llamada a un procedimiento almacenado llega al servidor, si existe
solidq.com
50
una entrada en dicha vista virtual, con mucha probabilidad (también depende de estadísticas de distribución de tablas asociadas) se
reutilizará dicho plan y no será necesario compilar el plan de ejecución. Esto supone que el sistema no tendrá que “gastar” tiempo de CPU
en generarlo, y lo más importante, el tiempo que se “ahorra” en dicho proceso. Cuando al servidor le llega una consulta ad-hoc, (por ejemplo
SELECT <columnas> FROM Tabla), SQL Server deberá:
1. Buscar si existe una consulta exactamente igual ejecutada anteriormente, 2. Si no existe, parsear la consulta (analizar su sintaxis), y generar su plan de ejecución.
Una vez que se genera un plan de ejecución, permanece en la caché de procedimientos. SQL Server sólo elimina de la caché los planes antiguos no utilizados cuando se necesita espacio. Cada plan de consultas y contexto de ejecución tiene un factor de costo asociado que indica cuánto cuesta compilar la estructura. Estas estructuras de datos tienen también un campo de antigüedad. Cada vez que una conexión hace referencia a un objeto, el factor de costo de compilación incrementa el campo de antigüedad. Por ejemplo, si un plan de consulta tiene un factor de costo de 8 y se le hace referencia dos veces, su antigüedad es 16. El proceso de escritura diferida recorre periódicamente la lista de objetos de la caché de procedimientos. A continuación, la escritura diferida reduce en 1 el campo de antigüedad de cada objeto en cada recorrido. La antigüedad de este plan de consultas de ejemplo se reduce hasta 0 después de 16 recorridos de la caché de procedimientos, a menos que otro usuario haga referencia al plan. El proceso de escritura diferida desasigna un objeto si se cumplen las siguientes condiciones:
El administrador de memoria necesita memoria y se está utilizando actualmente toda la memoria disponible. El campo de antigüedad del objeto es 0. Ninguna conexión hace referencia actualmente al objeto.
Debido a que el campo de antigüedad se incrementa cada vez que se hace referencia a un objeto, los campos de antigüedad de los objetos a los que se hace referencia con frecuencia nunca disminuyen a 0 y no quedan anticuados en la caché. Los objetos a los que no se hace referencia con frecuencia son pronto aptos para su desasignación, aunque no se cancelará tal asignación a menos que otros objetos necesiten memoria.
A continuación, se indican los campos más importantes de la “tabla” syscacheobjects. La columna cacheobjecttype es el tipo de objeto que
se encuentra en la caché:
solidq.com
51
• Compiled plan: el plan actual generado durante la compilación y optimización. Este objeto puede ser compartido por el resto de las sesiones que ejecuten el mismo procedimiento o sentencia
• Executable plan: el entorno de ejecución en el que se ejecuta el plan compilado. Cada una de las ejecuciones concurrentes del mismo plan compilado tendrá su propio plan de ejecución. Almacenar en caché este tipo de objeto reduce costes y puede ser beneficioso para una sentencia que se ejecute frecuentemente
• Parse tree: es un formato interno de una sentencia previo a la compilación y optimización
Por su parte, la columna objtype se refiere al tipo de objeto cuyo plan ha sido almacenado en caché. Puede tener los siguientes valores:
• Proc: para procedimientos almacenados y funciones en línea • Prepared: para sentencias ejecutadas mediante el procedimiento sp_executesql, usando sentencias preparadas o bien porque SQL
Server las pueda autoparametrizar • Ad hoc: para todas aquellas sentencias que no pueden incluirse dentro de las otras categorías • ReplProc: para agentes de replicación • Trigger: similar al de los procedimientos y funciones en línea • View: es un tipo del que se suele asociar un árbol de análisis, no a un plan. No sólo se ve este tipo cuando una vista es accedida, sino
también cuando una función no-en línea es encontrada • Table: este tipo para las tablas de usuario o de sistema generará un árbol de análisis si tiene columnas calculadas • Default, check, rule: son expandidos en árboles de análisis y compilados como parte de la sentencia desde la cual son llamados
Las otras columnas importantes, refcount y usecount, indican cuántas veces el plan ha sido referenciado (refcount) y al número de veces
que ha sido usado el objeto almacenado en la caché (usecount). Por último, también es importante conocer el número de páginas que el
objeto usa en memoria mirando el valor de la columna pagesused. Cada una de las páginas son 8Kb.
Desde SQL Server 2005
Desde SQL Server 2005 los planes de ejecución se almacenan en “sys.dm_exec_cached_plans”. El funcionamiento en cuanto a la búsqueda
y parseo es similar si viene se han realizado más modificaciones para que los algoritmos de búsqueda sean más eficientes.
La principal diferencia entre la versión 2000 y el resto de versiones es la columna cacheobjecttype. A continuación, se muestran los valores de las versiones 2005/2008:
solidq.com
52
Compiled Plan: el plan actual generado durante la compilación y optimización. Este objeto puede ser compartido por el resto de las sesiones que ejecuten el mismo procedimiento o sentencia
Compiled Plan Stub: Código auxiliar del plan compilado
Parse Tree: Árbol de análisis, es un formato interno de una sentencia previo a la compilación y optimización
Extended Proc: Es el plan de procedimiento extendido, que es un procedimiento interno de sql server almacenado en la bbdd master.
CLR Compiled Func: Es el plan de una función CLR. Las funciones CLR devuelven un valor escalar o tabla cuyo código fuente no es Transact-sql.
CLR Compiled Proc: Es el plan de un procedimiento CLR. Los procedimientos CLR realizan tareas con código fuente no Transact-sql.
El resto de columnas conservan los mismos valores y tipos que Sql 2000.
Análisis del uso de la caché de procesos
La siguiente tabla muestra la distribución de memoria destinada por SQL Server para los diferentes tipos de objetos en cache.
Base de datos Páginas cacheadas Memoria (MB)
DAX_PROD 81.890 639,77
Tempdb 36.658 286,39
Msdb 28.404 221,91
DAX_PROD_model 22.122 172,83
ResourceDb 2.866 22,39
SharePoint_Config 2.733 21,35
SharePoint_AdminContent_ dx31dfdd1121f10 1.456 11,38
WSS_Content 1.165 9,1
WSS_Logging 878 6,86
SOLIDQ 720 5,63
solidq.com
53
Se puede encontrar la tabla completa con los datos en el libro de Excel "SQL-AX_MemoryPools__20160913-0131" En la hoja "Data Pages
Table".
The following table shows how the procedure cache is distributed per component:
Cacheobjecttype ObjType Nº de entradas % de entradas Memoria (kb) % uso memoria
Compiled Plan Adhoc 70.130 27,2 561.040 27,26
Compiled Plan Prepared 148.429 57,57 1.187.432 57,7
Compiled Plan Proc 15.919 6,17 127.352 6,19
Compiled Plan Trigger 251 0,1 2.008 0,1
Extended Proc Proc 16 0,01 128 0,01
Parse Tree Check 4.937 1,91 39.496 1,92
Parse Tree Default 3 0 24 0
Parse Tree UsrTab 204 0,08 1.632 0,08
Parse Tree View 17.935 6,96 143.480 6,97
solidq.com
55
Existen un 27 % de planes ad hoc, para intentar disminuirlos sugerimos:
Activar el parámetro “Optimize for Ad Hoc Workloads”, Cuando una query ad hoc llega a SQL Server este tiene que:
1. Verificar su sintaxis 2. Buscar un plan de ejecución 3. Compilar el plan de ejecución 4. Ejecutar la query 5. Devolver los resultados
Con procedimientos almacenados o queries preparadas solo realiza esto la primera vez, las siguientes solo ejecuta los pasos 4 y 5.
Estas operaciones consumen CPU por lo que no es descabellado que sea uno de los factores que hace que influya en el alto grado
de uso de esta. Además, las queries ad hoc tienen otro problema, que es que para que se utilice el plan tiene que llegarle exactamente
la misma query. Si llega la query ad hoc “select * from nombres where nombre = ‘luis’” el plan de esta query solo se podrá reutilizar
si llega exactamente la misma otra vez. Si establecemos esta propiedad a True, en lugar de guardar el plan de ejecución guardará
un número que ocupa menos, si vuelve a llegar la query calculará de nuevo el número y si existe en la caché guardará el plan de
ejecución. Esta operación se puede realizar en caliente ya no necesita ningún tipo de reinicio.
Referencia: https://msdn.microsoft.com/es-es/library/cc645587(v=sql.120).aspx
solidq.com
56
Activar en la base de datos DAX_PROD la opción de parametrización forzada. Cualquier humano podría afirmar que la query ad hoc “select * from nombres where nombre = ‘luis’” y “select * from nombres where nombre = ‘pepe’” es la misma consulta ya que tan solo cambia el nombre que buscamos. Para SQL Server son distintas ya que el texto de la consulta no es el mismo. La parametrización forzada sustituye “pepe” y “luis” por un parámetro. Esto hace que el plan de ejecución pueda reutilizarse y se consuma menos memoria caché. La parametrización forzada tiene muchas excepciones por lo que es recomendable aplicar primero la opción Optimize for Ad Hoc Workloads, comprobar que funciona, poner la parametrización forzada y volver a medir cuanto mejoramos porque hay casos donde apenas se gana nada dependiendo el número de excepciones que se cumplan. Esta operación se puede realizar en caliente ya no necesita ningún tipo de reinicio por lo que la hemos aplicado en las bbdds.
Referencia: https://technet.microsoft.com/en-us/library/ms175037(v=sql.105).aspx
solidq.com
57
Databases Configuration
Estadísticas
No se ha identificado ninguna tarea de recalculo de estadísticas. Recomendamos su creación para que se mantengan actualizadas.
Modo de Bloqueo de BBDD de las bases de datos de AX
Microsoft recomienda para las bases de datos de AX activar el modo de bloqueo Read Committed Snapshot. Con este modo lo que se
pretende es que una modificación de un registro no bloquee (pare) a las consultas que se realizan sobre él ya que antes de la modificación
SQL Server guardará una copia de ese registro en la tempdb para que sea consultado.
Este cambio hay que hacerlo sobre la bbdd DAX_PROD.
solidq.com
58
Sección de visor de eventos
En el log de errores de SQL Server vemos que se repite el siguiente error:
Según nos indica Microsoft se debe a un error en la ejecución del procedimiento sys.fn_xe_file_target_read_file.
solidq.com
59
Referencia: https://support.microsoft.com/en-us/kb/2878139
La solución a aplicar es el CU6 que salió después del Service Pack 1, que curiosamente es el último que está instalado. Puesto que han salido
varios services packs más y cumulate updates recomendamos aplicar el último SP y CU. Ver punto versión de SQL Server.
solidq.com
60
Tiempos de espera (WAITSTATS)
Las aplicaciones cliente hacen peticiones al servidor de base de datos a través de consultas T-SQL. En el servidor, la petición pasa por una
serie de pasos internos (análisis sintáctico, parametrización, generación del plan de ejecución, etc.) que posteriormente se traducirá en
requerimientos de recursos del sistema operativo, como por ejemplo acceder a los dispositivos E/S, utilizar uno o más procesadores,
bloquear filas, páginas o tablas para garantizar la transaccionalidad de la operación, etc. En todos los casos, si el recurso solicitado no está
disponible, la petición tendrá que esperar a que el recurso esté libre para ser procesada; SQL Server mantiene internamente una lista de
tipos de esperas que cuenta el tiempo y las veces que han tenido que esperar las peticiones de las conexiones a que el recurso que necesitan
esté disponible.
Los diferentes tiempos de espera están documentados en los libros de pantalla en SQL Server 2005; sin embargo, en la versión 2000 hay
que recurrir a la WWW para encontrar información.
El que los tiempos de espera sean valores absolutos desde la última vez que se inició el servidor o desde la última vez que se ejecutó la
instrucción DBCC SQLPERF(WAITSTATS, CLEAR) implica que en realidad la información obtenida es de importancia relativa (si hubo una
espera de 15 segundos a las 16h., este mismo valor estará incluido en las esperas reportadas a las 19h.). Sin embargo, HealthCheck captura
a intervalos regulares (establecidos en una tabla de valores de configuración), ya sea por medio del comando DBCC SQLPERF(WAITSTATS)
(en SQL Server 2000) o de la DMV sys.dm_db_os_wait_stats (a partir de la versión 2005 y 2008) las razones por las que aplicaciones deben
de esperar para obtener los resultados solicitados en un momento dado; en cada nueva captura se calcula la diferencia del valor actual
respecto del valor de la captura anterior y todo ello se va almacenando sucesivamente en una tabla de históricos.
El esquema de funcionamiento es el siguiente:
<Obtención parámetros configuración> <Preparar sistema para captura>
[DBCC SQLPERF(WAITSTATS) | SELECT * FROM sys.dm_db_os_wait_stats]
MIENTRAS (<no fin día>)
[DBCC SQLPERF(WAITSTATS) | SELECT * FROM sys.dm_db_os_wait_stats]
Calcular valores deltas respecto a la captura anterior
solidq.com
61
Esperar el tiempo configurado (WAITFOR)
FIN MIENTRAS
La distribución de todas las esperas es la siguiente:
solidq.com
62
La siguiente tabla muestra las 5 esperas más importantes en el servidor:
La espera ASYNC_NETWORK_IO ocurre cuando SQL Server tiene que esperar por una tarea en la red. Imaginemos que un usuario hace una consulta de 10.000 registros, SQL Server la soluciona en al instante y le intenta enviar los datos al cliente. Imaginemos que al cliente le pasan uno de estos tres problemas:
o Está geográficamente muy lejos o Existen problemas en la red o Las dos anteriores están correctas pero su tarjeta de red o su ordenador no puede procesar tantos registros porque no tiene
recursos suficientes
En este caso y a pesar de que SQL Server puede y quiere pasar los datos al usuario le va a ser imposible y va a tener que pasarlos
poco a poco al usuario por motivos ajenos a él. Este tiempo de espera es el que se imputa en ASYNC_NETWORK_IO
Esta espera es muy habitual, en el ejemplo lo hemos magnificado para que se entienda un caso extremo. Si realmente se quiere
decrementar esta espera hay que actuar en los tres puntos anteriores:
o Eliminar problemas de red o Ampliar ancho de banda o Mejorar la tarjeta de red de los usuarios y aumentar los recursos de sus ordenadores
La espera BACKUPIO se produce cuando un backup está esperando por datos o está esperando por un buffer en el que guardar datos. Estamos teniendo problemas de lectura G disco por lo que creemos que está relacionado con esto. Mejorando los discos se disminuiría esta espera
solidq.com
63
ASYNC_IO_COMPLETION es una espera que sucede cuando una tarea está esperando a que termine una operación de escritura. Esta espera está relacionada con la anterior, imaginemos un backup full que tiene muchos datos, hay que leer del disco de datos en nuestro caso (G) y escribir en el disco de backups (K)
La espera TRACEWRITE ocurre cuando SQL Server está esperando un buffer libre para o un buffer con eventos para procesar. Como en los casos anteriores al final se vuelca o se pide el buffer a un disco por lo que mejorando los discos disminuimos esta espera
WRITELOG. Esta última es similar a las anteriores, imaginemos que en un momento determinado muchos usuarios realizan operaciones de escritura, como los discos de Azure solo tienen 500 IOPS estas operaciones de escritura (checkpoints o transacciones) se encolan y tienen que esperar.
Todos estos datos se encuentran en la Excel AnalisisWaitstatsFileIO.xlsx pestaña “Waitstats”.
solidq.com
64
Patrones de consulta en la instancia SQLAX-SERVER12
Patrón, un patrón consiste en una sentencia TSQL que se repite variando los parámetros que se le envían a SQL Server. Este es el ejemplo
de un patrón ante dos consultas:
Select a, b from X where nombre = ‘Luis’ and Edad = 8
Select a, b from X where nombre = ‘Pepe’ and Edad = 17
Patrón: Select a, b from X where nombre = ‘#’ and Edad = #
En la excel SQL-AX_PATTERNS.xlsx se encuentran todos los patrones recogidos con las trazas de profiler. En la Excel se puede ordenar por
cualquier criterio, consumo de CPU, Escrituras, Lecturas, Duración, número de ejecuciones... . Esta excel sirve para una vez identificados
los patrones más costosos optimizarlos.
Patrones más costosos por CPU
Patrones más costosos por Número de Lecturas
solidq.com
65
Patrones más costosos por Número de Escrituras
Patrones más costosos por Duración
Patrones más costosos por Número de Ejecuciones