spam filter - Page 119 - Developer IT

Introduction to Extended Events

- by extended_events

For those fighting with all the Extended Event terminology, let's step back and have a small overall Introduction to Extended Events. This post will give you a simplified end to end view through some of the elements in Extended Events. Before we start, let’s review the first Extented Events that we are going to use: - Events: The SQL Server code is populated with event calls that, by default, are disabled. Adding events to a session enables those event calls. Once enabled, they will execute the set of functionality defined by the session. - Target: This is an Extended Event Object that can be used to log event information. Also it is important to understand the following Extended Event concept: - Session: Server Object created by the user that defines functionality to be executed every time a set of events happen. It’s time to write a small “Hello World” using Extended Events. This will help understand the above terms. We will use: - Event sqlserver. error_reported: This event gets fired every time that an error happens in the server. - Target package0.asynchronous_file_target: This target stores the event data in disk. - Session: We will create a session that sends all the error_reported events to the ring buffer. Before we get started, a quick note: Don’t run this script in a production environment. Even though, we are going just going to be raise very low severity user errors, we don't want to introduce noise in our servers. -- TRIES TO ELIMINATE PREVIOUS SESSIONS BEGIN TRY DROP EVENT SESSION test_session ON SERVER END TRY BEGIN CATCH END CATCH GO -- CREATES THE SESSION CREATE EVENT SESSION test_session ON SERVER ADD EVENT sqlserver.error_reported ADD TARGET package0.asynchronous_file_target -- CONFIGURES THE FILE TARGET (set filename = 'c:\temp\data1.xel' , metadatafile = 'c:\temp\data1.xem') GO -- STARTS THE SESSION ALTER EVENT SESSION test_session ON SERVER STATE = START GO -- GENERATES AN ERROR RAISERROR (N'HELLO WORLD', -- Message text. 1, -- Severity, 1, 7, 3, N'abcde'); -- Other parameters GO -- STOPS LISTENING FOR THE EVENT ALTER EVENT SESSION test_session ON SERVER STATE = STOP GO -- REMOVES THE EVENT SESSION FROM THE SERVER DROP EVENT SESSION test_session ON SERVER GO -- REMOVES THE EVENT SESSION FROM THE SERVER select CAST(event_data as XML) as event_data from sys.fn_xe_file_target_read_file ('c:\temp\data1*.xel','c:\temp\data1*.xem', null, null) This query will output the event data with our first hello world in the Extended Event format: <event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2010-02-27T03:08:04.210Z"><data name="error"><value>50000</value><text /></data><data name="severity"><value>1</value><text /></data><data name="state"><value>1</value><text /></data><data name="user_defined"><value>true</value><text /></data><data name="message"><value>HELLO WORLD</value><text /></data></event> More on parsing event data in this post: Reading event data 101 Now let's move that lets move on to the other three Extended Event objects: - Actions. This Extended Objects actions get executed before events are published (stored in buffers to be transferred to the targets). Currently they are used additional data (like the TSQL Statement related to an event, the session, the user) or generate a mini dump. - Predicates: Predicates express are logical expressions that specify what predicates to fire (E.g. only listen to errors with a severity greater than 16). This are composed of two Extended Objects: o Predicate comparators: Defines an operator for a pair of values. Examples: § Severity > 16 § error_message = ‘Hello World!!’ o Predicate sources: These are values that can be also used by the predicates. They are generic data that isn’t usually provided in the event (similar to the actions). § Sqlserver.username = ‘Tintin’ As logical expressions they can be combined using logical operators (and, or, not). Note: This pair always has to be first an event field or predicate source and then a value Let’s do another small Example. We will trigger errors but we will use the ones that have severity >= 10 and the error message != ‘filter’. To verify this we will use the action sql_text that will attach the sql statement to the event data: -- TRIES TO ELIMINATE PREVIOUS SESSIONS BEGIN TRY DROP EVENT SESSION test_session ON SERVER END TRY BEGIN CATCH END CATCH GO -- CREATES THE SESSION CREATE EVENT SESSION test_session ON SERVER ADD EVENT sqlserver.error_reported (ACTION (sqlserver.sql_text) WHERE severity = 2 and (not (message = 'filter'))) ADD TARGET package0.asynchronous_file_target -- CONFIGURES THE FILE TARGET (set filename = 'c:\temp\data2.xel' , metadatafile = 'c:\temp\data2.xem') GO -- STARTS THE SESSION ALTER EVENT SESSION test_session ON SERVER STATE = START GO -- THIS EVENT WILL BE FILTERED BECAUSE SEVERITY != 2 RAISERROR (N'PUBLISH', 1, 1, 7, 3, N'abcde'); GO -- THIS EVENT WILL BE FILTERED BECAUSE MESSAGE = 'FILTER' RAISERROR (N'FILTER', 2, 1, 7, 3, N'abcde'); GO -- THIS ERROR WILL BE PUBLISHED RAISERROR (N'PUBLISH', 2, 1, 7, 3, N'abcde'); GO -- STOPS LISTENING FOR THE EVENT ALTER EVENT SESSION test_session ON SERVER STATE = STOP GO -- REMOVES THE EVENT SESSION FROM THE SERVER DROP EVENT SESSION test_session ON SERVER GO -- REMOVES THE EVENT SESSION FROM THE SERVER select CAST(event_data as XML) as event_data from sys.fn_xe_file_target_read_file ('c:\temp\data2*.xel','c:\temp\data2*.xem', null, null) This last statement will output one event with the following data: <event name="error_reported" package="sqlserver" id="100" version="1" timestamp="2010-03-05T23:15:05.481Z"> <data name="error"> <value>50000</value> <text /> </data> <data name="severity"> <value>2</value> <text /> </data> <data name="state"> <value>1</value> <text /> </data> <data name="user_defined"> <value>true</value> <text /> </data> <data name="message"> <value>PUBLISH</value> <text /> </data> <action name="sql_text" package="sqlserver"> <value>-- THIS ERROR WILL BE PUBLISHED RAISERROR (N'PUBLISH', 2, 1, 7, 3, N'abcde'); </value> <text /> </action> </event> If you see more events, check if you have deleted previous event files. If so, please run -- Deletes previous event files EXEC SP_CONFIGURE GO EXEC SP_CONFIGURE 'xp_cmdshell', 1 GO RECONFIGURE GO XP_CMDSHELL 'del c:\temp\data*.xe*' GO or delete them manually. More Info on Events: Extended Event Events More Info on Targets: Extended Event Targets More Info on Sessions: Extended Event Sessions More Info on Actions: Extended Event Actions More Info on Predicates: Extended Event Predicates Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

Search Results

Search found 6460 results on 259 pages for 'spam filter'.

Page 119/259 | < Previous Page | 115 116 117 118 119 120 121 122 123 124 125 126 | Next Page >

- by extended_events

- by Herve Roggero

- by darkAsPitch

- by John Magnolia

- by jamiet

- by MikeD

- by Ken Cox [MVP]

- by jtimperley

- by thatjeffsmith

- by El Barto

- by Shaun

- by Deepak Siddappa

- by pgoutin

- by Arunkumar Ramamoorthy

- by AtulThakor

- by M.A.X

- by Alois Kraus

- by clime

- by clime

- by user1820007

- by Jim Thomas

< Previous Page | 115 116 117 118 119 120 121 122 123 124 125 126 | Next Page >