De http://msdn.microsoft.com/es-es/library/bb630354.aspx:SQL Server Extended Events (Extended Events) es un sistema de control de eventos general para los sistemas del servidor. La infraestructura de Extended Events admite la correlación de datos de SQL Server y bajo ciertas condiciones, la correlación de datos de las aplicaciones de base de datos y sistema operativo. En este último caso, debe dirigirse el resultado al Seguimiento de eventos para Windows (ETW) para correlacionar los datos de eventos con los datos de eventos de aplicación o sistema operativo.
SQL Server Extended Events Packages
http://msdn.microsoft.com/es-es/library/bb677278.aspx,
Un paquete es un contenedor para objetos de SQL Server Extended Events. Se pueden mezclar objetos de distintos paquetes en una sesión de eventos. Para obtener más información, vea Sesiones de SQL Server Extended Events.
Un paquete puede contener cualquiera de los objetos siguientes, o todos ellos, que se describen pormenorizadamente más adelante en este tema:
- Eventos
- Destinos
- Acciones
- Tipos
- Predicados
- Asignaciones
Los paquetes se identifican por un nombre, un GUID y el módulo binario que contiene el paquete.
1) Necesitamos conocer cual es el evento asociado a page split:
SELECT xp.[name], xo.* FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp WHERE xp.[guid] = xo.[package_guid] AND xo.[object_type] = 'event' ORDER BY xp.[name];
para el ejemplo que estamos tratando, usaremos sqlserver.page_split.
2) necesitamos conocer qué columnas expone el evento page_split:
select * from sys.dm_xe_object_columns where [object_name] = 'page_split'
el evento expone file_id, ypage_id, pero necesitamos información adicional como database_id, o la consulta que causó el Page Split.
3) Averiguar que columnas adicionales podemos añadir:
SELECT xp.[name], xo.* FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp WHERE xp.[guid] = xo.[package_guid] AND xo.[object_type] = 'action' ORDER BY xp.[name], xo.[name];
Fijate en las columnas 25 y 34 que incluyen database_id, y sql_text. Revisa la lista porque para otras ocasiones igual necesitas por ejemplo application name, username, o plan_handle.
4) Destino (target) del evento:
SELECT xp.[name], xo.* FROM sys.dm_xe_objects xo, sys.dm_xe_packages xp WHERE xp.[guid] = xo.[package_guid] AND xo.[object_type] = 'target' ORDER BY xp.[name], xo.[name];
tratamos que sea un target asincrono para no afectar demasiado al rendimiento: usamos filessystem(3).
5) Ahora estamos listos para crear el Evento Extendido con el siguiente código:
create event session xe_page_split on server add event sqlserver.page_split (action (sqlserver.database_id, sqlserver.sql_text) where sqlserver.database_id > 4) add target package0.asynchronous_file_target (set filename=N'c:tempxe_page_split.xel', metadatafile=N'c:tempxe_page_split.xem');
Explicación:
- Nombre del evento: sqlserver.event
- Como acciones añadimos sqlserver.database_id, y sqlserver.sqltext; en este caso, la acción es sólamente añadir nuevas columnas al evento (recuerda que el evento expone las columnas file_id, y page_id).
- Además, podemos añadir filtros al evento: en este caso queremos que se active sólo para las bases de adtos de usuario.
- El destino del evento (target) será un fichero: c:tempxe_page_split.xel.
6) Iniciar el Evento Extendido:
alter event session xe_page_split on server state = start;
7) Rellenemos una tabla para generar actividad:
use Northwind go if not object_id ('dbo.t') is null drop table dbo.t go create table dbo.t (id int identity, v char(100) default 'a', constraint pk_t primary key (id)) go insert dbo.t (v) select top 100000 'a' from master.dbo.spt_values v cross join master.dbo.spt_values
8) espera unos segundos porque 1) el evento es asíncrono, y 2) no hemos especificado el argumento MAX_DISPATCH_LATENCY (que su valor por defecto es INFINITE).
9) Comprobemos si hay filas afectadas:
select COUNT(*) from sys.fn_xe_file_target_read_file ('c:tempxe_page_split*.xel', 'c:tempxe_page_split*.xem', null, null)
10) Alguna consulta XPath:
select xml_data , xml_data.value('(/event[@name=''page_split'']/@timestamp)[1]','datetime') time , xml_data.value('(/event/data[@name=''file_id'']/value)[1]','int') file_id , xml_data.value('(/event/data[@name=''page_id'']/value)[1]','int') page_id , xml_data.value('(/event/action[@name=''database_id'']/value)[1]','int') database_id , xml_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') sql_text from (select object_name as event, CONVERT(xml, event_data) as xml_data from sys.fn_xe_file_target_read_file ('c:tempxe_page_split*.xel', 'c:tempxe_page_split*.xem', null, null) ) v order by time
Que en resumen nuestra:
- Cuando sucedió el evento.
- qué fichero y página estaban involucradas.
- cual era la base de datos.
- la consulta que provocó el evento 🙂
Siguientes acciones:
- Hasta donde yo se no se puede asociar directamente sobre qué objeto sucedió el Page Split, pero revisando la sentencia es relativamente sencillo saberlo.
- Page split se activa para cada página dividida (spitted): en general, splits de la primera o la ultima página de un objeto no nos debe preocupar demasiado, lo que debemos atarcar son los splits que surgen “entre medias” del objeto. he hecho algunas pruebas, y con DBCC IND se puede localizar la correlación, pero de momento no lo tengo automatizado.
Nota: Antes de ejecutar este Evento Extendido en Producción, por favor, pruebalo cuidadosamente, y se consciente de los pontenciales problemas de rendimiento que puede causar.
Lecturas adicionales:
- Investigating SQL Server 2008 Wait Events with XEVENTS: bonito ejemplo de como capturar waitstats a nivel de conexión.
- Advanced Troubleshooting with Extended Events
- SQL Server Extended Events How-to Topics
- How to: Determine Which Queries Are Holding Locks
- How to: Find the Objects That Have the Most Locks Taken on Them
- How to: Get the Configurable Parameters for the ADD TARGET Argument
- How to: Get the Fields for All Events
- How to: Monitor System Activity Using Extended Events
- How to: View the Events for Registered Packages
- How to: View the Extended Events Targets for Registered Packages
