sqlserver 2008 R2扩展事件

概述--如果已经存在Event Session删除IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongQuery')DROP EVENT SESSION MonitorLongQuery ON SERVERGO--创建Extended Event sessionCREATE EVENT

--如果已经存在Event Session删除IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongquery')DROP EVENT SESSION MonitorLongquery ON SERVERGO--创建Extended Event sessionCREATE EVENT SESSION MonitorLongquery ON SERVER--增加Event(sql完成事件)ADD EVENT sqlserver.sql_statement_completed(--指定收集的Event信息ACTION(sqlserver.database_ID,sqlserver.session_ID,sqlserver.username,sqlserver.clIEnt_hostname,sqlserver.sql_text,sqlserver.tsql_stack)--Filter信息（cpu超过或者整个运行时间超过10S）WHERE sqlserver.sql_statement_completed.cpu> 10000OR sqlserver.sql_statement_completed.duration> 10000)--指定收集的Event信息储存位置（可以存储到内存也可以到文件）ADD TARGET package0.asynchronous_file_target(SET filename = N's:\monitor\Logquery.xet',MetaDATAfile = 'S:\monitor\Longquery.xem')GOSELECT sessions.name AS Sessionname,sevents.package as Packagename,sevents.name AS Eventname,sevents.predicate,sactions.name AS Actionname,stargets.name AS TargetnameFROM sys.server_event_sessions sessionsINNER JOIN sys.server_event_session_events seventsON sessions.event_session_ID= sevents.event_session_IDINNER JOIN sys.server_event_session_actions sactionsON sessions.event_session_ID= sactions.event_session_IDINNER JOIN sys.server_event_session_targets stargetsON sessions.event_session_ID= stargets.event_session_IDWHERE sessions.name='MonitorLongquery'GO--启动Event Session捕获数据ALTER EVENT SESSION MonitorLongqueryON SERVER STATE = STARTGO--查询SELECT CAST(event_data AS XML) event_data,*FROM sys.fn_xe_file_target_read_file('s:\monitor\Logquery_0_129954478780290000.xet','s:\monitor\Longquery_0_129954478780330000.xem',NulL,NulL)go-停掉Event SessionALTER EVENT SESSION MonitorLongqueryON SERVER STATE = StopGO   --删除Event SessionIF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorLongquery')DROP EVENT SESSION MonitorLongquery ON SERVERGO---查看结果以表格形式IF EXISTS ( SELECT  *            FROM    tempdb.dbo.sysobjects            WHERE   ID = OBJECT_ID(N'tempdb..#MyData')                    AND type = 'U' )    DROP table #MyDatagoCREATE table #MyData    (      database_ID INT NOT NulL,sql_text NVARCHAR(MAX) NOT NulL    )goDECLARE @xmlData XMLDECLARE @database_ID INTDECLARE @sql_text NVARCHAR(MAX)DECLARE myCur CURSOR READ_ONLYFORSELECT  CAST(event_data AS XML) event_dataFROM sys.fn_xe_file_target_read_file('s:\monitor\Logquery_0_129954594949480000.xet','s:\monitor\Longquery_0_129954594949480000.xem',NulL)OPEN myCurFETCH NEXT FROM myCur INTO @xmlDataWHILE @@FETCH_STATUS = 0    BEGIN        --获取database_ID        SET @database_ID = @xmlData.query('//action[@name="database_ID"]/value').value('(value)[1]','INT')        --获取sql_text        SET @sql_text = @xmlData.query('//action[@name="sql_text"]/value').value('(value)[1]','NVARCHAR(MAX)')        --开始插入数据        INSERT #MyData                ( database_ID,sql_text )        VALUES  ( @database_ID,-- database_ID - int                  @sql_text  -- sql_text - nvarchar(max)                  )                  FETCH NEXT FROM myCur INTO @xmlData    ENDCLOSE myCurDEALLOCATE myCurSELECT * FROM #MyData WHERE sql_textgo

总结

以上是内存溢出为你收集整理的sqlserver 2008 R2扩展事件全部内容，希望文章能够帮你解决sqlserver 2008 R2扩展事件所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。