in the previous blogs, we talked about storing performance counters
after that, we talked about persisting usage stats
today we are going to store operational stats
Persisting sys.dm_db_index_operational_stats
Now, the previous DMV, as we said in the link above, counts the usage of the index on a physical operator basis, on the other hand, this DMV measures on a row basis.
now we are to do what did in the DMV above
first, we create two tables
one that contains the copies = snapshots
after that,t we are going to collect it over time
after we collect it we choose two periods we want
now these two will be subtracted from each other to see the difference between both
and see the increase in activity where
now if we had a baseline that would be good
but if we did not
then we collect for a week
and see the period we suspect
then delta that from the baseline
and see what index is mostly used
now for the definition of the tables, we can reference the docs
let’s create our tables:
create table dbo.index_operational_stats_copies
(
id int identity(1,1),
recording_date datetime2(0),
database_id smallint not null,
object_id int not null,
index_id int not null,
partition_number int not null,
hobt_id bigint not null,
leaf_insert_count bigint not null,
leaf_delete_count bigint not null,
leaf_update_count bigint not null,
leaf_ghost_count bigint not null,
nonleaf_insert_count bigint not null,
nonleaf_delete_count bigint not null,
nonleaf_update_count bigint not null,
leaf_allocation_count bigint not null,
nonleaf_allocation_count bigint not null,
leaf_page_merge_count bigint not null,
nonleaf_page_merge_count bigint not null,
range_scan_count bigint not null,
singleton_lookup_count bigint not null,
forwarded_fetch_count bigint not null,
lob_fetch_in_pages bigint not null,
lob_fetch_in_bytes bigint not null,
lob_orphan_create_count bigint not null,
lob_orphan_insert_count bigint not null,
row_overflow_fetch_in_pages bigint not null,
row_overflow_fetch_in_bytes bigint not null,
column_value_push_off_row_count bigint not null,
column_value_pull_in_row_count bigint not null,
row_lock_count bigint not null,
row_lock_wait_count bigint not null,
row_lock_wait_in_ms bigint not null,
page_lock_count bigint not null,
page_lock_wait_count bigint not null,
page_lock_wait_in_ms bigint not null,
index_lock_promotion_attempt_count bigint not null,
index_lock_promotion_count bigint not null,
page_latch_wait_count bigint not null,
page_latch_wait_in_ms bigint not null,
page_io_latch_wait_count bigint not null,
page_io_latch_wait_in_ms bigint not null,
tree_page_latch_wait_count bigint not null,
tree_page_latch_wait_in_ms bigint not null,
tree_page_io_latch_wait_count bigint not null,
tree_page_io_latch_wait_in_ms bigint not null,
page_compression_attempt_count bigint not null,
page_copression_success_count bigint not null,
version_generated_inrow bigint not null,
version_generated_offrow bigint not null,
ghost_version_inrow bigint not null,
ghost_version_offrow bigint not null,
insert_over_ghost_version_inrow bigint not null,
insert_over_ghost_version_offrow bigint not null,
constraint pk_index_operational_stats_copies_id
primary key clustered (id),
constraint uq_index_operational_stats_copies
unique(recording_date,database_id,object_id,index_id,partition_number)
);
create table dbo.index_operational_stats_everything
(
id int identity(1,1),
recording_date datetime2(0),
database_id smallint not null,
object_id int not null,
index_id int not null,
partition_number int not null,
hobt_id bigint not null,
leaf_insert_count bigint not null,
leaf_delete_count bigint not null,
leaf_update_count bigint not null,
leaf_ghost_count bigint not null,
nonleaf_insert_count bigint not null,
nonleaf_delete_count bigint not null,
nonleaf_update_count bigint not null,
leaf_allocation_count bigint not null,
nonleaf_allocation_count bigint not null,
leaf_page_merge_count bigint not null,
nonleaf_page_merge_count bigint not null,
range_scan_count bigint not null,
singleton_lookup_count bigint not null,
forwarded_fetch_count bigint not null,
lob_fetch_in_pages bigint not null,
lob_fetch_in_bytes bigint not null,
lob_orphan_create_count bigint not null,
lob_orphan_insert_count bigint not null,
row_overflow_fetch_in_pages bigint not null,
row_overflow_fetch_in_bytes bigint not null,
column_value_push_off_row_count bigint not null,
column_value_pull_in_row_count bigint not null,
row_lock_count bigint not null,
row_lock_wait_count bigint not null,
row_lock_wait_in_ms bigint not null,
page_lock_count bigint not null,
page_lock_wait_count bigint not null,
page_lock_wait_in_ms bigint not null,
index_lock_promotion_attempt_count bigint not null,
index_lock_promotion_count bigint not null,
page_latch_wait_count bigint not null,
page_latch_wait_in_ms bigint not null,
page_io_latch_wait_count bigint not null,
page_io_latch_wait_in_ms bigint not null,
tree_page_latch_wait_count bigint not null,
tree_page_latch_wait_in_ms bigint not null,
tree_page_io_latch_wait_count bigint not null,
tree_page_io_latch_wait_in_ms bigint not null,
page_compression_attempt_count bigint not null,
page_copression_success_count bigint not null,
version_generated_inrow bigint not null,
version_generated_offrow bigint not null,
ghost_version_inrow bigint not null,
ghost_version_offrow bigint not null,
insert_over_ghost_version_inrow bigint not null,
insert_over_ghost_version_offrow bigint not null,
constraint pk_index_operational_stats_everything_id
primary key clustered (id),
constraint uq_index_operational_stats_everything
unique(recording_date,database_id,object_id,index_id,partition_number)
);
so we created a clustered ever ever-increasing and one unique based on the values supplied by the parameters
we based that on the docs
nothing new, nothing much here
now we are going to insert before and after testing into copies so we can keep the values
As we said before, this should be done using an agent job if we want the data to be recorded more frequently
GO
INSERT INTO dbo.index_operational_stats_copies
SELECT
GETDATE(),
database_id,
object_id ,
index_id ,
partition_number ,
hobt_id,
leaf_insert_count,
leaf_delete_count,
leaf_update_count,
leaf_ghost_count,
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
leaf_allocation_count,
nonleaf_allocation_count,
leaf_page_merge_count,
nonleaf_page_merge_count,
range_scan_count,
singleton_lookup_count,
forwarded_fetch_count,
lob_fetch_in_pages,
lob_fetch_in_bytes,
lob_orphan_create_count,
lob_orphan_insert_count,
row_overflow_fetch_in_pages,
row_overflow_fetch_in_bytes,
column_value_push_off_row_count,
column_value_pull_in_row_count,
row_lock_count,
row_lock_wait_count,
row_lock_wait_in_ms,
page_lock_count,
page_lock_wait_count,
page_lock_wait_in_ms,
index_lock_promotion_attempt_count,
index_lock_promotion_count,
page_latch_wait_count,
page_latch_wait_in_ms,
page_io_latch_wait_count,
page_io_latch_wait_in_ms,
tree_page_latch_wait_count,
tree_page_latch_wait_in_ms,
tree_page_io_latch_wait_count,
tree_page_io_latch_wait_in_ms,
page_compression_attempt_count,
page_compression_success_count,
version_generated_inrow,
version_generated_offrow,
ghost_version_inrow,
ghost_version_offrow,
insert_over_ghost_version_inrow,
insert_over_ghost_version_offrow
from sys.dm_db_index_operational_stats(null,null,null,null)
where database_id>4
now here we exclude system databases like master
everything else is included
now if we select* from copies like:
select*
from dbo.index_operational_stats_copies
you can see it contains the entire DMF plus the recording_date plus an id
now if we do some activity on some other database like:
use test_database
go
create table dbo.heapVSclustered
(id int not null,
col1 char(200) null
);
go
with N1(C) AS(select 0 union all select 0)
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2)
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2)
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2)
,N5(C) AS (SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2)
,ids(id) as (select row_number() over(order by(select null)) from n5)
INSERT INTO dbo.heapVSclustered(id,col1)
select id,'sssss' from ids
go 10
select*
from dbo.heapVsclustered
where id >100
and id < 5000
go 10
create clustered index clus on dbo.heapVSclustered(id)
go 10
update dbo.heapvsclustered
set col1 = replicate('s',200)
where id = 1
go 10
select*
from dbo.heapVsclustered
where id >100
and id < 5000
go 10
Now if we insert again like:
GO
INSERT INTO dbo.index_operational_stats_copies
SELECT
GETDATE(),
database_id,
object_id ,
index_id ,
partition_number ,
hobt_id,
leaf_insert_count,
leaf_delete_count,
leaf_update_count,
leaf_ghost_count,
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
leaf_allocation_count,
nonleaf_allocation_count,
leaf_page_merge_count,
nonleaf_page_merge_count,
range_scan_count,
singleton_lookup_count,
forwarded_fetch_count,
lob_fetch_in_pages,
lob_fetch_in_bytes,
lob_orphan_create_count,
lob_orphan_insert_count,
row_overflow_fetch_in_pages,
row_overflow_fetch_in_bytes,
column_value_push_off_row_count,
column_value_pull_in_row_count,
row_lock_count,
row_lock_wait_count,
row_lock_wait_in_ms,
page_lock_count,
page_lock_wait_count,
page_lock_wait_in_ms,
index_lock_promotion_attempt_count,
index_lock_promotion_count,
page_latch_wait_count,
page_latch_wait_in_ms,
page_io_latch_wait_count,
page_io_latch_wait_in_ms,
tree_page_latch_wait_count,
tree_page_latch_wait_in_ms,
tree_page_io_latch_wait_count,
tree_page_io_latch_wait_in_ms,
page_compression_attempt_count,
page_copression_success_count,
version_generated_inrow,
version_generated_offrow,
ghost_version_inrow,
ghost_version_offrow,
insert_over_ghost_version_inrow,
insert_over_ghost_version_offrow
from sys.dm_db_index_operational_stats(null,null,null,null)
where database_id>4
And select again like:
select*
from dbo.index_operational_stats_copies
We would get:
here as you can see it started recording on the new date, since we have two recording times, now we need to insert those into our dbo.index_operational_stats_everything table
now as we did before, here we are going to calculate the deltas between our two snapshots
exclude nulls using coalesce
exclude 0 and negatives through ‘>’ or ‘<’ or ‘and not’
get the values we want
we are going to use common table expressions
and outer apply to some subqueries
by the way there is a typo, in column page_compression_succes_count it is ‘coPressin’ and this is how we kept it here
use index_dmo
go
with index_operational_deltas
as
(
select
dense_rank() over (order by recording_date desc) as id,
recording_date,
database_id,
object_id,
index_id,
partition_number,
hobt_id,
leaf_insert_count,
leaf_delete_count,
leaf_update_count,
leaf_ghost_count,
nonleaf_insert_count,
nonleaf_delete_count,
nonleaf_update_count,
leaf_allocation_count,
nonleaf_allocation_count,
leaf_page_merge_count,
nonleaf_page_merge_count,
range_scan_count,
singleton_lookup_count,
forwarded_fetch_count,
lob_fetch_in_pages,
lob_fetch_in_bytes,
lob_orphan_create_count,
lob_orphan_insert_count,
row_overflow_fetch_in_pages,
row_overflow_fetch_in_bytes,
column_value_push_off_row_count,
column_value_pull_in_row_count,
row_lock_count,
row_lock_wait_count,
row_lock_wait_in_ms,
page_lock_count,
page_lock_wait_count,
page_lock_wait_in_ms,
index_lock_promotion_attempt_count,
index_lock_promotion_count,
page_latch_wait_count,
page_latch_wait_in_ms,
page_io_latch_wait_count,
page_io_latch_wait_in_ms,
tree_page_latch_wait_count,
tree_page_latch_wait_in_ms,
tree_page_io_latch_wait_count,
tree_page_io_latch_wait_in_ms,
page_compression_attempt_count,
page_copression_success_count,
version_generated_inrow,
version_generated_offrow,
ghost_version_inrow,
ghost_version_offrow,
insert_over_ghost_version_inrow,
insert_over_ghost_version_offrow
from dbo.index_operational_stats_copies
)
insert into dbo.index_operational_stats_everything
select
d1.recording_date,
d1.database_id,
d1.object_id,
d1.index_id,
d1.partition_number,
d1.hobt_id,
d1.leaf_insert_count - coalesce(d2.leaf_insert_count, 0) ,
d1.leaf_delete_count - coalesce(d2.leaf_delete_count, 0) ,
d1.leaf_update_count - coalesce(d2.leaf_update_count, 0) ,
d1.leaf_ghost_count - coalesce(d2.leaf_ghost_count, 0) ,
d1.nonleaf_insert_count - coalesce(d2.nonleaf_insert_count, 0) ,
d1.nonleaf_delete_count - coalesce(d2.nonleaf_delete_count, 0) ,
d1.nonleaf_update_count - coalesce(d2.nonleaf_update_count, 0) ,
d1.leaf_allocation_count - coalesce(d2.leaf_allocation_count, 0) ,
d1.nonleaf_allocation_count - coalesce(d2.nonleaf_allocation_count, 0) ,
d1.leaf_page_merge_count - coalesce(d2.leaf_page_merge_count, 0) ,
d1.nonleaf_page_merge_count - coalesce(d2.nonleaf_page_merge_count, 0) ,
d1.range_scan_count - coalesce(d2.range_scan_count, 0) ,
d1.singleton_lookup_count - coalesce(d2.singleton_lookup_count, 0) ,
d1.forwarded_fetch_count - coalesce(d2.forwarded_fetch_count, 0) ,
d1.lob_fetch_in_pages - coalesce(d2.lob_fetch_in_pages, 0) ,
d1.lob_fetch_in_bytes - coalesce(d2.lob_fetch_in_bytes, 0) ,
d1.lob_orphan_create_count - coalesce(d2.lob_orphan_create_count, 0) ,
d1.lob_orphan_insert_count - coalesce(d2.lob_orphan_insert_count, 0) ,
d1.row_overflow_fetch_in_pages - coalesce(d2.row_overflow_fetch_in_pages, 0) ,
d1.row_overflow_fetch_in_bytes - coalesce(d2.row_overflow_fetch_in_bytes, 0) ,
d1.column_value_push_off_row_count - coalesce(d2.column_value_push_off_row_count, 0) ,
d1.column_value_pull_in_row_count - coalesce(d2.column_value_pull_in_row_count, 0) ,
d1.row_lock_count - coalesce(d2.row_lock_count, 0) ,
d1.row_lock_wait_count - coalesce(d2.row_lock_wait_count, 0) ,
d1.row_lock_wait_in_ms - coalesce(d2.row_lock_wait_in_ms, 0) ,
d1.page_lock_count - coalesce(d2.page_lock_count, 0) ,
d1.page_lock_wait_count - coalesce(d2.page_lock_wait_count, 0) ,
d1.page_lock_wait_in_ms - coalesce(d2.page_lock_wait_in_ms, 0) ,
d1.index_lock_promotion_attempt_count - coalesce(d2.index_lock_promotion_attempt_count, 0) ,
d1.index_lock_promotion_count - coalesce(d2.index_lock_promotion_count, 0) ,
d1.page_latch_wait_count - coalesce(d2.page_latch_wait_count, 0) ,
d1.page_latch_wait_in_ms - coalesce(d2.page_latch_wait_in_ms, 0) ,
d1.page_io_latch_wait_count - coalesce(d2.page_io_latch_wait_count, 0) ,
d1.page_io_latch_wait_in_ms - coalesce(d2.page_io_latch_wait_in_ms, 0) ,
d1.tree_page_latch_wait_count - coalesce(d2.tree_page_latch_wait_count, 0) ,
d1.tree_page_latch_wait_in_ms - coalesce(d2.tree_page_latch_wait_in_ms, 0) ,
d1.tree_page_io_latch_wait_count - coalesce(d2.tree_page_io_latch_wait_count, 0) ,
d1.tree_page_io_latch_wait_in_ms - coalesce(d2.tree_page_io_latch_wait_in_ms, 0) ,
d1.page_compression_attempt_count - coalesce(d2.page_compression_attempt_count, 0) ,
d1.page_copression_success_count - coalesce(d2.page_copression_success_count, 0) ,
d1.version_generated_inrow - coalesce(d2.version_generated_inrow, 0) ,
d1.version_generated_offrow - coalesce(d2.version_generated_offrow, 0) ,
d1.ghost_version_inrow - coalesce(d2.ghost_version_inrow, 0) ,
d1.ghost_version_offrow - coalesce(d2.ghost_version_offrow, 0) ,
d1.insert_over_ghost_version_inrow - coalesce(d2.insert_over_ghost_version_inrow, 0) ,
d1.insert_over_ghost_version_offrow - coalesce(d2.insert_over_ghost_version_offrow, 0)
from
index_operational_deltas d1
left outer join index_operational_deltas d2
on d1.database_id = d2.database_id
and d1.object_id = d2.object_id
and d1.index_id = d2.index_id
and d1.partition_number = d2.partition_number
and d2.id = 2
where d1.id = 1
and (
d1.leaf_insert_count - coalesce(d2.leaf_insert_count, 0) > 0
or d1.leaf_delete_count - coalesce(d2.leaf_delete_count, 0) > 0
or d1.leaf_update_count - coalesce(d2.leaf_update_count, 0) > 0
or d1.leaf_ghost_count - coalesce(d2.leaf_ghost_count, 0) > 0
or d1.nonleaf_insert_count - coalesce(d2.nonleaf_insert_count, 0) > 0
or d1.nonleaf_delete_count - coalesce(d2.nonleaf_delete_count, 0) > 0
or d1.nonleaf_update_count - coalesce(d2.nonleaf_update_count, 0) > 0
or d1.leaf_allocation_count - coalesce(d2.leaf_allocation_count, 0) > 0
or d1.nonleaf_allocation_count - coalesce(d2.nonleaf_allocation_count, 0) > 0
or d1.leaf_page_merge_count - coalesce(d2.leaf_page_merge_count, 0) > 0
or d1.nonleaf_page_merge_count - coalesce(d2.nonleaf_page_merge_count, 0) > 0
or d1.range_scan_count - coalesce(d2.range_scan_count, 0) > 0
or d1.singleton_lookup_count - coalesce(d2.singleton_lookup_count, 0) > 0
or d1.forwarded_fetch_count - coalesce(d2.forwarded_fetch_count, 0) > 0
or d1.lob_fetch_in_pages - coalesce(d2.lob_fetch_in_pages, 0) > 0
or d1.lob_fetch_in_bytes - coalesce(d2.lob_fetch_in_bytes, 0) > 0
or d1.lob_orphan_create_count - coalesce(d2.lob_orphan_create_count, 0) > 0
or d1.lob_orphan_insert_count - coalesce(d2.lob_orphan_insert_count, 0) > 0
or d1.row_overflow_fetch_in_pages - coalesce(d2.row_overflow_fetch_in_pages, 0) > 0
or d1.row_overflow_fetch_in_bytes - coalesce(d2.row_overflow_fetch_in_bytes, 0) > 0
or d1.column_value_push_off_row_count - coalesce(d2.column_value_push_off_row_count, 0) > 0
or d1.column_value_pull_in_row_count - coalesce(d2.column_value_pull_in_row_count, 0) > 0
or d1.row_lock_count - coalesce(d2.row_lock_count, 0) > 0
or d1.row_lock_wait_count - coalesce(d2.row_lock_wait_count, 0) > 0
or d1.row_lock_wait_in_ms - coalesce(d2.row_lock_wait_in_ms, 0) > 0
or d1.page_lock_count - coalesce(d2.page_lock_count, 0) > 0
or d1.page_lock_wait_count - coalesce(d2.page_lock_wait_count, 0) > 0
or d1.page_lock_wait_in_ms - coalesce(d2.page_lock_wait_in_ms, 0) > 0
or d1.index_lock_promotion_attempt_count - coalesce(d2.index_lock_promotion_attempt_count, 0) > 0
or d1.index_lock_promotion_count - coalesce(d2.index_lock_promotion_count, 0) > 0
or d1.page_latch_wait_count - coalesce(d2.page_latch_wait_count, 0) > 0
or d1.page_latch_wait_in_ms - coalesce(d2.page_latch_wait_in_ms, 0) > 0
or d1.page_io_latch_wait_count - coalesce(d2.page_io_latch_wait_count, 0) > 0
or d1.page_io_latch_wait_in_ms - coalesce(d2.page_io_latch_wait_in_ms, 0) > 0
or d1.tree_page_latch_wait_count - coalesce(d2.tree_page_latch_wait_count, 0) > 0
or d1.tree_page_latch_wait_in_ms - coalesce(d2.tree_page_latch_wait_in_ms, 0) > 0
or d1.tree_page_io_latch_wait_count - coalesce(d2.tree_page_io_latch_wait_count, 0) > 0
or d1.tree_page_io_latch_wait_in_ms - coalesce(d2.tree_page_io_latch_wait_in_ms, 0) > 0
or d1.page_compression_attempt_count - coalesce(d2.page_compression_attempt_count, 0) > 0
or d1.page_copression_success_count - coalesce(d2.page_copression_success_count, 0) > 0
or d1.version_generated_inrow - coalesce(d2.version_generated_inrow, 0) > 0
or d1.version_generated_offrow - coalesce(d2.version_generated_offrow, 0) > 0
or d1.ghost_version_inrow - coalesce(d2.ghost_version_inrow, 0) > 0
or d1.ghost_version_offrow - coalesce(d2.ghost_version_offrow, 0) > 0
or d1.insert_over_ghost_version_inrow - coalesce(d2.insert_over_ghost_version_inrow, 0) > 0
or d1.insert_over_ghost_version_offrow - coalesce(d2.insert_over_ghost_version_offrow, 0) > 0
);
And select* from everything:
select*
from dbo.index_operational_stats_everything
so as you can see here we see our extra inserts at the leaf and non-leaf and we only see the extras
and with that, we are done here see you in the next blog.