Personalización de DataCollector

Enrique Catalá Bañuls

REL-319

Mentor – Área relacional MCT – MCTS – MCITP – MAP 2010

ecatala@solidq.com

α Comprensión del modelo de arquitectura DataCollector

α Internals β Aprender su modelo relacional

β Explotar la información

β Construir tus propios reportes! POR FIN ALGUIEN LO EXPLICA!!!!!

α Arquitectura de SolidQ

Objetivos de la sesión

α Active Directory β SQL Server y SQL Agent levantados con cuenta de AD

α Instancia con el datawarehouse SQL Server 2008 en adelante

α Instancias suscriptoras SQL Server 2008 en adelante

α Instancias de reporting SQL Server 2008 R2

DataCollector Requisitos

α Es el framework que enlaza capturas, análisis, solución de problemas y persistencia de los informes de diagnóstico de SQL Server

α Consiste en una suite de herramientas para β Captura de datos con poca sobrecarga

β Monitor de rendimiento, solucionador de problemas y optimización

β Persistencia de datos de diagnósticos

β Reporting

DataCollector Introducción

• Proveedor de datos

– Fuentes de información – Ej. SQL Trace, Perform counters, DMVs, consultas T-SQL, logs

• Tipo colector

– Conoce como leer y exponer datos de un proveedor de datos específico

• Elemento colección

– Instancia de un tipo colector

– Determina las entradas de datos y su frecuencia Ej. Solo recoge wait_time_ms y max_wait_time_ms desde sys.dm_os_wait_stats DMV cada 5

segundos).

DataCollector Conceptos

α Lo primero siempre es crear la instancia recolectora

Datacollector Configuración estandard

α Lo segundo será ir suscribiendo instancias para monitorizar

DataCollector Suscripción de nueva instancia

Reportes estandard Uso de disco

Reportes estandard Estadísticas de consultas

Reportes estandard Actividad de servidor

α Disk Usage (retained for 730 days)

β Disk Usage – Data Files: Transact-SQL data collection in non-cached mode – gathered and immediately uploaded every 6 hours

β Disk Usage – Log Files: Transact-SQL data collection in non-cached mode – gathered and immediately uploaded every 6 hours

α Query Statistics (retained for 14 days)

β Query Activity: special Query Activity collector type in cached mode – gathered every 10 seconds and uploaded every 15 minutes

α Server Activity (retained for 14 days)

β DMV Snapshots: Transact-SQL data collection in cached mode – gathered every 60 seconds and uploaded every 15 minutes

β Performance Counters: Performance Monitor collection in cached mode – gathered every 60 seconds and uploaded every 15 minutes

Colectores estandard

Internals Dependencias y relaciones

Internals Arquitectura de data collector

α Almacena información de configuración, información en tiempo de ejecución, auditorías e información de historial de recopilación.

α Todo los datos necesarios para configurar y ejecutar el recopilador de datos están en ella

α La configuración de la recopilación de datos puede implementarse en varios servidores sin tener que usar el sistema de archivos.

α El recopilador de datos puede usar los mecanismos de seguridad existentes de SQL Server para proteger los datos. Además, las funciones de base de datos pueden proporcionar seguridad granular y no es necesario implementar el encadenamiento entre bases de datos.

α Puesto que msdb es una base de datos relacional, es posible garantizar la integridad referencial de los datos de configuración y en tiempo de ejecución.

α Además de almacenar la información específica del recopilador, msdb también se usa para almacenar información de trabajo del Agente SQL Server e información de los paquetes de SSIS.

Internals MSDB

α Instancia normal y corriente con una BBDD

α Se encuentra como script de ejecución directo en $INSTALL_PATH\MSSQL\INSTALL

β C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2_2\MSSQL\Install

α Se chequea que no sea instancia SQL Express

α ¿Creías que era una BBDD mágica? β Eso si, nadie ha dicho que estuviera bien optimizada

Internals Instancia Datawarehouse

α Almacenan en MSDB la info critica!!! β Dbo.syscollector_*

