Misleading number of rows recorded

General
1

The value of row_count in sqlwatch_logger_disk_utilisation_table sums the number of rows across all indexes that belongs to the table.

Example:
I have a table with 100 rows and with 3 indexes. The row_count row_count in sqlwatch_logger_disk_utilisation_table is then 300.

Is this intended behavior or an omission?

I understand that the pages metrics there are summarized like this as well. It makes me sense, but for number of rows the same is slightly misleading…

What I did to fix this is that I updated the row 61 in [dbo].[usp_sqlwatch_logger_disk_utilisation_table] procedure as:

row_count = convert(real,sum(case when i.index_id IN (0,1) then p.rows else 0 end)),

Then it behaves more reasonably for me and collects number of rows just from clustered index or table (if it is heap)

2

Hi, thanks for this. Without looking at the code I’d think it’s an omission rather than intended behaviour.

3

Just small update, to get the correct number of rows, the fix in the [dbo].[usp_sqlwatch_logger_disk_utilisation_table] is as follows:

…
insert into @t
select
schema_name = s.name,
table_name = t.name,
database_name = sdb.name,
database_create_date = sdb.create_date,
row_count = convert(real,sum(case when i.index_id IN (0,1) and a.type = 1 then p.rows else 0 end)),
total_pages = convert(real,sum(a.total_pages)),
used_pages = convert(real,sum(a.used_pages)),
/* only take table compression into account and not index compression.
we have index analysis elsewhere */
[data_compression] = max(case when i.index_id = 0 then p.[data_compression] else 0 end)
from [?].sys.tables t
inner join [?].sys.indexes i on t.object_id = i.object_id
inner join [?].sys.partitions p on i.object_id = p.object_id AND i.index_id = p.index_id
inner join [?].sys.allocation_units a on p.partition_id = a.container_id
inner join [?].sys.schemas s on t.schema_id = s.schema_id
inner join sys.databases sdb on sdb.name = ‘’?‘’

group by s.name, t.name, sdb.name, sdb.create_date;

…