Ayer nos comentaba Percy Reyes que la versión definitiva de SQL Server 2008 está a punto de salir del horno con todas sus nuevas prestaciones y capacidades. Una de las grandes novedades que forman parte de SQL Server 2008 es Peformance Studio. Se trata de una nueva consola de administración que ayuda a monitorizar la velocidad y eficiencia de las bases de datos de una forma más efectiva. La clave de Peformance Studio está en que permite recolectar datos de rendimiento de múltiples bases de datos y almacenarlos en un data warehouse (DW) centralizado a partir del cuál construye una serie de informes de rendimiento. Además, con Performance Studio podremos:
- Comparar el rendimiento actual de nuestras BD's con el pasado.
- Detectar y tratar problemas de rendimiento.
- Hacer el tracking de métricas de rendimiento personalizadas.
- ...
Creación del Management Data Warehouse
Como hemos comentado, la clave de Performance Studio es una BD DW especial en la que se recolectan todos los datos de rendimientos de las BD's a monitorizar. Luego el primer paso para ver a Performance Studio en acción consiste en crear dicha BD. Por lo tanto, lo primero que vamos a hacer es crear una base de datos (BD) de gestión, denominada ManagementDW, que nos permita configurar los tipos de colecciones de datos que vamos a recolectar con Performance Studio, así como las estrategias de recogida de esta información (de forma continua o bien de acuerdo a una cierta planificación). Esta BD ManagementDW es una BD es como cualquier otra BD relacional que tengamos en nuestros sistemas, por lo que aplican las mismas reglas de administración que tengamos definidas para el resto de las bases de datos de nuestra organización.
Antes de instalar la BD ManagementDW, vamos a analizar la estructura de recolección de datos de Performance Studio:
La idea es que los Targets son instancias de SQL Server y para cada instancia tenemos definidos unos contadores que pueden ser de dos tipos: disk o bien query statistics (por lo tanto, nos permitirán recoger datos sobre el uso de disco que se está haciendo, así como estadísticas de las ejecuciones de consultas que se produzcan). La recolección de datos se realiza de acuerdo a una cierta planificación, usando el agente SQL y SQL Server Integration Services (SSIS), y los jobs y planificaciones se almacenan en la BD msdb (como cualquier otro job o planificación de SQL Server). El papel de este data warehouse de gestión es almacenar los datos, valores agregados y una histórico de información (configurable) que permita realizar una análisis de tendencias en el funcionamiento de la BD.
Para crear la BD ManagementDW, abrimos SQL Server Management Studio y seguimos los siguientes pasos:
- Desplegamos la sección Management. A continuación, seleccionamos la sección Data Collection, hacemos clic con el botón derecho del ratón y seleccionamos la opción Configure Management Data Warehouse.
- En la ventana que se abre, simplemente hacemos clic en el botón Next.
- En la siguiente ventana dejamos marcada la opción Create or upgrade a management data warehouse.
- Para configurar una data warehouse de gestión para Performance Studio, necesitamos una BD de SQL Server en la que almacenar los contadores de recolección de datos (idealmente, esta BD debería estar en un servidor diferente al servidor de SQL que vamos a monitorizar).
- En primer lugar, especificamos el nombre de la base de datos a través del botón New. Especificamos los siguientes parámetros de creación de la BD:
- Pulsamos el botón Next.
- En la siguiente pantalla, Map Logins and Users, configuramos como se va a usar la BD de gestión para Performance Studio. Marcamos la opción NT AUTORITHY\SYSTEM como Users mapped to this login y para esta opción marcamos los membership siguientes:
- mdw_admin.
- mdw_reader.
- mdw_writer.
- Pulsamos Next y en la siguiente pantalla verificamos que la configuración se corresponde con la que hemos realizado. Pulsamos Finish.
- A continuación veremos que se pone en marcha el proceso de setup del data warehouse de gestión:
- Creación de la BD ManagementDW.
- Creación de los logins de acuerdo a los roles definidos.
- Una vez que el proceso ha finalizado, comprobamos a través del Object Explorer de SQL Server Management Studio que la BD ManagementDW se ha creado sin problemas y con la configuración especificada.
Configuración de la recogida de datos
Una vez que hemos creado la BD ManagementDW, el siguiente paso es configurar como se va a realizar la recogida de datos que va a utilizar Performance Studio para monitorizar la salud y rendimiento de nuestras BD's:
- Seleccionamos de nuevo la sección Data Collection, hacemos clic con el botón derecho del ratón y seleccionamos la opción Configure Management Data Warehouse.
- En la ventana que se abre, simplemente hacemos clic en el botón Next.
- En la siguiente ventana dejamos marcamos ahora la opción Set up data collection.
- Especificamos el nombre del servidor a través del botón ... . En el combo de BD's seleccionamos BD creada con anterioridad.
- Especificamos el directorio de caché en el que almacenar datos temporales antes de cargarlos en el data warehouse.
- En la siguiente pantalla simplemente se muestra un resumen de las configuraciones realizadas y que se van a realizar. Pulsamos Finish.
- Si todo ha ido bien, en la siguiente pantalla veremos que:
- Se inicia la recolección de datos del sistema.
- Se habilita la recolección de datos.
Una vez que hemos habilitado y configurado la recogida de datos, el siguiente paso consiste en definir las planificaciones a utilizar en las recogidas de datos:
- En el Object Explorer de SQL Server nos vamos a Management -> Data Collection -> System Data Collection Sets -> Disk Usage. Hacemos clic con el botón derecho del ratón y seleccionamos la opción Properties.
- En la pantalla que se abre veremos las propiedades completas para las estadísticas de uso de disco. En la sección General podremos observar información diversa de cómo se puede configurar la recolección de datos para su posterior análisis en Perfomance Studio:
- Si los datos se tienen que tomar o no de caché. En el caso de que no se tomen de caché, podemos especificar la opción de que se recojan bajo demanda o de acuerdo a una cierta planificación.
- Los elementos de recolección definidos y la definición de los mismos.
- Si pulsamos el botón Pick podremos visualizar las planificaciones disponibles para realizar las recolecciones de datos:
- Si seleccionamos la opción Disk Usage – Data Files dentro de la sección Collection Items, podremos observar que la recolección de datos relativos a este parámetro se realizan en base a la siguiente consulta (que aparece en la sección Input parameteres):
|
DECLARE @dbsize bigint DECLARE @logsize bigint DECLARE @ftsize bigint DECLARE @reservedpages bigint DECLARE @pages bigint DECLARE @usedpages bigint SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) FROM sys.database_files SELECT @reservedpages = SUM(a.total_pages) ,@usedpages = SUM(a.used_pages) ,@pages = SUM(CASE WHEN it.internal_type IN (202,204) THEN 0 WHEN a.type != 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id SELECT @dbsize as 'dbsize', @logsize as 'logsize', @ftsize as 'ftsize', @reservedpages as 'reservedpages', @usedpages as 'usedpages', @pages as 'pages' |
- Si ejecutamos esta consulta contra la BD master en un editor de consultas, obtendremos el siguiente resultado:
Como vemos, se muestra una única fila de resultados que corresponde a una única BD. Sin embargo, esta query debería ser ejecutada para todas las BD’s. Este trabajo es realizado como parta de los jobs del agente de SQL Server que permiten ejecutar toda la recogida y subida de colecciones de datos.
- Si seleccionamos la opción Disk Usage - Log Files dentro de la sección Collection Items, podremos observar que la recolección de datos relativos a este parámetro se realizan en base a la siguiente consulta (que aparece en la sección Input parameteres):
| -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @tran_log_space_usage table( database_name sysname , log_size_mb float , log_space_used float , status int ); INSERT INTO @tran_log_space_usage EXEC('DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'); SELECT database_name, log_size_mb, log_space_used, status FROM @tran_log_space_usage |
- Si ejecutamos esta consulta contra la BD master en un editor de consultas, obtendremos el siguiente resultado:
Otra posibilidad que tenemos para consultar el uso de disco y logs de una BD es realizando un par de consultas SELECT como las siguientes:
| select * from ManagementDW.snapshots.disk_usage select * from ManagementDW.snapshots.log_usage |
Y hasta aquí llega el primer post sobre Performance Studio. En la siguiente capítulo os comentaré como explotar los datos recogidos en la BD ManagementDW mediante Performance Studio en situaciones de mucha actividad en la BD.