SQL Server / Monitoring / SSMS Activity Monitor and Page Splits per Second on TempDB

SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
AND IOS.OBJECT_ID = I.OBJECT_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'
ORDER BY IOS.LEAF_ALLOCATION_COUNT DESC

CREATE EVENT SESSION [TempTableCreation] ON SERVER
ADD EVENT sqlserver.object_created
(
ACTION
(
sqlserver.session_nt_username,
sqlserver.server_principal_name,
sqlserver.session_id,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.sql_text
)
WHERE
(
sqlserver.like_i_sql_unicode_string([object_name], N'#%')
AND ddl_phase = 1 -- just capture COMMIT, not BEGIN
)
)
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = 'D:\Temp\XE\TempTableCreation.xel',
MAX_FILE_SIZE = 32768,
MAX_ROLLOVER_FILES = 10
)
WITH
(
EVENT_RETENTION_MODE = NO_EVENT_LOSS
);
GO
ALTER EVENT SESSION [TempTableCreation] ON SERVER STATE = START;

DECLARE @delta INT = DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME());

;WITH xe AS
(
SELECT
[obj_name] = xe.d.value(N'(event/data[@name="object_name"]/value)[1]',N'sysname'),
[object_id] = xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
[timestamp] = DATEADD(MINUTE, @delta, xe.d.value(N'(event/@timestamp)[1]',N'datetime2')),
SPID = xe.d.value(N'(event/action[@name="session_id"]/value)[1]',N'int'),
NTUserName = xe.d.value(N'(event/action[@name="session_nt_username"]/value)[1]',N'sysname'),
SQLLogin = xe.d.value(N'(event/action[@name="server_principal_name"]/value)[1]',N'sysname'),
HostName = xe.d.value(N'(event/action[@name="client_hostname"]/value)[1]',N'sysname'),
AppName = xe.d.value(N'(event/action[@name="client_app_name"]/value)[1]',N'nvarchar(max)'),
SQLBatch = xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
FROM
sys.fn_xe_file_target_read_file(N'D:\Temp\XE\TempTableCreation*.xel',NULL,NULL,NULL) AS ft
CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT
DefinedName = xe.obj_name,
GeneratedName = o.name,
o.[object_id],
xe.[timestamp],
o.create_date,
xe.SPID,
xe.NTUserName,
xe.SQLLogin,
xe.HostName,
ApplicationName = xe.AppName,
TextData = xe.SQLBatch,
row_count = x.rc,
reserved_page_count = x.rpc
FROM xe
INNER JOIN tempdb.sys.objects AS o
ON o.[object_id] = xe.[object_id]
AND o.create_date >= DATEADD(SECOND, -2, xe.[timestamp])
AND o.create_date <= DATEADD(SECOND, 2, xe.[timestamp])
INNER JOIN
(
SELECT
[object_id],
rc = SUM(CASE WHEN index_id IN (0,1) THEN row_count END),
rpc = SUM(reserved_page_count)
FROM tempdb.sys.dm_db_partition_stats
GROUP BY [object_id]
) AS x
ON o.[object_id] = x.[object_id];