M3PiEUMC

1select o.name,i.name,i.type_desc,ios.leaf_insert_count,leaf_delete_count,leaf_ghost_count,leaf_update_count,leaf_allocation_count , range_scan_count,
2singleton_lookup_count,forwarded_fetch_count   
3from sys.dm_db_index_operational_stats(my_dbid,my_objectid,null,null) ios  
4inner join sys.objects o on o.object_id=ios.object_id  
5inner join sys.indexes i on i.object_id=o.object_id and i.index_id=ios.index_id
6	
7CREATE UNIQUE CLUSTERED INDEX [IX_clustered] ON [dbo].[Orders]
8(
9    [ORDERNR] ASC,
10    [TRANSACT] ASC,
11    [TRANSID] ASC
12)
13	
14CREATE TABLE [dbo].[ORDERS](
15    [TRANSID] [nvarchar](10) NOT NULL,
16    [ITEM] [nvarchar](10) NULL,
17    [ORDERNR] [nvarchar](10) NULL,
18    [SERIALNR] [nvarchar](10) NULL,
19    [TRANSACT] [nvarchar](10) NULL,
20    [DATE] [datetime] NULL,
21    [TIME] [nvarchar](5) NULL,
22    [STATE] [nvarchar](10) NULL,
23    [SUPPLIER] [nvarchar](10) NULL,
24    CONSTRAINT [idx_TRANSID] PRIMARY KEY NONCLUSTERED 
25    (
26        [TRANSID] ASC
27    )
28)
29	
30insert bulk [dbo].[ORDERS] 
31(
32    [TRANSID] nvarchar(10),
33    [ITEM] nvarchar(10),
34    [ORDERNR] nvarchar(10),
35    [SERIALNR] nvarchar(10),
36    [TRANSACT] nvarchar(10),
37    [DATE] datetime,
38    [TIME] nvarchar(5),
39    [STATE] nvarchar(10),
40    [SUPPLIER] nvarchar(10)
41)
42	
43IF EXISTS (SELECT 1 
44            FROM sys.server_event_sessions 
45            WHERE name = 'SQLskills_TrackPageSplits')
46        DROP EVENT SESSION [SQLskills_TrackPageSplits] 
47        ON SERVER
48
49-- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server
50CREATE EVENT SESSION [SQLskills_TrackPageSplits]
51ON    SERVER
52ADD EVENT sqlserver.transaction_log(
53    WHERE operation = 11  -- LOP_DELETE_SPLIT 
54      AND database_id = 6 -- CHANGE THIS BASED ON TOP SPLITTING DATABASE!
55)
56ADD TARGET package0.histogram(
57    SET filtering_event_name = 'sqlserver.transaction_log',
58        source_type = 0, -- Event Column
59        source = 'alloc_unit_id');
60GO
61
62-- Start the Event Session Again
63ALTER EVENT SESSION [SQLskills_TrackPageSplits]
64ON SERVER
65STATE=START;
66GO
67	
68SELECT
69    o.name AS table_name,
70    i.name AS index_name,
71    tab.split_count,
72    i.fill_factor
73FROM (    SELECT 
74            n.value('(value)[1]', 'bigint') AS alloc_unit_id,
75            n.value('(@count)[1]', 'bigint') AS split_count
76        FROM
77        (SELECT CAST(target_data as XML) target_data
78         FROM sys.dm_xe_sessions AS s 
79         JOIN sys.dm_xe_session_targets t
80             ON s.address = t.event_session_address
81         WHERE s.name = 'SQLskills_TrackPageSplits'
82          AND t.target_name = 'histogram' ) as tab
83        CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
84) AS tab
85JOIN sys.allocation_units AS au
86    ON tab.alloc_unit_id = au.allocation_unit_id
87JOIN sys.partitions AS p
88    ON au.container_id = p.partition_id
89JOIN sys.indexes AS i
90    ON p.object_id = i.object_id
91        AND p.index_id = i.index_id
92JOIN sys.objects AS o
93    ON p.object_id = o.object_id
94WHERE o.is_ms_shipped = 0;
95	
96USE TestDB
97GO
98
99DROP TABLE IF EXISTS leafmealone
100GO
101
102CREATE TABLE dbo.leafmealone (
103ID INT,
104bah UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
105humbug CHAR(1000)
106)
107GO
108
109DROP TABLE IF EXISTS #ouch
110SELECT TOP 1 cntr_value INTO #ouch
111FROM sys.dm_os_performance_counters
112WHERE counter_name = 'Page Splits/sec'
113GO
114
115CREATE OR ALTER PROC #dothesplits AS
116BEGIN
117    DECLARE @t bigint = (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page Splits/sec')
118    SELECT @t - cntr_value AS page_splits FROM #ouch
119    UPDATE #ouch SET cntr_value = @t
120END
121GO
122
123CREATE OR ALTER PROC #getinfo AS
124BEGIN 
125    SELECT o.name,i.type_desc,ios.leaf_insert_count,leaf_delete_count,leaf_ghost_count,leaf_update_count,leaf_allocation_count, ios.nonleaf_allocation_count
126    from sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('dbo.leafmealone'),null,null) ios  
127    inner join sys.objects o on o.object_id=ios.object_id  
128    inner join sys.indexes i on i.object_id=o.object_id and i.index_id=ios.index_id
129    EXEC dbo.#dothesplits
130END
131GO
132	
133INSERT dbo.leafmealone
134SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)), NEWID(), 'grrrr'
135FROM master..spt_values
136GO
137
138#getinfo
139GO
140
141UPDATE dbo.leafmealone
142SET bah = NEWID()
143WHERE ID%5 =0
144GO
145
146#getinfo
147GO
148	
149ALTER INDEX ALL ON dbo.leafmealone REBUILD
150GO
151
152#getinfo
153GO
154
155INSERT dbo.leafmealone
156SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)), NEWID(), 'grrrr'
157FROM master..spt_values
158GO
159
160#getinfo
161GO