Monitorizacion SQL

Problema:

Queda ralentizado enormemente un servidor a distintas horas y durante distintos periodos.

Solucion:

Agregar contadores logeando la actividad del servidor.
Se localizo el problema en la cola de disco y en el % de utilizacion de uno de los discos.
Era el que almacenaba gran parte de las BBDD.
Con Filemon (sysinternals) localizamos que base de datos esta consumiendo el tiempo de disco.
Con el SQL Manager (Administracion / Actividad Actual / Informacion del proceso o Bloqueos) vemos que usuarios estan generando trafico o bloqueos sobre la base de datos.
Con el Analizador SQL monitorizamos las tramas filtrando un usuario sospechoso.
(Propiedades de la traza / Filtros / Login name como: Usuario).

Resultado:

Un usuario a traves de VPN desde el analizador de consultas estaba lanzando scripts que recorrian con updates toda la base de datos, consumiendo durante varias horas al dia una gran parte del % de utilizacion del disco.



Listado de contadores para monitorizar un SQL

Contador Valor/Uso
Memoria Memory: Pages/sec < 20
Memoria Memory: Availability Mb > 5 Mb
Memoria SQLServer: Memory Manager: Total Server Memory vs. SQLServer: Memory Manager: Target Server Memory Si Total = Target -> Bien
Si Total <> Falta RAM
Procesador Processor: % CPU Usage < 80 %
Procesador System: Processor queue length < 2 por CPU
Procesador System: Context Switches/sec Si es > 8000, probar Fibras
Procesador System: % Total Privileged Time Si es < 20 %, posible problema de disco
Disco PhysicalDisk: Avg. Disk Queue Length Avg./(#Discos del RAID) < 2
Disco PhysicalDisk: % Disk Time < 55%
Disco PhysicalDisk: Disk Read Time vs. PhysicalDisk: Disk Write Time Si Read >> Write -> Bajar Fill Factor
Si Read <<> Subir Fill Factor
Red Network Interface: Bytes Total/sec vs. Network Interface: Current Bandwidth Bytes / Bandwidth < 6
Red Network Segment: % Network Utilization Comprobación de uso de las distintas tarjetas de red
Red Server: Bytes Received/sec vs. Server: Bytes Transmitted/sec. Saber si el servidor está perjudicando el resto de la red
Red SQLServer: SQL Statistics: Batch Request/sec 3000 por tarjeta de 100 Mbs
SQL Server (General) SQLServer: Access Methods: Page split/sec Si es < 100, posible problema de disco
SQL Server (General) SQLServer: Buffer Manager: Cache Hit Ratio En OLTP > 99 %; en OLAP > 80 %, uso de caché
SQL Server (Memoria) SQLServer: Buffer Manager: Page Life Expectancy > 300
SQL Server (Memoria) SQLServer: Buffer Manager: Lazy Write/sec < 20
SQL Server (Memoria) SQLServer: Buffer Manager: Checkpoint Pages/sec No debe aparecer con frecuencia
SQL Server (Memoria) SQLServer: Buffer Manager: Procedure Cache Pages Debe permanecer constante
SQL Server (Memoria) SQLServer: Databases: Log Flushes/sec A menor valor, mayor rendimiento
SQL Server (Usuarios) SQLServer: General Statistics: User connections Identificar horas punta y horas valle de usuarios
SQL Server (Usuarios) SQLServer: Databases: Transaction/sec Saber qué base de datos es más usada
SQL Server (Usuarios) SQLServer: SQL Statistics: SQL Compilations/sec < 100 (si no, los planes de ejecución están consumiendo mucho)
SQL Server (Bloqueos) SQLServer: Access Methods: Full scans/sec Si es relevante, estudiar con profiler
SQL Server (Bloqueos) SQLServer: Locks: Number of Deadlocks Mínimo (estudiar con profiler)
SQL Server (Bloqueos) SQLServer: Locks: Avg. Wait Time (ms) Mínimo posible
SQL Server (Bloqueos) SQLServer: Latches: Average Latch Wait Time (ms), Latch Waits/sec y Total Latch Wait Time (ms) Establecer línea base y observar qué se está entorno a ella
SQL Server (Backup) SQLServer: Backup Device: Device Throughput Bytes/sec Rendimiento de backup, fuerte actividad en tempdb

Fuente: http://msdn.microsoft.com/es-es/library/bb972264.aspx