β Por culpa de eso tendremos que ingeniarnoslas

α Tienen un job por cada accion de carga y captura hacia datawarehouse

α Imperativamente se crean, configuran y arrancan los colectores del sistema

α No se puede desconfigurar, solo deshabilitar

Internals Instancias suscriptoras

Datacollector predeterminado

α Esquemas β Core: Objetos de sistema de configuración de suscripciones

β Sysutility_ucp_core: Tablas de Utility Control Point

γ Solo SQL Server 2008 R2 y superior

γ Si, funciona con esta tecnologia

β Snapshots: Objetos de sistema relacionados con la captura de datos

β Custom_snapshots

γ Este es el esquema sobre el que trabajaremos si queremos añadir funcionalidad

α Triggers de base de datos β Solo sysadmin y mdw_admin pueden eliminar objetos

β A toda tabla creada sobre custom_snapshots se le añade una restricción que chequea el operador para ver si tiene permisos mdw_writer

Internals Algunas interioridades básicas

α Una fila por cada colector registrado β Collector_set_uid: identificación UID del colector

β Instance_name: Nombre de instancia registrada para seguimiento

β Days_until_expiration: Dias antes de que sea lanzado el purgado de datos

β Operator: Login encargado de realizar la conexión de carga

Internals Core.source_info_internal

α Contiene una fila por cada snapshot ocurrido en suscriptor

α Tabla intermedia con identificadores

α Imprescindible para correlacionar capturas con instancias

α Columnas β Snapshot_id: pk e identificador de la tabla

β Snapshot_time_id: fk hacia tabla que contiene la hora de captura

β Source_id: Importantisimo. Relaciona la captura con el colector y por tanto con la instancia

Internals core.snapshots_internal

α Tabla que contiene los contadores de rendimiento

α Columnas: β Performance_counter_id: Identificador del contador

β Path: Path completo al contador

β Object_name: Grupo al que pertenece el contador

β Counter_name: Contador

β Instance_name: Instancia al que se le aplica

β Counter_type: Id numerico identificando counter_name

Internals Snapshots.performance_counter_instances

α Tabla que contiene datos de captura de contadores

α Es la tabla mas gorda del entorno β Para que nos hagamos una idea, pensad en 100Millones de filas

α No está particionada de serie

α No utiliza compresión

Internals Snapshots.performance_counter_values

α Debe cumplir un simple esquema, medianamente documentado en BOL

Internals Añadir-modificar colectores

α Con valor de 6 instancias y una captura por minuto

Internals Estimacion de costes

Cálculos Tiempos Descripcion

horas/pagina/contador 2,016666667 horas se tardan en llenar una página

Paginas/h/contador 0,033611111 Páginas ocupadas por hora por un solo contador

horas/pagina 0,013908046 horas se tardan en llenar una página capturando todos los contadores

Horas/Gb/contador 2114628,267 horas para llenar 1Gb por contador para una instancia

Horas/Gb 14583,64322 horas para llenar 1Gb por todos los contadores para una instancia

Dias/Gb/Instancia 607,6518008 dias para llenar 1Gb por todos los contadores para una instancia

Meses/Gb/Instancia 20,25506003 Meses para llenar 1Gb por todos los contadores para una instancia

Dias/Gb 101,2753001 dias para llenar 1Gb por todos los contadores para todas las instancias

Meses/Gb 3,375843338 meses para llenar 1Gb por todos los contadores para todas las instancias

Código para crear tus reports

Reportes personalizados ¿Dónde está el límite?

Arquitectura final

SolidQDataCollector

α Modelo de arquitectura DataCollector «sencillo»

α Crear nuestros propios colectores es la gran potencia

α Modelo relacional β No es todo lo eficiente que nos gustaria

Objetivos de la sesión Conclusiones

Si quieres disfrutar de las mejores sesiones de

nuestros mentores de España y Latino América,

ésta es tu oportunidad.

http://summit.solidq.com/madrid/