--Enable change tracking
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON;
GO
--Column list for the transaction table
SELECT *
FROM sys.all_columns
WHERE object_id = OBJECT_ID('sys.syscommittab');
GO
--Enabling CT with retention
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP=ON, CHANGE_RETENTION=1 hours);
GO
--Enabling CT for a table
ALTER TABLE HumanResources.Employee
ENABLE CHANGE_TRACKING;
GO
--Applying Change Tracking context
DECLARE @context VARBINARY(128) =
CONVERT(VARBINARY(128), SUSER_SNAME());
WITH CHANGE_TRACKING_CONTEXT(@context)
UPDATE AdventureWorks2008.HumanResources.Employee
SET
JobTitle = 'Production Engineer'
WHERE
JobTitle = 'Design Engineer';
GO
--Enabling column tracking
ALTER TABLE HumanResources.Employee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
GO
--Getting the first version with CHANGETABLE
SELECT
c.SYS_CHANGE_VERSION,
c.SYS_CHANGE_CONTEXT,
e.*
FROM AdventureWorks2008.HumanResources.Employee e
CROSS APPLY CHANGETABLE
(
VERSION AdventureWorks2008.HumanResources.Employee,
(BusinessEntityId),
(e.BusinessEntityId)
) c;
GO
--Getting subsequent changes
DECLARE @last_version BIGINT = 8;
SELECT
c.*
FROM CHANGETABLE
(
CHANGES AdventureWorks2008.HumanResources.Employee,
@last_version
) c;
GO
--Find both changed and deleted rows
DECLARE @last_version BIGINT = 8;
SELECT
c.SYS_CHANGE_VERSION,
c.SYS_CHANGE_OPERATION,
c.SYS_CHANGE_CONTEXT,
e.*
FROM CHANGETABLE
(
CHANGES AdventureWorks2008.HumanResources.Employee,
@last_version
) c
LEFT OUTER JOIN AdventureWorks2008.HumanResources.Employee e ON
e.BusinessEntityID = c.BusinessEntityID;
GO
--Grant ALTER TRACE
GRANT ALTER TRACE TO Jane;
GO
--Which traces are running?
SELECT
status,
path,
max_size,
buffer_count,
buffer_size,
event_count,
dropped_event_count
FROM sys.traces
WHERE id = 2;
GO
--Which events and columns are selected?
SELECT *
FROM fn_trace_geteventinfo(2);
GO
--Join to get more information
SELECT
e.name AS Event_Name,
c.name AS Column_Name
FROM fn_trace_geteventinfo(2) ei
JOIN sys.trace_events e ON ei.eventid = e.trace_event_id
JOIN sys.trace_columns c ON ei.columnid = c.trace_column_id;
GO
--Which filters are used?
SELECT
columnid,
logical_operator,
comparison_operator,
value
FROM fn_trace_getfilterinfo(2);
GO
--Pull data from the trace file
SELECT *
INTO sql_server_internals
FROM fn_trace_gettable('c:\sql_server_internals.trc', 1);
GO
--Stop the trace
EXEC sp_trace_setstatus 2, 0;
GO
--Delete the trace
EXEC sp_trace_setstatus 2, 2;
GO
--Investigating XE packages
SELECT *
FROM sys.dm_xe_packages;
GO
--Investigating XE objects
SELECT *
FROM sys.dm_xe_objects;
GO
--Which columns are available for the page split event?
SELECT *
FROM sys.dm_xe_object_columns
WHERE
object_name = 'page_split';
GO
--Which types and maps are available?
SELECT *
FROM sys.dm_xe_objects
WHERE
object_type IN ('TYPE', 'MAP');
GO
--What are the values for the wait types map?
SELECT *
FROM sys.dm_xe_map_values
WHERE
name = 'wait_types';
GO
--Which >= predicate functions exist?
SELECT *
FROM sys.dm_xe_objects
WHERE
object_type = 'pred_compare'
AND name LIKE 'greater_than_equal%';
GO
--Which predicate sources are available?
SELECT *
FROM sys.dm_xe_objects
WHERE
object_type = 'pred_source';
GO
--Which actions are available?
SELECT *
FROM sys.dm_xe_objects
WHERE
object_type = 'action';
GO
--Which targets are available?
SELECT *
FROM sys.dm_xe_objects
WHERE
object_type = 'target';
GO
--Sample statement completed session
CREATE EVENT SESSION [statement_completed]
ON SERVER
ADD EVENT
sqlserver.sp_statement_completed,
ADD EVENT
sqlserver.sql_statement_completed
(
ACTION
(
sqlserver.sql_text
)
WHERE
(
sqlserver.session_id = 53
)
)
ADD TARGET
package0.ring_buffer
(
SET
max_memory=4096
)
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 1 SECONDS,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);
GO
--start the session
ALTER EVENT SESSION [statement_completed]
ON SERVER
STATE=START;
GO
--Digest the session data
SELECT
theNodes.event_data.value('(data/value)[1]', 'bigint') AS source_database_id,
theNodes.event_data.value('(data/value)[2]', 'bigint') AS object_id,
theNodes.event_data.value('(data/value)[3]', 'bigint') AS object_type,
theNodes.event_data.value('(data/value)[4]', 'bigint') AS cpu,
theNodes.event_data.value('(data/value)[5]', 'bigint') AS duration,
theNodes.event_data.value('(data/value)[6]', 'bigint') AS reads,
theNodes.event_data.value('(data/value)[7]', 'bigint') AS writes,
theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
SELECT
CONVERT(XML, st.target_data) AS ring_buffer
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets st ON
s.address = st.event_session_address
WHERE
s.name = 'statement_completed'
) AS theData
CROSS APPLY theData.ring_buffer.nodes('//RingBufferTarget/event') theNodes (event_data);
GO
